Dealing with Distinct Counts in OLAP. The "Distinct Count" problem is also known as the "many-to-many" problem. EG:

My data set involves health care. Some of the data involves patients, the different diagnoses they've had, and charges for visits. A typical view might look like this:

Charges Patients DistinctPatients
All Diagnosis $900 30 5
Injury and Poisoning $100 5 2
Mental Disorders $200 5 2
Neoplasms $300 8 4
Other Diagnosis $300 12 3

Note that the Charges add up (100+200+300+300 = 900), and Patients add up (5+5+8+12 = 30), but the Patients don't (2+2+4+3 != 5). This is correct because there were only 5 patients and some patients had multiple diagnosis. EG: There were only two patients diagnosed for Mental Disorders: Sam four times and Sue once.

Page Modified: (Hand noted: 2007-10-12 02:39:23Z) (Auto noted: 2007-11-17 06:45:59Z)