Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

The MDX slicer will not change anything on an axis (or?) part II

Posted by thomasivarssonmalmo on October 31, 2007

I have blogged,below, about the interaction between the slicer or where clause and what you see on each axis.  My previous idea about that the slicer will never change members on an axis is not correct and probably something from AS2000.

This discussion is mostly for MDX-rookies and part of a presentation that I have made for novice consultants and end users.

Lets start with a simple MDX-select statement with a simple slicer that points to another measure than the default measure(reseller sales) in the Adventure Works cube project. You can run this in Management Studio or in Mosha’s exellent MDX studio that you can find here.

Select [Date].[Calendar].[Calendar Year].Members On Columns,
[Customer].[Customer Geography].[Country].members On Rows
From [Adventure Works]
Where ([Measures].[Internet Sales Amount]); 

Nothing strange. You will get the years from 2001 to 2004 on columns and customer countries on rows.

Here comes a rule that you will have to remember. You cannot slice on the same user hierarchy that you already have on columns or rows. If you try the next statement you will get the error "The Calendar hierarchy already appears in the Axis0 axis.Execution completed"

Select [Date].[Calendar].[Calendar Year].Members On Columns,
[Customer].[Customer Geography].[Country].members On Rows
From [Adventure Works]
Where ([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Year].&[2002]);

But if you use the attribute hierarchy instead the slicer will work and only 2004 will show up on columns.

Select [Date].[Calendar].[Calendar Year].Members On Columns,
[Customer].[Customer Geography].[Country].members On Rows
From [Adventure Works]
Where ([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2004]);

Now it is time to filter on the columns that have sales above 9.000.0000. You will only see 2003 and 2004 on columns. Remember that the filter is set for the totals of the column, not single cells so we will filter on years.

Select Filter([Date].[Calendar].[Calendar Year].Members,[Measures].[Internet Sales Amount] > 9000000) On Columns,
[Customer].[Customer Geography].[Country].members On Rows
From [Adventure Works]
Where ([Measures].[Internet Sales Amount]);

Finally! If we slice, with an attribute hierarchy, on the MDX-Select with a filter, what will happen?

Select Filter([Date].[Calendar].[Calendar Year].Members,[Measures].[Internet Sales Amount] > 9000000) On Columns,
[Customer].[Customer Geography].[Country].members On Rows
From [Adventure Works]
Where ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2003]);

If you choose 2002 in the slicer returns no cells, only empty rows. The year 2003 returns 2003 but not 2004.

The where clause rules over the axis with the same dimension. Things can be a little bit more complicated than this but I think these scenarios will cover most of the examples you will see in real life.

Next I will cover Filter(), Having and Subcubes in business scenarios.

Advertisements

One Response to “The MDX slicer will not change anything on an axis (or?) part II”

  1. John said

    Select [Date].[Calendar].[Calendar Year].Members On Columns,
    [Customer].[Customer Geography].[Country].members On Rows
    From [Adventure Works]
    Where ([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2004]);

    Though above query run but wont give any value. we can solve using the attribute name instead of hierarchy in WHERE condition if attribute name is
    different then hierarcy name

    like

    Select [Date].[Calendar].[Calendar Year].Members On Columns,
    [Customer].[Customer Geography].[Country].members On Rows
    From [Adventure Works]
    Where ([Measures].[Internet Sales Amount], [Date].[Calendar yr].&[2004]);

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: