COGNOiSe.com - The IBM Cognos Community

Hi All,

I am facing a problem with the roll up / roll down in my DMR model.

Design is like this:

Dimension: Corp Headquarters> LE Office > Regional Office > Stores > Employee

Fact : It contains sales data for all levels (Employee, Store, LE) because the stores and regional office can also do sales directly.

Now, when I roll up the sales data keeping my employee at the lowest level in the hierarchy, I need sales at store level as sales(employees) + sales(store) and so on.

How can I achieve this?

Pls help.

Thanks,
-
Sapp

Any help pls ?

Thanks,
--
Sapp

Quote from: sapp on 23 Sep 2014 08:43:59 AM
Hi All,

I am facing a problem with the roll up / roll down in my DMR model.

Design is like this:

Dimension: Corp Headquarters> LE Office > Regional Office > Stores > Employee

Fact : It contains sales data for all levels (Employee, Store, LE) because the stores and regional office can also do sales directly.

Now, when I roll up the sales data keeping my employee at the lowest level in the hierarchy, I need sales at store level as sales(employees) + sales(store) and so on.

How can I achieve this?

Pls help.

Thanks,
-
Sapp

Your fact needs to be split into multiple fact query subjects in the model - one for each level of granularity. You will need to join these to the dimension based on the relevany key (Employee ID / Store ID / LE Office ID) and your dimension will need determinants corresponding to each "level" you are joining to it at.

Cheers!

MF.

Thanks for your response MF.

I created 4 facts for LE / Reg / Store / Employee and collected sales for these levels and joined it to the dimension with corresponding keys. I also created determinants in the dimension for all these levels with employee as unique.

Now, all these facts have ids and sales like emp_id, emp_sales, store_id, store_sales, ... and so on in the respective fact tables.

Now, my question is how can I calculate the sales at each level of the hierarchy? and how it will behave in drill up/down because on Store level it has to show stores for all employees those have made any sale + stores those have done sales directly?

Thanks,
--
sapp

Any input on this issue ???

br,
-
Sapp