How to build Dimensional model for commission received and return

[RJKumar]

I am working on the Insurance broker Dimensional model and we have Policy table and relevant other tables like Customer and date table..

Now we are working for further to add some more fact and dimension table when we received commission from the insurance company when policy is sold and Commission returning to insurance Company when policy cancel.

So basically the Finance Invoice billing Reporting to reconcile with Different Insurance Providers.

Here is some sample data:
PID expected_commission created

​

start_date cancelled_date CommissionID payment_type stmt_date amount
1 1402.65 2022-05-27 14:32:22 2023-05-17 2023-05-17 888 Payment Return 2023-05-05 -1402.65
1 1402.65 2022-05-27 14:32:22 2023-05-17 2023-05-17 888 Payment Received 2022-10-11 1402.65
2 3199.63 2022-06-24 15:10:43 2023-05-12 555 Payment Received 2023-05-13 3200.82
3 2322.13 2022-08-04 10:18:46 2023-05-11 666 Payment Received 2023-06-12 2322.13
4 849.51 2022-10-13 11:22:13 2023-05-09 2023-05-09 999 Payment Return 2023-07-05 -944.02
4 849.51 2022-10-13 11:22:13 2023-05-09 2023-05-09 999 Payment Received 2023-05-10 944.02
5 679.22 2022-10-17 12:11:49 2023-05-25 444 Payment Received 2023-06-26 679.22
6 3119.31 2022-11-28 12:26:26 2023-05-18 777 Payment Received 2023-05-24 6239.38
6 3119.31 2022-11-28 12:26:26 2023-05-18 777 Payment Return 2023-08-02 -3017.26

So How should we build the fact table and dimension table to track the --How much amount we received for each Insurance which are linked to each policy --How much amount we paid back Return to each Insurance which each policy is cancelled..

We have already a Policy table in our model which has policy details including the Expected Commission, policy start date, policy cancel date etc... now we need for Commission and refund fact table as i explained how can we design this new fact table grain. shall we keep the 2 separate fact tables : one fact table for Commission received transaction, AND Another fact table for Commission returned.

OR we should have one single fact table to having 2 rows for each Policy Id where 1 row is for Commission received and 2nd rows is for Commission returned and some sort of payment Type column to indicate where ( Payment Received, Payment Returned) . We need to see the report against the billing statement we are getting from the each Insurance company

 

[johnherman]

It seems that every policy will have a actual commission, so I would put that data in the Policy Fact table. With a special value for "not yet received" to avoid situations with nulls. From a processing and performance point of view, I would put the "return" data in the same table with a special values for "no return" to avoid dealing with nulls. Maybe 12/31/9999 for no returns?