Data with different granularity

View previous topic View next topic Go down

Data with different granularity

Post  tawnyap on Tue Mar 29, 2016 6:36 pm

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

tawnyap

Posts : 2
Join date : 2016-03-29

View user profile

Back to top Go down

Re: Data with different granularity

Post  ngalemmo on Tue Mar 29, 2016 7:58 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum