separate fact table/different grain - do I need a bridge table

View previous topic View next topic Go down

separate fact table/different grain - do I need a bridge table

Post  jmather on Wed Oct 26, 2011 4:16 pm

Dealing with Healthcare here. I have a fact tbl with the grain on the admission. With this admission_f, I have bridge tables for ICD9Diagnosis, another for ICD9Procedures - all works fine. I want to add labs, so I am building a separate fact table and currently have the grain at the lab itself, so no bridge table, just a link to the lab dimension. I can link the 2 facts by account# (which is repeated for each lab in the labs_f table). This seems to work OK so Far. What would be the disadvantage of this, as opposed to getting rid of the Labs fact table and creating a labs bridge table to the admissions_f table? I'm just getting started here and want to be sure I'm ok design-wise.
thanks
Jeff

jmather

Posts : 6
Join date : 2011-04-26
Age : 59
Location : Hartford, CT

View user profile

Back to top Go down

Re: separate fact table/different grain - do I need a bridge table

Post  ngalemmo on Wed Oct 26, 2011 5:15 pm

You are in Hartford, so I need to ask... are you a provider or payor?

That you have an admissions fact leads me to think you are a provider.

The separate lab fact is correct. You want fact tables to fit the process, so it would not make sense to try to jam admissions and lab work together as they are different processes that occur at different times. Same goes for procedures. Procedures should be in a different fact table from admissions because a procedure is an event that occurs at a different grain from the admission. Since procedures represent revenue, you may also want to include lab work in the procedure fact as well. Assuming the lab fact has more information than what you would need in the procedure fact, both tables would co-exist with each other. Procedure facts would be used for revenue measures, while the lab fact would be a subset and contain more specific information germain to the lab. Diagnosis, on the other hand, is a context and treated as a multi-valued dimension in all cases.

As for the lab and procedure facts themselves, they should carry a lot of the same dimensions the admission fact has, such as patient, diagnosis, admission id (a degenerate dimension), etc...
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: separate fact table/different grain - do I need a bridge table

Post  jmather on Thu Oct 27, 2011 12:26 pm

We are a provider and thanks very much for the feedback. What are the adv/disadvantages to having an extremely large lab_f table (every lab for every patient visit) with a lab_d as opposed to a lab_fact (at the visit level=account#), then a lab_bridge table (account#, lab_key), and the lab dimension.
thanks again,
Jeff

jmather

Posts : 6
Join date : 2011-04-26
Age : 59
Location : Hartford, CT

View user profile

Back to top Go down

Re: separate fact table/different grain - do I need a bridge table

Post  ngalemmo on Thu Oct 27, 2011 2:50 pm

The biggest drawback is a bridge really won't work. And the moment someone wants to do analysis on lab activity, you are stuck.

The basic concept in dimensional design is a fact table represents a business event or state and with its dimensions can stand on its own. This allows you to construct an enterprise wide warehouse in small steps. Analysis can be performed on a single star or combined with other stars (across common, conformed dimension attributes) for more complex analysis. Each new piece expands the scope of analysis that can be performed against the warehouse.

It is also very critical that when you bring in a fact you bring it in at the lowest level of detail as possible. It takes just as much work to bring in lab facts at its most detailed level as it is to bring in the same facts at an aggregate level... the difference is the latter is a waste of time and effort because the moment the business decides they want to do anaysis of lab activity, you have to go back to the source and do the load you should have done in the first place.

A bridge is used to associate facts with a dimension. You do not use a bridge to associate two facts. It would perform poorly, violates basic principals of dimensional design (you are basically creating an ER model), and limits the analysis end users can perform because it requires the lab facts to be loaded in aggregate.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: separate fact table/different grain - do I need a bridge table

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