MDX: Slicer Override

In MDX, the WHERE-clause is commonly referred to as the “slicer”. This is because the criteria placed in this clause is used to “slice the cube”. Any query scoped named sets or members will be created/calculated within the context specified in the WHERE-clause.

For example, the query below will return the Internet Sales Amount for each Calendar Year:

SELECT
    {
         [Measures].[Internet Sales Amount]
    } ON 0,
    {
        [Date].[Calendar].[Calendar Year]
    } ON 1
FROM
    [Adventure Works]

Query 1

Now, if I add a WHERE-clause and specify the slice for 2006…

SELECT
    {
         [Measures].[Internet Sales Amount]
    } ON 0,
    {
        [Date].[Calendar].[Calendar Year]
    } ON 1
FROM
    [Adventure Works]
WHERE 
    [Date].[Calendar Year].&[2006]

…then we get only the Internet Sales Amount for Calendar Year 2006…

Query 2

However, there are certain scenarios that come up where the criteria specified in the WHERE-clause needs to be overridden. To demonstrate how this can be achieved, let’s start with the following query where we are calculating the number of customers who have made an online purchase in 2006.

WITH
    SET CustomerSet AS
        NonEmpty(
             [Customer].[Customer].[Customer]
            ,[Measures].[Internet Sales Amount]
        )
    MEMBER CustomerCount AS Count(CustomerSet)
SELECT
    {
         CustomerCount
    } ON 0
FROM
    [Adventure Works]
WHERE
    [Date].[Calendar Year].&[2006]

Running this query against the AdventureWorks sample data set, it will return 2677 customers. Let’s say we now want to calculate the number of customers who have made an online purchase *regardless of the Calendar year*. The obvious solution would be to simply remove the Calendar Year criteria from the slicer. However, for the sake of argument, let’s assume there are other named sets and members to which we want the Calendar Date = 2006 context applied. In that case, we can override the slicer-criteria by forcing the context within the calculation.

WITH
    SET CustomerSetSlicerOverride AS
        NonEmpty(
             [Customer].[Customer].[Customer]
            ,([Date].[Calendar Year].[All Periods],[Measures].[Internet Sales Amount])
        )
    MEMBER CustomerCountSlicerOverride AS Count(CustomerSetSlicerOverride)
SELECT
    {
        CustomerCountSlicerOverride
    } ON 0
FROM
    [Adventure Works]
WHERE
    [Date].[Calendar Year].&[2006]

Notice in the second argument of the NonEmpty function in the named set CustomerSetSlicerOverride, I’ve forced the context to the ALLMEMBER of the Calendar Year attribute in the Date dimension. The result of this query is a count of 18484 (which is quite a bit higher than the number of customers in 2006…2677).

In fact, we can combine these 2 queries to show the results side by side:

WITH
    SET CustomerSet AS
        NonEmpty(
             [Customer].[Customer].[Customer]
            ,[Measures].[Internet Sales Amount]
        )
    SET CustomerSetSlicerOverride AS
        NonEmpty(
             [Customer].[Customer].[Customer]
            ,([Date].[Calendar Year].[All Periods],[Measures].[Internet Sales Amount])
        )
    MEMBER CustomerCount AS Count(CustomerSet)
    MEMBER CustomerCountSlicerOverride AS Count(CustomerSetSlicerOverride)
SELECT
    {
         CustomerCount
        ,CustomerCountSlicerOverride
    } ON 0
FROM
    [Adventure Works]
WHERE
    [Date].[Calendar Year].&[2006]

image

Here’s another example of overriding the slicer by manually specifying a different context via a FILTER function: WHERE Clause Overrides

By the way, this is a great example to demonstrate the difference between the “Sub-Select” and “WHERE-clause”. In the query below, I’ve converted the WHERE-clause into a Sub-Select…

WITH
    SET CustomerSet AS
        NonEmpty(
             [Customer].[Customer].[Customer]
            ,[Measures].[Internet Sales Amount]
        )
    SET CustomerSetSlicerOverride AS
        NonEmpty(
             [Customer].[Customer].[Customer]
            ,([Date].[Calendar Year].[All Periods],[Measures].[Internet Sales Amount])
        )
    MEMBER CustomerCount AS Count(CustomerSet)
    MEMBER CustomerCountSlicerOverride AS Count(CustomerSetSlicerOverride)
SELECT
    {
         CustomerCount
        ,CustomerCountSlicerOverride
    } ON 0
FROM (
    SELECT    {[Date].[Calendar Year].&[2006]} ON 0
    FROM    [Adventure Works]
)

…notice the different results…image

You can read more about the difference between a WHERE-clause and Sub-Select in this thread on the MSDN forums.

Leave a Reply