Combining Facts

[cbusch]

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.

[Prasanna]

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.

[ngalemmo]

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.

[Sponsored content]