Calculated measure value in aggregate fact table

View previous topic View next topic Go down

Calculated measure value in aggregate fact table

Post  shinji23 on Thu Apr 04, 2013 2:00 pm

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!

shinji23

Posts : 11
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Calculated measure value in aggregate fact table

Post  ngalemmo on Thu Apr 04, 2013 5:30 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Calculated measure value in aggregate fact table

Post  shinji23 on Mon May 20, 2013 7:24 pm

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!

shinji23

Posts : 11
Join date : 2012-01-19

View user profile

Back to top Go down

Account DIMENSION?

Post  owvi4 on Fri May 24, 2013 2:39 pm

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

Posts : 3
Join date : 2009-04-16
Location : Indianapolis, IN

View user profile

Back to top Go down

Re: Calculated measure value in aggregate fact 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