Painted into a healthcare DW "digital corner" -- or is it wrong to have keys and attributes in a single table

[pzajkowski]

Here's the short version: Is it OK to have a table which contains keys from other dimensions AND includes additional fields for attributes? (Is this a dimension, outrigger, factless, or merely unusual or just wrong?)

Here's the long version (with background information):
I've been maintaining a healthcare guidelines data warehouse that was originally built by someone else. The basic purpose of the warehouse is to track a patient's compliance to various defined guidelines according to the condition of the patient: for example, if a patient is Obese, there are certain tests/labs that should be performed in a given time period. A guideline can essentially be defined by three components: patient condition, a subpopulation criteria that refines the targeted patient population more specifically, and the service to be measured ("Service Metric"):

For example:
Patient Condition = Patient is Obese
Subpopulation = Patient does not have diabetes
Service Metric = Patient has had a diabetic screening within the last 3 years.

Using the three components, a variety of mixing-n-matching of various conditions to various services to be measured can be defined. (One type of service that is fairly common across any chronic patient condition is an annual office visit.) A PatientCompliance fact table tracks whether a patient is compliant for the relevant combination of condition/subpopulation/service metric per datekey.

So, at the time this data warehouse was developed, a decision was made to create a separate dimension for each of the three guideline measure components: DimCondition, DimSubpopulation, and DimServiceMetric. The main argument at that time for this approach was that it afforded simplicity, reduced redundancy, and ensured consistency of attribute descriptions. A fourth table keeps track of the various combinations of the keys, along with a very detailed description of what each combination is measuring; rarely is this very detailed description actually used for any reporting-- instead, the attributes from the three dimensions are referenced in all reports.

Given all that I've read in the various Kimball books, it seems that the three separate dimensions (DimCondition, DimSubpopulation, DimServiceMetric) are unnecessary, and that a single dimension might have been the better choice. But alas, too many processes and reports have been built around the three-dimension model since its inception, that changing the structure isn't an option. Fortunately, the current structure hasn't been difficult to maintain nor to generate reports.

So, here's the newest problem. The company is implementing an initiative to focus attention on a very small slice of chronic conditions (e.g., Diabetes , Cardiac care) and related essential services. We've found that our physicians can be overwhelmed by all of the information we provide regarding their patients' performances for all guidelines, so an effort is being made to target only the most critical care that we believe has the best return regarding patient health prevention/maintenance and curbing of expenses. A total of three "target programs" are planned to be rolled out - each target program has three subprograms (overdue office visits, overdue labs/tests, and test results not meeting goal)

I'm struggling with how to model this new initiative. In the past, the highest rollup has been at the Condition level, but this new initiative actually repackages some of the conditions into "target programs", thus creating a new highest rollup grouping. My initial thought was to create a separate dimension (DimTargetProgram) that only contains the three target programs along with the three subprograms, ultimately generating a 9-row dimension; a second table would combine the keys from DimTargetProgram with the keys from DimCondition, DimSubpopulation, and DimServiceMetric, as well as fields for rowstartdate & rowenddate. But, this snowflake approach seems unnecessary, and that only a single table that combines the Target program/subprogram attribute descriptions with the keys from DimCondition, DimSubpopulation, and DimServicemetric is the better way to go. But, I don't ever recall seeing in any Kimball book a table with multiple key fields combined with multiple attribute fields.

What do others people think?

Thanks in advance
Pete

[rob.hawken]

Hi,

If I understand correctly then it sounds like you need to create a new dimension DimTargetProgram which would have the Target Program & Sub Program attributes. This would then be added to the relevant fact table(s). So your fact table would consist of the existing 3 dimension keys (PatientCondition, SubPopulation & ServiceMetric) and the new DimTargetProgram key and whatever other dim keys and measures there are.
The population of the DimTargetProgram key on the fact table would require look-ups to the 3 existing dimensions, so I'd suggest you add their business keys as attributes to DimTargetProgram.
Trust this makes sense.

[Jeff Smith]

I am not entirely sure what the ultimate objective is. But 2 thoughts come to mind.

One concept is a factless fact table that defines the target program. It has the combination of the dimension keys that is essentially the program.

The other concept is creating the information for the patients in the target program. In the staging area, you can create whatever you need to create to facilicate this. If you can utilize the factless fact table to load the fact table with the patinet info, then great. But you might need to create other staging tables that contain the combinations of the conditions, treatments, etc.

I suspect that you should be prepared to add new targetted conditions to the list.

You may want some type of reference table that has the combination of the different conditions, treatments, etc.

I would try to kept the design of the final tables simple - avoiding the snow flake. Remember, the star schema is intended to make it easy to use. The downside is that it can be hard to design.

I know people who think that basically anything goes in the staging area as long as the finished product does what you need it do and adheres to the star.

[Sponsored content]