Free Sample
+ Collection

MDX with SSAS 2012 Cookbook

Sherry Li, Tomislav Piasevoli

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.
RRP $35.99
RRP $59.99
Print + eBook

Want this title & more?

$12.99 p/month

Subscribe to PacktLib

Enjoy full and instant access to over 2000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

Book Details

ISBN 139781849689601
Paperback420 pages

About This Book

  • 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

Who This Book Is 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.

Table of Contents

Chapter 1: Elementary MDX Techniques
Putting data on x and y axes
Skipping axes
Using a WHERE clause to filter the data returned
Optimizing MDX queries using the NonEmpty() function
Using the PROPERTIES() function to retrieve data from attribute relationships
Basic sorting and ranking
Handling division by zero errors
Setting a default member of a hierarchy in the MDX script
Chapter 2: Working with Sets
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
Chapter 3: Working with Time
Calculating the YTD (Year-To-Date) value
Calculating the YoY (Year-over-Year) growth (parallel periods)
Calculating moving averages
Finding the last date with data
Getting values on the last date with data
Calculating today's date using the string functions
Calculating today's date using the MemberValue function
Calculating today's date using an attribute hierarchy
Calculating the difference between two dates
Calculating the difference between two times
Calculating parallel periods for multiple dates in a set
Calculating parallel periods for multiple dates in a slicer
Chapter 4: Concise Reporting
Isolating the best N members in a set
Isolating the worst N members in a set
Identifying the best/worst members for each member of another hierarchy
Displaying few important members, others as a single row, and the total at the end
Combining two hierarchies into one
Finding the name of a child with the best/worst value
Highlighting siblings with the best/worst values
Implementing bubble-up exceptions
Chapter 5: Navigation
Detecting a particular member in a hierarchy
Detecting the root member
Detecting members on the same branch
Finding related members in the same dimension
Finding related members in another dimension
Calculating various percentages
Calculating various averages
Calculating various ranks
Chapter 6: Business Analytics
Forecasting using the linear regression
Forecasting using the periodic cycles
Allocating the nonallocated company expenses to departments
Analyzing fluctuation of customers
Implementing the ABC analysis
Chapter 7: When MDX is Not Enough
Using a new attribute to separate members on a level
Using a distinct count measure to implement histograms over existing hierarchies
Using a dummy dimension to implement histograms over nonexisting hierarchies
Creating a physical measure as a placeholder for MDX assignments
Using a new dimension to calculate the most frequent price
Using a utility dimension to implement flexible display units
Using a utility dimension to implement time-based calculations
Chapter 8: Advanced MDX Topics
Displaying members without children (leaves)
Displaying members with data in parent-child hierarchies
Displaying random values
Displaying a random sample of hierarchy members
Displaying a sample from a random hierarchy
Performing complex sorts
Using recursion to calculate cumulative values
Chapter 9: On the Edge
Clearing the Analysis Services cache
Using Analysis Services stored procedures
Executing MDX queries in T-SQL environments
Using SSAS Dynamic Management Views (DMV) to fast-document a cube
Using SSAS Dynamic Management View (DMVs) to monitor activity and usage
Capturing MDX queries generated by SSAS frontends
Performing a custom drillthrough

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
  • Register SSAS-related assemblies and use stored procedures in them

In Detail

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.



Read More