How to build Dimensional model for commission received and return

Question

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:

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

Answer 1

You would follow the same process you would for any dimensional model:

From your business requirements:

• identify the measures
• identify the attributes required to slice/dice/aggregate the measures

The attributes give you your dimensions.

Determine the grain of your measures

If measures have the same grain then they can go into the same fact table.

If measures have a different grain then they must go into different fact tables