Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

most recent surrogate key

most recent surrogate key

most recent surrogate key

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

RE: most recent surrogate key

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

RE: most recent surrogate key

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),
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.

RE: most recent surrogate key

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close