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 AND On Members of the Same Hierarchy

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

In my prior blog post, I discussed implementing logical OR in MDX on members of different hierarchies.  As a follow-up to that post, I will now demonstrate how to implement logical AND on members of the same hierarchy.  If you have not read my prior post, I recommend doing so before proceeding.

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 AND logic to members of the same hierarchy?

For example, what if we want to only see data for months where we sold products to customers in both Mexico and Brazil?

Let’s run an MDX query for Brazil only. 

SELECT {[Measures].[Unique Purchasers]} ON 0, NON EMPTY {[GL Date].[Fiscal Month Of Year].MEMBERS} ON 1
FROM [MyCube]
WHERE {[Customers].[Country].&[Brazil]}

As we can see from the output we started selling product to customers in Brazil starting in Aug 2013.

image

Now let’s run a similar MDX query for Mexico.  The output is shown below:

image

In this case, we have data back to 2011.

If we restrict this output to only include Aug 2013 onward, we see that we have sold product to 54 unique customers in Mexico.

SELECT {[Measures].[Unique Purchasers]} ON 0
FROM [MyCube]
WHERE ({[Customers].[Country].&[Mexico]},{[GL Date].[Fiscal Month Of Year].&[Aug 2013]:NULL})

image

To only include data for months when we sold product to customers in both Mexico and Brazil, we can use the following MDX query:

SELECT {[Measures].[Unique Purchasers]} ON 0, NON EMPTY {[Customers].[Country].MEMBERS} ON 1
FROM (
        SELECT
           {[Customers].[Country].&[Brazil],[Customers].[Country].&[Mexico]} ON 0
        FROM [MyCube]
      )
WHERE
(
    Exists(
    Exists( {[GL Date].[Fiscal Month Of Year].[Fiscal Month Of Year].MEMBERS},
        {[Customers].[Country].&[Brazil]}, “Sales”
        ),
        {[Customers].[Country].&[Mexico]}, “Sales”
        )
    )   

The output of this query is:

image

As you can see, the output for both Brazil and Mexico is the aggregate of the months starting in Aug 2013 onward since we are only including months where we had sales in both countries.

A few note-worthy details:

In this query, we have a sub-select that includes the two members [Customers].[Country].&[Brazil] and [Customers].[Country].&[Mexico].

In the WHERE clause we are using two Exists, the first argument is the ‘Fiscal Month of Year’ level (not hierarchy).  It is important to note that you need to use the level and not the hierarchy for this query to work properly.  We also need to specify the measure group of interest, in this case “Sales”.  The second Exists function uses the output of the initial Exists as its first argument.

Comments

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

Loading comments...