Pivot table very slow

We are using MS SQL Server 2000 with its Analysis Services and Excel 2002.
We have 3 facts tables (less than 100 rows each) ,
8 dimensions (from 10 to 1000 members),
3 regular cubes and 1 virtual cube (with 20 calculated members).
The pivot table is produced from the virtual cube.
In Excel, when I put 2 dimensions side by side as row fields with all the calculated members,
it's very very slow (2-3 minutes).
When I remove 1 dimension as row field, it's fast (5 sec).
Or when I replaced all calculated members by regular measures (with 2 dimensions as row fields),
it's fast too !
I have tried different storage design or adding "default isolation mode=1" to Excel
connection string but it's still very slow.
Your help/suggestions will be very appreciated.
Thanks.
Create aggregation based on those two dimensions, or create an extra
dimension (dimension1 -> dimension 2), see if they help
Eric Li
SQL DBA
MCDBA
T.Huynh wrote:

> We are using MS SQL Server 2000 with its Analysis Services and Excel 2002.
> We have 3 facts tables (less than 100 rows each) ,
> 8 dimensions (from 10 to 1000 members),
> 3 regular cubes and 1 virtual cube (with 20 calculated members).
> The pivot table is produced from the virtual cube.
> In Excel, when I put 2 dimensions side by side as row fields with all the calculated members,
> it's very very slow (2-3 minutes).
> When I remove 1 dimension as row field, it's fast (5 sec).
> Or when I replaced all calculated members by regular measures (with 2 dimensions as row fields),
> it's fast too !
> I have tried different storage design or adding "default isolation mode=1" to Excel
> connection string but it's still very slow.
> Your help/suggestions will be very appreciated.
> Thanks.
>
|||How do you create an extra
dimension (dimension1 -> dimension 2) in Analysis Services ?
I have dim 1 (20 members) and dim 2 (1000 members).
Thanks for your advice.
|||Take a look to SQL Server Accelerator for BI. it includes an utility to
create your own Aggregations
ALEJANDRO LEGUIZAMO
MVP SQL - Colombia
"T Huynh" <anonymous@.discussions.microsoft.com> wrote in message
news:3D6120F1-228E-4E52-8BBA-E9F548970030@.microsoft.com...
> How do you create an extra
> dimension (dimension1 -> dimension 2) in Analysis Services ?
> I have dim 1 (20 members) and dim 2 (1000 members).
> Thanks for your advice.