most recent surrogate key

[angelkaur]

i am working with claims data
I have two models one for counting no. of claims and other for relevant payments..both have thier own star schemas conformed using claimant dimension

the issue is that claimang dimension is snapshot over period but payments are transactions..thus if i want to see total paid for claimamt it will not be possible as it will lnly look up the payments made for that particular ss of claimamt.

Please guide me how cab i ensure that independent of which slice of ss i select i am able to see all payment transactions.

see attached an example

 

[johnherman]

I think the problem is in the design. There should be a fact table for claim, and a fact table for payment. It is possible that a payment could be made for more than one claim, and that a claim could have multiple payments. Therefore, a bridge or relation table between those two. That bridge table would allow you to see all payments for any subset of claims across any range of dates, as well as see all claims to which a payment or group of payments apply. So, even though there are two dimensional star schemas, one each for claim and payment, you still need a relational bridge table - not just conformed dimensions.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[angelkaur]

Hi John,

Thanks a lot for prompt reply.
I am attaching the two models.

Does that mean the bridge table will have natural keys (crash no. and name no.), claimant surrogate key (claimant key) and payment surrogate key (Payment Key)?
If a claimant SS is valid for (say) three periods – 1/2/2015 to 26/5/2015 (SK10102); 27/5/2015 to 5/11/2016 (SK20323); and 6/11/2016 to 31/12/9999 (SK30211),
AND
I have payments generated on 1/2/2015, 22/4/2015, 3/7/2016, and settlement (final payment) on 6/11/2016:
I would expect the first two payments to be generated with claimant key 10102, the third with 20323 and the final with 30211.

How do I select the current claimant SS, with the total paid across the life of the claim?

A claimant can have multiple payments
A single payment is only linked to one claim.

 

[johnherman]

The bridge table will usually contain the surrogate keys of each of the tables being bridged. Usually that's 2, sometimes 3 tables. These surrogate keys to the table being bridged are usually indexed in the database for performance. The bridge table also has its own surrogate key as primary key.

To get summaries by claimant and crash, inner join those two tables to the bridge table. You don't care about the dates since you want totals across all date ranges.


==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity