MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition

Over 70 practical recipes to analyze multi-dimensional data in SQL Server 2016 Analysis Services cubes
MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition Book Cover

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition

Tomislav Piasevoli, Sherry Li

2 customer reviews
Over 70 practical recipes to analyze multi-dimensional data in SQL Server 2016 Analysis Services cubes
Mapt Subscription
FREE
€29.98/m after trial
eBook
€32.76
RRP €46.78
Save 29%
Print + eBook
€47.99
RRP €47.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
€32.76
€47.99
€29.98p/m after trial
RRP €46.78
RRP €47.99
Subscription
eBook
Print + eBook
Start 30 Day Trial
Subscribe and access every Packt eBook & Video.
 
  • 5,000+ eBooks & Videos
  • 50+ New titles a month
  • 1 Free eBook/Video to keep every month
Start Free Trial
 
Code Files
Preview in Mapt

Book Details

ISBN 139781786460998
Paperback586 pages

Book Description

If you're often faced with MDX challenges, this is a book for you. It will teach you how to solve various real-world business requirements using MDX queries and calculations.

Examples in the book introduce an idea or a problem and then guide you through the process of implementing the solution in a step-by-step manner, inform you about the best practices and offer a deep knowledge in terms of how the solution works. Recipes are organized by chapters, each covering a single topic. They start slowly and logically progress to more advanced techniques.

In case of complexity, things are broken down. Instead of one, there are series of recipes built one on top of another. This way you are able to see intermediate results and debug potential errors faster.

Finally, the cookbook format is here to help you quickly identify the topic of interest and in it a wide range of practical solutions, that is – MDX recipes for your success.

Table of Contents

Chapter 1: Elementary MDX Techniques
Introduction
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
Introduction
Implementing the NOT IN set logic
Implementing the logical OR on members from different hierarchies
Iterating on a set to reduce it
Iterating on a set to create a new one
Iterating on a set using recursion
Performing complex sorts
Dissecting and debugging MDX queries
Implementing the logical AND on members from the same hierarchy
Chapter 3: Working with Time
Introduction
Calculating the year-to-date (YTD) value
Calculating the year-over-year (YoY) 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
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 a few important members, with the 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
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 6: MDX for Reporting
Introduction
Creating a picklist
Using a date calendar
Passing parameters to an MDX query
Getting the summary
Removing empty rows
Getting data on the column
Sorting data by dimensions
Chapter 7: Business Analyses
Introduction
Forecasting using linear regression
Forecasting using periodic cycles
Allocating non-allocated company expenses to departments
Analyzing the fluctuation of customers
Implementing the ABC analysis
Chapter 8: 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 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 9: Metadata - Driven Calculations
Introduction
Setting up the environment
Creating a reporting dimension
Implementing custom rollups using MDX formulas
Implementing format string, multiplication factor, and sort order features
Implementing unary operators
Referencing reporting dimension's members in MDX formulas
Implementing the MDX dictionary
Implementing metadata-driven KPIs
Chapter 10: 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 (DMVs) to fast-document a cube
Using SSAS Dynamic Management Views (DMVs) to monitor activity and usage
Capturing MDX queries generated by SSAS frontends
Performing a custom drillthrough

What You Will Learn

  • Grasp the fundamental MDX concepts, features, and techniques
  • Work with sets
  • Work with Time dimension and create time-aware calculations
  • Make analytical reports compact, concise, and efficient
  • Navigate cubes
  • Master MDX for reporting with Reporting Services (new)
  • Perform business analytics
  • Design efficient cubes and efficient MDX queries
  • Create metadata-driven calculations (new)
  • Capture MDX queries and many other techniques

Authors

Table of Contents

Chapter 1: Elementary MDX Techniques
Introduction
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
Introduction
Implementing the NOT IN set logic
Implementing the logical OR on members from different hierarchies
Iterating on a set to reduce it
Iterating on a set to create a new one
Iterating on a set using recursion
Performing complex sorts
Dissecting and debugging MDX queries
Implementing the logical AND on members from the same hierarchy
Chapter 3: Working with Time
Introduction
Calculating the year-to-date (YTD) value
Calculating the year-over-year (YoY) 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
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 a few important members, with the 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
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 6: MDX for Reporting
Introduction
Creating a picklist
Using a date calendar
Passing parameters to an MDX query
Getting the summary
Removing empty rows
Getting data on the column
Sorting data by dimensions
Chapter 7: Business Analyses
Introduction
Forecasting using linear regression
Forecasting using periodic cycles
Allocating non-allocated company expenses to departments
Analyzing the fluctuation of customers
Implementing the ABC analysis
Chapter 8: 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 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 9: Metadata - Driven Calculations
Introduction
Setting up the environment
Creating a reporting dimension
Implementing custom rollups using MDX formulas
Implementing format string, multiplication factor, and sort order features
Implementing unary operators
Referencing reporting dimension's members in MDX formulas
Implementing the MDX dictionary
Implementing metadata-driven KPIs
Chapter 10: 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 (DMVs) to fast-document a cube
Using SSAS Dynamic Management Views (DMVs) to monitor activity and usage
Capturing MDX queries generated by SSAS frontends
Performing a custom drillthrough

Book Details

ISBN 139781786460998
Paperback586 pages
Read More
From 2 reviews

Read More Reviews

DEAL OF THE DAY

Sign up here to get exclusive deep discounts on our latest and bestselling eBooks, delivered straight to your inbox every day.

FREE LEARNING FOREVER

Master new IT skills and unlock valuable software & web development knowledge with a FREE eBook every day.

Recommended for You

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook Book Cover
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
€ 25.18
€ 17.64
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
May 2012
564 pages
€ 17.64
Microsoft Tabular Modeling Cookbook Book Cover
Microsoft Tabular Modeling Cookbook
€ 31.18
€ 21.84
Microsoft Tabular Modeling Cookbook
Dec 2013
320 pages
€ 21.84
SQL Server Analysis Services 2012 Cube Development Cookbook Book Cover
SQL Server Analysis Services 2012 Cube Development Cookbook
€ 32.38
€ 22.68
SQL Server Analysis Services 2012 Cube Development Cookbook
Dec 2013
340 pages
€ 22.68
Getting started with Shiny [Video] Book Cover
Getting started with Shiny [Video]
€ 142.78
€ 121.38
Getting started with Shiny [Video]
Sep 2017
1 hour 40 minutes
€ 121.38
Vue.js 2 Recipes [Video] Book Cover
Vue.js 2 Recipes [Video]
€ 142.78
€ 121.38
Vue.js 2 Recipes [Video]
Sep 2017
4 hours and 42 minutes
€ 121.38
Front-End Web Development using Go [Video] Book Cover
Front-End Web Development using Go [Video]
€ 142.78
€ 121.38
Front-End Web Development using Go [Video]
Sep 2017
3 hours and 41 minutes
€ 121.38