Cube Browser Woes
I had an interesting issue this week. I was working on a small test cube with financial data. The lowest grain in the measure group was the Account dimension which represents a customer bank account. I had to implement rolling 12 calculations on semi-additive measures (e.g. Avg Account Balance) by weighting the balances at the account level:
Since I had to weight the account balances by the number of the number of the days the account was active, I decided to scope at the account level, e.g.; SCOPE (Leaves(Account), Leaves(Date)…). Also, I had to use the ParallelPeriod() MDX function to sum the weighted balanced for the 12-month period.
This statement was causing the server to evaluate the Account All member which was triggering the formula evaluation for each account. Oops! The moral of this story is to test your cube initialization time in SQL Server Management Studio. As for me, I decided to reduce the scope (always a good idea) to Leaves (Date) only by pro-rating the Active Days in the data source so I could use the days in the month. This makes the formula universal on rows irrespective of the cube slice and makes the Cube Browser happy.
