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
Preview in Mapt

MDX with Microsoft SQL Server 2008 R2 Analysis Services 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
Mapt Subscription
FREE
$29.99/m after trial
eBook
$10.00
RRP $29.99
Save 66%
Print + eBook
$49.99
RRP $49.99
What do I get with a Mapt Pro subscription?
  • Unlimited access to all Packt’s 5,000+ eBooks and Videos
  • Early Access content, Progress Tracking, and Assessments
  • 1 Free eBook or Video to download and keep every month after trial
What do I get with an eBook?
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with Print & eBook?
  • Get a paperback copy of the book delivered to you
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with a Video?
  • Download this Video course in MP4 format
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
$0.00
$10.00
$49.99
$29.99 p/m after trial
RRP $29.99
RRP $49.99
Subscription
eBook
Print + eBook
Start 30 Day Trial

Frequently bought together


MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook Book Cover
MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook
$ 29.99
$ 10.00
MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition Book Cover
MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition
$ 43.99
$ 10.00
Buy 2 for $20.00
Save $53.98
Add to Cart

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

Recommended for You

Microsoft SQL Server 2008 R2 Administration Cookbook Book Cover
Microsoft SQL Server 2008 R2 Administration Cookbook
$ 32.99
$ 10.00
SQL Server 2012 with PowerShell V3 Cookbook Book Cover
SQL Server 2012 with PowerShell V3 Cookbook
$ 35.99
$ 10.00
Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube Book Cover
Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube
$ 14.99
$ 10.00
Instant RubyMotion App Development Book Cover
Instant RubyMotion App Development
$ 14.99
$ 10.00
Drupal: Creating Blogs, Forums, Portals, and Community Websites Book Cover
Drupal: Creating Blogs, Forums, Portals, and Community Websites
$ 23.99
$ 10.00
IBM Cognos Business Intelligence 10.1 Dashboarding Cookbook Book Cover
IBM Cognos Business Intelligence 10.1 Dashboarding Cookbook
$ 29.99
$ 10.00