Problem solve Get help with specific problems with your technologies, process and projects.

Data warehouse for hospital patient information

I am building a data warehouse that captures hospital patient information. My issues arrises with the patients diagnosis and treatements. I have a normalized database with a patient table, a diagnosis table and a treatment table. I would like to have the treatments and diagnosis dimensions in the same cube (star schema), but a patient may have many diagnosis and the patient may have many treatments. So my counts are extremely exaggerated. I would like to avoid the issues associated with distnict counts (drill through's are confusing, MDX filtering is limited...). Are there any other methods to acheive better results?

Is there a direct relationship between diagnosis and treatment, and if so what are the facts of interest in this intersection? What is in the fact table of the star that has diagnosis and treatment as dimensions? Maybe the dimensions you are after are the "types" of treatment, where treatments can be categorized or characterized by some meaningful criterion. Likewise for the diagnosis. Surely there must be a huge number of possible derivatives for a diagnosis or a treatment. Maybe you could consider aggregating the number of treatments for a particular type of treatment and the number of diagnosis for a type of diagnosis. Still, I wonder what correlation between diagnosis and treatment you are trying to discover? Is it the average duration of treatment given certain types of treatments for certain types of diagnosis? Is it the tally of treatments that were considered completely successful, moderately successful, etc.?

For More Information

Dig Deeper on Oracle data warehousing