Dimensional Modelling

[Sep2013]

Hi All,
I am a beginner in dimensional modelling and trying to propose a dimensional model for my current organisation which is an insurance agency.
I have attached the picture of my logical model, however I suffer in below:
1) Yellow highlighted items are confusing
2) What are the design or modelling facts to be considered fro slowing changing dimensions, conformed dimensions etc.
3) I am not sure that the logical model looks good from its design or nor and would appreciate if you could provide feedback based on your experience
4) How are the dimensions linked to each other... ?

Many thanks for your help in advance

[bdbits]

I think there may be multiple facts co-mingled here. Just glancing, it looks like you have accidents, claims, investigations, trials, etc. You may want multiple facts, with conforming dimensions like claim. This might also bring some clarity to those items off to the side.

You have dim_name (and what is that - a customer? call it that) with attributes like Age, Gender, and Marital Status. You might want to make those separate dims. The same for some of the other tables, I would review them all for things like that.

Link the dimensions to the facts with keys (I prefer surrogate keys). So there should be a key for dim_provider that links to the foreign key, "FK Provider_ID" I presume. It is a preference, but I would have common names, e.g. dim_provider and the fact table both have dim_provider_id or whatever the key will be named.

Slowly changing dimensions apply when you have something that changes over time and you want to preserve the historic values. e.g. someone changes their name and you want the old name to show up when that was their name, and the new name on data after the change. Whether you need them or not is a business decision.

You may want to get some training (could be online or even youtube) or books on data warehousing and dimensional modeling. This book is practically a standard on the subject and would be a good starting point. Ralph Kimball has a whole series of books on different aspects, if books are your thing.

https://www.amazon.com/Data-Warehouse-Toolkit-Definitive-Dimensional/dp/1118530802/ref=sr_1_1?s=books&ie=UTF8&qid=1478013206&sr=1-1&keywords=data+warehouse+toolkit+3rd+edition

[Sep2013]

Hi There, thank you so much for your reply.
I have been reading Kimball's book and its really helpful.
Basically the process is crash occurs, claim is filed, and we pay providers for providing services to clients.
Now with names, we have a one name system in which all names are stored without distinction between, people involved in crash, service providers, payee etc.
With your suggestions and feedback I have updated the model and split into two, one with claims (fact less as it has count of claims and crashed) and other as payments).
I have also removed name and geo from the model and created separate dimensions for them. E.g. instead of one name I have 3 separate for dim_claims that includes person involved in crash and claimants, dim_provder for service providers and dim_payee for payee.
That will mean, we will have separate star schemas for Legal panel briefs, investigations, trial etc.
Please see my two latest updated stars and I will really appreciate your feedback on them.

[bdbits]

My main comment was that I think you have multiple facts, but you have not addressed this. As it stands it looks to me like you have just one of the facts modeled - let's call it Fact_Payments. I think you also need at least one more fact table, let's call that Fact_Accident. And even this is incomplete - how are you going to accomodate the multiple parties involved in the crash (likely need a bridge table)? Multiple providers and multiple payments on the claim? And you've not begun to address investigations and trials.

If you don't create separate facts, you will be co-mingling all these different grains in one table and it will be difficult to get accurate reports out of it. Essentially, cross-joining your fact tables.

As you look at your model, ask yourself some typical questions you would be trying to answer from the data. How many claims did I have last month? How much did I pay each provider, and for which clients? How many people were involved in each accident on average? I do not know if these are the particular questions of course, but I hope you get the idea.

[Sep2013]

Hi Thank you so much for the response and I have finally worked out my models.
Following are the questions I wish to answer out of my model in a period (like rolling 13 months):
1) No. of new claims received;
2) No. of Open Claims at start of each period;
3) No. of closed claims in the period;

4) Total paid on claims closed in this period;
5) Average paid on claims closed in this period;

5) No. of writs received in period (These are the writs issued by claimant which may not be linked to claims in 1,2 and 3 in same period that is if 100 claims are open at 01/01/2015, a writ that is issued on 15/01/2015 may be for a claim that was not open on 01/01/2015);

6) No. of briefs issued in period excluding writs(Company will issue a brief to an internal solicitor to get advise which may or may not link to a writ like general advise on a claim, we report only briefs which were not related to writs and this can be easily identified by type of brief)

Now I have created 2 models for first 5 questions, one called claimant and another called payments with 2 conformed dimensions namely DIM_CRASH and DIM_CRASH.

The issue is that I am  finding it hard to decide whether I should create 2 separate star models for questions 5 and 6 or should I just add required information on DIM_Claimant e.g. Writ No., Writ received Date, Plaintiff solicitor and Defendant solicitor (drill through information) ?

I am attaching my models where tab 1 has claimant model, tab 2 has payments model and tab 3 has legal ER operational model which is outstanding due to the above question.
I will really appreciate your review on the attached models and advise on how to progress with writs and briefs.

[bdbits]

In FM, you should have three layers.
* The data layer is a direct reflection of the underlying database, with selects from data source tables.
* The business layer selects from the data layer and is where you resolve data issues, and put filters, derived data items, etc.
* The presentation layer contains shortcuts to business layer objects, and is what gets published to users in one or more packages.

You should have "conformed dimensions" (Kimball will emphasize this). Meaning you only have one definition of dim_claimant, for example. In your business layer, you will have all of your fact tables and dimensions. Each fact table and its related dimensions should form a star schema. Then in your presentation layer, you can publish as many star schemas as you need. You can also create one package that has everything in it, however users of this package would need to understand the granularity of the data so they do not make inappropriate selections from multiple fact tables with different grains.

So in your case, all of your models are melded together in the business layer, and only separated in the presentation layer.

I hope that provides some clarification for you.

[Sep2013]

Hi There,

Thanks a lot for elaborated reply.
Does that also mean that I will have a separate fact table for writs and separate for brief....
that is in total 4 fact tables for below facts?:
1) No. of claims
2) Paid Amount
3) No. of Writs
4) No. of Briefs

Or I can just add writ no and brief no on the dim claimant and use only one star model for claims, writs and briefs?

[bdbits]

You don't put measures (like a count) in a dimension.
Numbers are facts.