Data with different granularity
2 posters
Page 1 of 1
Data with different granularity
I need to model a scenario where a person can be a primary account holder and can have some dependents sharing the same contract. The business wants to look at the total revenue generated every month and a count which helps identify the total members (primary account holder+dependents) that are being billed for every month.
I have a clear understanding about the first part of the requirement, but when it comes to the second part of calculating the total count this is where I go back and forth deciding whether to come up with a separate summary fact table or modify the existing one to include this additional count attribute. The dimensions the business user uses to slice and dice the data seems to be the same, and they want to look at all the members ,not just the primary account holder. Is there any advantage of having this information summarized in a separate table or do I rather modify the existing table to include the count metric? The one advantage I see in having this as a separate table is giving the summarized information at primary account holder level and if they have to look at the details, they can always use the other table.
Let's take this example of how the source data looks, and how the fact table looks and how the new fact table would look like with the count included to get a better understanding. In this example, rebate amount is at individual member level(not at primary account holder level). And with my option 2, I might not be able to get this amount to primary account holder level since it's at individual level. What would be a better way of tackling with this modeling situation? Thanks in advance.
Source:
Primary Account # Contract# Due_Date Premium_Amt_Primary Premium_Amt_Dependent Rbt_amt
1 1000 01-Jan-2010 1000 3000 1000
0 1000 01-Jan-2010 1000 4000 2000
0 1000 01-Jan-2010 1000 1000 3000
This primary account holder (1) has three other dependents: 2,3, 4
So in fact table this is how it shows the details
Primary Account Id Contract Id Month_Due Total_Amt Rbt Amt
1 1 201001 4000 1000
2 1 201001 5000 2000
3 1 201001 2000 3000
New measure count added(Option 1)
Primary Account Id Contract Id Month_Due Total_Amt Total_Cnt
1 1 201001 4000 3
2 1 201001 5000 0
3 1 201001 2000 0
New Summary table(Option 2):
Primary Account Id Contract Id Month_Due Total_Amt Total_Cnt
1 1 201001 11000 3
I have a clear understanding about the first part of the requirement, but when it comes to the second part of calculating the total count this is where I go back and forth deciding whether to come up with a separate summary fact table or modify the existing one to include this additional count attribute. The dimensions the business user uses to slice and dice the data seems to be the same, and they want to look at all the members ,not just the primary account holder. Is there any advantage of having this information summarized in a separate table or do I rather modify the existing table to include the count metric? The one advantage I see in having this as a separate table is giving the summarized information at primary account holder level and if they have to look at the details, they can always use the other table.
Let's take this example of how the source data looks, and how the fact table looks and how the new fact table would look like with the count included to get a better understanding. In this example, rebate amount is at individual member level(not at primary account holder level). And with my option 2, I might not be able to get this amount to primary account holder level since it's at individual level. What would be a better way of tackling with this modeling situation? Thanks in advance.
Source:
Primary Account # Contract# Due_Date Premium_Amt_Primary Premium_Amt_Dependent Rbt_amt
1 1000 01-Jan-2010 1000 3000 1000
0 1000 01-Jan-2010 1000 4000 2000
0 1000 01-Jan-2010 1000 1000 3000
This primary account holder (1) has three other dependents: 2,3, 4
So in fact table this is how it shows the details
Primary Account Id Contract Id Month_Due Total_Amt Rbt Amt
1 1 201001 4000 1000
2 1 201001 5000 2000
3 1 201001 2000 3000
New measure count added(Option 1)
Primary Account Id Contract Id Month_Due Total_Amt Total_Cnt
1 1 201001 4000 3
2 1 201001 5000 0
3 1 201001 2000 0
New Summary table(Option 2):
Primary Account Id Contract Id Month_Due Total_Amt Total_Cnt
1 1 201001 11000 3
tawnyap- Posts : 2
Join date : 2016-03-29
Re: Data with different granularity
Placing the member count on the transactional fact allows you to capture that count at that point in time. On the other hand, placing the count as an attribute of the contract, you would have access to the current count (or a separate fact if you don't have a contract dimension). Usually you do both.
Similar topics
» Same Data at different Granularity
» Aggregating data at different levels of granularity
» Different Granularity Data - Life Insurance Model
» Designing Sales Promotion for Packed Products
» Granularity - One Fact Table or Two
» Aggregating data at different levels of granularity
» Different Granularity Data - Life Insurance Model
» Designing Sales Promotion for Packed Products
» Granularity - One Fact Table or Two
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum