topic Re: SAS OLAP 4.2 MDX Performance (Unix) in SAS Web Report Studio

SAS OLAP 4.2 MDX & Prompt Performance (Unix)

metalray — Tue, 22 Mar 2011 14:56:31 GMT

Hello,

I have written this simple MDX query and it takes more than 1 minute to execute (the timer is still running as
I am writing this and it looks like my EG 4.2 crashed).

SELECT
NON EMPTY { [Measures].[FACTSUM] } ON COLUMNS,
NON EMPTY CrossJoin
(NONEMPTYCROSSJOIN
(NONEMPTYCROSSJOIN
(NONEMPTYCROSSJOIN
(
{ descendants([DIM_KPI].[All DIM_KPI] )},
{ descendants([DIM_PERIOD].[HRH_CALENDERPERIOD].[All HRH_CALENDERPERIOD]) }),
{ [DIM_CUSTOMER].[DISCRIPTOR].AllMembers }),
{ descendants([DIM_SZENARIO].[All DIM_SZENARIO]) }),
{ descendants([DIM_SALES].[All DIM_SALES]) }
) ON ROWS
FROM
[mySmallCube]
WHERE mySmallCube.... NOT IS MISSING AND
mySmallCube.... NOT IS MISSING AND
mySmallCube.... NOT IS MISSING AND
mySmallCube.... NOT IS MISSING AND
mySmallCube.... NOT IS MISSING AND
mySmallCube.... NOT IS MISSING AND
...;

The cube is build from a fact table with 14! facts. So an ms excel spreadsheet is faster than the OLAP cube
in the moment. I wonder why this is? Have I done some big mistake in the code?
The descendants are an average of 4 Levels and are about 100 members whereas the DIM_SALES has about 3000 members in total
but are only two are important because I have an MDX filter on the cube that only gets members
from the DIM_SALES dimension where they equal my user id.

Thanks for any hints,
Bob

Re: SAS OLAP 4.2 MDX Performance (Unix)

metalray — Tue, 22 Mar 2011 15:53:26 GMT

I think what happens indeed (at least if I dont use the MDX filter)
is a cross join of the following number of rows:

Total Dimension Members KPI
10

Total Dimension Members DIM_PERIOD
24000

Total Dimension Members DIM_CUSTOMER
4

Total Dimension Members DIM_SZENARIO
5

Total Dimension Members DIM_SALES
3500

Total Dimension Members Facts
14

When I paste my above query in the OLAP Viewer EG it creates an endless
list of rows/combinations after 30 minutes of waiting. Using it with a filter
it actually turns out to be 14 unique facts. It would be great if
the MDX performanc would not dig through all possible combinations but also through
the ones that actually exist.

Re: SAS OLAP 4.2 MDX Performance (Unix)

metalray — Wed, 23 Mar 2011 14:38:38 GMT

NOTE: Table mylib.myOlapfact created, with 9 rows and 17 columns.
real time 1:36.63 (minutes)

after the creation the counting takes less then one seconds, without the creation of
a table, using a view instead, the access time jumps up to to 1.36.63 minutes.

Conclusion: a table saves performance since otherwise we have to retrieve
for every data request the whole lot of data.

Problem: I have prompts about 10 cascading prompts on this table and
each one of them takes about 1 minute to load, it seems each one of them
retrievs the data anew. - emphasizing that it is only 9 rows and 17 columns and it still takes quite long!

The filtering using the output data viewer in EG 4.2 filters through those
rows very fast, why are the cascading prompts so slow when I test them in InfoMap Studio 4.2?