Handling SCD2

[Sep2013]

Hi Experts,


I have designed two star models one for claim processing and another for payments processing.
They have their non conformed dimensions and some conformed dimensions.
One of the business questions is  (attached model)
1) Total payments made for claims that were in claim status of 'Open' status as of 31/6/2019.
The issue is that if a payment was made on 1/11/2019 where claim status was 'Open' however the claim status changed to 'Finalised'  on 5/11/2019, if i run the report as of 5/11 onwards, I will still get the claim information as of 1/11/2019 due to my claim surrogate key in payment fact.How do I ensure that my scd2 are also filtered for same selected date?

[CognosPaul]

Can you post the generated SQL? I'm a little confused how the model works.

It looks like the DIM_GID_CLAIM dim is the SCD, right? Are you using a between clause in the joins? Also, what happens if you change the join from DIM_GID_CLAIM to FACT_GID_CLAIM to 1..n? 1..1 indicates a snowflake or outrigger tables, which may affect how the SQL is generated.

[Sep2013]

Thanks you for getting bak to me. Please see generated SQL below:
SELECT
    "DIM_GID_CLAIM0"."RC_CLAIM_NO" AS "Claim_Number",
    "DIM_GID_CLAIM0"."STATUS" AS "Claim_Status",
    "DIM_GID_CLAIM0"."CLAIM_LOSS_CODE" AS "Claim_Loss_Code",
    "DIM_GID_CLAIM0"."INC_LOSS_CODE" AS "Loss_Code",
    "DIM_RC_PAYMENT_TYPE0"."PAY_TYPE" AS "Payment_Type",
    "FACT_RC_PAYMENTS0"."PAYMENT_NO" AS "Payment_Number",
    SUM("FACT_RC_PAYMENTS0"."PAID_AMT") AS "Paid_Amount",
    SUM("FACT_RC_PAYMENTS0"."GST_PAID") AS "GST_Paid_Amount",
    "FACT_RC_PAYMENTS0"."CERTIFIED_DATE" AS "Certified_Date"
FROM
    "DTMP_RC"."DIM_CALENDAR" "Snapshot_Calendar"
        INNER JOIN "DTMP_RC"."FACT_RC_PAYMENTS_5YEARS" "FACT_RC_PAYMENTS0"
        ON
            "Snapshot_Calendar"."CALENDAR_DATE" >= "FACT_RC_PAYMENTS0"."FROM_DATE" AND
            "Snapshot_Calendar"."CALENDAR_DATE" <= "FACT_RC_PAYMENTS0"."TO_DATE"
            INNER JOIN "DTMP_RC"."DIM_GID_CLAIM_5YEARS" "DIM_GID_CLAIM0"
            ON "FACT_RC_PAYMENTS0"."RC_CLAIM_NO" = "DIM_GID_CLAIM0"."RC_CLAIM_NO"
                INNER JOIN "DTMP_RC"."DIM_RC_PAYMENT_TYPE" "DIM_RC_PAYMENT_TYPE0"
                ON "FACT_RC_PAYMENTS0"."PAYMENT_TYPE_KEY" = "DIM_RC_PAYMENT_TYPE0"."RC_PAYMENT_TYPE_KEY"
                    INNER JOIN "DTMP_RC"."DIM_REPORT_CLASS" "DIM_REPORT_CLASS0"
                    ON "FACT_RC_PAYMENTS0"."REPORT_CLASS_KEY" = "DIM_REPORT_CLASS0"."REPORT_CLASS_KEY"
WHERE
    "DIM_REPORT_CLASS0"."REPORT_CLASS" <> 'Workers Compensation' AND
    "DIM_GID_CLAIM0"."CLAIM_LOSS" = 'HISTORICAL SEXUAL ABUSE' AND
    "DIM_GID_CLAIM0"."RC_CLAIM_NO" = '17/008173-01' AND
    "FACT_RC_PAYMENTS0"."PAYMENT_NO" = 1 AND
    "Snapshot_Calendar"."CALENDAR_DATE" = {d '2020-04-23'}
GROUP BY
    "DIM_GID_CLAIM0"."RC_CLAIM_NO",
    "DIM_GID_CLAIM0"."STATUS",
    "DIM_GID_CLAIM0"."CLAIM_LOSS_CODE",
    "DIM_GID_CLAIM0"."INC_LOSS_CODE",
    "DIM_RC_PAYMENT_TYPE0"."PAY_TYPE",
    "FACT_RC_PAYMENTS0"."PAYMENT_NO",
    "FACT_RC_PAYMENTS0"."CERTIFIED_DATE"

[CognosPaul]

What happens if you change the join from DIM_GID_CLAIM to FACT_GID_CLAIM to 1..n?