PacktLib: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook

Credits

About the Author

Acknowledgement

About the Reviewers

www.PacktPub.com

Preface

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

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

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

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

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

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

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

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

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

Conclusion

Glossary of Terms