Let's say I've got a cube with these 2 dimensions:

Store
...All Stores
...Region
...Store (1000 stores)

Time
...All Time
...Year
...Quarter
...Month
...Day

It's a daily snapshot of the monthly financials, so I put a custom rollup
formula on the Month level of the Time dimension:

IIf(
Tail(Filter({[Time].CurrentMember.Children},Not
IsEmpty([Time].CurrentMember)),1).Count>0
,Tail(Filter({[Time].CurrentMember.Children},Not
IsEmpty([Time].CurrentMember)),1).Item(0)
,Null
)

It works fine and gives me the right numbers, but it's darn slow when I
browse the cube in Analysis Manager. Before I put in the custom rollup
formula, you could browse to the ALL-ALL slice in less than a second in
Analysis Manager. Now it takes over a minute. Because it's so slow, I suspect
it's aggregating the Time dimension before the Store dimension... meaning it
runs that custom rollup formula 1000 times (one for each store) for every
month. I don't know this for sure, but it seems logical. Is there any way to
tell it to aggregate stores before time? It would run the formula 1000 times
less each for every month that way.

Looking at a slice closer to the leaf performs OK. It's just that the data
browser in Analysis Manager is so slow to start because the ALL-ALL slice is
slow.

Any help is appreciated.

Do aggregations (as in Design Storage aggregations) use the custom rollup
formulas? Or do custom rollup formulas have to be calculated "at runtime"?

Using Partition Aggregation Utility to design an aggregation on the Month
level is feasible... that aggregation turns out to only be 0.1 MB. Just want
to know if it has any effect.

How about trying NonEmptyCrossJoin(), as discussed in this paper on
"Inventory Management Calculations"?

http://www.sqlserveranalysisservices...oryManagement%
20in%20AS2005v2.htm


The Custom Rollup Formula could then look like:

NonEmptyCrossJoin([Time].Children).Count > 0,
NonEmptyCrossJoin([Time].Children).Item(0),
Null)

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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