SSAS 2008R2 mdx query using Date and Parent Child in WHERE clause

Question

I newbie in olap using AdventureWorksDW2008R2 db and Adventure Works 2008R2 olap with Adventure Works cube.

I want to write an mdx query as per the result using T-SQL: -

select 
  FactResellerSales.OrderDateKey, 
  SUM(FactResellerSales.SalesAmount) as 'Reseller Sales Amount' 

from FactResellerSales with (nolock)

inner join DimEmployee on DimEmployee.EmployeeKey = FactResellerSales.EmployeeKey

where 
   FactResellerSales.OrderDateKey=20070901
   and DimEmployee.FirstName='Jae' and DimEmployee.LastName='Pak'

group by FactResellerSales.OrderDateKey

The result:

OrderDateKey      Reseller Sales Amount
20070901          415046.9295

Using the mdx query, I can only execute the following: -

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,

NON EMPTY [Date].[Date].members ON ROWS

FROM [Adventure Works]

WHERE [Employee].[Employees].&[291]

The result is from July 1, 2006 to June 1, 2008 with the respective Reseller Sales Amount summary for each day.

My questions are, within the mdx query WHERE clause how do I:

a) Filter by date which is September 1, 2007? It doesn't accept the date dimension.

b) Filter by Employee using the name 'Jae B. Pak' which is in a multi-level hierarchy instead of using the EmployeeKey which is 291.

Can you provide more information about the structure of the Employee dimension? Has the dimension some attribute properties which might be used for the name? — Emiliano Poggi
My apologies, I am a newbie. How to I provide the information on the structure of Employee dimension? It is the example cube provided by Microsoft. — user2189798

Daryl Wenman-Bateson Daryl Wenman-Bateson · Accepted Answer · 2013-03-24T21:09:57

You need to crossjoin the employee dimension with the date you are interested in.

The following will give you just the value;

SELECT { [Measures].[Reseller Sales Amount] } ON COLUMNS
FROM [Adventure Works]
WHERE ({ [Employee].[Employee Department].[Employee].[Jae B. Pak]},
         {[Date].[Date].&[20070901] })

The where clause in MDX is a slicer which behaves differently from the SQL where clause (worth reading about). The following cross join on rows would provide the same result without using the slicer, but will include the dimension attributes within the result.

SELECT { [Measures].[Reseller Sales Amount] } ON COLUMNS,
CROSSJOIN({ [Employee].[Employee Department].[Employee].[Jae B. Pak]}, {[Date].[Date].&[20070901] }) ON Rows
FROM [Adventure Works]

and finally, if you want to return based on a 'SQL Like', you can use instr (the following gives all employees where the name include ja)

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
{FILTER([Employee].[Employees].allmembers,
instr([Employee].[Employees].currentmember.member_caption,'Ja')>0) *  [Date].[Date].&[20070901]} ON ROWS
FROM [Adventure Works]

hopefully that will get you going

SSAS 2008R2 mdx query using Date and Parent Child in WHERE clause

1 Answers