What do you get with Print?

Instant access to your digital eBook copy whilst your Print order is Shipped

Paperback book shipped to your preferred address

Download this book in EPUB and PDF formats

Access this title in our online reader with advanced features

DRM FREE - Read whenever, wherever and however you want

MDX with SSAS 2012 Cookbook

Chapter 2. Working with Sets

In this chapter, we will cover:

Implementing the NOT IN set logic
Implementing the logical OR on members from different hierarchies
Implementing the logical AND on members from the same hierarchy
Iterating on a set in order to reduce it
Iterating on a set in order to create a new one
Iterating on a set using recursion
Dissecting and debugging MDX queries

Implementing the logical OR on members from different hierarchies

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:

	Reseller Order Quantity	Reseller Order Count
All Products	72,013	2,970
Cranksets	1,107	261
Gloves	11,553	991
Helmets	4,447	922
Mountain Bikes	12,771	1,119
Mountain Frames	5,604	736
Road Bikes	14,304	1,237
Road Frames	3,456	769
Shorts	8,946	758
Tights	4,562	470
Wheels	5,263	716

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...

Iterating on a set in order to reduce it

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.

	Customer Count	Growth in Customer Base
July 2005	31	(null)
August 2005	31	0.00%
September 2005	13	-58.06%
October 2005	27	107.69%
November 2005	32	18.52%
December 2005	39	21.88%

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

Getting ready

Start...

Iterating on a set in order to create a new one

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.

Getting ready

Let's start a new query in SSMS against the Adventure Works DW 2012 database. Then write the following query:

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]

The query returns four years on columns and six countries plus the top level [All Sales Territories] on rows. The result is shown as follows:

	CY 2005	CY 2006	CY 2007	CY 2008
	Sales Amount	Sales Amount	Sales Amount	Sales Amount
All Sales Territories...

Key benefits

A wide range of time-related, context-aware, and business-related calculations

Combine MDX with utility dimensions

Illustration of techniques to enrich business intelligence solutions, aided by practical, hands-on Cookbook recipes

Description

MDX is the BI industry standard for multidimensional calculations and queries. Proficiency with this language is essential for the realization of your Analysis Services' full potential. MDX is an elegant and powerful language, and also has a steep learning curve.SQL Server 2012 Analysis Services has introduced a new BISM tabular model and a new formula language, Data Analysis Expressions (DAX). However, for the multi-dimensional model, MDX is still the only query and expression language. For many product developers and report developers, MDX is the preferred language for both the tabular model and multi-dimensional model. MDX with SSAS 2012 Cookbook is a must-have book for anyone who wants to be proficient in the MDX language and to enhance their business intelligence solutions.MDX with SSAS 2012 Cookbook is packed with immediately usable, practical solutions. It starts with elementary techniques that lay the foundation for designing advanced MDX calculations and queries. The discussions after each solution will provide you with a solid foundation and best practices. It covers a broad range of real-world topics and solutions and provides you with learning materials to become proficient in the language.This book will guide you through the hands-on and practical MDX solutions, best practices, and many intricacies that hide within the MDX calculations and queries. We will start by working with sets, creating time-aware, context-aware calculations, and business analytics solutions, through to the techniques of enhancing the cube design when MDX is not enough. We will then move on to capturing MDX generated by SSAS front-ends and using SSAS stored procedures, and we will explore the whole range of MDX solutions for real-world BI projects.

Who is this book for?

This book is aimed towards developers. However people experienced with other BI and/or SSAS functions will be able to use this book. If you are a Microsoft SQL Server Analysis Services developer and want to improve your solutions using MDX, then this book is for you. This book is also an essential resource for report developers who need to access the multidimensional cubes through the MDX language. The book assumes you have some basic working knowledge of MDX and a basic understanding of dimensional modelling and cube design.

What you will learn

Create time-aware calculations that are relative to the current date

Construct context-aware calculations that are relative to members on axes

Implement business-related calculations such as forecasting, allocation of values, and ABC analysis

Combine MDX with utility dimensions

Implement error handling

Apply AND, OR, NOT logic

Conditionally format your MDX calculations

Optimize, dissect, and debug MDX calculations and queries

Capture MDX generated by SSAS front-ends

What do you get with Print?

Instant access to your digital eBook copy whilst your Print order is Shipped

Paperback book shipped to your preferred address

Download this book in EPUB and PDF formats

Access this title in our online reader with advanced features

DRM FREE - Read whenever, wherever and however you want

Frequently bought together

SQL Server Analysis Services 2012 Cube Development Cookbook

£49.99

Expert Cube Development with SSAS Multidimensional Models

£41.99

£48.99

Total £ 140.97

Filter reviews by

All

Amazon verified reviews

Amazon Customer Jun 09, 2016

Good

Amazon Verified review

Desert Fun Nov 30, 2013

This book is a must have. I have struggled trying to find a book with good illustrations and easy to follow samples UNTIL this book! It is written so well that I actually enjoy reading the material. It is a must have for anyone like me who is new to MDX and needs to understand not only what the functions are but how the functions actually work!

Gerhard Brueckl Dec 19, 2013

This book is the follower of the "MDX with Microsoft SQL Server 2008R2 Analysis Services Cookbook" originally written by Tomislav Piasevoli. For this second edition Sherry Li joined Tomislav and they combine their in deep knowledge of MDX and Analysis Services to bring the content of the book to an even higher level. The structure of the books is very similar and is again very easy to follow. It starts very simple with basic MDX techniques, evolves to frequently used patterns for e.g. TOP N calculations up to time intelligence calculations. Each single recipe describes a specific real world business scenario and splits it up into 4 sections: Getting ready, How to do it ..., How it works ... and There is more ...Getting ready … explains the general prerequisites and when a given recipe can be appliedHow to do it … describes the basic idea and each single step in detail finally coming up with a solutionHow it works … gives further insights on the elaborated solution and why it works like thatThere is more … is dedicated to those who want to dig deeper into a given recipe/topic and the used techniquesThis structure is what makes this book a “cookbook” – search for your topic and you find all necessary information in one place instead of having to work through several different chapters and topics.One of the things I liked the most about the book is the fact that it is not dedicated to MDX only. It also shows how to solve common problems without using MDX but by doing simple changes to the underlying data model which make calculations simpler and also improve overall performance of the cube. It also features some sections about MDX Script and cube calculations in general which you also barely find in any other book.All in all I would say the book is a good read for all beginners and intermediate MDX developers but also experts will learn some new things too – just as I did!

hxy0135 NJ Oct 29, 2013

This is a very advanced MDX book. But if you are a beginner and you are willing to spend time to go over the book a couple of times, it can boost your MDX skill a lot in a short time frame. It offers many very practical examples you can mimic to solve the problems in your daily job projects.I love the book. I bought the first version for SQL 2008 R2. It helped me a lot in my projects and helped me advanced my MDX skill in a very short time. So I bought and read this version for SQL 2012. I feel its explanations on some topics and examples have improved and are clearer. It is very easy to read. Learning by example is an easy and fast way to learn MDX skill. Highly recommend it. A Must have book to a MDX developer!

Dan English Nov 24, 2013

This is a definite must have MDX reference book for anyone that is learning or already doing multidimensional Analysis Services development. The information contained in this book goes from elementary to novice to expert. What I like about the cookbook style is the scenario and solution design. Not only are the solutions provided, but it also explains them, which is extremely helpful. The recipes start out with a getting ready section, then explain how to do it, then explain how it actually works, and then wait, 'there's more':) So if you are getting into OLAP development or have been doing it for years, this is a must have book to add to your library.

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

What do you get with Print?

MDX with SSAS 2012 Cookbook

Chapter 2. Working with Sets

Introduction

Implementing the NOT IN set logic

Getting ready

Implementing the logical OR on members from different hierarchies

Implementing the logical AND on members from the same hierarchy

Getting ready

Iterating on a set in order to reduce it

Getting ready

Iterating on a set in order to create a new one

Getting ready

Iterating on a set using recursion

Getting ready

Dissecting and debugging MDX queries

Getting ready

How to do it…

Page 1 of 9

Key benefits

Description

Who is this book for?

What you will learn

Product Details

What do you get with Print?

Product Details

Frequently bought together

Table of Contents

Recommendations for you

Customer reviews

Filter reviews by

People who bought this also bought

FAQs

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

What do you get with Print?

Contact Details

Shipping Address

Billing Address

Key benefits

Description

Who is this book for?

What you will learn

Product Details

What do you get with Print?

Contact Details

Shipping Address

Billing Address

Product Details

Packt Subscriptions

Frequently bought together

Table of Contents

Recommendations for you

Customer reviews

Filter reviews by

People who bought this also bought

FAQs