Horrible Custom-Rollup in Parent Child Hierarchy - SSAS 2005 vs 2000

[nep]

Hi,

We have migrated a cube from SSAS 2000 to 2005, and are having
performance problems with one of the dimensions.

It is a parent-child hierarchy with approximately 1100 members. It is
ragged, with a maximum of 11 levels. When we enable custom rollup the
performance goes through the floor when querying anything other than
leaf level. We have attempted many of the MDX script changes that
people recommend with no improvement. (When custom rollup is disabled
performance is just fine). There are no particularly complex formulas
- some shared members, some IIF - usual stuff.

The concerning thing is that this dimension functions just fine on a
2000 cube - for example a 2000 cube built atop the same datasource on
the same box will return an sample crossjoin in 5 seconds. On 2005 it
will sit there for upto 8 minutes.

I understand that there are incremental improvements we can make to
the rollup formulae - but this kind of difference in performance makes
me think we are missing something.

I can supply more information where required - and would appreciate
any pointers. (We are in the process of flattening the hierarchy and
trying attribute based - but for various reasons I think this may not
be a long term solution).

thanks
Noel

[Deepak Puri]

Could you give a better idea of what the custom rollups are computing,
at a high level - and what MDX script changes you've already tried?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

[nep]

Post by Deepak Puri
Could you give a better idea of what the custom rollups are computing,
at a high level - and what MDX script changes you've already tried?
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdexhttp://www.developersdex.com***

The rollups break down like this (of ~1100 members)

5% - simple iif statements, some of which are nested.
10% - additive formluae [Dim].&[x] + [Dim].&[y]
10% - shared members - i.e. referencing a single member
75% - no custom rollup


In terms of the changes we have tried - there is obviously not a lot
to do...we have tried scopes and case statements, and using sums
rather than sequential + operators - but it has made zero impact in
the performance.

I guess the main questions is what is the fundamental change between
2000 and 2005 that is causing this problem? (Also, is there any
guidance as to whether we should be referencing the members using the
dimension, hierarchy or both?)

thanks
Noel

[Deepak Puri]

Browsing through the AS 2005 Performance Guide, there's not much
discussion of custom members/rollups. But it does mention that custom
rollups are cached in the global scope, but that use of Visual Totals
will cause the query-scoped ca
che to be used by the Query Execution Engine.

So, if you're using Excel for testing the crossjoin times, could you
compare performance when Visual Totals are turned off (ie. Include
Hidden Items in Total)? Also, are different security roles, or dynamic
dimension security being used when testing?

Don't know whether you could reproduce this issue in Adventure Works -
the Employee dimension has about 300 members - that would make it easier
to investigate.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

[Frank Russell]

Hi Deepak,

I agree with You: If you do not use Visual Totals you get much better
performance. But if you use custom rollup formulas this only holds for SSAS
2005 SP1. In SP2 you get problems...

1. In some cases the server answers a query from cache with the result of a
former query -- even if these queries differ and must have different results.
(see my post "Subject: Analysis Server 2005 returns wrong results (from
cache)" from 4/13/2007).

2. The run-time without VisualTotals is slower. For instance: I have a
complex testcase with custom rollup formulas and a few where-statements with
multiselected elements. This testcase runs slower without VisualTotals than
with VisualTotals -- this also holds, if it is called a second time (See
below for detailled results).

We reported the error to Microsoft report. I'm citing from the answer:

"...the problem is really because of the CustomRollupFormula, where we are
seeing performance issues as well as problems like yours. Our developer team
recommended that these people should start using “Cache Policy = 9” with SP2
in order to obtain better performance (connection string setting). Now, Cache
Policy is not doable in SSMS or BIDS, so what you can do is to change this
setting server wide by changing inside the msmdsrv.ini:
<CalculationCoverPolicy>9</CalculationCoverPolicy>"

If we work with this setting the error does not occur and the performance is
better (Details see below).

My question at this point is: Custom Rollup Formulas are a very powerful
part of Analysis Services. You can create very interesting solutions with
them. But it seems to me that the use of Custom Rollup Formulas is not in the
main focus of the Analysis Services development team. Otherwise I could not
explain the error and performance problems in SP2. Perhaps it would help, if
Microsoft has real-life examples of the usage of this feature. Is it possible
to establish a deeper contact between the development team and users of
custom rollup formulas to exchange testcases and experiences for this topic?

---------------
Appendix:
Comparison of runtimes between SP1, SP2 and SP2' (= SP1 with
CalculationCoverPolicy set to 9):

Case SP Runtime Count of
(seconds) QuerySubcube
Events

Testcase A SP1 110 43 000
First call SP2 377 186 000
SP2’ 47 32 000

Testcase A SP1 35 15 000
Second call SP2 335 160 000
(from cache) SP2’ 1 0

Testcase A SP1 172 49 000
with SP2 308 362 000
VisualTotals SP2’ 53 67 000

[Deepak Puri]

"Perhaps it would help, if
Microsoft has real-life examples of the usage of this feature. Is it
possible
to establish a deeper contact between the development team and users of
custom rollup formulas to exchange testcases and experiences for this
topic?"

From the results you posted, there was a big improvement after changing
the Cache Policy to 9, as suggested by MS support. Of course, ideally
such arcane settings shouldn't be necessary. Not sure whether you
already sent your test case to MS support; but if I'll let you know if I
become aware of any opportunity to provide real-life examples of Custom
Rollup issues.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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