Hi Audrey,

What is the grain of the fact table? Assuming that there are a large
number of [Cases] , which is causing a performance problem, you can
define a view on the fact table that returns 1 record per leaf [Case],
with [Total LOS] and [ELOS] summed up. Then you can define a cube w/ a
measure on this view, whose source column is defined as:

case when [Total LOS] > [ELOS]
then [Total LOS] - [ELOS]
else 0 end

This measure can be aggregated as 'Sum', without any MDX calculation,
and made available in a virtual cube.


- Deepak

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

Hi Deepak,

Here's an entry you have once suggested earlier on...is that the same
thing what you have just suggested?

From: Deepak Puri
Date Posted: 11/26/2002 11:29:00 PM


Can't you make this a base rather than a calculated measure
(in which case, aggregation would speed up retrieval of that measure)?
If there are fact table columns, C1 and C2, corresponding to [DAD
Measures].[Raw Conservable Days] and [DAD Measures].[ALC LOS], then the
measure column could be defined in SQL as: case when C1>C2 then C1-C2
else 0 end. The sum aggregation type would apply to this measure...

If so, can you please provide me a step-by-step instructions as I have
no idea what is being asked =(
My knowledge in this kind of stuff is so limited...

Thanks,

Audrey

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

Audrey,

The problem sounds similar to the earlier situation, so
can you provide some basic info about the cube and measures in question,
and the fact table details for the cube?

- What does each fact table record represent?
- How many fact table records per Case - is it fixed?
- Are the [LOS] and [ELOS] measures defined as Sums?
- Is performance problem due to large no. of Cases?

The idea is to replace "on-the-fly" summation in MDX over large sets
with cube-based aggregations, where possible.


- Deepak

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