SCD 2 and Fact Table

[Sep2013]

Hi Experts,

I have two models 1) Claim Model and 2) Payment Model
->Claim Dim and Claim Fact have effective from and effective to date as we record changes to a claim
-> Claim Dimension is also linked to payment fact (as attached picture) on natural keys
-> In FM when I fetch data from Dim Claimant, Fact Payment and Dim Payment and place a filter  of  2017-01-01 between effective from and effective to in order to select a slice of claimant dimension and payment date <=2017-01-01 it doesn't behave correctly
-> I expect to get details of the claim as of 2017-01-01 and all payments made by 2017-01-01.
-> FM query works as below, which means it ignores the SS selection and gets all snapshots of claim instead of on as of 2017-01-01:
     ( Select xx from dim_claimant where 2017-01-01 between effective from and effective to) D5
     FULL OUTER JOIN
     ( Select xx from dim_claimant,DIM_PAYMENTS a,FACT_PAYMENTS b where a.claimant_no=b.claimant_no payment_date<=2017-01-01 ) D6


How should I resolve this in my FM Model.

[MFGF]

Hi Experts,

I have two models 1) Claim Model and 2) Payment Model
->Claim Dim and Claim Fact have effective from and effective to date as we record changes to a claim
-> Claim Dimension is also linked to payment fact (as attached picture) on natural keys
-> In FM when I fetch data from Dim Claimant, Fact Payment and Dim Payment and place a filter  of  2017-01-01 between effective from and effective to in order to select a slice of claimant dimension and payment date <=2017-01-01 it doesn't behave correctly
-> I expect to get details of the claim as of 2017-01-01 and all payments made by 2017-01-01.
-> FM query works as below, which means it ignores the SS selection and gets all snapshots of claim instead of on as of 2017-01-01:
     ( Select xx from dim_claimant where 2017-01-01 between effective from and effective to) D5
     FULL OUTER JOIN
     ( Select xx from dim_claimant,DIM_PAYMENTS a,FACT_PAYMENTS b where a.claimant_no=b.claimant_no payment_date<=2017-01-01 ) D6


How should I resolve this in my FM Model.

Hi,

You appear to be getting an unwanted query split (ie a stitch query where it is not appropriate). This generally indicates a problem with the cardinalities in your model. A fact table should be at the 'n' end of all relationships, and a dimension table should be at the 1 end of at least one relationship used in the query. You haven't indicated the cardinalities in the image you posted - can you check they are correct?

MF.
MF.

[Sep2013]

Hi MF,

It does appear like a stitch query, but I cant think of a solution
Attached is FM model with cardinalities.

For now I have to have separate queries for claim and payments and then do an inner join in report studio.
I feel my model has some issues as below:
1) Connection of SCD 2 with Fact tables (fro fact claim its OK as it also joins to dim claimant on ss dates as a new fact is also created if claim details change otherwise I get outer join and all snapshots)
2) I cant select facts from multiple fact tables in same query as unwanted data is produced e..g No . of claims from fact claim, no. of briefs from fact brief, no. of writs from fact writ and paid amount from fact payments.

The only SCD2 in this model is dim claimant.

Below is the Cognos SQL whenI select data from Dim claimant .dim payment and fact payments, it can be seen as in bold text that ss date filter is applied to claimant but then full outer join ignores it:

with
Dim_Claimant7 as
    (select
           Dim_Claimant.FROM_DATE  as  FROM_DATE,
           Dim_Claimant.TO_DATE  as  TO_DATE,
           Dim_Claimant.CRASH_NO  as  CRASH_NO,
           Dim_Claimant.CLAIMANT_NAME_NO  as  CLAIMANT_NAME_NO,
           Dim_Claimant.ITEM_STATUS_CODE  as  ITEM_STATUS_CODE,
           Dim_Claimant.ITEM_STATUS  as  ITEM_STATUS
     from
           "Datamart Presentation Area"..DTMP_PSTATIC.DIM_CLAIMANT Dim_Claimant
     where
           (DATE '2017-09-30' between cast(Dim_Claimant.FROM_DATE as date) and cast(Dim_Claimant.TO_DATE as date))
    ),
D5 as
    (select
           Dim_Claimant7.ITEM_STATUS  as  Item_Status,
           Dim_Claimant7.CLAIMANT_NAME_NO  as  Claimant_Name_No,
           Dim_Claimant7.CRASH_NO  as  Crash_No,
           Dim_Claimant7.ITEM_STATUS_CODE  as  Item_Status_Code,
           Dim_Payments.CERTIFIED_DATE  as  Certified_Date,
           XSUM(Fact_Payments.PAID_AMOUNT  for Dim_Claimant7.ITEM_STATUS,Dim_Claimant7.CLAIMANT_NAME_NO,Dim_Claimant7.CRASH_NO,Dim_Claimant7.ITEM_STATUS_CODE,Dim_Payments.CERTIFIED_DATE )  as  Paid_Amount
     from
           Dim_Claimant7,
           "Datamart Presentation Area"..DTMP_PSTATIC.DIM_PAYMENTS Dim_Payments,
           "Datamart Presentation Area"..DTMP_PSTATIC.FACT_PAYMENTS Fact_Payments
     where
           (Dim_Claimant7.ITEM_STATUS_CODE = 'L') and
           ((Fact_Payments.CRASH_NO = Dim_Claimant7.CRASH_NO) and (Fact_Payments.CLAIMANT_NAME_NO = Dim_Claimant7.CLAIMANT_NAME_NO)) and
           (Fact_Payments.PAYMENT_KEY = Dim_Payments.PAYMENT_KEY)
     group by
           Dim_Claimant7.ITEM_STATUS,
           Dim_Claimant7.CLAIMANT_NAME_NO,
           Dim_Claimant7.CRASH_NO,
           Dim_Claimant7.ITEM_STATUS_CODE,
           Dim_Payments.CERTIFIED_DATE
    ),
D6 as
    (select distinct
           Dim_Claimant8.ITEM_STATUS  as  Item_Status,
           Dim_Claimant8.CLAIMANT_NAME_NO  as  Claimant_Name_No,
           Dim_Claimant8.CRASH_NO  as  Crash_No,
           Dim_Claimant8.ITEM_STATUS_CODE  as  Item_Status_Code
     from
           "Datamart Presentation Area"..DTMP_PSTATIC.DIM_CLAIMANT Dim_Claimant8
     where
           (DATE '2017-09-30' between cast(Dim_Claimant8.FROM_DATE as date) and cast(Dim_Claimant8.TO_DATE as date)) and
           (Dim_Claimant8.ITEM_STATUS_CODE = 'L')
    )
select
       coalesce(D5.Item_Status,D6.Item_Status)  as  Item_Status,
       coalesce(D5.Claimant_Name_No,D6.Claimant_Name_No)  as  Claimant_Name_No,
       coalesce(D5.Crash_No,D6.Crash_No)  as  Crash_No,
       coalesce(D5.Item_Status_Code,D6.Item_Status_Code)  as  Item_Status_Code,
       D5.Paid_Amount  as  Paid_Amount,
       D5.Certified_Date  as  Certified_Date
from
       D5
        full outer join
       D6
        on ((((D5.Item_Status = D6.Item_Status) and (D5.Claimant_Name_No = D6.Claimant_Name_No)) and (D5.Crash_No = D6.Crash_No)) and (D5.Item_Status_Code = D6.Item_Status_Code))

[MFGF]

Hi MF,

It does appear like a stitch query, but I cant think of a solution
Attached is FM model with cardinalities.

For now I have to have separate queries for claim and payments and then do an inner join in report studio.
I feel my model has some issues as below:
1) Connection of SCD 2 with Fact tables (fro fact claim its OK as it also joins to dim claimant on ss dates as a new fact is also created if claim details change otherwise I get outer join and all snapshots)
2) I cant select facts from multiple fact tables in same query as unwanted data is produced e..g No . of claims from fact claim, no. of briefs from fact brief, no. of writs from fact writ and paid amount from fact payments.

The only SCD2 in this model is dim claimant.

Below is the Cognos SQL whenI select data from Dim claimant .dim payment and fact payments, it can be seen as in bold text that ss date filter is applied to claimant but then full outer join ignores it:

with
Dim_Claimant7 as
    (select
           Dim_Claimant.FROM_DATE  as  FROM_DATE,
           Dim_Claimant.TO_DATE  as  TO_DATE,
           Dim_Claimant.CRASH_NO  as  CRASH_NO,
           Dim_Claimant.CLAIMANT_NAME_NO  as  CLAIMANT_NAME_NO,
           Dim_Claimant.ITEM_STATUS_CODE  as  ITEM_STATUS_CODE,
           Dim_Claimant.ITEM_STATUS  as  ITEM_STATUS
     from
           "Datamart Presentation Area"..DTMP_PSTATIC.DIM_CLAIMANT Dim_Claimant
     where
           (DATE '2017-09-30' between cast(Dim_Claimant.FROM_DATE as date) and cast(Dim_Claimant.TO_DATE as date))
    ),
D5 as
    (select
           Dim_Claimant7.ITEM_STATUS  as  Item_Status,
           Dim_Claimant7.CLAIMANT_NAME_NO  as  Claimant_Name_No,
           Dim_Claimant7.CRASH_NO  as  Crash_No,
           Dim_Claimant7.ITEM_STATUS_CODE  as  Item_Status_Code,
           Dim_Payments.CERTIFIED_DATE  as  Certified_Date,
           XSUM(Fact_Payments.PAID_AMOUNT  for Dim_Claimant7.ITEM_STATUS,Dim_Claimant7.CLAIMANT_NAME_NO,Dim_Claimant7.CRASH_NO,Dim_Claimant7.ITEM_STATUS_CODE,Dim_Payments.CERTIFIED_DATE )  as  Paid_Amount
     from
           Dim_Claimant7,
           "Datamart Presentation Area"..DTMP_PSTATIC.DIM_PAYMENTS Dim_Payments,
           "Datamart Presentation Area"..DTMP_PSTATIC.FACT_PAYMENTS Fact_Payments
     where
           (Dim_Claimant7.ITEM_STATUS_CODE = 'L') and
           ((Fact_Payments.CRASH_NO = Dim_Claimant7.CRASH_NO) and (Fact_Payments.CLAIMANT_NAME_NO = Dim_Claimant7.CLAIMANT_NAME_NO)) and
           (Fact_Payments.PAYMENT_KEY = Dim_Payments.PAYMENT_KEY)
     group by
           Dim_Claimant7.ITEM_STATUS,
           Dim_Claimant7.CLAIMANT_NAME_NO,
           Dim_Claimant7.CRASH_NO,
           Dim_Claimant7.ITEM_STATUS_CODE,
           Dim_Payments.CERTIFIED_DATE
    ),
D6 as
    (select distinct
           Dim_Claimant8.ITEM_STATUS  as  Item_Status,
           Dim_Claimant8.CLAIMANT_NAME_NO  as  Claimant_Name_No,
           Dim_Claimant8.CRASH_NO  as  Crash_No,
           Dim_Claimant8.ITEM_STATUS_CODE  as  Item_Status_Code
     from
           "Datamart Presentation Area"..DTMP_PSTATIC.DIM_CLAIMANT Dim_Claimant8
     where
           (DATE '2017-09-30' between cast(Dim_Claimant8.FROM_DATE as date) and cast(Dim_Claimant8.TO_DATE as date)) and
           (Dim_Claimant8.ITEM_STATUS_CODE = 'L')
    )
select
       coalesce(D5.Item_Status,D6.Item_Status)  as  Item_Status,
       coalesce(D5.Claimant_Name_No,D6.Claimant_Name_No)  as  Claimant_Name_No,
       coalesce(D5.Crash_No,D6.Crash_No)  as  Crash_No,
       coalesce(D5.Item_Status_Code,D6.Item_Status_Code)  as  Item_Status_Code,
       D5.Paid_Amount  as  Paid_Amount,
       D5.Certified_Date  as  Certified_Date
from
       D5
        full outer join
       D6
        on ((((D5.Item_Status = D6.Item_Status) and (D5.Claimant_Name_No = D6.Claimant_Name_No)) and (D5.Crash_No = D6.Crash_No)) and (D5.Item_Status_Code = D6.Item_Status_Code))

Hi,

Can you elaborate on the join between Dim Claimant and Fact Payments? I'd expect this to be on the surrogate key in Dim Claimant (Claimant Key?) but the query appears to be using a combination of Claimant Name No and Crash No to join them? If you have multiple dimension rows with the same Claimant Name No and Crash No (because of Type 2 SCD changes) you're going to get double-counting of your facts?

Can you elaborate on how you are applying this filter to your report? Is it a regular detail filter with no casting? Are you using the same query subject for the filter as you are for the items in the main query?

Are there any other modelling levels above / below the diagram you posted that re-define relationships?

MF.

[Sep2013]

Hi MF.
You are right. These two tables are joined on naturak keys and crash n claimant name no. can have multiple rows in dim claimant as its a ss table.
1) If i use surrogate key ... payments in lifetime of a claim will.br attributed to different snapshots of claimant and thus i cant derive all payments made until 2017-01--01 for claimant record as it looked like on 2017-01-01. because only payments made for claim during that snapshot will appear n ignore any previous payments.

2) I have only one query in report studio with detail filter as 2017-01-01 between cast (from date, date) and cast (yo date, date).
i am casting dates as they are in datetime format and until i fix this issue in fm for next realesse i am resolving this temporarily.

[Sep2013]

also... no other modelling levels above this. although i was thinking ti create star schema groupings and separate out all star models in namespaces with shorcuts.
For now i have to use a separate query to filter claims for ss 2017-01-01 and then do an inner join to.main query to get
inner join.

But i thought there could be a better way to design the.model so that i can apply filter in main query instead of separate query .

i used kimballs techniques for modelling.. I somehow feel my model is too normalised and.may be thats why hard to use?

[Sep2013]

:)