To understand the Dimensional modeling and its advantages for particular
industries, let’s take the Insurance industry as an example to understand how
dimensional modeling can help track their performance.
Suppose an insurance company XYZ, provides insurance policies in various
categories mainly Car Insurance, Home Insurance and Life Insurance. Their
customers come from different demographics. The company processes claims and
payments for these customers.
They have a database system to maintain all this information. Their
database mainly contains of:
Example: Data model design
Customers table with attributes like Customer ID, Customer Name, Address
Policies Table with the type of policies that they offer- Home, Life and
Car
Branches table with Branch ID, Branch Name, Branch Location
Payments table with Payment ID, Payment Type, Payment Date
Claims table with Claim ID, Claim Amount, Claim Reason
Customer Policies table with start date, end date
Some of the metrics that the CEO would be interested in are:
1) Rate of change in the number of customers by policy category i.e.,
compared to last month, by what number has the customer base increased or
decreased?
This metric would be of interest to the CEO to understand if the company
is progressing or not. This metric will help judge if the company is going in
the right direction, if the company is able to maintain relations with their
customers and retain them or not.
2) Which type of policy is generating more revenue? i.e., What is their
total revenue per month from each category of policies?
This metric would help the company understand which policy category is
doing well, and which policy category services need to be improved upon.
3) Total number of new policies this month for each category of policy
Again this metric would help the company judge
which policy is doing well and which type of policy has to be improved.
4) Total claims made per month and Total settlements that are made per
category
These would help them understand how much of the claims made are
actually being settled per category of policy. Helping them judge which
category has the maximum false claims, so that they can be wary of settling
claims in that category in the future.
5) Which country or state has the most number of customers?
This metric would help the company judge their performance in each
demographic. So that they identify regions where they can do better.
To measure all the above metrics and arrive at conclusions to better
their performance, the company can use a data warehouse model with a dimensional
model that would help them measure these metrics. A data warehouse model will
help the company make informed decisions that driven by the data and metrics
that matter to them.
Most of the performance
metrics mentioned above have to be measured over a period of month, year or
maybe even per day. To reflect these results, we need a periodic snapshot
table, with a grain of period. These type usually contain many facts as any
measurement consistent with the fact table grain us permitted.
Sample Dimensional Model for the Insurance
Company
References:
