dimension table design question for around 100 attributes and higher level calculated attributes

View previous topic View next topic Go down

dimension table design question for around 100 attributes and higher level calculated attributes

Post  dw_lalic on Sat Feb 08, 2014 11:00 am

Hi,

I have scenario for medical test:

For one normal medical test there are around 100 attributes to be collected. There will be attributes calculated based on these basic data element to reflect high level indicator. For example based on attributes 1-10 final indicator A is get. Usually the report only reflects the final indicator.

My questions are:
1. in data warehouse stage part for sure basic data element are kept. But at data mart side should I keep both basic data element and calculated indicator ? If “Yes” should I put them in separated tables?
2. In fact for indicators the possible value is finite and I can say they are text measurement. I prefer to organize them as dimension. Or I should put them in the fact table?
3. there are multiple indicators and for every indicator there are only three attributes—indicator code, English description, French description. One approach is putting such more than 30 indicator in 30 dimensions the result is too much dimension tables. Another approach is combining 30 as (maybe 3 times 10) one junk dimension. But there is question should I include three attributes for every indicator—my understanding for Junk dimension is one flag occupy one column.

dw_lalic

Posts : 1
Join date : 2014-02-08

View user profile

Back to top Go down

dimension table design question for around 100 attributes and higher level calculated attributes

Post  zoom on Mon Feb 10, 2014 12:33 pm

You need to bring everything in DW. Talk to business user what data elements they want to see in reports and then bring only those data elements in the data mart. Create a biometric dim to store test result for a person and tie it to a fact table. The fact table is a factless fact table that shows test result for a person on a given date. Or you can ask your business user what you are measuring and store that measure on the fact table.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

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