BookmarkSubscribeRSS Feed
metalray
Calcite | Level 5
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
2 REPLIES 2
metalray
Calcite | Level 5
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.
metalray
Calcite | Level 5
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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 759 views
  • 0 likes
  • 1 in conversation