I have the following scenario which i am trying to model for a OLAP cube. I am currently using Microsoft Analysis Services.
We have many studies. A Patient always belongs to 1 study. A Patient can have 1 or many visits for a study. During a visit a patient can fill 1 or many forms. Forms are different for different studies. The same form can have different questions for different studies. A Patient fills a Central Demographics form which has information about the patient. The Central Demographics form can have different questions depending upon the study.
I have a Study Dimension, Patient Dimension, Visit Dimension and a Fact table with foreign keys to Study, Patient and Visit Dimension. The questions on the Central Demographics form are attributes for a Patient Dimension. But as these vary study to study so i am confused how would i handle them. For example Study A can have 4 questions on a Central Demographics form where as Study B can have 8 question on a Central Demographics form.
I would really appreciate if someone can guide me with the modelling for this kind of scenario.
Thanks.
Kimball uses the term "Multivalued Dimensions and Bridge Tables" for this. You would group your survey/form questions and link questions from the individual question dimension to the group via a many-to-many bridge. Here is an MSDN article about how to model this with SSAS. By the way, I have not yet encountered a dimensional modelling problem that Kimball has not documented a solution for.