How to join two fact tables at different granularity.

[nsaxena]

Hi ,

I am new to FM model designing.

We have 2 fact tables (F1 AND F2) . In F1 data is at month granularity and in F2 data is at day granularity.

In report we need to pull numbers from F1 and F2 at same time through conformed dimension. Let us say TIME which is at day level.

How to fix this mis match in granularity issue so that correct numbers gets pull up when we query items from both fact tables .
I have heard about determinant,but where to apply etc.

Kindly advice!

Thanks!

[bus_pass_man]

Given what you know about SQL and handling multi-fact multi-grain queries, think of the problem as how to give the query engine information about how to generate the SQL which you would want to be generated in the context.

Some stuff which might help would be:

read the bits of the FM user guide and the dynamic query redbook (sg248121) which mention determinants. 

Click on the COGNOiSe.com - The IBM Cognos Community link, type in determinant in the search field, and wade through the topics which are returned.

Fiddle around with determinants in FM to get a feel for them.  The query information tab will give you a glimpse of the sql.

One thing you would also need to know about is how Cognos handles multi-fact queries (Coalesce, fact streams, etc.)

hope that helps

[cognostechie]

Hi ,

I am new to FM model designing.

We have 2 fact tables (F1 AND F2) . In F1 data is at month granularity and in F2 data is at day granularity.

In report we need to pull numbers from F1 and F2 at same time through conformed dimension. Let us say TIME which is at day level.

How to fix this mis match in granularity issue so that correct numbers gets pull up when we query items from both fact tables .
I have heard about determinant,but where to apply etc.

Kindly advice!

Thanks!

I presume there is a Date Key in the TIME dimension. If so, create two determinants in the TIME dimension with the following settings:

1st Determinant
------------------

Drag the DateKey into the 'Key' section
Check on the checkbox called 'Uniquely Identified'. 


2nd Determinant
--------------------

Drag the Year, Quarter and Month in the 'Key' section and drag all other columns in the Attributes section. Check the checkbox called 'Group By'.


If there is no DateKey then use Date field. By the way, if your dimension is at Day level, then it is a DATE dimension, not a TIME dimension.

Join the dimension to F1 with the Month field and F2 with the DateKey field. It's been a while since I did this so I will double check the settings for the 2nd Determinant and post it again tomorrow. The order of determinants is very important and this should be done in the layer in which the joins are created. If the joins are created in the Physical Layer then do this in that layer.

[MFGF]

I presume there is a Date Key in the TIME dimension. If so, create two determinants in the TIME dimension with the following settings:

1st Determinant
------------------

Drag the DateKey into the 'Key' section
Check on the checkbox called 'Uniquely Identified'. 


2nd Determinant
--------------------

Drag the Year, Quarter and Month in the 'Key' section and drag all other columns in the Attributes section. Check the checkbox called 'Group By'.


If there is no DateKey then use Date field. By the way, if your dimension is at Day level, then it is a DATE dimension, not a TIME dimension.

Join the dimension to F1 with the Month field and F2 with the DateKey field. It's been a while since I did this so I will double check the settings for the 2nd Determinant and post it again tomorrow. The order of determinants is very important and this should be done in the layer in which the joins are created. If the joins are created in the Physical Layer then do this in that layer.

Just to add to these very useful steps, the order in which these are held in your query subject is important. Your determinant for Year-Quarter-Month needs to be above your determinant for Date. To make this more intuitive, I often create the higher grain determinant(s) first, then the lower grain. The higher-grain determinant(s) should have attributes that pertain to just those columns - not all attributes. For example, if you had a "Day Name" query item, this would not belong as an attribute of the higher grain determinant.

Cheers!

MF.

[cognostechie]

Yes, the order of the determinants is very important because Cognos will generate the SQL according to only one determinant and the one it finds first which fits the criteria will be considered. The one with the higher grain should be on top. As I mentioned that I did this a while ago and I will post again today, this is what should be done:

1st Determinant
------------------

Key - Year, Quarter Number, Month Number
Attribute - Year, Quarter Name, Month Name
Check the checkbox called 'Group By'

2nd Determinant
-------------------

Key - Year Number, Quarter Number, Month Number, Datekey
Check the checkbox called 'Uniquely Identified'
It will automatically put the rest of the fields in the Attribute section

Joins will be as I mentioned earlier.

[nsaxena]

Thank you everyone!!!

I was not aware of few points about determinants and it gets cleared with your valuable inputs.
Thanks!!

[cognos4321]

Hi Cognostechie,

I understood that in the 1st determinant you took Date key as 'Uniquely identified' and rest of them as 'Group By'.

Could you please explain what is the 'Uniquely identified' element taken in the 2nd determinant.

Pardon my ignorance and anyone please let me know what is the use of adding the 2nd determinant, wasn't  one enough.

Thanks Always

[MFGF]

Hi Cognostechie,

I understood that in the 1st determinant you took Date key as 'Uniquely identified' and rest of them as 'Group By'.

Could you please explain what is the 'Uniquely identified' element taken in the 2nd determinant.

Pardon my ignorance and anyone please let me know what is the use of adding the 2nd determinant, wasn't  one enough.

Thanks Always

Hi,

That information was not quite correct. The first determinant should be the higher-grain non-unique determinant - with "group by" checked. Only the second determinant should have "uniquely identified" checked.

If all facts are held at the same grain and join to the dimension using the same key, determinants are not needed at all (although you could add one for the key and mark it as "uniquely identified" and still get the same results.

If some facts are held at a higher grain (eg Monthly targets) and some are held at a lower grain (eg daily sales), then they are joining to the dimension using different keys. In this situation you need to define determinants to allow the query engine to understand how to aggregate facts from each grain based around each key and the attributes that belong to each key. You need one for the higher grain and one for the lower grain.

Cheers!

MF.

[cognos4321]

Thank You MF.