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
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.
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
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..