When I process a cube with the HOLAP (100% performance gain) storage option the querytime from Excel XP is very fast.
When I process the same cube with the MOLAP (100% performance gain) storage option the same query takes forever.
I'm puzzled. Can someone explain this?
P.S. THe cube has a DistinctCount measure in it. Does this have anything to do with the poor MOLAP performance?
You are correct - that is unusual. How big are both of the cubes ? Have you tried duplicating the same behavior with the foodmart sample ? Since you are suspicious of the distinctcount - have you eliminated that measure to see how it affects performance ?
Excel 2000 gives me the same problem, as well as the cube data browser in the Analysis Manager itself.
I replaced the Distinct Count by a Sum. Performance was OK in both HOLAP and MOLAP, so the Distinct Count causes the pain (but I really need it).
I couldn't reproduce the same thing in the Foodmart cubes (size perhaps?).
Maybe I stumbled on some undocumented limit.
Another thing: In HOLAP mode with 100% performance gain it still computes aggregation data in Oracle. I know this, because when I shut Oracle I get #value errors. I thougt 100% means 'all aggregations in Analysis Services'. Also I'm often exactly 1 above the exact value with the Distinct Counts, because NULL seams to count a distinct value.
I rfead somewhere that Analysis Services transform a NULL into a 0 when one of the other measures in the source table is not NULL.
Is that correct, and if so, is there a patch for that? Otherwise I must define fact-views for each Distinct Count measure with "WHERE ... IS NOT NULL".
Development Server : PIII 500, 512 MB memory
(should eat this 7.5MB cube for breakfast)
Relational database: Oracle 126.96.36.199
Analysis Services : Service Pack 2
Fact table: : 69000 rows
Measures: : 1 Distinct Count
Dimension data : 1 3000 members, 1 level
2 69000 members, divided in 7 levels
3 Price-dimension (hierarchy 1)
4 Price-dimension (hierarchy 2)
Cube in MB 7.5 MB (in both HOLAP and MOLAP mode)