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:
Sent: Fri September 23, 2022 10:58 AM
From: IAN HENDERSON
Subject: Data Module Joins
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:
Sent: Fri September 23, 2022 09:09 AM
From: Philipp Hornung
Subject: Data Module Joins
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:
Sent: Fri September 23, 2022 09:00 AM
From: Patrick Neveu
Subject: Data Module Joins
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
Original Message:
Sent: Fri September 23, 2022 08:51 AM
From: Philipp Hornung
Subject: Data Module Joins
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 witch the dimension? @IAN HENDERSON could you please give a quick idea?
------------------------------
Philipp Hornung
------------------------------
#CognosAnalyticswithWatson