CODEWITHSUNDEEP
ssasmdx
nop
nop

Reputation: 5

MDX Problems grouping Dimension with Fact-Table

I'm new on the SSAS and MDX. We have the following structure of our data:

FactTable(Medical cases):

ID | Date | Result Test A | Result Test B | Patient ID

1 | 1.9.15 | 1 | -1 | Patient_1

2 | 1.9.15 | -1 | 1 | Patient_1

3 | 1.9.15 | 1 | -1 | Patient_2

4 | 1.9.15 | 0 | -1 | Patient_2

5 | 1.9.15 | -1 | 0 | Patient_2

Every case has the result either for test a or for test b. It is possible that a case contains results for test a and test b but in the most cases the result for test a is filled and the result for test b is empty or reversed.

Patient Dimension:

ID | Firstname | Lastname | Date of Birth

1 | Max | Mustermann | 1.9.1989

2 | Tina | Mustermann | 1.9.1989

UPDATED

I have added the dimension for the results

Result Test A Dimension:

ID | Name

-1 | Undefined

 0 | Negative

 1 | Positive

 2 | Suspect

Result Test B Dimension:

ID | Name

-1 | Undefined

 0 | Negative

 1 | Positive

We have a foreign key relation between

  1. [FactTable].[Patient ID] and [Patient].[ID]
  2. [FactTable].[Result Test A] and [Result Test A].ID
  3. [FactTable].[Result Test B] and [Result Test B].ID

In addition to this structure we have dimension for the column date.

Our Measurements we have:

  1. [Measurement].[Number of Cases] (Count on [FactTable].[ID])
  2. [Measurement].[Number of Patients] (Count on [FactTable].[Patient ID])

Our Problem is that we want to create a Measurement for the Number of Patients and combine that with the results. So that we get the following result when we Filter vor Result Test A und Result Test B.

Number of Patients (Filter: [Result Test A].Positive, [Result Test B].Positive) -> 1 

Number of Patients (Filter: [Result Test A].Negative, [Result Test B].Negative) -> 1

Number of Patients (Filter: [Result Test A].Positive) -> 2

Number of Patients (Filter: [Result Test B].Positive) -> 1

I have no clue how to achive this Measurement. Is this even possible or do i have to create another FactTable for Patients?

Upvotes: 0

Views: 106

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

I am confusing about your FactTable, are Result Tests measures? How do you handle strings in the FactTables?

This code is not tested but you can give a try: Create query members

EDIT: Added untested code, I know nothing about your cube structure so I guessed a bit.

WITH MEMBER [Measures].[Patients Results A] AS
  Sum
  (
    [Patient].[PatientID].[PatientID]
    , IIf
    (
      ( [Measures].[Medical Case Ms], [Result A].[Name].CurrentMember ) = NULL
      , NULL
      , 1
    )
  )
MEMBER [Measures].[Patients Results B] AS
  Sum
  (
    [Patient].[PatientID].[PatientID]
    , IIf
    (
      ( [Measures].[Medical Case Ms], [Result B].[Name].CurrentMember ) = NULL
      , NULL
      , 1
    )
  )
MEMBER [Positive A] AS
  ( [Measures].[Patients Results A], [Result A].[Name].&[1] )
MEMBER [Positive B] AS
  ( [Measures].[Patients Results B], [Result B].[Name].&[1] )
MEMBER [Positive A&B] AS
  ( [Positive A] + [Positive B] )
SELECT
{ [Positive A], [Positive B], [Positive A&B] } ON COLUMNS
FROM [Cube]

In the above code were created members for Positive A and Positive B measures, you have to create the missing members for the measures you specified in your question. Let me know if this was helpful

Upvotes: 1

Related Questions