MDX with SSAS 2012 Cookbook电子书

MDX with SSAS 2012 Cookbook

Table of Contents

MDX with SSAS 2012 Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Database Installation

Sample Adventure Works 2012 Database

For Creating PivotTable

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Elementary MDX Techniques

Introduction

Putting data on x and y axes

Getting ready

How to do it…

How it works…

There's more…

Putting more hierarchies on x and y axes with cross join

Skipping axes

Getting ready

How to do it…

How it works…

There's more…

The idea behind it

Possible workarounds – dummy column

Using a WHERE clause to filter the data returned

Getting ready

How to do it…

How it works…

There's more…

Optimizing MDX queries using the NonEmpty() function

Getting ready

How to do it…

How it works…

There's more…

NonEmpty() versus NON EMPTY

Common mistakes and useful tips

Using the PROPERTIES() function to retrieve data from attribute relationships

Getting ready

How to do it…

How it works…

There's more…

Basic sorting and ranking

Getting ready

How to do it…

How it works…

There's more…

Handling division by zero errors

Getting ready

How to do it…

How it works…

There's more…

Earlier versions of SSAS

Setting a default member of a hierarchy in the MDX script

Getting ready

How to do it…

How it works…

There's more…

Helpful tips

2. Working with Sets

Introduction

Implementing the NOT IN set logic

Getting ready

How to do it…

How it works…

There's more…

See also

Implementing the logical OR on members from different hierarchies

Getting ready

How to do it…

How it works…

There's more…

A special case of a non-aggregatable dimension

A very complex scenario

See also

Implementing the logical AND on members from the same hierarchy

Getting ready

How to do it…

How it works…

There's more…

Where to put what?

A very complex scenario

See also

Iterating on a set in order to reduce it

Getting ready

How to do it…

How it works…

There's more…

Hints for query improvements

See also

Iterating on a set in order to create a new one

Getting ready

How to do it…

How it works…

There's more…

Did you know

See also

Iterating on a set using recursion

Getting ready

How to do it…

How it works…

There's more…

Earlier versions of SSAS

See also

Dissecting and debugging MDX queries

Getting ready

How to do it…

How it works…

There's more…

Useful string functions

See also

3. Working with Time

Introduction

Calculating the YTD (Year-To-Date) value

Getting ready

How to do it...

How it works...

There's more...

Inception-To-Date calculation

Using the argument in the YTD() function

Common problems and how to avoid them

YTD() and future dates

See also

Calculating the YoY (Year-over-Year) growth (parallel periods)

Getting ready

How to do it...

How it works...

There's more...

ParallelPeriod is not a time-aware function

See also

Calculating moving averages

Getting ready

How to do it...

How it works...

There's more...

Other ways to calculate the moving averages

Moving averages and the future dates

Finding the last date with data

Getting ready

How to do it...

How it works...

There's more...

See also

Getting values on the last date with data

Getting ready

How to do it...

How it works...

There's more...

Format members on the Date dimension properly

Optimizing time-non-sensitive calculation

Calculating today's date using the string functions

Getting ready

How to do it...

How it works...

There's more...

Relative periods

Potential problems

See also

Calculating today's date using the MemberValue function

Getting ready

How to do it...

How it works...

There's more...

Using the ValueColumn property in the Date dimension

See also

Calculating today's date using an attribute hierarchy

Getting ready

How to do it...

How it works...

There's more...

Member Yes as a default member?

Other approaches

See also

Calculating the difference between two dates

Getting ready

How to do it...

How it works...

There's more...

Dates in other scenarios

The problem of non-consecutive dates

See also

Calculating the difference between two times

Getting ready

How to do it...

How it works...

There's more...

Formatting the duration

Examples of formatting the duration on the web

Counting working days only

See also

Calculating parallel periods for multiple dates in a set

Getting ready

How to do it...

How it works...

There's more...

Parameters

Reporting covered by design

See also

Calculating parallel periods for multiple dates in a slicer

Getting ready

How to do it...

How it works...

There's more...

See also

4. Concise Reporting

Introduction

Isolating the best N members in a set

Getting ready

How to do it...

How it works...

There's more...

The top N member is evaluated in All Periods, not in the context of the opposite query axis

The top N member will be evaluated in the context of the slicer

Use a tuple in the third argument of the TopCount() function to overwrite the member on the slicer

Testing the correctness of the result

Multidimensional sets

TopPercent() and TopSum() functions

See also

Isolating the worst N members in a set

Getting ready

How to do it...

How it works...

There's more...

See also

Identifying the best/worst members for each member of another hierarchy

Getting ready

How to do it...

How it works...

There's more...

Support for the relative context and multidimensional sets in SSAS frontends

See also

Displaying few important members, others as a single row, and the total at the end

Getting ready

How to do it...

How it works...

There's more...

Making the query even more generic

See also

Combining two hierarchies into one

Getting ready

How to do it...

How it works...

There's more...

Use it, but don't abuse it

Limitations

Finding the name of a child with the best/worst value

Getting ready

How to do it...

How it works...

There's more...

Variations on a theme

Displaying more than one member's caption

See also

Highlighting siblings with the best/worst values

Getting ready

How to do it...

How it works...

There's more...

Troubleshooting

See also

Implementing bubble-up exceptions

Getting ready

How to do it...

How it works...

There's more...

Practical value of bubble-up exceptions

Potential problems

See also

5. Navigation

Introduction

Detecting a particular member in a hierarchy

Getting ready

How to do it...

How it works...

There's more...

Important remarks

Comparing members versus comparing values

Detecting complex combination of members

See also

Detecting the root member

Getting ready

How to do it...

How it works...

There's more...

The scope-based solution

See also

Detecting members on the same branch

Getting ready

How to do it...

How it works...

There's more...

The query-based alternative

Children() will return empty sets when out of boundaries

Various options of the Descendants() function

See also

Finding related members in the same dimension

Getting ready

How to do it...

How it works...

There's more...

Tips and trick related to the EXISTING keyword

Filter() versus Exists(), Existing(), and EXISTING

A friendly warning

See also

Finding related members in another dimension

Getting ready

How to do it...

How it works...

There's more...

Leaf and non-leaf calculations

See also

Calculating various percentages

Getting ready

How to do it...

How it works...

There's more...

Use cases

The alternative syntax for the root member

The case of nonexisting [All] level

The percentage of leaf member values

See also

Calculating various averages

Getting ready

How to do it...

How it works...

There's more...

Preserving empty rows

Other specifics of average calculations

See also

Calculating various ranks

Getting ready

How to do it...

How it works...

There's more...

Tie in ranks

Preserving empty rows

Ranks in multidimensional sets

The pluses and minuses of named sets

See also

6. Business Analytics

Introduction

Forecasting using the linear regression

Getting ready

How to do it...

How it works...

There's more...

Tips and tricks

Where to find more information?

See also

Forecasting using the periodic cycles

Getting ready

How to do it...

How it works...

There's more...

Other approaches

See also

Allocating the nonallocated company expenses to departments

Getting ready

How to do it...

How it works...

There's more...

Choosing a proper allocation scheme

Analyzing fluctuation of customers

Getting ready

How to do it...

How it works...

There's more...

Identifying loyal customers in a particular period

More complex scenario

The alternative approach

Implementing the ABC analysis

Getting ready

How to do it...

How it works...

There's more...

Tips and tricks

See also

7. When MDX is Not Enough

Introduction

Using a new attribute to separate members on a level

Getting ready

How to do it...

How it works...

There's more...

So, where's the MDX?

Typical scenarios

Using a distinct count measure to implement histograms over existing hierarchies

Getting ready

How to do it...

How it works...

There's more...

See also

Using a dummy dimension to implement histograms over nonexisting hierarchies

Getting ready

How to do it...

How it works...

There's more...

DSV or DW?

More calculations

Other examples

See also

Creating a physical measure as a placeholder for MDX assignments

Getting ready

How to do it...

How it works...

There's more...

Associated measure group

See also

Using a new dimension to calculate the most frequent price

Getting ready

How to do it...

How it works...

There's more...

Using a utility dimension to implement flexible display units

Getting ready

How to do it...

How it works...

There's more...

Set-based approach

Format string on a filtered set approach

Using a utility dimension to implement time-based calculations

Getting ready

How to do it...

How it works...

There's more...

Interesting details

Fine-tuning the calculations

Other approaches

See also

8. Advanced MDX Topics

Introduction

Displaying members without children (leaves)

Getting ready

How to do it...

How it works...

There's more...

Second and third argument in Descendant() are optional

A reverse case

Possible problems with ragged hierarchies

See also

Displaying members with data in parent-child hierarchies

Getting ready

How to do it...

How it works...

There's more...

Alternative solution

See also

Displaying random values

Getting ready

How to do it...

How it works...

There's more...

See also

Displaying a random sample of hierarchy members

Getting ready

How to do it...

How it works...

There's more...

Alternative solution

See also

Displaying a sample from a random hierarchy

Getting ready

How to do it...

How it works...

There's more...

See also

Performing complex sorts

Getting ready

How to do it...

How it works...

There's more...

Things to be extra careful about

A costly operation

See also

Using recursion to calculate cumulative values

Getting ready

How to do it...

How it works...

There's more...

A simplified version of the solution

Choosing between recursive and cumulative calculations

See also

9. On the Edge

Introduction

Clearing the Analysis Services cache

Getting ready

How to do it...

How it works...

There's more...

Objects whose cache can be cleared

Additional information

Tips and tricks

See also

Using Analysis Services stored procedures

Getting ready

How to do it...

How it works...

There's more...

Tips and tricks

Existing assemblies

Additional information

See also

Executing MDX queries in T-SQL environments

Getting ready

How to do it...

How it works...

There's more...

Additional information

Useful tips

Accessing Analysis Services 2000 from a 64-bit environment

Troubleshooting the linked server

See also

Using SSAS Dynamic Management Views (DMV) to fast-document a cube

Getting ready

How to do it...

How it works...

There's more...

Tips and tricks

Warning!

More information

See also

Using SSAS Dynamic Management View (DMVs) to monitor activity and usage

Getting ready

How to do it...

How it works...

There's more...

See also

Capturing MDX queries generated by SSAS frontends

Getting ready

How to do it...

How it works...

There's more...

Alternative solution

Tips and tricks

See also

Performing a custom drillthrough

Getting ready

How to do it...

How it works...

There's more...

Allowed functions and potential problems about them

More info

Other examples

See also

Index