Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Cube Design | Combining fact tables with different hierarchy level

6 views
Skip to first unread message

Jason

unread,
Jun 26, 2007, 1:49:00 PM6/26/07
to
Hello, I need to design a cube that includes two separate fact tables with
different levels of dimension attributes. Below are my dimensions and
attributes ( i simplified them for discussion purposes):

Fact: Sales
Fact: Forecast
DimTime: Year > Qtr > Month > Day
DimChannel: Business Unit > Segment > Channel
DimSku: Product > Sku

My Sales table goes to the Day, Channel, Sku level (the lowest level on all
dimension). But my Forecast table goes to the Month, Segment, and Product
level.

How do I combine these two fact tables into a single cube?

My first thought was that I need to modify all my dimension tables into
Parent Child dimensions, but this may take a performance hit. Is this a good
way, or is there some other method I can use?

Thanks for your help!

Jason

Vidas Matelis

unread,
Jun 26, 2007, 2:05:16 PM6/26/07
to
Jason,

Assuming you are using SSAS 2005.
First of all make sure that you define proper attribute relationship for
your dimensions and create proper hierarchies.
Then create new cube, add fact tables and dimensions into the cube and then
go to dimension usage tab. There for each measure group you can specify at
what level you have specific dimensions. For fact Forecast you will choose
intersection Dimension "DimTime" and Measure group "Forecast", then choose
relationship type "Regular", then "Granularity attribute" Month and will
specify fields in dim and measure group to join by.

Vidas Matelis

http://www.ssas-info.com


"Jason" <jaso...@online.nospam> wrote in message
news:82DC4E1A-B8E4-4117...@microsoft.com...

Jason

unread,
Jun 26, 2007, 2:16:00 PM6/26/07
to
Thanks Vidas. I am new to SSAS2005.

Do I need to use Parent Child hierarchies?

In my data source view, do I join DimTime.Day to Fact.Sales.Day and also
join DimTime.Month to Fact.Budget.Month?

Vidas Matelis

unread,
Jun 26, 2007, 3:09:51 PM6/26/07
to
Jason,

For the structure you defined you do not need Parent-Child dimensions.

> In my data source view, do I join DimTime.Day to Fact.Sales.Day and also
> join DimTime.Month to Fact.Budget.Month?

Join is optional, but recommended. If you do a join in data source view,
then when you will add dimension in the cube designer, it will join at
correct level to a measure group. If no join exists in data source view, you
can still create join between dimension and measure group in "Dimension
Usage" tab.

Vidas Matelis

http://www.ssas-info.com

"Jason" <jaso...@online.nospam> wrote in message

news:739C96BE-817F-4AE3...@microsoft.com...

Jason

unread,
Jun 26, 2007, 5:15:02 PM6/26/07
to
Thanks Vidas!

You website also great info on SSAS.

Jason

0 new messages