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???
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.
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....
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.
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.