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

Calculated measure value in aggregate fact table

3 posters

Go down

Calculated measure value in aggregate fact table Empty Calculated measure value in aggregate fact table

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

Back to top Go down

Calculated measure value in aggregate fact table Empty Re: Calculated measure value in aggregate fact table

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

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

http://aginity.com

Back to top Go down

Calculated measure value in aggregate fact table Empty Re: Calculated measure value in aggregate fact table

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

Back to top Go down

Calculated measure value in aggregate fact table Empty Account DIMENSION?

Post  owvi4 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?
owvi4
owvi4

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

Back to top Go down

Calculated measure value in aggregate fact table Empty Re: Calculated measure value in aggregate fact table

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