healthcare screening

Hi

I am looking for recommendation to my design problem. It relates to questions people are asked when they go to GP practice. For instance they can be asked about alcohol units consumed + frequency etc but they also can be asked how many cigarretes they smoke + frequency.

Each set of questions relates to specific topic (alcohol, cigarettes etc) but actual questions (facts) are different.

Is there common DW design approach to this problem?

Many thanks in advance
Emil

The design will be dependent on what you are measuring. The types of questions you listed indicate that there are Yes/No answers as well as multiple-choice (smoking twice a day, once a day, once a week, once a month). This implies that you would use your DW to answer questions such as:

"How percentage people from the a certain area in a particular demographic smoke more than once a day?"

If this is the case, my first thoughts are:

Question Dimension
Answer Dimension
Other Dimensions (demographics, locality, response date, etc.)
Q&A Factless Fact

This may help give you a good start....

Hi

Thanks for the info. That will work for what I initially need. The requirements are very "thin" so I'm trying to undestand adv/disadv for static and more dynamic approach.

Your suggested approach will help me with % and counts which meets current requirements. I'm trying to understand what will I have to do if users requests to do AVG on specific questions (SUM should be very rare).

I think there would be some kind of comprise here. I could potentially record numbers if fact table using "universal" units and do AVG but if they use cube I would probably have to overwrite any fact values that are above question level to "not applicable" and do it only on question level. For example alcohol unit consumed would need to take frequency into consideration 30units per month could be recorded as 1 (per day) in fact table together with answer dimension (free text). Sounds complicated so I hope I won't have to do that.