Lost in fact granularity

[exhortae]

Hi,

I have the following requirement and I have to say that I can't find a way to fulfill it with elegance.


I'm trying to design a fact table, this fact table consist of payments made for loan reimbursement (for the sake of simplicity I will remove some details from the real life scenario).

An individual can take a loan and each month he makes a payment to reimburse it. So I have the following fact table

FT_REIMBURSMENTS
FK_DT_REIMBURSMENT : the date the reimbursement was made.
ID_DM_LOAN_NUMBER : the loan number the reimbursement was made on
REIMBURSMENT_AMOUT : the amount of the reimbursement made.

As of now everything looks quite simple.

The problem comes when you speak with the business user. He tells me that individuals can take loans, but the company can give loans to group of individuals and this is how it works.

A group of 3 individuals take a loan (say 5 000 $), the company establish on their name 3 contracts (they get 3 loan numbers, each one his own) but only one group number (same shared group number for all).

When they come to reimburse the loan we have 2 possible scenari.

1) Each individual come alone and make the payment. In this case the reimbursement is done on the loan number and the initial fact table design still covers this scenario.
2) The three individuals come together and they make one whole payment for the group. In this case the reimbursement is done on the group number (in the source system for this case the reimbursement is linked to the group number, not to the loan number) and the initial fact table design doesn't cover this scenario.


What I have done to cover the second scenario is add a new column in the fact table so it becomes like this:

FT_REIMBURSMENTS
FK_DT_REIMBURSMENT : the date the reimbursement was made.
ID_DM_LOAN_NUMBER : the loan number the reimbursement was made on
ID_DM_GROUP_NUMBER : the group number the reimbursement was made on
REIMBURSMENT_AMOUT : the amount of the reimbursement made.

The problem is that I have no way to know how to split the amount paid between the 3 group members (it is done several days after by a very complex logic by the source system). So for every loan number reimbursement amount I put the overall amount the 3 members paid.

Example

We have 3 group members, they make a single shared reimbursement of 1000$, so this is what I put in the fact table :

julian(20120703)
123345 (loan number of individual n°1)
GROUP123
1000

julian(20120703)
456789 (loan number of individual n°2)
GROUP123
1000

julian(20120703)
8888888 (loan number of individual n°3)
GROUP123
1000


So basically they appear like they have made a 1000 $ reimbursement each while in reality they only made a 1000$ reimbursement for all.


I wonder how you guys would have handled this business case.

Thank you for your help

[VHF]

Sounds similar to multiple account owners, which is usually handled with a many-to-many bridge table in a dimensional model, but you still need to be careful not to over-report by couting the account balance multiple times for each owner. (Microsoft SSAS very nicely handles this scenario when building a cube.)

Obviously, you want to avoid over-reporting as in your example. If all you know is that $1000 was paid on the group, either you need to model that in your fact (and not try to tie the payment to specific loans) or you need to allocate (for example divide it equally*.) However, it is dangerous to allocate unless your business users are in agreement this makes sense for reporting purposes.

You said the actual allocation would be determed later by the source system. Will that information be fed to your DW? If so, you could record the group payment at the time it happened, then when the split payment detail comes through, add a row to reverse the original payment and the replace it with rows representing how the payment was split between the individual loans.

So, when you get a single group payment you would put this in your fact table:

julian(20120703)
NA
GROUP123
1000

(Ideally the 'NA' value would be the SK of a special record in your loan dimension representing a group payment.)

Once you have the detail data available, you wouild first reverse the orignal records by adding this row to your fact table:

julian(20120703)
NA
GROUP123
-1000

Then add the actual breakdown by individual:

julian(20120703)
123345 (loan number of individual n°1)
GROUP123
400

julian(20120703)
456789 (loan number of individual n°2)
GROUP123
400

julian(20120703)
8888888 (loan number of individual n°3)
GROUP123
200



*P.S. If you do allocate in the DW, be careful of cumulative rounding errors! You need to make sure the total matches to the penny!

[exhortae]

you need to allocate (for example divide it equally*.)  However, it is dangerous to allocate unless your business users are in agreement this makes sense for reporting purposes.

You said the actual allocation would be determed later by the source system.  Will that information be fed to your DW?  If so, you could record the group payment at the time it happened, then when the split payment detail comes through, add a row to reverse the original payment and the replace it with rows representing how the payment was split between the individual loans.

At the time the splitting is made the information would be useless to them (too late)

So, when you get a single group payment you would put this in your fact table:

julian(20120703)
NA
GROUP123
1000

(Ideally the 'NA' value would be the SK of a special record in your loan dimension representing a group payment.)

I don't know how I didn't see this solution, thank you this is what I will use.

[Sponsored content]