Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.

MDX Implementing Logical OR On Members of Different Hierarchies

Reference: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook  by Tomislav Piasevoli

In MDX, when two or more members of the same set are placed on a slicer, OR logic is automatically applied.  For example, if you have two members (e.g. sizes ‘Small’ and ‘Large’) on a slicer, the resulting dataset will contain data where the first, second or both members occurred in the associated fact table.  Similarly, a tuple formed by members of multiple hierarchies (e.g. size ‘Small’ and color ‘Red’)  will only return results that have data associated to both members; AND logic is applied in this instance.

To summarize, in MDX

  • Sets imply logical OR
  • Tuples imply logical AND

What if you need to apply OR logic to members of different hierarchies (e.g. tuples)?

In this example, we will combine the results of the following two MDX queries into a single query by implementing OR logic between members of the two hierarchies.

The first MDX query returns the quantity shipped for orders handled by Inside Sales Reps, broken down by fiscal month of year:

SELECT {[Measures].[Quantity Shipped]} ON COLUMNS, NON EMPTY { [GL Date].[Fiscal Month Of Year].MEMBERS } ON ROWS
FROM [MyCube]
WHERE [Sale Source].[ISR].&[Y]

The second MDX query returns the quantity shipped for online orders, again broken down by fiscal month of year:

SELECT {[Measures].[Quantity Shipped]} ON COLUMNS, NON EMPTY { [GL Date].[Fiscal Month Of Year].MEMBERS } ON ROWS
FROM [MyCube]
WHERE [Customer].[Market].&[Online]

To combine the result of the above queries in a single MDX query, we must first take the two members ([Sale Source].[ISR].&[Y] and [Customer].[Market].&[Online]) and convert them into tuples of the same dimensionality.  To accomplish this, we combine each member with the ‘All’ or root member of the other dimension as shown below (in this case, we have two tuples):

([Sale Source].[ISR].&[Y], [Customers].[Market].[All])

([Sale Source].[ISR].[All],[Customers].[Market].&[Online])

We then combine these tuples into a set (the tuples are separated by a comma and surrounded by curly brackets that define the set).  The set is located in the WHERE clause of our new query:

SELECT {[Measures].[Quantity Shipped]} ON COLUMNS, NON EMPTY { [GL Date].[Fiscal Month Of Year].MEMBERS } ON ROWS
FROM [MyCube]
WHERE { ([Sale Source].[ISR].&[Y], [Customers].[Market].[All]), ([Sale Source].[ISR].[All],[Customers].[Market].&[Online])}

In this case, the [Quantity Shipped] value from our combined query is the sum of the [Quantity Shipped] values from the original two MDX queries since there in no overlap in each queries resulting output.

Comments

Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...