Calculated measure value in aggregate fact table

Hi everyone,

I'm in the healthcare industry and I have some question regarding aggregate table. We have fact table based on patient's transaction. For every patient visits, patient can have multiple transactions (medicine, lab test, surgery, etc).

In addition, we also have some patient and visit specific measures we have to track. For example, average blood sugar level during patient visit. However, some of our visit level calculated values cannot derived from fact table, but it is still required for our DW. In this situation, do we store this high level measure value in our current fact table or do we create new aggregate table to store this calculated value and create relationship between fact and aggregate table?

If I go with first idea, since each patient visit can have multiple transaction, visit level cal value will be repeated n times. If I go with aggregate table, since dimension is designed around patient level, I have noway to utilize dimension for this aggregate table, and I would have to create relationship between fact and aggregate table.

What is the best way to resolve this issue?

Thanks!

Because the grain is different, you store it in a different fact table, but you do not "create a relationship". Relationships between fact tables are implied by common conforming dimensions between the tables.

Thanks you sir. One more question.

This is current fact table:

Trans. # / Trans. Date / Amount / Account / Return Ratio
1000 - 1/1/2013 - $60 - A1111 - 1.2
2000 - 1/2/2013 - $40 - A1111 - 1.2
3000 - 1/3/2013 - $12 - A1111 - 1.2
4000 - 1/1/2013 - $60 - A2222- 2.1
5000 - 1/2/2013 - $40 - A2222 - 2.1
6000 - 1/3/2013 - $12 - A2222 - 2.1
7000 - 1/1/2013 - $60 - A3333 - 0.2
8000 - 1/2/2013 - $40 - A3333 - 0.2
9000 - 1/3/2013 - $12 - A3333 - 0.2

As you will see our fact table was designed to capture information at transaction level, but we also combine some account level information (Ratio) into our fact table. In this case, is it better to create account level fact table or keep the current design?

Thank you!

It sounds like you need an Account DIMENSION table, and then link the Transaction Fact to the Account Dimension. Make sense?