Looking at what you have id be suggesting a data model with a fact table built around the Claim, Claimant and Payment Dimensions
Facts I can see are
I assume they'd be cancellations and rejections and escalations to be added to reflect the workflow.
ClaimantId, ClaimId, PaymentId, Status, Date, Comments, Amount
Would probably work as a fact table structure.
Load each state into that fact table populating what you can and then distinct your dimension data into the dimension tables you will then have a star schema you can play with in lots of ways.
Final or latest states to be stored against each dimension.
Looks like you may have a missing relationship between your payment and what it is for. You are authorising a payment for a claim at the moment you have no context for what it is for. Also id guess the payment may be to a third party beneficary not just a claimant but thats just me over thinking it.
Anyway hope this helps
Thank you for your reply.
You may be on the right track but you're missing the point.
my problem is not to link those together. is to link the different dates. I'll explain better.
in the DW I have the historical data of my client. (insurance) the tables I shared are just example because I cannot share anything for data protection reasons.
Let's say that I am at the beginning of November and I need to display quarter end reports. I need to show what happened until the 30th of September. so if my data set for the claim is
1 Created 03-May 2 Created 05-Sept 1 Registered 09-Sep 1 Assigned 10-Oct 2 Registered 25-Oct
so I need to show that claim 1 is Registered and claim 2 is created, but if I select the end of October to see the month end reports I need to show that Claim 1 is Assigned and Claim 2 is Registered.
At the same time, and this is the issue,
I need to show the same logic but on the claimants on the claim date and for the payments on the payment date, to show all the payments made at that point with the correct status.
PaymentID ClaimantID Payment Date Amount 1 1 10-Sep 10 1 1 05-Oct 15 2 1 06-Sept 5 3 2 06-Oct 10
for the quarter end, I need to show Payment 1 with amount of 10 , payment 2 with amount 5 and the total some is 15. while for the October report, Payment 1 is now 15 and I have also payment 3 with amount 10 so the total sum is 30.
So, I'm trying to find a way to link everything together.
Now what comes in mind is set analysis. but I would prefer another way.
Thanks for your help.
I'd recommend to validate your data against a functional design, meaning comprehend what the actions are and how the data reflect those actions. With a quick look at your data, I would try joining Claims and Payments in a Transaction table and leaving Claimants as the SCD... although I couldn't figure out what was the value that would change for a claimant.
For example in Oracle eBusiness (R12) when you create a transaction against an Inventory Item, the system saves in the transaction table the unit of measure and relationship against the base unit of measure. This is because the UoM of an item can be changed at any time but the transactions will remain with the information they need regardless. Oracle could have linked UoMs and transactions by the dates but this would have been costly in terms of performance without adding much value.
Going back to your design, or at least to the data you provided, you could attempt to link transactions with Claimant but make sure that each transaction refers to only one dimension (claimant), for which you may need a composite key.
Of course, this is just quick recommendation base on a 5-min analysis as I'm not familiar with your business or your data and I agree with you in trying to create a model that will simplify your life in terms of set analysis
All the best