Book Details

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

Authors

Tomislav Piasevoli

Tomislav Piasevoli is a Business Intelligence (BI) specialist with years of experience working with Microsoft SQL Server Analysis Services (SSAS). He successfully implemented many still-in-use BI solutions, helped numerous people on MSDN forum, achieved the highest certification for SQL Server Analysis Services (SSAS Maestro), and shared his expertise in form of MDX cookbooks.

Tomislav currently works as a consultant at Piasevoli Analytics company (www.piasevoli.com) together with his brother Hrvoje. They specialize in Microsoft SQL Server Business Intelligence platform, SSAS primarily, and offer their BI skills worldwide.

In addition to his regular work, Tomislav manages to find the time to present at local conferences or to write an article or two for local magazines. His contribution to the community has been recognized by Microsoft honoring him with the Most Valuable Professional (MVP) award for six consecutive years (2009-2015).

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

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.