0

I am new to data warehouse concept and currently modelling a star schema structure for private medical insurance. One insurance policy can have up to 15 people, and each person can have up to 70 different measures such as their combined medical conditions' rating, different risk rating, ethnicity rating, gender rating, medical price, tax etc etc. But not all policies will have 15 people.

97% of the reporting will be done based on insurance level and not person level and it will be done by connecting a BI tool. Preferable method is star schema.

I need to explicitly show each and every measure of a person on the insurance in a fact table. For example, 'person 1 risk rating', 'person 2 risk rating', 'person 3 risk rating' ...... 'person 15 risk rating'.

But if I do this I will end up creating 1050 columns (15 people * 70 attributes) in fact table which will look huge.

Can someone please suggest a better design idea for this problem?

Thanks in advance.

Natasha
  • 23
  • 2
  • The things that you've called measures look like attributes to me; measures are values you can aggregate. Please can you describe what your measures actually are? What are you trying to count, sum, average, etc? What is/are the grain(s) of these measures i.e. are they counts per policy, per person, etc? – NickW Apr 07 '21 at 21:12
  • @NickW All the ratings are my measures, where I will be applying average on top of them, also I have measures like price per person, tax per person, any additional fees per person etc which I will be summing up. – Natasha Apr 07 '21 at 22:10
  • 1
    From your response it looks like the grain of your measures should be per person per insurance policy - so you would have dimensions for the insurance policy and for the people (one record per person) plus any other dimensions you might need and then your fact table will reference these dimensions. If you have any measures that have a different grain (e.g. that apply just to the policy and not to the person/policy) then they would need to be in a different fact table: you should never have measures with different grains in the same fact table – NickW Apr 08 '21 at 08:17
  • @NickW, if I create a separate fact table with policy grains and separate one with person grains with one record per person, then when I join these 2 tables, I will then end up with one to many relationship? Will that be ok? I will be creating reports in BI tool like count of policies, count of person, sum of policy price, count of cancelled policies, and based on policy creation dates. – Natasha Apr 08 '21 at 08:35
  • You should never join fact tables directly in SQL queries. Instead you need to query them independently and combine the result sets based on common dimensions. Most BI tools (if configured correctly) will do this automatically – NickW Apr 08 '21 at 08:47
  • Perfect, thank you so much :) – Natasha Apr 08 '21 at 08:52
  • 1
    The two fact tables will contain measure for different events, one fact has a grain at the level of insurance policy (Fact Policy), the other is at the grain of the Person AND Insurance policy (FactPersonPolicy). Each fact will only point to the dimensions it needs to reference. For example, both facts will reference DimPolicy, but only FactPersonPolicy would reference DimPerson – Wes H Apr 27 '21 at 19:21

0 Answers0