Hi all,
Any ideas of how to implement a scalable Paging
soluion using MDX (subset is not an option since
it first retrieves all data).

"yanec" <yaniv.inbar (AT) retalix (DOT) com> wrote

Can you please elaborate more on the problem that you are trying to solve.
Usually, PTS will do intelligent paging automatically, and its behaviour can
be tuned by using Cache Policy, Cache Ratio etc connection string
properties.

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

Hi Mosha,
We have a web based Olap Viewer that we developed.
The problem is that if a user performs a query that
generates large results, we do not want to display
the entire cellset at the client - we would like
to give the user a paging like mechanisem.

We prefer to solve this issue at the Olap server level
(by using MDX) and not in our middle tier.
So we played with SubSet but then we realized that
subset is not a scalable solution becouse it seems that
first the olap prepares the entire mdx query data and
only then apply's the subset - this prooved to be very
slow while working with medium-large results.

We also noticed that after the first query - most data
is in the Olap cach so future subset querys at the same
dimmension are fast.

This is not really true. Example:

SELECT Head(Dim1.Members, 100) on COLUMNS, Head(Dim2.Members, 100) on ROWS
FROM cube

will not populate all the cells in the intersection of Dim1 and Dim2, but
just those which have coordinates in the first hundrend.
You could take the following approach to the paging problem:

CREATE SET cube.MyColumns AS 'whatever you want your columns to be'
CREATE SET cube.MyRows AS 'whatever you want your columns to be'

SELECT SubSet(MyColumns, ColStart, ColLen) ON COLUMNS, SubSet(MyRows,
RowStart, RowLen) ON ROWS

where ColStart/ColLen/RowStart/RowLen is the moving window. I beleive you
should get reasonable performance with it.
If you still have performance problems - we can take a look at various
connection string properties...

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================