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

Instantly access this course right now and get the skills you need in 2016

With unlimited access to a constantly growing library of over 3,500 courses, a subscription to Mapt gives you everything you need to get that next promotion or to land that dream job. Cancel anytime.

+ Collection
Free Sample

Book Details

ISBN 139781849681308
Paperback480 pages

Book Description

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.

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

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

Read More Reviews