Cognos Analytics

 View Only
  • 1.  Data Module Joins

    IBM Champion
    Posted Fri September 23, 2022 08:51 AM
    Edited by System Fri January 20, 2023 04:41 PM

    We experience an impact on the report SQLs when we switched the sides of the tables for a relationship (inner join, retained cardinality and no filter). Unfortunately for some reports one variant is far more performant and for some the other. Is there a general best practice on which side to put the fact and on which the dimension? @IAN HENDERSON could you please give a quick idea? 



    ------------------------------
    Philipp Hornung
    ------------------------------
    #CognosAnalyticswithWatson


  • 2.  RE: Data Module Joins

    IBM Champion
    Posted Fri September 23, 2022 09:01 AM
    Hi Philipp,

    There is a quite good documentation available for both Framework Manager and Data Module:
    IBM Cognos Analytics Version 11.2 : Metadata Modeling Guidelines

    See the Chapter 3. Remove ambiguity.

    Best regards,

    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    ------------------------------



  • 3.  RE: Data Module Joins

    IBM Champion
    Posted Fri September 23, 2022 09:10 AM
    Thanks Patrick, in our model it's really very basic: A clean star schema with 1 fact table and n:1 dimensions. So there is initially no ambiguity. I don't expect a difference on which side is what table (given the n:1 is also switched accordingly).

    ------------------------------
    Philipp Hornung
    ------------------------------



  • 4.  RE: Data Module Joins

    Posted Fri September 23, 2022 10:59 AM

    The order of table selection determines the order of the tables in the relationship editor.

    As far as I know, and if it is not the case we should be presenting that information in a slightly less subtle manner, what should be important is the definition of the cardinality. A relationship where the fact table is on the left hand side and the cardinality is N to 1 should be the same as if the fact table is on the right hand side and the cardinality is 1 to N.

    Is the SQL which is generated different for the cases which you have seen?

    If so, you might want to contact customer support.



    ------------------------------
    IAN HENDERSON
    ------------------------------



  • 5.  RE: Data Module Joins

    IBM Champion
    Posted Mon September 26, 2022 04:55 AM
    Hi Ian, Thanks for your estimation. We're using 11.1.7 and it's indeed just the visual position inside the relationship editor we changed. Result and cardinality are correct in both scenarios. So we consider opening a case because of the unwanted performance impact. 

    A) Fact on the left side (N:1): 
    WITH 
    	...
    SELECT
        SUM("Fakt_MD_Auftrag"."MD_AUFTRAG_ANZ") AS "Anzahl_Auftrag"
    FROM
        "Fakt_MD_Auftrag"
            INNER JOIN "Dim_MD_Auftragsart"
            ON "Fakt_MD_Auftrag"."MD_AUFTRAG_ART_RSK" = "Dim_MD_Auftragsart"."MD_AUFTRAG_ART_RSK"​

    B) Fact on the right side (1:N): 
    WITH 
    	... 
    SELECT
        SUM("Fakt_MD_Auftrag"."MD_AUFTRAG_ANZ") AS "Anzahl_Auftrag"
    FROM
        "Dim_MD_Auftragsart"
            INNER JOIN "Fakt_MD_Auftrag"
            ON "Dim_MD_Auftragsart"."MD_AUFTRAG_ART_RSK" = "Fakt_MD_Auftrag"."MD_AUFTRAG_ART_RSK"​


    ------------------------------
    Philipp Hornung
    ------------------------------