Multifact Query using column sc in the join

[akshara_a]

Hi,

I am trying to create a report using two fact tables. I have attached the model. Below is the query cognos is generating.
I am looking for a proper stitched query on the columns from confirmed dimension CLIENT_D and without the sc column. Can you please help me figure out what I am missing.

WITH D1 AS
(SELECT
T0.C1 AS "CLIENT NAME",
T0.C2 AS "DATE OF BIRTH",
T0.C3 AS "SSN",
"T0"."C4" AS "PO DATE"
T0.C4 AS "PO$",
SUM("T0"."C6") over (partition BY "T0"."C1", "T0"."C2", "T0"."C3" order by  "T0"."C1" ASC, "T0"."C2" ASC, "T0"."C3","T0"."C4" ASC rows unbounded preceding) "sc"
(SELECT
CLIENT_D.NAME C1,
CLIENT_D.SSN C2,
CLIENT_D.DOB C3,
FACT2.PO_DATE C4,
PO$ C5,
1 C6
FROM
FACT2,
CLIENT_D
WHERE FACT2.CLIENT_ID = CLIENT_D.CLIENT_ID
GROUP BY
CLIENT_D.NAME,
CLIENT_D.SSN,
CLIENT_D.DOB) T0),

WITH D2 AS
(SELECT
T0.C1 AS "CLIENT NAME",
T0.C2 AS "DATE OF BIRTH",
T0.C3 AS "SSN",
"T0"."C4" AS "REG START DATE"
T0.C4 AS "Sales$",
SUM("T0"."C6") over (partition BY "T0"."C1", "T0"."C2", "T0"."C3" order by  "T0"."C1" ASC, "T0"."C2" ASC, "T0"."C3","T0"."C4" ASC rows unbounded preceding) "sc"
(SELECT
CLIENT_D.NAME C1,
CLIENT_D.SSN C2,
CLIENT_D.DOB C3,
REGISTRATION_START_DATE.REGISTRATION_START_DT C4,
PO$ C5,
1 C6
FROM
FACT2,
CLIENT_D,
REGISTRATION_START_DATE
WHERE FACT2.CLIENT_ID = CLIENT_D.CLIENT_ID
AND FACT2.REG_DSG_KEY = REGISTRATION_START_DATE.DATE_KEY
GROUP BY
CLIENT_D.NAME,
CLIENT_D.SSN,
CLIENT_D.DOB,
REGISTRATION_START_DATE.REGISTRATION_START_DT) T0)

SELECT (COALESCE("D1"."CLIENT NAME", "D2"."CLIENT NAME")) "CLIENT NAME",
  "D2"."REG START DATE" "REG START DATE",
   (COALESCE("D1"."DATE OF BIRTH", "D3"."DATE OF BIRTH")) "DATE OF BIRTH",
  (COALESCE("D1"."SSN", "D3"."SSN")) "SSN",
  "D1"."PO DATE" "PO DATE",
  "D1"."PO$" "PO$",
  "D2"."Sale$" "Sale$"
FROM "D3"
FULL OUTER JOIN "D"
ON "D3"."CLIENT NAME" ="D"."CLIENT NAME"
AND "D3"."DATE OF BIRTH"="D"."DATE OF BIRTH"
AND "D3"."SSN"          ="D"."SSN"
AND "D3"."sc"           ="D"."sc"

[Lynn]

It looks like you are using registration start date in the query which is only conformed to fact1, not fact2.

[akshara_a]

Thanks for your response.
My understanding is one confirmed dimension (CLIENT_D in this case) is sufficient to get correct stitched query.
Please correct me if I am wrong.

[MFGF]

Hi,

I am trying to create a report using two fact tables. I have attached the model. Below is the query cognos is generating.
I am looking for a proper stitched query on the columns from confirmed dimension CLIENT_D and without the sc column. Can you please help me figure out what I am missing.

WITH D1 AS
(SELECT
T0.C1 AS "CLIENT NAME",
T0.C2 AS "DATE OF BIRTH",
T0.C3 AS "SSN",
"T0"."C4" AS "PO DATE"
T0.C4 AS "PO$",
SUM("T0"."C6") over (partition BY "T0"."C1", "T0"."C2", "T0"."C3" order by  "T0"."C1" ASC, "T0"."C2" ASC, "T0"."C3","T0"."C4" ASC rows unbounded preceding) "sc"
(SELECT
CLIENT_D.NAME C1,
CLIENT_D.SSN C2,
CLIENT_D.DOB C3,
FACT2.PO_DATE C4,
PO$ C5,
1 C6
FROM
FACT2,
CLIENT_D
WHERE FACT2.CLIENT_ID = CLIENT_D.CLIENT_ID
GROUP BY
CLIENT_D.NAME,
CLIENT_D.SSN,
CLIENT_D.DOB) T0),

WITH D2 AS
(SELECT
T0.C1 AS "CLIENT NAME",
T0.C2 AS "DATE OF BIRTH",
T0.C3 AS "SSN",
"T0"."C4" AS "REG START DATE"
T0.C4 AS "Sales$",
SUM("T0"."C6") over (partition BY "T0"."C1", "T0"."C2", "T0"."C3" order by  "T0"."C1" ASC, "T0"."C2" ASC, "T0"."C3","T0"."C4" ASC rows unbounded preceding) "sc"
(SELECT
CLIENT_D.NAME C1,
CLIENT_D.SSN C2,
CLIENT_D.DOB C3,
REGISTRATION_START_DATE.REGISTRATION_START_DT C4,
PO$ C5,
1 C6
FROM
FACT2,
CLIENT_D,
REGISTRATION_START_DATE
WHERE FACT2.CLIENT_ID = CLIENT_D.CLIENT_ID
AND FACT2.REG_DSG_KEY = REGISTRATION_START_DATE.DATE_KEY
GROUP BY
CLIENT_D.NAME,
CLIENT_D.SSN,
CLIENT_D.DOB,
REGISTRATION_START_DATE.REGISTRATION_START_DT) T0)

SELECT (COALESCE("D1"."CLIENT NAME", "D2"."CLIENT NAME")) "CLIENT NAME",
  "D2"."REG START DATE" "REG START DATE",
   (COALESCE("D1"."DATE OF BIRTH", "D3"."DATE OF BIRTH")) "DATE OF BIRTH",
  (COALESCE("D1"."SSN", "D3"."SSN")) "SSN",
  "D1"."PO DATE" "PO DATE",
  "D1"."PO$" "PO$",
  "D2"."Sale$" "Sale$"
FROM "D3"
FULL OUTER JOIN "D"
ON "D3"."CLIENT NAME" ="D"."CLIENT NAME"
AND "D3"."DATE OF BIRTH"="D"."DATE OF BIRTH"
AND "D3"."SSN"          ="D"."SSN"
AND "D3"."sc"           ="D"."sc"

Hi,

Wow! Where to start? I'm trying to unpick the SQL you posted, and it makes no sense. Is it possible you pasted it in incorrectly?

For example, how can this make sense in the code for D1...

Code Select Expand


"T0"."C4" AS "PO DATE"
T0.C4 AS "PO$",


Where is the comma after PO DATE? How can column C4 be both PO Date and PO$?

Also in the code for D2:

Code Select Expand


"T0"."C4" AS "REG START DATE"
T0.C4 AS "Sales$",


The same thing here. It's not legal syntax and could not run. Can you explain?

Then lower down:

Code Select Expand


FROM "D3"


There's nothing anywhere in the SQL you posted that defines what D3 is.

The entire set of SQL posted here is incorrect, and can never and would never parse and execute.

One other thing that appears to be an issue, too. You appear to be bringing in PO_DATE from Fact2. This should be an item in a dimension query subject, and not coming from a fact query subject.

Also, the SQL (albeit wonky SQL) seems to show that you have a relationship defined between REGISTRATION_START_DATE and Fact2, based on DATE_KEY = REG_DSG_KEY. Your posted image doesn't show this relationship, though.

Bottom line is this doesn't add up. The SQL is incorrect and the diagram is incorrect. I'm not sure how to advise you, other than to suggest you post up an accurate representation of what is really there?

Cheers!

MF.

[Lynn]

Thanks for your response.
My understanding is one confirmed dimension (CLIENT_D in this case) is sufficient to get correct stitched query.
Please correct me if I am wrong.

I agree with all of MFGF's comments. I assume you've pasted the SQL incorrectly.

A multi-fact query will generate a stitch column when you use an element from a non-conformed dimension. It cannot show you information about fact2 by registration start date because it has no relationship. Based on MFGF's analysis of the SQL, you've got some sort of relationship defined but your model does not indicate that.

You should be able to use elements from client_D since it is conformed to both facts, but if your model is not representative of the actual situation then it is difficult to say for sure which end is up.

[akshara_a]

MFGF..My apologies for the incorrect SQL. I tried to rewrite the SQL as original query has too many sub queries in it. I corrected my query now.  It is still not taken directly from Cognos. But I rechecked it twice to resemble cognos query. I still have PO_DATE being taken from FACT2. But can you please clarify if that can be the cause to adding sc column in the stitched query.

WITH D1 AS
(SELECT
T0.C1 AS "CLIENT NAME",
T0.C2 AS "DATE OF BIRTH",
T0.C3 AS "SSN",
"T0"."C4" AS "PO DATE",
T0.C5 AS "PO$",
SUM("T0"."C6") over (partition BY "T0"."C1", "T0"."C2", "T0"."C3" order by  "T0"."C1" ASC, "T0"."C2" ASC, "T0"."C3" ASC,"T0"."C4" ASC rows unbounded preceding) "sc"
from
(SELECT
CLIENT_D.NAME C1,
CLIENT_D.SSN C2,
CLIENT_D.DOB C3,
FACT2.PO_DATE C4,
PO$ C5,
1 C6
FROM
FACT2,
CLIENT_D
WHERE FACT2.CLIENT_ID = CLIENT_D.CLIENT_ID
GROUP BY
CLIENT_D.NAME,
CLIENT_D.SSN,
CLIENT_D.DOB,
FACT2.PO_DATE) T0),

D2 AS
(SELECT
T0.C1 AS "CLIENT NAME",
T0.C2 AS "DATE OF BIRTH",
T0.C3 AS "SSN",
"T0"."C4" AS "REG START DATE",
T0.C5 AS "Sales$",
SUM("T0"."C6") over (partition BY "T0"."C1", "T0"."C2", "T0"."C3" order by  "T0"."C1" ASC, "T0"."C2" ASC, "T0"."C3","T0"."C4" ASC rows unbounded preceding) "sc"
from
(SELECT
CLIENT_D.NAME C1,
CLIENT_D.SSN C2,
CLIENT_D.DOB C3,
REGISTRATION_START_DATE.REGISTRATION_START_DT C4,
Sales$ C5,
1 C6
FROM
FACT1,
CLIENT_D,
REGISTRATION_START_DATE
WHERE FACT1.CLIENT_ID = CLIENT_D.CLIENT_ID
AND FACT1.REG_DSG_KEY = REGISTRATION_START_DATE.DATE_KEY
GROUP BY
CLIENT_D.NAME,
CLIENT_D.SSN,
CLIENT_D.DOB,
REGISTRATION_START_DATE.REGISTRATION_START_DT) T0)

SELECT (COALESCE("D1"."CLIENT NAME", "D2"."CLIENT NAME")) "CLIENT NAME",
  "D2"."REG START DATE" "REG START DATE",
   (COALESCE("D1"."DATE OF BIRTH", "D2"."DATE OF BIRTH")) "DATE OF BIRTH",
  (COALESCE("D1"."SSN", "D3"."SSN")) "SSN",
  "D1"."PO DATE" "PO DATE",
  "D1"."PO$" "PO$",
  "D2"."Sale$" "Sale$"
FROM "D2"
FULL OUTER JOIN "D1"
ON "D2"."CLIENT NAME" ="D1"."CLIENT NAME"
AND "D2"."DATE OF BIRTH"="D1"."DATE OF BIRTH"
AND "D2"."SSN"          ="D1"."SSN"
AND "D2"."sc"           ="D1"."sc"

[MFGF]

Hi,

If you want an accurate, reliable verdict on the query, you need to post all of it, regardless of complexity. Can you guarantee you haven't chopped out something important? If so, that means you must have in-depth knowledge of how stitch queries operate, in which case I doubt you'd be posting here asking for help with it?

To answer your point above, it's highly likely you are getting strange results by bringing in attributes from a fact query subject on one side of a query split. As Lynn alluded to, your attributes should be sourced from dimension query subjects, ideally conformed dimensions linking to all facts where possible.

Cheers!

MF.


Sent from my iPhone using Tapatalk

[akshara_a]

I have removed attributes from fact query and moved them to a dimension. Please find the attached model and SQL. I have taken them exactly from cognos FM and cognos report studio. I am still seeing sc column in the stitched query. Can you please look into it and help me on how to avoid the sc column.

WITH "CLIENT_D4" AS
  (SELECT *
  FROM "PEI_RDM"."CLIENT_D"
  WHERE "IND_DLTD"  ='N'
  AND "IND_CURR_ROW"='Y'
  ),
  "TJJD_PERSON_MATCH_F5" AS
  (SELECT "TJJD_PERSON_MATCH_F"."SK",
    "TJJD_PERSON_MATCH_F"."SEND_ID_CLNT"
  FROM "PEI_RDM"."TJJD_PERSON_MATCH_F"
  ),
  "TJJD_PERSON_MATCH_D" AS
  (SELECT "TJJD_PERSON_MATCH_F"."SK",
    "TJJD_PERSON_MATCH_F"."MATCH_REFRL_NBR",
    "TJJD_PERSON_MATCH_F"."MATCH_REFRL_TYP",
    "TJJD_PERSON_MATCH_F"."MATCH_REFRL_DT",
    "TJJD_PERSON_MATCH_F"."MATCH_REFRL_COUNTY"
  FROM "PEI_RDM"."TJJD_PERSON_MATCH_F"
  ),
  "D" AS
  (SELECT "T0"."C0" "Client_ID",
    "T0"."C1" "First_Name",
    "T0"."C2" "Middle_Name",
    "T0"."C3" "SSN",
    "T0"."C4" "Date_of_Birth",
    "T0"."C5" "Total_Receive_Clients",
    "T0"."C6" "Match_Referral_Number",
    "T0"."C7" "Match_Referral_Type",
    "T0"."C8" "Match_Referral_Date",
    "T0"."C9" "Match_Referral_County",
    SUM("T0"."C10") over (partition BY "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C3", "T0"."C4" order by "T0"."C0" ASC, "T0"."C1" ASC, "T0"."C2" ASC, "T0"."C3" ASC, "T0"."C4" ASC, "T0"."C6" ASC, "T0"."C7" ASC, "T0"."C8" ASC, "T0"."C9" ASC rows unbounded preceding) "sc"
  FROM
    (SELECT "CLIENT_D4"."ID_CLNT" "C0",
      "CLIENT_D4"."NM_FIRST" "C1",
      "CLIENT_D4"."NM_MID" "C2",
      "CLIENT_D4"."SSN" "C3",
      "CLIENT_D4"."DT_DOB" "C4",
      COUNT(DISTINCT "TJJD_PERSON_MATCH_F5"."SEND_ID_CLNT") "C5",
      "TJJD_PERSON_MATCH_D"."MATCH_REFRL_NBR" "C6",
      "TJJD_PERSON_MATCH_D"."MATCH_REFRL_TYP" "C7",
      "TJJD_PERSON_MATCH_D"."MATCH_REFRL_DT" "C8",
      "TJJD_PERSON_MATCH_D"."MATCH_REFRL_COUNTY" "C9",
      1 "C10"
    FROM "CLIENT_D4",
      "TJJD_PERSON_MATCH_F5",
      "TJJD_PERSON_MATCH_D"
    WHERE "TJJD_PERSON_MATCH_F5"."SEND_ID_CLNT"="CLIENT_D4"."ID_CLNT"
    AND "TJJD_PERSON_MATCH_D"."SK"             ="TJJD_PERSON_MATCH_F5"."SK"
    GROUP BY "CLIENT_D4"."ID_CLNT",
      "CLIENT_D4"."NM_FIRST",
      "CLIENT_D4"."NM_MID",
      "CLIENT_D4"."SSN",
      "CLIENT_D4"."DT_DOB",
      "TJJD_PERSON_MATCH_D"."MATCH_REFRL_NBR",
      "TJJD_PERSON_MATCH_D"."MATCH_REFRL_TYP",
      "TJJD_PERSON_MATCH_D"."MATCH_REFRL_DT",
      "TJJD_PERSON_MATCH_D"."MATCH_REFRL_COUNTY"
    ) "T0"
  ),
  "REGISTRATION_D9" AS
  (SELECT *
  FROM "PEI_RDM"."REGISTRATION_D"
  WHERE "IND_DLTD"  ='N'
  AND "IND_CURR_ROW"='Y'
  ),
  "D3" AS
  (SELECT "T0"."C0" "Client_ID",
    "T0"."C1" "First_Name",
    "T0"."C2" "Middle_Name",
    "T0"."C3" "SSN",
    "T0"."C4" "Date_of_Birth",
    "T0"."C5" "Registration_Start_Date",
    "T0"."C6" "Registration_Discharge_Date",
    "T0"."C7" "Registration_ID",
    "T0"."C8" "Total_Send_Clients",
    SUM("T0"."C9") over (partition BY "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C3", "T0"."C4" order by "T0"."C0" ASC, "T0"."C1" ASC, "T0"."C2" ASC, "T0"."C3" ASC, "T0"."C4" ASC, "T0"."C5" ASC, "T0"."C6" ASC, "T0"."C7" ASC rows unbounded preceding) "sc"
  FROM
    (SELECT "CLIENT_D4"."ID_CLNT" "C0",
      "CLIENT_D4"."NM_FIRST" "C1",
      "CLIENT_D4"."NM_MID" "C2",
      "CLIENT_D4"."SSN" "C3",
      "CLIENT_D4"."DT_DOB" "C4",
      "Registration_Start_Date"."CALENDAR_DATE" "C5",
      "Registration_Discharge_Date"."CALENDAR_DATE" "C6",
      "REGISTRATION_D9"."ID_REGSTRTN" "C7",
      COUNT(DISTINCT "TJJD_SEND_F"."SEND_ID_CLNT") "C8",
      1 "C9"
    FROM "CLIENT_D4",
      "PEI_RDM"."DATE_D" "Registration_Start_Date",
      "PEI_RDM"."DATE_D" "Registration_Discharge_Date",
      "REGISTRATION_D9",
      "PEI_RDM"."TJJD_SEND_F" "TJJD_SEND_F"
    WHERE "TJJD_SEND_F"."SEND_ID_CLNT"   ="CLIENT_D4"."ID_CLNT"
    AND "TJJD_SEND_F"."ID_REGSTRTN"      ="REGISTRATION_D9"."ID_REGSTRTN"
    AND "TJJD_SEND_F"."DT_REG_START_KEY" ="Registration_Start_Date"."DATE_KEY"
    AND "TJJD_SEND_F"."DT_REG_DSCHRG_KEY"="Registration_Discharge_Date"."DATE_KEY"
    GROUP BY "CLIENT_D4"."ID_CLNT",
      "CLIENT_D4"."NM_FIRST",
      "CLIENT_D4"."NM_MID",
      "CLIENT_D4"."SSN",
      "CLIENT_D4"."DT_DOB",
      "Registration_Start_Date"."CALENDAR_DATE",
      "Registration_Discharge_Date"."CALENDAR_DATE",
      "REGISTRATION_D9"."ID_REGSTRTN"
    ) "T0"
  )
SELECT (COALESCE("D"."Client_ID", "D3"."Client_ID")) "Client_ID",
  (COALESCE("D"."First_Name", "D3"."First_Name")) "First_Name",
  (COALESCE("D"."Middle_Name", "D3"."Middle_Name")) "Middle_Name",
  (COALESCE("D"."SSN", "D3"."SSN")) "SSN",
  (COALESCE("D"."Date_of_Birth", "D3"."Date_of_Birth")) "Date_of_Birth",
  "D3"."Total_Send_Clients" "Total_Send_Clients",
  "D"."Total_Receive_Clients" "Total_Receive_Clients",
  "D3"."Registration_Start_Date" "Registration_Start_Date",
  "D3"."Registration_Discharge_Date" "Registration_Discharge_Date",
  "D3"."Registration_ID" "Registration_ID",
  "D"."Match_Referral_Number" "Match_Referral_Number",
  "D"."Match_Referral_Type" "Match_Referral_Type",
  "D"."Match_Referral_Date" "Match_Referral_Date",
  "D"."Match_Referral_County" "Match_Referral_County"
FROM "D3"
FULL OUTER JOIN "D"
ON "D3"."Client_ID"     ="D"."Client_ID"
AND "D3"."First_Name"   ="D"."First_Name"
AND "D3"."Middle_Name"  ="D"."Middle_Name"
AND "D3"."SSN"          ="D"."SSN"
AND "D3"."Date_of_Birth"="D"."Date_of_Birth"
AND "D3"."sc"           ="D"."sc"

[tjohnson3050]

Usually a stitch query is used to pull together numeric measures from two facts tables, and compare those numbers on a conformed dimension.

It looks like you are using TJJD_Person_Match_F as a bridge table, not a fact table.  You are not trying to pull any measures from that fact table.

If that is the case, see the attached document on fact tables acting as bridge tables in Cognos.

[MFGF]

Usually a stitch query is used to pull together numeric measures from two facts tables, and compare those numbers on a conformed dimension.

It looks like you are using TJJD_Person_Match_F as a bridge table, not a fact table.  You are not trying to pull any measures from that fact table.

If that is the case, see the attached document on fact tables acting as bridge tables in Cognos.

Hi,

Looking at the SQL, I can see the two halves of the stitch are as follows:

CLIENT_D, TJJD_PERSON_MATCH_D and TJJD_PERSON_MATCH_F on one side

CLIENT_D, REGISTRATION_D, DATE_D (Registration_Start_Date), DATE_D (Registration_Discharge_Date) and TJJD_SEND_F on the other side.

You can see that you are only using one conformed dimension - CLIENT_D

This means that on one side you are using non-conformed dimension TJJD_PERSON_MATCH_D and on the other you are using non-conformed dimensions REGISTRATION_D, Registration_Start_Date and Registration_Discharge_Date.

As you only have one conformed dimension (CLIENT_D), this means you will have the possibility of multiple rows being returned each side of the stitch for each client:

On one side, a client could have multiple rows with different MATCH_REFRL_NBR (Match_Referral_Number) values, for example.
On the other side, a client could have multiple rows with different ID_REGSTRTN (Registration_ID), Registration_Start_Date and Registration_Discharge_Date values.

This means your stitch can't just sum up the facts on either side to a single value for each client. So what is the only other thing it can do?

For each client it will add an ascending count to the rows returned on each side. One side might have two rows and the other seven, for example - driven by the combinations of values found for the client facts in the non-conformed dimensions. It then uses these counts to stack the rows in the result set, so the counts 1 and 1 from each side of the stitch appear next to each other, 2 and 2 the same, then 3,4,5,6 and 7 have no opposite rows on the other side. The fact values on each side are related to the conformed dimension but not necessarily to each other - they just have differing values of the non-conformed dimensions.

Is this a problem? No - not at all. Cognos is doing exactly as it should, based on the model and the data you requested. The presence of non-conformed dimensions is driving the generation of the sc counts for each side.

If you have a stitch query that uses only conformed dimensions, it returns a contiguous result set. A stitch query that has conformed and non-conformed dimensions present returns a correlated list. This is what you should be seeing here.

Are you seeing unexpected results? Something that appears incorrect? Why do you believe the presence of the sc column is a problem?

Cheers!

MF.