Modelling multigranular fact data in cube

[Arunavo B]

Hello,

Can someone tell me how to model a multigrain transaction data in cube? I have two facts -- one at the material code level and another one at material franchise level. How can I model both the facts into a single dimension material having the following hierarchy :

Material global franchise --> material franchise -- > some level 1 --> some level 2 .... material long code.

Please help.

[MFGF]

Hi,

For the higher level measure, you can define the scope as being at the Material Franchise level (hint - you will need to mark the members of this level as unique), then you have the choice of allocating these measure values to the lower levels of the dimension - either as a constant, or in proportion to another measure (or thirdly you could choose not to allocate, meaning you would not see measure values for this displayed at the lower levels).

Regards,

MF.

[Arunavo B]

Hi,

The problem in this is the lower level measures are not showing up as the higher level is marked as unique and the lower level cannot be joined hence. I had already tried that and the measures from the fact having granularity at material code is blank.

Regards,
Arunavo.

[MFGF]

You will need to have the two different measures defined in separate data sources - one containing the Material Franchise code and the corresponding Material Franchise measure, and the other containing the Material Code and the Material Code measure.  The granular measure with then attach to the dimension at the Material Long Code level, and the aggregated measure will attach at the Material Franchise level.  You can then choose how the Material Franchise measure gets displayed (or not displayed) at the lower levels of the dimension by using the Allocation techniques I described before.

Good luck!

MF.

[Arunavo B]

In that case I need to define only the higher level as unique right?