Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Combining Facts

3 posters

Go down

Combining Facts Empty Combining Facts

Post  cbusch 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 : 61
Location : Albany NY

Back to top Go down

Combining Facts Empty RE:Combining Facts

Post  Prasanna 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

Back to top Go down

Combining Facts Empty Re: Combining Facts

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

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

http://aginity.com

Back to top Go down

Combining Facts Empty Re: Combining Facts

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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