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

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook Third Edition

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook Third Edition

Credits

About the Authors

About the Reviewer

www.PacktPub.com

Why subscribe?

Preface

What this book covers

What you need for this book

Who this book is for

Sections

Getting ready

How to do it…

How it works…

There's more…

See also

Conventions

Reader feedback

Customer support

Downloading the example code

Downloading the color images of this book

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

Iterating on a set 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 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

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

Dissecting and debugging MDX queries

Getting ready

How to do it...

How it works...

There's more...

Useful string functions

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

3. Working with Time

Introduction

Calculating the year-to-date (YTD) 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 year-over-year (YoY) 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...

Formatting members on the Date dimension properly

Optimizing time-non-sensitive calculations

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...

The Yes member 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 members is evaluated in All Periods, not in the context of the opposite query axis

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

Using 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 a few important members, with the 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 combinations 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 the 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. MDX for Reporting

Introduction

Creating a picklist

Getting ready

How to do it...

How it works...

There's more...

See also

Using a date calendar

Getting ready

How to do it...

How it works...

There's more...

Alternative - allowing users to select by Date hierarchies

See also

Passing parameters to an MDX query

Getting ready

How to do it...

How it works...

There's more...

Getting the summary

Getting ready

How to do it...

How it works...

There's more...

Getting visual totals at multiple levels

Removing empty rows

Getting ready

How to do it...

How it works...

Checking empty sets

There's more...

Trouble with zeros

See also

Getting data on the column

Getting ready

How to do it...

How it works...

There's more...

Named set or DIMENSION PROPERTIES has no effect in the shape of the reports

Creating a column alias in MDX queries can mean data duplication

Creating a column alias is a must with role-playing dimensions

Avoiding using the NON EMPTY keyword on the COLUMNS axis

Query Editor in SSRS only allowing measures dimension in the COLUMNS

A few more words...

See also

Sorting data by dimensions

Getting ready

How to do it...

How it works...

There's more...

Taking advantage of hierarchical sorting

Using the Date type to sort in a non-hierarchical way

"Break hierarchy" - sorting a set in a non-hierarchical way

Sorting can be done in the frontend reporting tool

See also

7. Business Analyses

Introduction

Forecasting using 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 periodic cycles

Getting ready

How to do it...

How it works...

There's more...

Other approaches

See also

Allocating non-allocated company expenses to departments

Getting ready

How to do it...

How it works...

There's more...

Choosing a proper allocation scheme

Analyzing the fluctuation of customers

Getting ready

How to do it...

How it works...

There's more...

Identifying loyal customers in a 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

8. 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

9. Metadata - Driven Calculations

Introduction

Setting up the environment

Getting ready

How to do it...

How it works...

There's more...

Additional information

Tips and tricks

See also

Creating a reporting dimension

Getting ready

How to do it...

How it works...

There's more...

See also

Implementing custom rollups using MDX formulas

Getting ready

How to do it...

How it works...

There's more...

Why not a built-in feature?

Why the Sum() function?

More complex formulas

See also

Implementing format string, multiplication factor, and sort order features

Getting ready

How to do it...

How it works...

There's more...

Tips and tricks

Additional information

See also

Implementing unary operators

Getting ready

How to do it...

How it works...

There's more...

See also

Referencing reporting dimension's members in MDX formulas

Getting ready

How to do it...

How it works...

There's more...

See also

Implementing the MDX dictionary

Getting ready

How to do it...

How it works...

There's more...

Additional information

Tips and tricks

See also

Implementing metadata-driven KPIs

Getting ready

How to do it...

How it works...

There's more...

Additional information

Tips and tricks

See also

10. 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 (DMVs) 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 Views (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

More info

Other examples

See also