Build two separate dimension from one database table

[shainkijain]

Hello Experts,

My first attempt at Cognos Cube designer. I am stuck at two issues. One of that is -

I want to create two dimensions from same table. So dimension creation is working fine.
But in cube on implementation tab - this table is joined with fact table twice and because of that data in adhoc is not right (multiplied by number of members in dimension).

E.g. Table is Scenario_Calendar_Dim and two dimensions are Scenario and Period. And this table is joined with Fact on scenario_key and Period_key. In adhoc when I filter for one scenario and one period - scenario filter is only getting applied to one table and period filter to another table. because of that huge data discrepancy in report.

It works in FM hunky dowry. So question is how to get this two dimensions working from same table. The way requirement is - there is no scope of splitting this table into two.

Please help!!!

[bus_pass_man]

Howdy

Behind the scenes aliases ARE being created.  It would be nice if the implementation diagram would indicate that, though, wouldn't it?

I think the double counting would be coming from your scenario dimension.  How have you modeled it?  Have you had a chance to look at section 6.9 in the dynamic cubes red book?

I suspect that there are many distinct period_key values for each scenario_key.  Is that a correct understanding?  My guess is that the scenario dimension is causing the double counting as all of the scenarios are being brought into the query.  The all member is the default member if nothing else is defined. 

Another thing you would need to do is look at the query logs and see what is happening there. 

But my first stop would be the model.

Hope that helps.

[shainkijain]

Thanks bus_pass_man!

Attaching implementation diag. Where it shows two joins on Scenario_calendar (and its alias), which is perfect.

But problem is, Period filter is getting applied in only one and scenario filter is getting applied in other. I could replicate same by writing sql -

Double counting SQL (data) -
from
      Fact_table F,
      Scenario_Calendar SC1,
      Scenario_Calendar SC2
   where
      F.Scenario_key = SC1.Scenario_key
      AND F.PERIOD_KEY = SC1.PERIOD_KEY
      AND F.Scenario_key = SC2.Scenario_key
      AND F.PERIOD_KEY = SC2.PERIOD_KEY
      AND SC1.SCENARIO = 'ACT'
      AND SC2.PERIOD = 201501

And hence the double counting. Ideally Scenario and Period filter should be applied on SC1 and SC2 both (which gives correct result).

Hope this gives you some perspective of issue I am facing.

Cheers!
SJ

[bus_pass_man]

Attaching implementation diag. Where it shows two joins on Scenario_calendar (and its alias), which is perfect.

Actually, that's not the case.  The diagram shows the opposite.  It shows the fact table and the dimension table and two relationships between the two tables.  If a person relied on the diagram to tell him what was going on he would be mislead, which I'm still not all together happy about. Inside the sausage factory aliases are created but you can only know that if you know that there's a sausage factory and what is going on in it.

Ideally Scenario and Period filter should be applied on SC1 and SC2 both (which gives correct result).

personally, I'd be not too happy about creating a filter in one dimension and finding it in another one too.   The filter for the first dimension will be applied only to the object in the relational model representing that dimension.  The first for the second dimension will be applied only to the object representing the second dimension. 

Any way you probably ought to consider looking at the redbook.   

Could you be kind enough to answer the questions that are in my first response please.  It will help me help you.  My guess is that your scenario dimension isn't modeled correctly. 

[shainkijain]

I suspect that there are many distinct period_key values for each scenario_key.  Is that a correct understanding?  My guess is that the scenario dimension is causing the double counting as all of the scenarios are being brought into the query.  The all member is the default member if nothing else is defined. 

Yes, attached file shows how this table is populated and how modelling is done in FM - DMR model where this works like a charm.

From my analysis so far - In Dynamic Cube issue is because this table is joined with fact table twice. And filter scenario "Actual" in this example is applied from one table and period 201601 is applied on other table which actually brings three records instead of 1. (201601,201501,201401).

If this is joined with fact table only once or Scenario and Period filter is applied in both tables, this issue can be resolved in my opinion. But have no clue how to reach there apart from changing logic of this scenario period table.

Thanks!