CODEWITHSUNDEEP
t-sqlssasmdx
BI Dude
BI Dude

Reputation: 2016

What is the difference between WHERE & FROM in MDX query?

What is the difference between WHERE & FROM looking from T-SQL perspective.

Please see a copy of Pivot Table MDX. I have filters on [Customer Status], [Enquiry Creation Date.Date Hierarchy] and [User-Team Hierarchy] in the pivot table.

I do not understand why some filters go to WHERE and others to FROM.

SELECT
{ [Measures].[Unique Cust Ref Distinct Count], [Measures].[Number of Distinct Customers] } ON COLUMNS,
{ [Request].[Cust Type].children } ON ROWS
FROM (
  SELECT (
  { [Request].[Request Created By Team].&[CINS]
    ,[Request].[Request Created By Team].&[CIST]
    ,[Request].[Request Created By Team].&[DCISS]
    , [Request].[Request Created By Team].&[WRT] } ) ON COLUMNS
  FROM [CRM Reporting]
)
WHERE ( [Customer].[Customer Status].&[Active], [Request Creation Date].[Date Hierarchy].[Year].&[2014] )

Upvotes: 0

Views: 385

Answers (1)

FrankPl
FrankPl

Reputation: 13315

The main differences between WHERE and subselect in MDX are:

  • WHERE changes the slicer axis, which means you cannot put any hierarchy on any other axis like columns and rows.
  • Subselect does not change the CurrentMember setting. This means that it is more likely that you can get into trouble if you use multiselect in a WHERE condition. On teh other hand, you cannot easily reference the selected members if you use multiselect in a subselect , you would use something like EXISTS.
  • Subselect changes the value of the All member of the hierarchies selected ("VisualTotals") - at least in the default setting. However, normally, the different behavior of the WHERE condition with regard to this cannot be seen, as the All member - as it is part of the hierarchy - cannot be put on any other axis. It could - however - be referenced in calculated members.
  • Subselects can be nested, WHERE cannot.
  • Performance wise, sometimes one and sometimes the other is better. You will have to test.

Upvotes: 3

Related Questions