I have a single cube in which I need to show measures at two different levels of granularity and still aggregate correctly

here are the details
the granularity is
Date/Flight/Clas

I have a fact table that has a "Class" granularit
the measure is
TotalBooking

so a simplified fact row would look like this
Class Total bookings mileage Capacit
1 5 1000 10
2 10 1000 10


However, the measures mileage and capacity are only valid at the flight leve
the cube needs to contain calculated measures tha
use mileage and capacity and agregate corectl

Flight TotalBookings Mileage Capacity TotalBooking/Capacity(calculated measure
1 15 1000 100 .1

I was thinking of creating a cube for each level of granularity and use a virtual cube to try and pull data from each cube
Is this a valid approach or can I solve this problem by using MDX

Thanks

Your virtual cube route is the way to go. Basically, build your shared
dimension as if it were to go to the lowest level. Then, in a cube that has
data at a different level of granularity, you will disable the level(s)
below the level of granularity for that cube. You can then build a virtual
cube off these two cubes, and use calculated members to pull data from the
correct cube using MDX fucntions like
(IIF(<<YourDimension>>.currentmember.level.ordinal =<<Your specific level>>,
[Measures].[From Cube A], [Measures].[From Cube B])


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Jason" <anonymous (AT) discussions (DOT) microsoft.com> wrote