Categories
Uncategorized

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.

One reply on “MDX: Slicer Override”

Hello,
Thank you for your explanation. This is really useful when you want to calcule the global count since you don’t need another dimension attribute in the select query. But as soon as you use one, the same value is repreated for each member of the dimension attribute.
Do you think we should specify something else or I did something wrong ?
Thank you

Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s