×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

How to build Dimensional model for commission received and return

How to build Dimensional model for commission received and return

How to build Dimensional model for commission received and return

(OP)
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

RE: How to build Dimensional model for commission received and return

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?

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