Joins and shortcuts in FM

[tupac_rd]

Hi Gurus,

We are using Cognos 8.3 FM. Following is the issue I am facing. We have a Date dimension and a fact table. The fact table has 4 FKs for the PK in the date dimension. I need to do a join between the dim and fact between the dim and fact for all 4 FKs in the fact. Should I create 3 shorcuts of Fact and 3 shortcuts of dim and do the 4 individual joins? If yes, what kind of shortcuts should I create alias or reference. Please let me know whats the best practice.

thanks
Deepak

[blom0344]

Multiple dates in the facttable would require multiple instances of the time dimension table. (Not need for more than one factobject) You need aliasses for the dimensiontable , but there is more than one approach that would work.

[tupac_rd]

Hi blom0344,

Thankyou very much for the reply.

I had started out by creating 3 alias shortcuts for the date dimension. Each shortcut and the parent table was joined to one FK in the fact table. I think the query is bringing back correct results. But when I run the Model Advisor in Cognos 8.3 FM, I get a message saying

Issue 2: Determinants that conflict with relationships

There is a determinant specified that conflicts with the relationship you have defined. This ambiguity can cause a query to be split unnecessarily. It is recommended that you examine and resolve this in the model. More information about determinants... 

Object Name
T_DATE_DIM

Problem Description

The relationship 'Shortcut to T_DATE_DIM <--> T_....._FACT' references all key query items of determinant 'DATE_SK' in query subject 'T_DATE_DIM'. The determinant uniquely identifies a row, which suggests the cardinality of the relationship must be 1:1 or 0:1. Review the cardinality of the relationship and the determinant information to determine if adjustments are required.

Since 'DATE_SK' is the PK in date dimension, upon importing the table T_DATE_DIM into FM, a determinant is automatically created for the query subject.

I am not sure what the message is all about. Please let me know if I am doing anything wrong. Or how to fix that issue. What is the best way to get around this.

Thanks
Deepak

[blom0344]

That must be a new feature in 8.3..
Cardinality must be set properly , which means that fact:dimension is n:1.
Check how you have set the cardinalities..