Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Question 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. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote 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
    ??
    George
    Home | Blog

  3. #3
    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.


    Quote 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
  •