10. On the Edge | MDX with Microsoft SQL Server 2016 Analysis Services Cookbook

Book Overview & Buying
Table Of Contents

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

By : Tomislav Piasevoli, Sherry Li

5 (3)

Buy this Book

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook

5 (3)

By: Tomislav Piasevoli, Sherry Li

Buy this Book

Overview of this book

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.

Preface

What this book covers

What you need for this book

Who this book is for

Sections

Conventions

Reader feedback

Customer support

Free 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

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

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

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

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

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

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

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

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

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

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

By : Tomislav Piasevoli, Sherry Li

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook

By: Tomislav Piasevoli, Sherry Li

Overview of this book

Executing MDX queries in T-SQL environments

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access