MDX with SSAS 2012 Cookbook: In this book you'll find 90 clearly written recipes to help developers advance their skills with the demanding but powerful language MDX and SQL Server Analysis Services. All leading to greatly improved business intelligence solutions. , Second Edition

Sets are collections of tuples with the same dimensionality. Logical operations, NOT, OR and AND, are performed on sets in MDX. When putting the two tuples together to form a set, we ask for the results that contain a tuple. Therefore, the sets in MDX naturally implies logic OR.

The first part of the chapter focuses on the challenges and solutions of performing logic operations NOT, OR and AND, on sets.

Iterations and recursions can also be performed on sets. The second half of the chapter concentrates on iterations and recursions, and the different ways to perform them. Finally, we will cover how to apply the iteration technique to dissect and debug MDX queries and calculations.

There are times when we want to exclude some members from the result. We can perform this operation using a set of members on an axis or using a set of members in a slicer, that is, the WHERE part of an MDX query. This recipe shows how to do the latter, that is, how to exclude some members from a set in a slicer. The principle is the same for any part of an MDX query.

SELECT
   { [Measures].[Reseller Order Count] } ON 0,
   NON EMPTY
   { [Promotion].[Promotion].MEMBERS }
   DIMENSION PROPERTIES
       [Promotion].[Promotion].[Discount Percent] 
   ON 1
FROM
   [Adventure Works]

If we need to slice the data by only black color for products, we would put the Black member in the where clause, such as this:

WHERE
   ( [Product].[Color].&[Black] )

In the Adventure Works DW 2012 database, putting Reseller Order Quantity and Count on the columns, we would get this result:

Similarly, to get only the products whose size is XL, we can put the member XL in the slicer as:

WHERE
   ( [Product].[Size Range].&[XL] )

What if we want to get the products whose size is XL in the same result set as the result set for black only?

Somehow, we need to combine the black member with the XL member. Simply by putting...

This recipe shows how to implement AND logic using members from the same hierarchy.

In the Adventure Works DW 2012 database, there are two members [New Product] and [Excess Inventory] in the [Promotion Type] hierarchy:

[Promotion].[Promotion Type].&[New Product]
[Promotion].[Promotion Type].&[Excess Inventory]

These two promotion types have reseller orders, but the only two months in which they both have reseller orders are July and August.

The idea is to have a single query that displays the reseller orders, where both of these promotion types occur in the same month. In other words, we want to show the reseller orders for July and August.

Our goal is to somehow combine these two members from the same hierarchy so that we perform logic AND along the [Month of Year] hierarchy on the Date dimension.

Iteration is a very natural way of thinking for us humans. We set a starting point, we step into a loop, and we end when a condition is met. While we're looping, we can do whatever we want: check, take, leave, and modify items in that set.

In this recipe, we will start from a result set as shown in the following table, and iterate through the days in each fiscal month to count the number of days for which the growth was positive. By "to reduce", we mean the filtering effect; in our example, we need to "filter out" the days for which the growth was not positive. Our goal is still to only display the fiscal months on ROWS, not the days.

Then we will look at a different approach that takes performance advantage of the block- mode calculation.

There are situations when we don't want to eliminate certain members from a set, but instead execute for each type of loop. This is done using the GENERATE() function. The GENERATE() function applies a set to each member of another set, and then joins the resulting sets by union. In this recipe we'll show you how to create a new set of members from the existing one.

SELECT
   NON EMPTY
   { [Date].[Calendar].[Calendar Year].MEMBERS *
     [Measures].[Sales Amount] } ON 0,
   NON EMPTY
   { [Sales Territory].[Sales Territory Country].MEMBERS }
   ON 1
FROM
   [Adventure Works]

Recursion is sometimes the best way to iterate a collection. Why? Because iterations using set functions (including the GENERATE() function) require that we loop through the whole set. But what if that set is big and we only need to find something specific in it? Wouldn't it be great to be able to stop the process when we've found what we wanted? Recursion enables just that – to stop when we're done.

In this recipe we're going to see how to calculate the average of an average using recursion.

SELECT
   { [Measures].[Order Count] } ON 0,
   NON EMPTY   
   { Descendants( [Date].[Fiscal Weeks].[All Periods],
                   1 , SELF_AND_BEFORE) } ON 1
FROM
   [Adventure Works]

When writing a query involving complex calculations, you might have a hard time trying to debug it, in case there is a problem inside the calculation. But there is a way. By breaking complex sets and calculations into smaller pieces and/or by converting those sets and members into strings, we can visually represent the intermediate results and thereby isolate the problematic part of the query.

True, there's no real debugger in the sense that you can pause the calculation process of the query and evaluate the variables. What you can do is to simulate that by concatenating intermediate results into strings for visual verification.