Clients hang on drilldown into moderately large dimension

[abbaer]

I have a simple MS OLAP cube with three dimensions - one small (~10 members) one medium (~200 members & 4 levels) and one large (~60,000 members in just 1 level other than total).

All the dimensions are parent-child and use preaggregated data. The resulting cube works fine except when the user drills into largest dimension the client always hangs, and suck up 100% of the client CPU indefinately, even if the query is constrained by choosing a member of one of the other dimensions. Or, what's stranger, almost all clients hang. I've tried Excel, Crystal Analysis, Intellibrowser, and the data browser in the MS Analysis manager. ProClarity, however, works fine, with little fuss.

Any thoughts about what's causing the drill-down hang?

 

[alfredjp]

yeah... i too am experiencing the same thing... im using PTS in Excel 2000

is there any fine-tuning stuff for this?

i wonder how this would affect an excel file embedded in an HTML frame? ie., the excel file containing the pivot table is hosted on a server where clients can connect to, and view them on their respective web browsers.

 

[MDXer]

abbaer: Have you looked at any AS or SQL performance counters using Perfmon? Is there a way to make the 60K dim into a standard heirarchy rather than using a Parent child? (just for testing purposes). Also profile the SQL server to see what the query being sent to the SQL server is and then execute the same query using Query analyzer. One last thing Is the cube Schema optomized? if the schema isn't optomized then it may be joining out where it doesn't have to.


alfredjp: Odds are the web page would hang until either the report rturined or the web page timed out. As for performance there are a number of things that can be done to fine tune a cubes performance. Partioning, Slicing your partitions, Memory allocation, Thread allotment to name a few. However, most performance issues I have encountered start with the cube design, and aggregations




"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck