MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries with Microsoft SQL Server 2008 R2 Analysis Services with this book and eBook

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

Cookbook
Tomislav Piasevoli

80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries with Microsoft SQL Server 2008 R2 Analysis Services with this book and eBook
$29.99
$49.99
RRP $29.99
RRP $49.99
eBook
Print + eBook
$12.99 p/month

Want this title & more? 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.
+ Collection
Free Sample

Book Details

ISBN 139781849681308
Paperback480 pages

About This Book

  • Enrich your BI solutions by implementing best practice MDX calculations
  • Master a wide range of time-related, context-aware, and business-related calculations
  • Enhance your solutions by combining MDX with utility dimensions
  • Become skilled in making reports concise
  • Learn how to optimize, dissect, and debug your MDX calculations
  • Maximize your learning with detailed explanations following each solution
  • Packed with practical, hands-on cookbook recipes, illustrating the techniques to enrich your Business Intelligence solutions

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

Table of Contents

Chapter 1: Elementary MDX Techniques
Introduction
Skipping axis
Handling division by zero errors
Setting special format for negative, zero and null values
Applying conditional formatting on calculations
Setting default member of a hierarchy in MDX script
Implementing NOT IN set logic
Implementing logical OR on members from different hierarchies
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
Using NON_EMPTY_BEHAVIOR
Optimizing MDX queries using the NonEmpty() function
Implementing logical AND on members from the same hierarchy
Chapter 2: Working with Time
Introduction
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
Hiding calculation values on future dates
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 3: Concise Reporting
Introduction
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 4: Navigation
Introduction
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 5: Business Analytics
Introduction
Forecasting using the linear regression
Forecasting using the periodic cycles
Allocating the non-allocated company expenses to departments
Calculating the number of days from the last sales to identify the slow-moving goods
Analyzing fluctuation of customers
Implementing the ABC analysis
Chapter 6: When MDX is Not Enough
Introduction
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 non-existing 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 7: Context-aware Calculations
Introduction
Identifying the number of columns and rows a query will return
Identifying the axis with measures
Identifying the axis without measures
Adjusting the number of columns and rows for OWC and Excel
Identifying the content of axes
Calculating row numbers
Calculating the bit-string for hierarchies on an axis
Preserving empty rows
Implementing utility dimension with context-aware calculations
Chapter 8: Advanced MDX Topics
Introduction
Displaying members without children (leaves)
Displaying members with data in parent-child hierarchies
Implementing the Tally table utility dimension
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
Introduction
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 Views (DMVs) to monitor activity and usage
Capturing MDX queries generated by SSAS front-ends
Performing custom drillthrough

What You Will Learn

  • 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

In Detail

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.

Authors

Table of Contents

Chapter 1: Elementary MDX Techniques
Introduction
Skipping axis
Handling division by zero errors
Setting special format for negative, zero and null values
Applying conditional formatting on calculations
Setting default member of a hierarchy in MDX script
Implementing NOT IN set logic
Implementing logical OR on members from different hierarchies
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
Using NON_EMPTY_BEHAVIOR
Optimizing MDX queries using the NonEmpty() function
Implementing logical AND on members from the same hierarchy
Chapter 2: Working with Time
Introduction
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
Hiding calculation values on future dates
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 3: Concise Reporting
Introduction
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 4: Navigation
Introduction
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 5: Business Analytics
Introduction
Forecasting using the linear regression
Forecasting using the periodic cycles
Allocating the non-allocated company expenses to departments
Calculating the number of days from the last sales to identify the slow-moving goods
Analyzing fluctuation of customers
Implementing the ABC analysis
Chapter 6: When MDX is Not Enough
Introduction
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 non-existing 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 7: Context-aware Calculations
Introduction
Identifying the number of columns and rows a query will return
Identifying the axis with measures
Identifying the axis without measures
Adjusting the number of columns and rows for OWC and Excel
Identifying the content of axes
Calculating row numbers
Calculating the bit-string for hierarchies on an axis
Preserving empty rows
Implementing utility dimension with context-aware calculations
Chapter 8: Advanced MDX Topics
Introduction
Displaying members without children (leaves)
Displaying members with data in parent-child hierarchies
Implementing the Tally table utility dimension
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
Introduction
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 Views (DMVs) to monitor activity and usage
Capturing MDX queries generated by SSAS front-ends
Performing custom drillthrough

Book Details

ISBN 139781849681308
Paperback480 pages
Read More