SSAS MDX WHERE clause syntax - filtering multiple values from the same hierarchy

Question

I'm quite new to data warehousing and MDX queries, and I realize that I'm probably not using the MDX syntax properly in the case below.

I'm using SSAS 2005 on MS SQL Server 2005 and would like to query my cube and filter the results with multiple members from the same hierarchy (the filtered members are "sibling"), given a specific year ([2013]) and markets breakdown on rows.

WITH
    MEMBER [Measures].[Value] AS Format([Measures].[Expected Sale Price EUR], '#,###')
    MEMBER [Measures].[Pieces] AS Format([Measures].[Line Quantity], '#,###')
SELECT
    CrossJoin(     
    {[Time].[Calendar Year].&[2013]},
    {[Measures].[Value],[Measures].[Measures].[Pieces]}) 
        ON COLUMNS,

    {[Customers].[Markets].Members} 
        DIMENSION PROPERTIES MEMBER_NAME ON ROWS
FROM [Po System] 
WHERE ({[Order Type].[Order Type].&[1], 
        [Order Type].[Order Type].&[5], 
        [Order Type].[Order Type].&[6]},
       {[Customers].[Customers].&[1012],
        [Customers].[Customers].&[1922]})

The result I get is an aggregation of the measurables for the whole market to which those customers belong.

The thing is that if I use only member of the [Customers] dimensions inside the WHERE clause, and only if I don't wrap it with { } brackets, I get the results I want - aggregation of the data for customer [1012] only, as it should be (verified with my original data source).

Here's an example for a WHERE clause in the same query that gives me the correct results for one customer filtering:

WHERE ({[Order Type].[Order Type].&[1], 
        [Order Type].[Order Type].&[5], 
        [Order Type].[Order Type].&[6]},
       [Customers].[Customers].&[1012])

In the query with this WHERE clause I get the proper results, but just for one member.

I constantly verify the integrity of the data in my cube by using the Cube Browser within the BI Development Studio (v. 2005).

I've also looked into the SQL Server Profiler to get the queries generated by the Cube Browser, but those are unreadable and non-executable from the Management Studio, where are run my MDX queries for tests.

Any idea on what's wrong with the syntax I use?

Are you sure that you are actually getting a total for the entire markets to which those customers belong? Or is it showing you the market names next to the totals of those 2 customers? What do you get if you change the {[Customers].[Markets].Members} to {[Customers].[Customers].children}? — mmarie
I'm pretty certain. If I replace the Members attribute to Children (for the markets), then I still get an aggregation. I did manage to get the answer from my problem, please see below. Thanks fro replying! — Yuval Herziger
By the way, I'm certain because I have two cross-validation practices - (1) using the cube browser within the BI Dev. Studio, and (2) querying the data from the original data source, an SQL database. But that was actually a problem with me misunderstanding the concept of MDX WHERE clause rather than anything else :) See offered solution below. — Yuval Herziger

Yuval Herziger Yuval Herziger · Accepted Answer · 2014-01-05T10:05:07

OK, so thanks to a helpful BI consultant who answered the question in a group on another network, I understand the correct line of thinking for MDX queries.

The WHERE clause in MDX can be considered as an additional axis within the query's cube space. This axis, often referred to as the slicer axis, has one position occupied by the tuple assigned to the WHERE clause. The member or member combinations at this one position influence every tuple in the query’s cube space.

A useful practice to implement the filter in this scenario would be using an inner query.

The below query has perfectly delivered the desired results.

WITH 
    MEMBER [Measures].[Value] AS Format([Measures].[Expected Sale Price EUR], '#,###') 
    MEMBER [Measures].[Pieces] AS Format([Measures].[Line Quantity], '#,###') 
SELECT CrossJoin( 
    {[Time].[Calendar Year].&[2013]}, 
    {[Measures].[Value],[Measures].[Measures].[Pieces]}) 
    ON COLUMNS, 
    {[Tbl Customers].[Market Name].Members } 
    DIMENSION PROPERTIES MEMBER_NAME ON ROWS 
FROM
    ({[Measures].[Expected Sale Price EUR],[Measures].[Line Quantity]})on COLUMNS, 
    ({[Customers].[Customers].&[1012],,[Customers].[Customers].&[1922]}) on ROWS 
    FROM [Po System] 
    WHERE ({[Order Type].[Order Type].&[1], 
        [Order Type].[Order Type].&[5], 
        [Order Type].[Order Type].&[6]}))

Again, credits for the person who offered a closer look into the concept of the WHERE clause in MDX, which is conceptually different from an SQL WHERE clause in many ways.

Do pay attention to the fact that members I declare in the outer query, for the sake of number formatting, are unrecognized within the scope of the inner query. That's why the measurables in the inner MDX query are referred by their original name and the formatting is relevant for the outer query.

SSAS MDX WHERE clause syntax - filtering multiple values from the same hierarchy

1 Answers