15 Aug 06 16:04
I am exploring the design options for the dimensional model of Health Care System. In this design we don't have any financial data or any sort of concrete measurements. The business process works as follows:
The patient enters into the Hospital or Health Services Center and answers some questions on an assessment form. And by the answered questions, it is determined what kind of services are needed by the patient. This entire process is termed as "in-take assessment" and focuses solely on assessment.
Mostly the reports will be measuring the count of assessments based on various dimensions. In this model we have Patient, Assessing Organization, Diagnosis (multivalued), Time, User (one who assesses), Assessment Type, Care Product, etc. as Dimensions and the Actual Assessments as the Factless Fact. The assessment process form consists of objective questions(defined values/radio button choices - mostly int or string) and open-ended questions (free text). There are approximately 100 questions in the form. The reporting criteria(where clause) can be one of the above mentioned dimensions or may be one of the answers given by the patient in the form. If I create one dimension per Q/A it will be a centipede design with too many dimensions.
Now my question is
*whether the assessment Fact should contain all answers in one row in different question columns (in which case, it will be a very big table - approximately 120 columns in one row with 30,000 Assessments each month) or
*should the questions be in a dimension, in which case the dimension will be as large as the fact, in terms of rows. It will act more like a fact table. It is like having a Primary Key in the Fact table Assessment and Foreign Key in the Assessment Q/A table or
*should I try to come up with various combinations of possible answers for these questions and then put them in a dimension, in which case the dimension again will be very large or
*should I distribute the questions in combinations of 20 each and then apply possible answer combinations in 5 different dimensions or
*should I create 1 dimension per questions/answer (centipede design)?
Any thoughts/suggestions/guidance will be really appreciated.