Cognos Determinant Demystified

Check list of all posts

Determinants can play a very important role of Cognos Framework Manager model. However, its concept may not always work for different cases. This document is intended to fully explore the behavior and list different cases.

Sample data and expected result

 

Test cases and their results

•  Column is Derived at Business layer from Business layer:  C2 is derived as CASE WHEN [Business Layer].[DIM].[C1]   IN (1,2) THEN 10 ELSE 20 END
• Column is Derived at Business layer from Database layer: C2 is derived as CASE WHEN [Database Layer].[DIM].[C1]   IN (1,2) THEN 10 ELSE 20 END
• Column is Derived from Query: select C1, CASE WHEN C1 in (1,2) THEN 10 ELSE 20 END C2 from DIM
• Column is Not Derived: select * from DIM

 

Case 1: FAILED
        Column is Derived at Business layer from Business layer
        Relationship defined at Business layer
        Determinant defined at Business layer
    SQL: select case when DIM.C1 in (1, 2) then 10 else 20 end C2 , FACT.M1 M1 from DIM, FACT where FACT.C2 = case when (DIM.C1 in (1, 2)) then 10 else 20 end

 

Case 2: SUCCEED
        Column is Derived at Business layer from Database layer
        Relationship defined at Business layer
        Determinant defined at Business layer
    SQL: select DIM.C2 C2 , FACT.M1 M1 from ( select case when DIM.C1 in (1, 2) then 10 else 20 end C2 from DIM group by case when DIM.C1 in (1, 2) then 10 else 20 end ) DIM, FACT where FACT.C2 = DIM.C2

 

Case 3: SUCCEED
        Column is Derived from Query
        Relationship defined at database layer
        Determinant defined at database layer
    SQL: select DIM.C2 C2 , FACT.M1 M1 from ( select DIM.C2 C2 from ( select C1, case when C1 in (1, 2) then 10 else 20 end C2 from DIM) DIM group by DIM.C2) DIM, FACT where FACT.C2 = DIM.C2

 

Case 4: FAILED
        Column is Derived from Query
        Relationship defined at Business layer
        Determinant defined at database layer
    SQL: select DIM.C2 C2 , FACT.M1 M1 from ( select DIM.C2 C2 from ( select C1 C1 , case when C1 in (1, 2) then 10 else 20 end C2 from DIM) DIM) DIM, FACT where FACT.C2 = DIM.C2

 

Case 5: FAILED
        Column is Derived from Query
        Relationship defined at database layer
        Determinant defined at Business layer
    SQL: select case when DIM.C1 in (1, 2) then 10 else 20 end C2 , FACT.M1 M1 from DIM, FACT where FACT.C2 = case when (DIM.C1 in (1, 2)) then 10 else 20 end

 

Case 6: SUCCEED
        Column is Derived from Query
        Relationship defined at Business layer
        Determinant defined at Business layer
    SQL: select DIM.C2 C2 , FACT.M1 M1 from ( select DIM.C2 C2 from ( select case when C1 in (1, 2) then 10 else 20 end C2 from DIM) DIM group by DIM.C2) DIM, FACT where FACT.C2 = DIM.C2

 

Case 7: SUCCEED
        Column is Not Derived
        Relationship defined at database layer
        Determinant defined at database layer
    SQL: select DIM.C2 C2 , FACT.M1 M1 from ( select DIM.C2 C2 from DIM group by DIM.C2) DIM, FACT where DIM.C2 = FACT.C2

 

Case 8: FAILED
        Column is Not Derived
        Relationship defined at Business layer
        Determinant defined at database layer
    SQL:select DIM.C2 C2 , FACT.M1 M1 from DIM, FACT where DIM.C2 = FACT.C2

 

Case 9: FAILED
        Column is Not Derived
        Relationship defined at database layer
        Determinant defined at Business layer
    SQL: select DIM.C2 C2 , FACT.M1 M1 from DIM, FACT where FACT.C2 = DIM.C2

 

Case 10: SUCCEED
        Column is Not Derived
        Relationship defined at Business layer
        Determinant defined at Business layer
    SQL: select DIM.C2 C2 , FACT.M1 M1 from ( select DIM.C2 C2 from DIM group by DIM.C2) DIM, FACT where DIM.C2 = FACT.C2

 

 

Overall Findings:
1) The relationship and determinant must defined at the same level
2) Derived column at business layer from business layer doesn’t work for determinant.