Thread: OLAP Design or MDX Syntax to handle 1-many and distinct counts

1. Registered User
Join Date
Jan 2008
Posts
2

Unanswered: OLAP Design or MDX Syntax to handle 1-many and distinct counts

All,

My first post so go easy. Also, still a bit green on both OLAP and MDX but I need help figuring this out.

The data set I'm using has patient level information and I'm interested in distinct counts of patients. There are several dimensions I have in my existing cube but there are three different types. I have a time dimension, a 1-1 dimension and a 1-many dimension (sorry not getting the proper terminology correct here).

The time dimension (month is the lowest level) I have a handle on as well as the 1-1 dimension (lets say age or gender is a good example). For the purpose of the explanation, lets just add a cost measure for a placeholder. The bugger is the 1-many dimension. When you visit a doctor, you typically get diagnosed with a code. The code rolls up to a disease category. THis happens every visit and every visit can have multiple codes. For example, you come in with a broken arm and they diagnose you with the flu too.

The business question I'm trying to answer would be something like:

How many female patients have been diagnosed with disease Y OR disease Z or disease T in the past 12 months? and how much did it cost?

Please note in the above question, the operand is "OR" meaning they would have to present with any 1 of the three to meet the criteria. Its my understanding cubes typically will be additive in nature and try to come back with counts of females with instances of all three. This is not the case and fails the business need.

I know this is a high level question but any ideas into either a design or a raw MDX syntax to tackle the issue would help.

Thanks! jmac

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Originally Posted by jmac
How many female patients have been diagnosed with disease Y OR disease Z or disease T in the past 12 months? and how much did it cost?
Code:
```SELECT disease
, Count(*)
, Sum(cost)
FROM    diagnosis
INNER
JOIN patients
ON patients.patientID = diagnosis.patientID
WHERE  pateint.gender = 'F'
AND    disease IN ('Y', 'Z', 'T')
AND    diagnosis_date >= DateAdd(mm, -12, GetDate())
GROUP
BY disease```
??

3. Registered User
Join Date
Jan 2008
Posts
2
I need to have the information cubed up. Straight SQL is not an option for me at this time (millions of rows of data). Appreciate the feedback on an approach.

Originally Posted by georgev
Code:
```SELECT disease
, Count(*)
, Sum(cost)
FROM    diagnosis
INNER
JOIN patients
ON patients.patientID = diagnosis.patientID
WHERE  pateint.gender = 'F'
AND    disease IN ('Y', 'Z', 'T')
AND    diagnosis_date >= DateAdd(mm, -12, GetDate())
GROUP
BY disease```
??

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•