Custom Rollup: Is there a better alternative?

#1

I am having some performance problems on my cube and they seem to be tied to
using a custom rollup. I am trying to brainstorm another design, but I am
having no luck.

At a high level, I want to keep track of the Revenue and SQFT of each suite
on a monthly basis. SQFT can change if a tenant reconfigures, so it must be
updated each month. Each suite will have multiple revenue entries, but only
one SQFT entry per month.

My design:

I have two cubes, joined together in a virtual cube. One tracks revenue,
the other SQFT. The measure on my SQFT is MAX, since the SQFT for each suite
across time is not added up. However, when suites rollup to a building, then
the SQFT should be added together to get the Buildings total SQFT. I use
custom rollup on the suite/building dimension to switch from MAX to SUM for
every level above suite.

This produces the correct answers, but is slow to work in real-time. Any
suggestions on what I could do differently?

#2

That is just what I needed. The math works out and it is indeed faster than
the roll-up. Thanks so much!

"Deepak Puri" wrote:

Quote:

In the second SQFT cube, can you use the closing inventory approach to
semi-additive measures? So, you could define [RawSQFT] as a SUM measure,
and [SQFT] as a calculated measure using the [Time] dimension, like:

'([Measures].[SQFT], ClosingPeriod([Time].[Month]))'

This would avoid having to sum suites/buildings on-the-fly in a custom
rollup, which may be slow (depending on how many are being rolled up).
Here's a Microsoft article:

http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/semiadd2.asp

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Custom Rollup: Is there a better alternative?

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Custom Rollup: Is there a better alternative? - 11-05-2004 , 02:10 PM

Re: Custom Rollup: Is there a better alternative? - 11-08-2004 , 11:40 AM