Slicer and axes interaction in MDX Part 1 - coordinate overwrites
OLAP is often described as 'slice-and-dice', which reflects the fact that slicing is the most fundamental OLAP operation. Traditionally, in MDX, the slicing was done by using the WHERE clause. While Analysis Services 2005 introduced the notion of subselects, there are still very few client tools which take advantage of them (Excel 2007 and ReportBuilder are the only ones known to me.) The majority of client tools (and the list grows almost weekly at http://www.mosha.com/msolap/util.htm) use the good ol' WHERE clause, and this will be the focus of this article. Prior to Analysis Services 2005, the behavior of WHERE clause was pretty straightforward - the tuple specified in it was set into the current coordinate, and then the query axes and cells were evaluated in that context. Analysis Services 2005 introduced two major changes: First, it became possible to use sets in the WHERE clause, i.e. what is commonly known as multiselect; Second, since AS2005 allows same attributes to be used across multiple hierarchies, it became possible to have same attribute both in the axis and in the WHERE clause. Multiselect deserves it own deep discussion, here we will focus on interaction of attributes between axis and WHERE clause.
Please also read part 2 here.
Tags: mdx, subselect, multiselect