Good day,
I have a very simple model with 3 tables (using Oracle), Dim1, Dim2, Fact1. Dim1 and Fact 1 have a 1..n relationship, so do Dim2 and Fact 1. I've created a Physical Layer, a Logical Layer and a Dimensional Layer of the 3 objects. I defined the relationships in the Physical Layer. In the Dimensional model, Dim1 and Dim 2 do not have hierarchies, just one level (I just have dates, I didn't bother bringing Months and Years). Fact1 just have one measure with a default aggregation set to Sum. Other than using DQM, everything else is using the default Cognos settings.
When I create a report, something unexpected happens. Instead of just creating a simple query (select data_items from Dim1, Dim2, Fact1 where Fact1.col1=Dim1.col1 and Fact1.col2=Dim2.col2) with the three tables to retrieve the data, Cognos first do a "select * from Dim1", then do the query above with the 3 tables to retrieve the data. I can only assume it does some sort of stitching because the last query is resolved in 1 sec in Oracle but takes 5 minutes in Cognos.
Troubleshooting the issue, I used objects from the Physical Layer to create a report and got the expected simple query. Same behavior using the Logical Layer. It's only when I'm using the Dimensional objects that things get wonky.
What am I missing or what have I not set properly in Cognos?
Thanks for your answers in advance!
Quote from: flag_2004 on 16 Dec 2022 07:45:19 AM
Good day,
I have a very simple model with 3 tables (using Oracle), Dim1, Dim2, Fact1. Dim1 and Fact 1 have a 1..n relationship, so do Dim2 and Fact 1. I've created a Physical Layer, a Logical Layer and a Dimensional Layer of the 3 objects. I defined the relationships in the Physical Layer. In the Dimensional model, Dim1 and Dim 2 do not have hierarchies, just one level (I just have dates, I didn't bother bringing Months and Years). Fact1 just have one measure with a default aggregation set to Sum. Other than using DQM, everything else is using the default Cognos settings.
When I create a report, something unexpected happens. Instead of just creating a simple query (select data_items from Dim1, Dim2, Fact1 where Fact1.col1=Dim1.col1 and Fact1.col2=Dim2.col2) with the three tables to retrieve the data, Cognos first do a "select * from Dim1", then do the query above with the 3 tables to retrieve the data. I can only assume it does some sort of stitching because the last query is resolved in 1 sec in Oracle but takes 5 minutes in Cognos.
Troubleshooting the issue, I used objects from the Physical Layer to create a report and got the expected simple query. Same behavior using the Logical Layer. It's only when I'm using the Dimensional objects that things get wonky.
What am I missing or what have I not set properly in Cognos?
Thanks for your answers in advance!
Hi,
If you're getting a stitch query, the things to look out for are the FULL OUTER JOIN and (usually) the COALESCE statements in the Cognos SQL. Can you confirm you are seeing these in the queries? That will tell us if it really is an unwanted query split (Stitch Query) or something else.
When you refer to the Dimensional Layer, do you mean you have built DMR objects in this layer based on the query subjects in the Logical Layer? (ie Regular Dimensions and a Measure Dimension)? If so, have you verified that the scope relationships are set up correctly between these DMR objects?
Cheers!
MF.
Quote from: MFGF on 16 Dec 2022 11:56:39 AM
Hi,
If you're getting a stitch query, the things to look out for are the FULL OUTER JOIN and (usually) the COALESCE statements in the Cognos SQL. Can you confirm you are seeing these in the queries? That will tell us if it really is an unwanted query split (Stitch Query) or something else.
When you refer to the Dimensional Layer, do you mean you have built DMR objects in this layer based on the query subjects in the Logical Layer? (ie Regular Dimensions and a Measure Dimension)? If so, have you verified that the scope relationships are set up correctly between these DMR objects?
Cheers!
MF.
Hi MF,
No outer joins nor COALESCE in the query. Here is the actual SQL generated;
SELECT DISTINCT
"INTERACTION_CASE_DIM"."INTERACTION_CASE_ID" AS "column0",
"INTERACTION_CASE_DIM"."SUMMARY" AS "column1",
"INTERACTION_CASE_DIM"."SLA" AS "column2"
FROM "WORKLOAD_DB"."INTERACTION_CASE_DIM" "INTERACTION_CASE_DIM"
ORDER BY "column1" ASC NULLS LAST, "column0" ASC NULLS LAST;
SELECT "CALENDAR_DIM"."YEAR_NO" AS "column0",
"CALENDAR_DIM"."MONTH_NO" AS "column1",
"INTERACTION_CASE_DIM"."INTERACTION_CASE_ID" AS "column2",
SUM ("INTERACTION_CASE_CHANGE_HISTORY_FACT"."TIME_DIFF") AS "column3"
FROM "WORKLOAD_DB"."CALENDAR_DIM" "CALENDAR_DIM"
INNER JOIN
"WORKLOAD_DB"."INTERACTION_CASE_CHANGE_HISTORY_FACT"
"INTERACTION_CASE_CHANGE_HISTORY_FACT"
ON "CALENDAR_DIM"."CALENDAR_DT" =
"INTERACTION_CASE_CHANGE_HISTORY_FACT"."CHANGE_DT"
INNER JOIN
"WORKLOAD_DB"."INTERACTION_CASE_DIM" "INTERACTION_CASE_DIM"
ON "INTERACTION_CASE_CHANGE_HISTORY_FACT"."INTERACTION_CASE_ID" =
"INTERACTION_CASE_DIM"."INTERACTION_CASE_ID"
WHERE "CALENDAR_DIM"."YEAR_NO" = 2022 AND "CALENDAR_DIM"."MONTH_NO" = 11
GROUP BY "CALENDAR_DIM"."YEAR_NO",
"CALENDAR_DIM"."MONTH_NO",
"INTERACTION_CASE_DIM"."INTERACTION_CASE_ID";Second query I'm expecting... The first one? No idea.
In the Native SQL, I'm noticing the word CROSSJOIN... I've never paid much attention to this SQL as I don't understand it, but it seems weird to perform a CROSSJOIN to me given I only have one-to-many relationships in the model;
SELECT
NON EMPTY CROSSJOIN([Presentation Layer_Calendar Dimension].[Calendar Dates].[Months].MEMBERS, CROSSJOIN([Presentation Layer_Interaction Cases Dimension].[Interaction Cases].[Interaction Cases].MEMBERS, {[Measures].[Measures].[Presentation Layer_Interaction Case Processing Time_Interaction Case Processing Time]})) DIMENSION PROPERTIES PARENT_LEVEL, PARENT_UNIQUE_NAME, [Presentation Layer_Calendar Dimension].[Calendar Dates].[Months].[Month No], [Presentation Layer_Calendar Dimension].[Calendar Dates].[Years].[Year No], [Presentation Layer_Interaction Cases Dimension].[Interaction Cases].[Interaction Cases].[Interaction Case ID], [Presentation Layer_Interaction Cases Dimension].[Interaction Cases].[Interaction Cases].[Interaction Case SLA] ON AXIS(0)
FROM [WORKLOAD_DB] CELL PROPERTIES CELL_ORDINAL, FORMAT_STRING, VALUEYes, I meant the Regular Dimensions and Measure Dimensions objects. The scope has been set properly, both Regular Dimensions are in scope for the Measure Dimension used in this example.
Thanks!
F.
Quote from: flag_2004 on 16 Dec 2022 12:47:44 PM
Hi MF,
No outer joins nor COALESCE in the query. Here is the actual SQL generated;
SELECT DISTINCT
"INTERACTION_CASE_DIM"."INTERACTION_CASE_ID" AS "column0",
"INTERACTION_CASE_DIM"."SUMMARY" AS "column1",
"INTERACTION_CASE_DIM"."SLA" AS "column2"
FROM "WORKLOAD_DB"."INTERACTION_CASE_DIM" "INTERACTION_CASE_DIM"
ORDER BY "column1" ASC NULLS LAST, "column0" ASC NULLS LAST;
SELECT "CALENDAR_DIM"."YEAR_NO" AS "column0",
"CALENDAR_DIM"."MONTH_NO" AS "column1",
"INTERACTION_CASE_DIM"."INTERACTION_CASE_ID" AS "column2",
SUM ("INTERACTION_CASE_CHANGE_HISTORY_FACT"."TIME_DIFF") AS "column3"
FROM "WORKLOAD_DB"."CALENDAR_DIM" "CALENDAR_DIM"
INNER JOIN
"WORKLOAD_DB"."INTERACTION_CASE_CHANGE_HISTORY_FACT"
"INTERACTION_CASE_CHANGE_HISTORY_FACT"
ON "CALENDAR_DIM"."CALENDAR_DT" =
"INTERACTION_CASE_CHANGE_HISTORY_FACT"."CHANGE_DT"
INNER JOIN
"WORKLOAD_DB"."INTERACTION_CASE_DIM" "INTERACTION_CASE_DIM"
ON "INTERACTION_CASE_CHANGE_HISTORY_FACT"."INTERACTION_CASE_ID" =
"INTERACTION_CASE_DIM"."INTERACTION_CASE_ID"
WHERE "CALENDAR_DIM"."YEAR_NO" = 2022 AND "CALENDAR_DIM"."MONTH_NO" = 11
GROUP BY "CALENDAR_DIM"."YEAR_NO",
"CALENDAR_DIM"."MONTH_NO",
"INTERACTION_CASE_DIM"."INTERACTION_CASE_ID";
Second query I'm expecting... The first one? No idea.
In the Native SQL, I'm noticing the word CROSSJOIN... I've never paid much attention to this SQL as I don't understand it, but it seems weird to perform a CROSSJOIN to me given I only have one-to-many relationships in the model;
SELECT
NON EMPTY CROSSJOIN([Presentation Layer_Calendar Dimension].[Calendar Dates].[Months].MEMBERS, CROSSJOIN([Presentation Layer_Interaction Cases Dimension].[Interaction Cases].[Interaction Cases].MEMBERS, {[Measures].[Measures].[Presentation Layer_Interaction Case Processing Time_Interaction Case Processing Time]})) DIMENSION PROPERTIES PARENT_LEVEL, PARENT_UNIQUE_NAME, [Presentation Layer_Calendar Dimension].[Calendar Dates].[Months].[Month No], [Presentation Layer_Calendar Dimension].[Calendar Dates].[Years].[Year No], [Presentation Layer_Interaction Cases Dimension].[Interaction Cases].[Interaction Cases].[Interaction Case ID], [Presentation Layer_Interaction Cases Dimension].[Interaction Cases].[Interaction Cases].[Interaction Case SLA] ON AXIS(0)
FROM [WORKLOAD_DB] CELL PROPERTIES CELL_ORDINAL, FORMAT_STRING, VALUE
Yes, I meant the Regular Dimensions and Measure Dimensions objects. The scope has been set properly, both Regular Dimensions are in scope for the Measure Dimension used in this example.
Thanks!
F.
Hi,
That's definitely not a stitch query. Weird! It looks to me like the relationship between INTERACTION_CASE_DIM and INTERACTION_CASE_CHANGE_HISTORY_FACT is being ignored for some reason. Is there anything odd-looking about the relationship between these two query subjects in the Physical Layer? Any weird determinants on the dimension query subject?
MF.
Quote from: MFGF on 16 Dec 2022 01:16:54 PM
Hi,
That's definitely not a stitch query. Weird! It looks to me like the relationship between INTERACTION_CASE_DIM and INTERACTION_CASE_CHANGE_HISTORY_FACT is being ignored for some reason. Is there anything odd-looking about the relationship between these two query subjects in the Physical Layer? Any weird determinants on the dimension query subject?
MF.
Nothing weird, no... Nothing different from CALENDAR_DIM either.
I played around with determinants in INTERACTION_CASES_DIM, having one, having none, without success unfortunately.
I recreated the model (Thank God it's small) but get the same behavior
I'm currently exploring the possibility that the database may have a role to play. I'll keep you posted.
F.
Quote from: flag_2004 on 16 Dec 2022 01:59:19 PM
Nothing weird, no... Nothing different from CALENDAR_DIM either.
I played around with determinants in INTERACTION_CASES_DIM, having one, having none, without success unfortunately.
I recreated the model (Thank God it's small) but get the same behavior
I'm currently exploring the possibility that the database may have a role to play. I'll keep you posted.
F.
Just making sure I understand exactly what you're doing here. So you're authoring a report based on the package, and creating a crosstab using members from each dimension - one for rows and the other for columns - and the measures in cells?
How are you viewing the generated query - is this from the ellipsis on the toolbar > Show Generated SQL/MDX?
What version of Cognos are you using?
Cheers!
MF.
Quote from: MFGF on 19 Dec 2022 08:31:29 AM
Just making sure I understand exactly what you're doing here. So you're authoring a report based on the package, and creating a crosstab using members from each dimension - one for rows and the other for columns - and the measures in cells?
How are you viewing the generated query - is this from the ellipsis on the toolbar > Show Generated SQL/MDX?
What version of Cognos are you using?
Cheers!
MF.
Happy Monday!
Yes, I'm authoring a report based on the package, I'm creating a crosstab or a list, it shows the same behavior. Each contains a member from each dimension and a measure.
For the SQL/MDX, I used the ellipsis to get the MDX and I had to get the SQL from Oracle directly as Cognos wouldn't generate it for us.
We're using IBM Cognos Analytics 11.1.7
Regards.
Quote from: flag_2004 on 19 Dec 2022 08:46:28 AM
Happy Monday!
Yes, I'm authoring a report based on the package, I'm creating a crosstab or a list, it shows the same behavior. Each contains a member from each dimension and a measure.
For the SQL/MDX, I used the ellipsis to get the MDX and I had to get the SQL from Oracle directly as Cognos wouldn't generate it for us.
We're using IBM Cognos Analytics 11.1.7
Regards.
Do you have access to the old GO Sales (Analysis) sample package? Just wondering if you can replicate this using the sample data?
This is what I see in the MDX when creating a crosstab:
SELECT
NON EMPTY [Sales (analysis)_Time].[Time].[Year].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL, PARENT_UNIQUE_NAME ON AXIS(0),
NON EMPTY [Sales (analysis)_Products].[Products].[Product line].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL, PARENT_UNIQUE_NAME ON AXIS(1),
NON EMPTY {[Measures].[Measures].[Sales (analysis)_Sales_Quantity]} DIMENSION PROPERTIES PARENT_LEVEL, PARENT_UNIQUE_NAME ON AXIS(2)
FROM [go_sales] CELL PROPERTIES CELL_ORDINAL, FORMAT_STRING, VALUE
Was the MDX you posted also from a crosstab, or was it a list? I'd expect to see crossjoin syntax from a list but not from a crosstab?
Cheers!
MF.
Quote from: MFGF on 19 Dec 2022 09:29:40 AM
Do you have access to the old GO Sales (Analysis) sample package? Just wondering if you can replicate this using the sample data?
This is what I see in the MDX when creating a crosstab:
SELECT
NON EMPTY [Sales (analysis)_Time].[Time].[Year].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL, PARENT_UNIQUE_NAME ON AXIS(0),
NON EMPTY [Sales (analysis)_Products].[Products].[Product line].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL, PARENT_UNIQUE_NAME ON AXIS(1),
NON EMPTY {[Measures].[Measures].[Sales (analysis)_Sales_Quantity]} DIMENSION PROPERTIES PARENT_LEVEL, PARENT_UNIQUE_NAME ON AXIS(2)
FROM [go_sales] CELL PROPERTIES CELL_ORDINAL, FORMAT_STRING, VALUE
Was the MDX you posted also from a crosstab, or was it a list? I'd expect to see crossjoin syntax from a list but not from a crosstab?
Cheers!
MF.
The MDX was from a List, but the Crosstab onme is very similar
SELECT
NON EMPTY CROSSJOIN([Presentation Layer_Calendar Dimension].[Calendar Dates].[Months].MEMBERS,
CROSSJOIN([Presentation Layer_Interaction Cases Dimension].[Interaction Cases].[Interaction Cases].MEMBERS,
{[Measures].[Measures].[Presentation Layer_Interaction Case Processing Time_Interaction Case Processing Time]}))
DIMENSION PROPERTIES PARENT_LEVEL, PARENT_UNIQUE_NAME ON AXIS(0)
FROM [MCCS_WORKLOAD] CELL PROPERTIES CELL_ORDINAL, FORMAT_STRING, VALUENo Go Sales database available right now unfortunately.
I'll contact IBM shortly... Thanks for your help and I'll try to post the solution here once we know.
Happy holidays!
F.
Quote from: flag_2004 on 19 Dec 2022 10:22:53 AM
The MDX was from a List, but the Crosstab onme is very similar
SELECT
NON EMPTY CROSSJOIN([Presentation Layer_Calendar Dimension].[Calendar Dates].[Months].MEMBERS,
CROSSJOIN([Presentation Layer_Interaction Cases Dimension].[Interaction Cases].[Interaction Cases].MEMBERS,
{[Measures].[Measures].[Presentation Layer_Interaction Case Processing Time_Interaction Case Processing Time]}))
DIMENSION PROPERTIES PARENT_LEVEL, PARENT_UNIQUE_NAME ON AXIS(0)
FROM [MCCS_WORKLOAD] CELL PROPERTIES CELL_ORDINAL, FORMAT_STRING, VALUE
No Go Sales database available right now unfortunately.
I'll contact IBM shortly... Thanks for your help and I'll try to post the solution here once we know.
Happy holidays!
F.
I'd definitely be looking at the Scope Relationships in FM. Can you post a screen shot of what they look like (without divulging any sensitive data, of course)? You can't add images directly to a post, but you can add them to sites like imgbb or PhotoBucket and post the bb code to link to them.
Cheers!
MF.