Logical AND and logical OR with MDX

In principle:

A set of members from the same hierarchy implies a logical OR;

A set of tupels from different hierarchies implies a logical AND.

How to make a logical AND with members from the same hierarchy

From the AdventureWorks 2012 OLAP cube, we want to find out the customers who purchased the products "Road-250 Black, 44" AND "Road Bottle Cage".

In order to do this, we need to use the EXISTS function. This function takes 3 parameters, the first 2 being sets where it looks for members from the first set that also exists within the second set. The third parameter is a measure group which provides the context for the 2 member to associate fact rows and search for the results.


SELECT
{} ON 0,
EXISTS(
EXISTS(
{[Customer].[Customer].[Customer].MEMBERS},
{[Product].[Product].&[347]},"Internet Sales"),
{[Product].[Product].&[479]},"Internet Sales") ON 1
FROM [Adventure Works]

In the above code, two nested EXISTS functions are used on the row axis. The inner function will return the set of customer members from all the members of the customer dimension who exists within the "Internet Sales" measure group for the product member key 347 which is the product "Road-250 Black, 44" from the product dimension. The resulting customer members from the inner EXISTS function will then be fed in to the outer EXISTS function. The outer function will then return the set of customer members from the fed-in members who exists within the "Internet Sales" measure group for the product member key 479 which is the product "Road Bottle Cage".


How to make a logical OR with members from different hierarchies.

From the AdventureWorks 2012 OLAP cube, we want to find the Reseller Sales Count by Product Sub Category for products who's colour is black OR who's size range is XL.

SELECT
[Measures].[Reseller Order Count] ON 0,
NON EMPTY {[Product].[Subcategory].MEMBERS} ON 1
FROM (SELECT
{([Product].[Color].&[Black],[Product].[Size Range].[All Products]),
([Product].[Color].[All Products],[Product].[Size Range].&[XL])} ON COLUMNS
FROM [Adventure Works])

In the above code the 2 hierarchies of the product dimension is brought into the same dimensionality by adding in the [All Products] member expression in both [Product].[Color] and [Product].[Size Range] hierarchies in the FROM clause. It creates a set comprising of 2 tuples, one for colour black among all the size ranges and the other for XL size among all the colours.



0 comments:

Post a comment