A simple MDX question

Question

Hi,

I am new to MDX and I know that this must be a simple question but I haven't been able to find an answer.

I am modeling a a questionnaire that has questions and answers. What I am trying to achieve is to find out the number of people who gave specific answers to questions., e.g. the number of males aged between 20-25

When I run the query below for the questions individually the correct result is returned

SELECT
      [Measures].[Fact Demographics Count] ON Columns
FROM
      [Dsv All]            
WHERE
      [Answer].[Dim Answer].&[1]

[Measures].[Fact Demographics Count] is a count of the primary key column

[Answer].[Dim Answer].&[1] is the key for the Male answer

Result for number of people who are male = 150 Result for number of people who are between 20-25 = 12

But when I run the next query below rather than getting the number people who are males and aged between 20-25. I get the sum of the number of people who are males and the number of people who are between 20-25.

SELECT 
      [Measures].[Fact Demographics Count] ON Columns
FROM
      [Dsv All]            
WHERE
      {[Answer].[Dim Answer].&[1],[Answer].[Dim Answer].&[9]}

result = 162

The structure of the fact table is

FactDemographicsKey,

RespodentKey,

QuestionKey,

AnswerKey

Any help would be greatly appreciated

Thanks

Answer 1

If you are using MSSQL, you can use the "WITH ROLLUP" to get some extra rows which would have the information you want. Also, you are not using a "GROUP BY" which you will need.

Use the GROUP BY to break up the set into groups and then use aggregate functions to get your counts and other stats.

Example:

select AGE, GENDER, count(1)
from MY_TABLE
group by AGE, GENDER
with rollup

This would give you the number of each gender of person in your table in each age group, and the "rollup" would give you the total number of people in your table, the numbers in each age group regardless of gender, and the numbers of each gender regardless of age. Something like

AGE GENDER COUNT
--- ------ -----
 20      M  1245
 21      M  1012
 20      F   942
 21      F   838
         M  2257
         F  1780
 20         2187
 21         1850
            4037

Answer 2

Take a look at the MDX function FILTER - this may give you what you need. A combination of FILTER and Member Properties to filter against the ID's might do it. You're having a problem because what you're trying to do is a little against the grain of how an OLAP cube is structured (from my experience) because Age and Gender are both members of the same dimension (Answers), which means that they each get their own cells for aggregation, but unlike if Age and Gender were each on their own dimension, they don't get aggregated with respect to one another except to get added together. In an OLAP cube, each combination of each member of each dimension with each member of every other dimension gets a "cell" with the value of each measure that is unique to that combination - that is what you want, but members of the same dimension (such as Answers) aren't cross-calculated in that way.

If possible, I would recommend breaking out the individual answers into individual dimensions, i.e. Age and Gender each have their own dimensions with their own members, then what you want to do will naturally flow out of your cube. Otherwise, I'm afraid you will have lots of MDX fiddelry to do. (I am not an MDX expert, though, so I could be completely off base on this one, but that is my understanding)

Also, definitely read the book previously mentioned, MDX Solutions, unless this is the only MDX query you think you'll need to write. MDX and Multidimensional analysis are nothing like SQL, and a solid understanding of the structure of an OLAP database and MDX in general is absolutely essential, and that book does a very, very nice job of getting you where you need to be in that department.

Answer 3

When trying to figure out problems with where-criteria or slices I find it helpful to breakdown the items that you're slicing on into dimensions, rather than measures.

select
      [Measures].[Fact Demographics Count] on Columns
from  [Dsv All]            
where
{
    [Answer].[Dim Answer].&[1],
    [Dim Age Band].[20-25]
}

Although then you're not really using the power of MDX - you're getting just one value.

select
      [Dim Answer].Members on Columns,
      [Dim Age Band].Members on Rows
from  [Dsv All] 
where ( [Measures].[Fact Demographics Count] )

Will give you a pivot table (or crosstable) breaking down gender (on columns) by age-bands (on rows).

BTW - ff you're learning MDX this book: MDX Solutions is far and away the best starting point that I've found.

Answer 4

Hi,

Firstly thanks to everyone for their replies. This was an interesting one to solve and for anyone new to MDX and coming from SQL its an easy trap to fall into.

So for those interested here is a brief overview of the solution.

I have 3 tables

1. factDemographics: holds respondents and their answers (who answered what)

2. dimAnswer: the answers

3. dimRespondent: the respondents

In the datasource view for the cube I duplicated factDemographics 5 times using Named Queries and I named these fact1, fact2, ..., fact5. (which will create 5 measure groups)

Using VS Studio's create cube wizard I set the following fact tables

1. fact1, fact2, ... as fact tables
2. dimRespondent a fact table. I use this table to get the number of respondents.
3. Removed the original factDemographics table.

Once the cube was created I duplicated the dimAnswer dimension 5 times, naming them filter1, filter2, ...

Finally in the Cube Structure's Dimension Usage tab I linked these together as follows

filter1 many to many dimRespondent

filter2 many to many dimRespondent

filter3 many to many dimRespondent

filter4 many to many dimRespondent

filter5 many to many dimRespondent

filter1 regular relationship fact1

filter2 regular relationship fact2

filter3 regular relationship fact3

filter4 regular relationship fact4

filter5 regular relationship fact5

This now enables me to rewrite the query I used in my original post as

SELECT
    [Measures].[Dim Respondent Count] On 0
FROM
    [DemographicsCube]
WHERE
    (
     [Filter1].[Answer].&[Male],
     [Filter2].[Answer].&[20-25]
    )

My query can now be filtered by up to 5 questions.

Although this works I'm sure that there is a more elegant solution. If anyone knows what that is I'd love to hear it.

Thanks