Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only

  • 1.  Data Module Joins

    Posted 09/23/22 08:51 AM
    Edited by System Admin 01/20/23 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

    Posted 09/23/22 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

    Posted 09/23/22 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
    ------------------------------

    Original Message


  • 4.  RE: Data Module Joins

    Posted 09/23/22 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
    ------------------------------

    Original Message


  • 5.  RE: Data Module Joins

    Posted 09/26/22 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
    ------------------------------

    Original Message


Related Content