Combining Facts

View previous topic View next topic Go down

Combining Facts

Post  cbusch on Tue Oct 20, 2009 3:09 pm

We have 2 entities in our logical model for authorizations. One for inpatient authorizations and another for service authorizations. They are both children of the parent authorization entity.

We are considering combining them into a single authoriztion fact in our dimensional model. The table would be an accumulating snapshot updated daily. There are about 100 attributes of which 25 are specific to inpatient and 25 are specific to services. The total volume is < 1 million.

The requirements are split 50/50 where half are specific to one type or the other and the other half are for analysis across both types.

I would appreciate any insights on this approach and when it would make sense to keep them separate.

cbusch

Posts : 4
Join date : 2009-02-03
Age : 56
Location : Albany NY

View user profile

Back to top Go down

RE:Combining Facts

Post  Prasanna on Tue Oct 20, 2009 11:53 pm

If both Service and inpatient authorizations do not have similar measures,
I would suggest to have seperate facts instead of combining them as you have said both types are divided 50/50 based on the requirements.
Combining both the facts would reduce the performance and is a mere burden on fetching data as it is around 1M.

If both Service and Inpatient authorizations have similar measures, you can just add a Degenerate dimensions called authorization mark Inpatient/Service (whichever applicable) and add it to the fact. So that you can slice whatever desired.

Prasanna

Posts : 6
Join date : 2009-10-20

View user profile

Back to top Go down

Re: Combining Facts

Post  ngalemmo on Wed Oct 21, 2009 11:39 am

If the facts are basically the same, and there is a basic need to analyze them in toto, by all means use a single fact table. I worked at a payor a long time ago where the data warehouse was segregated (i.e. nearly identical yet separate data structures) by product (HMO, PPO, Medicare) yet most analysis was performed across the entire population. Needless to say, reporting was a real pain.

If you have Ralph's and Margy's book handy, review the banking chapter. They describe a sub-dimension approach where you have a common dimension, Account, which has additional sub-dimensions for different types of accounts that have unique attributes. The trick is both the common and sub-dimensions use the same primary key, so it is not a snowflake. Also, if you filter on a type specific attribute, the query will naturally limit selections to facts for that type as the sub-dimension would only contain rows for that type.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Combining Facts

Post  Sponsored content


Sponsored content


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