I've built my first cube using SLQ 2000 AS, SP3. The cube is accessed via a pivot table in XL2002, and also by the activeX web control on an intranet page. Response is OK as long as I am using no more than four dimensions. If I add more, the pivot table becomes empty in Excel, and hangs the browser when using the web control. I've set Execution Location=3 and Default Isolation Mode=1 in the .oqy file, but it makes no difference.

If I query the fact table directly and populate a worksheet with the results, I can use that as the source of a pivot table in Excel, and it works perfectly. What should I look for as the cause of the poor performance when connecting directly to OLAP? I'm wondering if it's because I have a snowflake rather than star (unnormalized) schema in the cube.

Greetings

This has probably more to do with either your dimension size/depth or (and
?) your agregation schema since once your cube is processed, the
star/snowflake argument for the data source should not matter any more.

What is the member count of the dimensions you use? Are you crossjoining
them on the rows/columns of your report? Are you at the top or lower levels
of the dimensions? How many rows do you have in the fact table?

Depending on the exact queries you make, you can end up trying to retrieve
thousands of individual cells from your cube; if agregations are not design
properly, this could explain the poor performance you experience. So I would
suggest running the Storage Design Wizard, choosing MOLAP as the storage
mode and then use a 25-50% optimization for a start, depending on the amount
of data you have.

HTH


Olivier MATRAT

WINSIGHT
Microsoft Gold Partner for Business Intelligence
www.winsight.fr


"kaborka" <kaborka (AT) discussions (DOT) microsoft.com> wrote

page. Response is OK as long as I am using no more than four dimensions.
If I add more, the pivot table becomes empty in Excel, and hangs the browser
when using the web control. I've set Execution Location=3 and Default
Isolation Mode=1 in the .oqy file, but it makes no difference.
works perfectly. What should I look for as the cause of the poor
performance when connecting directly to OLAP? I'm wondering if it's because
I have a snowflake rather than star (unnormalized) schema in the cube.