Re: Odd BeginRange/EndRange behavior

• Previous message: jwalker_at_wp.shawcable.net: "Me biteing my budgies head off"
• Messages sorted by: [ date ] [ thread ]

Date: Fri, 30 Apr 2004 18:01:30 -0700

User has the knowledge of what MDX query she is sending and what's on each
axis, so it's up to the user to define BeginRange and EndRange. The
usability of a rectangle for BeginRange/EndRange is more appropriate from a
user interface point of view.

The ability to cache a response for BeginRange/EndRange is a server
capability. Its not unreasonable to assume that servers will do some caching
for this scenario in the future.

Also, consider the following:
- possible support for Bindings in the future would alleviate the cost of
the axes being serialized each time.
- asking for exactly a range of cells can mean that the server doesn't have
to calculate the values for the remaining 10,000 that aren't in the range.
- the 10,000 cells not in the range don't need to be cached because they
were never calculated in the first place.

Thanks,
Akshai & Hanying

-- 
This posting is provided "AS IS" with no warranties, and confers no rights.
"Neil Thomson" <summer_porch@hotmail.com> wrote in message
news:eTxPL1ZLEHA.2592@tk2msftngp13.phx.gbl...
> OK. So the reality is this only works if you know the shape of your result
> set in advance and can then guarantee defining a useful rectangle.
>
> Unless I'm missing something, this substantially reduces the usability as
> any dynamic expression on either row or column (which changes the numbers
of
> tuples one or more axes) will make it impossible to know how to define a
> usable rectangle. This means executing the query at least once just to
> determine how the row/column boundaries match to the cell ordinals.
>
> While it would be possible to do an execute with Begin/EndRange only to
> retrieve one or two Cells (you always get all the row/column tuples) to
> determine the row/colum to cell ordinal mapping, this becomes impractical
> from a performance perspective as I've learned (from a separate question
> that Akshai answered) that there there is no caching done (you get a query
> execution each time vs. execute, get range, get range, get range) then
it's
> not clear where this feature was intended to help.
>
> Or am I missing something?
>
> The requirement from my perspective is for SQL cursor-like control
providing
> the ability to incrementally fetch results.
>
> I could see satisfying this with one or preferably two additional
features:
>
>  - caching of results and the ability to fetch results independent of
> execution (separate command from Execute - GetCachedResult). This alone
> would make the feature useable.
>
>  - the ability to ask for just rows - in the literal sense - all the cell
> ordinals and members that correspond to complete rows in the result set.
> This removes the need to know the row/column mapping to cell ordinals.
Make
> begin and end range have a "full rows" option. The return a rectangle is
> more elegant, but ....
>
>
>
> "Hanying Feng [MS]" <hfeng@online.microsoft.com> wrote in message
> news:usuaxzXLEHA.3852@TK2MSFTNGP10.phx.gbl...
> > This is by design.
> >
> > The BeginRange's position must be preceding EndRange's position on an
> axis.
> > If your BeginRange is 1 and EndRange is 40, then the BeginRange's
position
> > on axis 0 is 1, and EndRange's position on axis 0 is 0, this is not
going
> to
> > work. Imagine in a two-dimensional cellset, BeginRange and EndRange
define
> a
> > rectangle in this space. All cells returned will fall into this
rectangle.
> >
> > BeginRange and EngRange returns smaller set of cells than you expected
is
> > also for the same reason. In your case, BeginRange=28, EndRange=60, then
> you
> > are getting the first column of cells starting from row 7 to row 15, and
> > there are exactly two cells in this range.
> >
> > Thanks,
> > Hanying
> > -- 
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "summer_porch" <summer_porch@hotmail.com> wrote in message
> > news:e$yxnTbIEHA.2844@tk2msftngp13.phx.gbl...
> > > I've been playing with the XMLA Sample VB application to determine how
> > > BeginRange and EndRange work and what caching it does of results and
> found
> > a
> > > series of odd behaviors.
> > >
> > > I inserted additional properties in the ExecuteQuery Function in the
> > > xlsXmlDataAccess.vb file to add BeginRange and EndRange properties.
> > >
> > >         m_Properties.SetNameValue(cst_strBeginRange, "1")
> > >         m_Properties.SetNameValue(cst_strEndRange, "40")
> > >
> > > The Query, below, produces approx 256 rows and 4 columns. The natural
> > > boundary for each row is a factor of 4.
> > >
> > > What I found is as follows:
> > >    - For EndRange values of 20, 40 or 60 any BeginRange not a multiple
> of
> > 4
> > > fails an error "The specified range rowset is not valid".
> > >
> > >       So in this case a BeginRange of  -1, 0, 4, 8, 12, .. work, but
> > > 1,2,3.,5,6,7,.9,10, 11 all fail with the above error.
> > >
> > >  -  If the EndRange is -1 (all) then a BeginRange works (e.g. 1
to -1).
> > >
> > > - The actual cell ordinals returned are not what I expected.
> > >        > Begin = 28 to End = 60 only returned CellOrdinals 27, 31
> > >        > Begin = -1 to End = 60 returned a CellOrdinals -1 (!), plus
15,
> > 23,
> > > 27, 31
> > >        > Begin = -1 to End -1 returned CellOrdinals in the 0-60 range
of
> > 0,
> > > 3, 5, 6, 10, 14, 16, 22, 24, 26, 28, ..
> > >
> > > This would suggest that the cells returned for other than the complete
> > > CellSet (-1, -1) are incomplete. I can think of no explanation for the
> > > variance in the results.
> > >
> > > Query:
> > >
> > > SELECT
> > >        NON EMPTY CROSSJOIN( DISTINCT( { [Time].[1997].CHILDREN } ),
> > > DISTINCT( { [Measures].[Unit Sales] } ) )  ON COLUMNS,  NON EMPTY
> > > GENERATE( DISTINCT( TOPCOUNT( DISTINCT( { [Store].[Store
> > Name].MEMBERS } ),
> > > 10, ( [Measures].CURRENTMEMBER ) ) ), CROSSJOIN( {
> > [Store].CURRENTMEMBER },
> > > GENERATE( DISTINCT( TOPCOUNT( DISTINCT( {
> [Customers].[Name].MEMBERS } ),
> > > 10, ( [Measures].[Store Sales] ) ) ), CROSSJOIN( {
> > > [Customers].CURRENTMEMBER }, DISTINCT( TOPPERCENT( DISTINCT( {
> > > [Product].[Product Name].MEMBERS } ), 10, ( [Measures].[Store
> > > Cost] ) ) ) ) ) ) )  ON ROWS
> > > FROM [Sales]
> > > CELL PROPERTIES VALUE, FORMATTED_VALUE, FONT_NAME, FORE_COLOR,
> BACK_COLOR,
> > > FONT_SIZE, FONT_FLAGS
> > >
> > >
> >
> >
>
>

• Previous message: jwalker_at_wp.shawcable.net: "Me biteing my budgies head off"
• Messages sorted by: [ date ] [ thread ]