2 Fact tables with different granularities one cube

[raja1718]

Hello Folks,

I am trying to create a cube with 2 fact tables one with Daily sales and another one with monthly sales.
Its both are in different granularity levels but connected with the all same dimension tables.

will this approach will work and give accurate results ???? (If its possible)

Thanks,

Raj

[MFGF]

Are the measure values different in each fact, or are the monthly sales figures just an aggregation of the daily sales figures? If they are, then there is no need to bring in the monthly sales figures at all.

If you have different measures in each fact (eg daily sales in one and monthly sales targets in the other) you can easily bring these in - Transformer supports measures at different levels of granularity. It also gives you options to allocate the monthly measures down to the day level if you choose to do so - either as a constant value, or in proportion to another measure.

Cheers!

MF.

[adam_mc]

In Framework Manager, create a Query Subject that combines both of your fact tables using a UNION ALL.
Make sure that each of the union'd SQL's has all the fields/columns defined in the same sequence.
Depending on how you need it you may want to add a Period field/column with the value 'DAILY' or 'MONTHLY' in it.
If you don't want this (and just want one row of Daily/Monthly Sales), ensure that Daily Sales measures are set to zero in the Monthly SQL section (and Monthly Sales measures are set to zero in the Daily SQL section).
Then create this as a Transformer Query Package.

Then in Transformer, when you are creating the Model using the wizard, define the Data Source type as a Package.
The wizard should then take you through so that you can select the Package you created in Framework Manager.
You'll then have to go through and define your Hierarchies and Measures (if Transformer doesn't do it for you).

This is how I do similar cube builds.
Hope this helps.