Full Other Join, SC1 and SC2 in one model

[Sep2013]

Hi, I am working on an insurance BI system.
We have claims and payments made against those claims.
1) I created two models, one claimant and other payment ( dim and facts for both)
2) The claimant dimension is snapshotted (slowly changing dimension) but the payment is transactional (most recent data)
3) Both are joined visa fact to dim join that is fact payment is connected to dim claimant based on accident number and name no (natural keys)
4) I could not use claimant surrogate key as in most cases we want to know total paid and not what was paid for a claimant key record.
5) We did not implement a most recent surrogate key table which will help me to get claimant data for most recent claimant record.
6) When I use the attached model where Fact claimant is linked to a calendar, dim claimant and crash dimension and try to get payment made for claims, I expect to get following:
->I filter the claimant fact using calendar date = '30/06/2017' which means it will select a single slice of claimant data from snapshots and drag and drop the payment facts, I expect it to get me all payments made for claimant.
-> However, it does not do that, the FM builds a query that joins fact claimant,dim claimant, claimant calendar full outer join fact payments and dim claimant, which multiploes the total paid to the no. of snapshot records, if $100 was paid, and I had 5 snapshots of claim, it will show I paid 500 due to full other join.
-> I want it to resolve the date filter on claimant calendar to select single slice and then get payments .

Can you help me to find out how to handle this situation in FM?

The model is attached for your reference

[MFGF]

Hi, I am working on an insurance BI system.
We have claims and payments made against those claims.
1) I created two models, one claimant and other payment ( dim and facts for both)
2) The claimant dimension is snapshotted (slowly changing dimension) but the payment is transactional (most recent data)
3) Both are joined visa fact to dim join that is fact payment is connected to dim claimant based on accident number and name no (natural keys)
4) I could not use claimant surrogate key as in most cases we want to know total paid and not what was paid for a claimant key record.
5) We did not implement a most recent surrogate key table which will help me to get claimant data for most recent claimant record.
6) When I use the attached model where Fact claimant is linked to a calendar, dim claimant and crash dimension and try to get payment made for claims, I expect to get following:
->I filter the claimant fact using calendar date = '30/06/2017' which means it will select a single slice of claimant data from snapshots and drag and drop the payment facts, I expect it to get me all payments made for claimant.
-> However, it does not do that, the FM builds a query that joins fact claimant,dim claimant, claimant calendar full outer join fact payments and dim claimant, which multiploes the total paid to the no. of snapshot records, if $100 was paid, and I had 5 snapshots of claim, it will show I paid 500 due to full other join.
-> I want it to resolve the date filter on claimant calendar to select single slice and then get payments .

Can you help me to find out how to handle this situation in FM?

The model is attached for your reference

Hi,

It looks to me like your only conformed dimension here is Dim Claimant? It also looks like Fact Claim and Fact Payments join to it using different keys at different granularities? If I'm right, did you define determinants on Dim Claimant to differentiate the different levels of granularity, so that the measures in the two facts get grouped and aggregated differently?

MF.

[Sep2013]

Hi MF,

Thank you for your prompt response.
you are right, I have joined faact claim to dim claim on surrogate key but fact payment to dim claim on natural keys.
Only because I want to access all payments irrespective of the snapshots of claim. claimant key is surrogate key where crash no and claimant name no are natural keys.
I have never used determinants before, Can you guide me whether i will have one determinant for claimant key and other for natural keys? what goes in key and attributes when defining determinant.
once i specify them do i have to use them in relevant facts?

[MFGF]

Hi MF,

Thank you for your prompt response.
you are right, I have joined faact claim to dim claim on surrogate key but fact payment to dim claim on natural keys.
Only because I want to access all payments irrespective of the snapshots of claim. claimant key is surrogate key where crash no and claimant name no are natural keys.
I have never used determinants before, Can you guide me whether i will have one determinant for claimant key and other for natural keys? what goes in key and attributes when defining determinant.
once i specify them do i have to use them in relevant facts?

Hi,

From what you are describing, it sounds like the natural keys are non-unique in the table, and the surrogate keys are unique (ie you may have many surrogate keys for each natural key)? You'd need two determinants in that case.
The first would be for the natural key, with the natural key in the Key section. If there are any columns that are described by the natural key (I'd guess many if not all might be, except for any SCD management columns), then these columns need to be in the Attributes section. This determinant would have the "Group By" checkbox selected.
The second would be for the surrogate key, with the surrogate key in the Key section. This determinant would have the "Uniquely Identified" checkbox selected (which automatically adds all other columns to the Attributes section).

For a description of how determinants work and why you need them, take a look at the FM User Guide, Chapter 5 > Query Subjects > Determinants (on page 74):

http://public.dhe.ibm.com/software/data/cognos/documentation/docs/en/10.2.2/ug_fm.pdf

You don't generally use determinants in fact tables. Just in the conformed dimensions where facts are joining using different keys at different levels of granularity.

MF.