MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

09/06/2011 · No Responses

Microsoft SQL Server is an enterprise database platform that contains a multitude of technologies, Analysis Services being one of them. SQL Server Analysis Services (SSAS) provides OLAP and data mining capabilities and allows users to analyze multidimensional data stored in cubes using the MDX query language.

This cookbook contains over 80 practical, task-based recipes that show how Microsoft SQL Server 2008 R2 Analysis Services solutions can be taken further by enriching them with high-performance MDX calculations and flexible MDX queries.

Packed with immediately usable, real-world recipes, the book starts with elementary techniques that lay the foundation for designing further MDX calculations and queries. Here you will find topics such as iterations on a set, Boolean logic, and dissecting and optimizing MDX calculations.

In the first half of the book you will learn how to efficiently work with time, strings, metadata, calculated members and sets in general, and how to implement MDX solutions that are appropriate in a particular context: a time-aware calculation, a concise report, a calculation relative to another. You will also learn how to implement various types of conditional formatting, how to perform typical MDX calculations like ranks, percentages and averages, and year-to-date calculations.

The book then deep dives into topics such as enhancing cube design with utility dimensions, context-aware calculations, and other advanced topics. In this part you will learn how a utility dimension can be of great help, for example when you want to calculate histograms or implement time-based calculations. The advanced topics also cover parent-child hierchies, recursion, random values, and complex sorts.

Enrich your Business Intelligence solutions with over 80 recipes for high-performance MDX calculations and flexible MDX queries

What you will learn from this book :

Create time-aware calculations (relative to the current date)
Create context-aware calculations (relative to members on axes)
Implement business-related calculations like forecasting, allocation of values and ABC analysis
Calculate various percentages, averages, and ranks
Work with related members (on the same and other dimensions)
Combine MDX with utility dimensions
Implement error handling
Implement AND, OR, NOT logic
Conditionally format your MDX calculations
Optimize, dissect, and debug MDX calculations and queries
Capture MDX generated by SSAS front-ends
Register SSAS-related assemblies and use stored procedures in them

Approach
This book offers practical, task-based, and immediately usable recipes covering a wide range of MDX calculations and queries. In addition to its cookbook style, which ensures the solutions are presented in a clear step-by-step manner, the explanations are done in great detail, which makes it good learning material for everyone who has experience in MDX and wants to improve.

The book is designed in such a way that you can read it chapter by chapter or refer to recipes in no particular order. However, some of the recipes depend on each another. When this is the case, you will be notified.

The book is focused on Microsoft SQL Server 2008 R2 Analysis Services, but most of the concepts and explanation are also applicable to previous versions of Microsoft SQL Server Analysis Services.

Who this book is written for
If you are a Microsoft SQL Server Analysis Services developer and want to improve your solutions using MDX, then this book is for you.

The book assumes you have a working knowledge of MDX and a basic understanding of dimensional modeling and cube design.