topic Re: PROC OLAP: Aggregating Calculated members in SAS Web Report Studio

PROC OLAP: Aggregating Calculated members

Pgloria — Wed, 10 Feb 2010 19:36:05 GMT

Hello,
I have one olap cube that is build with custom measures, my problem is that every drill on the web report studio is painfully slow.....
I used the advanced aggregation tunning from the Olap Cube Studio and I've created every possible aggregation to try it out, but the performance stays the same....

Don't the cube aggregations work with custom measures????
Any hints to improve my drill performance???

Thanks for your help

Re: PROC OLAP: Aggregating Calculated members

LinusH — Thu, 11 Feb 2010 08:21:52 GMT

Please leave some more information of the cube.
How "big" is it, number of dimension, granularity etc.
Have you tried to access the cube from other clients than WRS such as EG and Excel (so you can eliminate the possibility of a slow front-end).
You should also try to log your OLAP queries, see on-line doc for help.

/Linus

http://support.sas.com/kb/38/429.html
http://support.sas.com/onlinedoc/913/docMainpage.jsp?_topic=biasag.hlp/a003145925.htm
http://support.sas.com/documentation/cdl/en/biasag/61237/HTML/default/a003145925.htm

Re: PROC OLAP: Aggregating Calculated members

Pgloria — Thu, 11 Feb 2010 10:32:08 GMT

The cube is a star schema, fact table 1.2 million observations.
The cube has 5 dimensions, 3 are used for group breaks and have 1 level each, the other 2 are used to drill thru and have 2 levels each.

The group break dimensions are small, two of them only have 1 key each with this test data, the other one has 4 distinct keys.

The drill thru dimensions are also small, one has 12 keys the other a little bigger has 50 keys.

On this project that I'm working I'm not allowed to change configuration/manage the servers, so I can not reboot them to allow debugging 😕

But apart from that, my guess is that the aggregations don't work with the custom measures.... and I think that because if I keep the cube structure and only use the default measures(sum for example) the cube drills take like a second, oposing to some 30 seconds when I'm using my custom measures....

Thanks in advance for your help

Re: PROC OLAP: Aggregating Calculated members

AngelaHall — Thu, 11 Feb 2010 15:10:19 GMT

Do you have any distinctcount functions applied in your calculated measures?
These were operating extremely slowly for me as well. Had to change tactics to improve performance.

Here is a blog post re: that topic ~
http://sas-bi.blogspot.com/2009/10/distinctcount-vs-nunique-in-olap-92.html

Also, are you expeirencing this slowness only on the Web, or are other tools (ie EG Cube Viewer) also slow?

~ Angela Hall

Re: PROC OLAP: Aggregating Calculated members

Pgloria — Fri, 12 Feb 2010 10:42:41 GMT

Hi again,
@Angela Hall:
I don't use distinctcount in my measures, only iif's and sum's

I got some new insight to the performance problem, I guess it's the number of measures I display.
The report displays 5 measures (all custom), if I use only one, the report drills fast, with two a little slower but aceptable, with 3 and more it gets really slow.
I don't have access to EG or the MS Office addin at the moment, so I can't say for shure that the problem is not in the WRS view, but seeing that it loads fast with one measure my new guess is that in can be a memory bottleneck, maybe from the olap server.

What do you think???

Gona try to get EG untill the end of the day to test this there.

Thanks for your help
Pedro

Re: PROC OLAP: Aggregating Calculated members

AngelaHall — Fri, 12 Feb 2010 18:02:26 GMT

This could be a memory 'spill-over' event where then I/O is utilized to complete the transaction.

In the advanced OLAP server settings in SAS Management Console, you could tweek a couple of the settings:

Performance - Number of Threads to Spawn - should equal the # of CPUs
Query Thread Pool - Minimum Number = 1
- Maximum Number = 2*# available CPUs
Memory size for subquery cache - modify this upwards to increase amt memory used before going to I/O - HOWEVER monitor this closely since it could negatively affect memory available for other types of processing.

~Angela Hall
http://sas-bi.blogspot.com

Re: PROC OLAP: Aggregating Calculated members

F_DeSa — Fri, 30 Jul 2010 13:59:55 GMT

We have a similar problem - adding more calc measure slows performance dramatically. Did you find a solution to this?

Re: PROC OLAP: Aggregating Calculated members

AngelaHall — Fri, 30 Jul 2010 15:14:58 GMT

Is the custom measure defined within the Information Map using a 'New Data Item' expression or via the 'Calculated Member' in OLAP Cube Studio?

I have helped several organizations significantly improve their performance by moving custom measures from the Information Map back to the OLAP Cube.

~ Angela
http://sas-bi.blogspot.com