Excel and Pivot Table performances

[Database Administrator]

Hi all,

I would like to know what is happening on my SSAS 2005 cube when I browse it
with Excel 2007 Pivot Table, and eventually how can I resolve the problem.

I have a finance cube where we defined different roles: one of this (global
role), can see all data, while the other roles have a filter on the measures
(only some measures can be seen by the user who belong to that role) and a
filter on the geography (different areas for different roles)

Furthermore, we partitioned the cube on a monthly basis.

Let's consider the client dimension, which is very huge.

Imagine to browse the cube in Excel 2007, with users who belong to different
roles;

put the time dimension on column labels position in Excel, showing 2008 and
all the quarters (Q1, Q2, Q3, Q4)
put the client dimension on row labels positions in Excel
put one of the measures on the Values position in Excel. This is not a
calculated measure.

I get this performances:
If I browse the cube with the global role I have a response time of few
seconds,even if the list of clients is very huge (In real analysis the user
needs to see ALL the list of clients, but this report is generated once a
year and adding some other filters that are not important for this test)

If I browse the cube with another role I have a response time nearly to 1-2
hours, even if the list of client is smaller because I'm filtering on the
geography.

If I put the date dimension in the report filter position in Excel, I have a
response time of few minutes, sometimes in seconds.

What is happening? Why I have this difference between the two roles?
It is something related to the partitions and the roles?

If I use the performance monitor I don't see any difference between the MDX
generated by the two roles.
Any suggestions?

Thanks in advance,
Luca

[Database Administrator]

".. the other roles have a filter on the measures
(only some measures can be seen by the user who belong to that role) and
a filter on the geography (different areas for different roles) .." -
when filtering by geography, are Visual Totals enabled for the security
role? That might be creating more work for the server than with the
global role.



- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

[Database Administrator]

Hi Deepak,

yes, the Visual Totals are enabled. I did a lot of tests on the Excel Pivot
Table and I think that this could be the reason for the slow performances.

Thanks again,
Luca

"Deepak Puri" wrote:

> ".. the other roles have a filter on the measures
> (only some measures can be seen by the user who belong to that role) and
> a filter on the geography (different areas for different roles) .." -
> when filtering by geography, are Visual Totals enabled for the security
> role? That might be creating more work for the server than with the
> global role.
>
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex.com ***
>