book

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

Name: MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition
ISBN: 9781786460998

by Tomislav Piasevoli, Sherry Li

November 2016

Beginner to intermediate

586 pages

12h 26m

English

Packt Publishing

Read now

Unlock full access

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 readyHow to do it…How it works…There's more…See also

Conventions
Reader feedback
Customer support
Downloading the example codeDownloading the color images of this book ErrataPiracyQuestions
1. Elementary MDX Techniques
Introduction
Putting data on x and y axes
Getting readyHow to do it...How it works...There's more...Putting more hierarchies on x and y axes with cross join
Skipping axes
Getting readyHow to do it...How it works...There's more...The idea behind itPossible workarounds - dummy column
Using a WHERE clause to filter the data returned
Getting readyHow to do it...How it works...There's more...
Optimizing MDX queries using the NonEmpty() function
Getting readyHow to do it...How it works...There's more...NonEmpty() versus NON EMPTYCommon mistakes and useful tips
Using the Properties() function to retrieve data from attribute relationships
Getting readyHow to do it...How it works...There's more...
Basic sorting and ranking
Getting readyHow to do it...How it works...There's more...
Handling division by zero errors
Getting readyHow 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 readyHow to do it...How it works...There's more...Helpful tips
2. Working with Sets
Introduction
Implementing the NOT IN set logic
Getting readyHow to do it...How it works...There's more...See also
Implementing the logical OR on members from different hierarchies
Getting readyHow to do it...How it works...There's more...A special case of a non-aggregatable dimensionA very complex scenarioSee also
Iterating on a set to reduce it
Getting readyHow to do it...How it works...There's more...Hints for query improvementsSee also
Iterating on a set to create a new one
Getting readyHow to do it...How it works...There's more...Did you know?See also
Iterating on a set using recursion
Getting readyHow to do it...How it works...There's more...Earlier versions of SSASSee also
Performing complex sorts
Getting readyHow to do it...How it works...There's more...Things to be extra careful aboutA costly operationSee also
Dissecting and debugging MDX queries
Getting readyHow to do it...How it works...There's more...Useful string functionsSee also
Implementing the logical AND on members from the same hierarchy
Getting readyHow to do it...How it works...There's more...Where to put what?A very complex scenarioSee also
3. Working with Time
Introduction
Calculating the year-to-date (YTD) value
Getting readyHow to do it...How it works...There's more...Inception-To-Date calculationUsing the argument in the YTD() functionCommon problems and how to avoid themYTD() and future datesSee also
Calculating the year-over-year (YoY) growth (parallel periods)
Getting readyHow to do it...How it works...There's more...ParallelPeriod is not a time-aware functionSee also
Calculating moving averages
Getting readyHow to do it...How it works...There's more...Other ways to calculate the moving averagesMoving averages and the future dates
Finding the last date with data
Getting readyHow to do it...How it works...There's more...See also
Getting values on the last date with data
Getting readyHow to do it...How it works...There's more...Formatting members on the Date dimension properlyOptimizing time-non-sensitive calculations
Calculating today's date using the string functions
Getting readyHow to do it...How it works...There's more...Relative periodsPotential problemsSee also
Calculating today's date using the MemberValue function
Getting readyHow to do it...How it works...There's more...Using the ValueColumn property in the Date dimensionSee also
Calculating today's date using an attribute hierarchy
Getting readyHow to do it...How it works...There's more...The Yes member as a default member?Other approachesSee also
Calculating the difference between two dates
Getting readyHow to do it...How it works...There's more...Dates in other scenariosThe problem of non-consecutive datesSee also
Calculating the difference between two times
Getting readyHow to do it...How it works...There's more...Formatting the durationExamples of formatting the duration on the WebCounting working days onlySee also
Calculating parallel periods for multiple dates in a set
Getting readyHow to do it...How it works...There's more...ParametersReporting covered by designSee also
Calculating parallel periods for multiple dates in a slicer
Getting readyHow to do it...How it works...There's more...See also
4. Concise Reporting
Introduction
Isolating the best N members in a set
Getting readyHow 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 axisThe top N members will be evaluated in the context of the slicerUsing a tuple in the third argument of the TopCount() function to overwrite the member on the slicerTesting the correctness of the resultMultidimensional setsTopPercent() and TopSum() functionsSee also
Isolating the worst N members in a set
Getting readyHow to do it...How it works...There's more...See also
Identifying the best/worst members for each member of another hierarchy
Getting readyHow to do it...How it works...There's more...Support for the relative context and multidimensional sets in SSAS frontendsSee also
Displaying a few important members, with the others as a single row, and the total at the end
Getting readyHow to do it...How it works...There's more...Making the query even more genericSee also
Combining two hierarchies into one
Getting readyHow to do it...How it works...There's more...Use it, but don't abuse itLimitations
Finding the name of a child with the best/worst value
Getting readyHow to do it...How it works...There's more...Variations on a themeDisplaying more than one member's captionSee also
Highlighting siblings with the best/worst values
Getting readyHow to do it...How it works...There's more...TroubleshootingSee also
Implementing bubble-up exceptions
Getting readyHow to do it...How it works...There's more...Practical value of bubble-up exceptionsPotential problemsSee also
5. Navigation
Introduction
Detecting a particular member in a hierarchy
Getting readyHow to do it...How it works...There's more...Important remarksComparing members versus comparing valuesDetecting complex combinations of membersSee also
Detecting the root member
Getting readyHow to do it...How it works...There's more...The scope-based solutionSee also
Detecting members on the same branch
Getting readyHow to do it...How it works...There's more...The query-based alternativeChildren() will return empty sets when out of boundariesVarious options of the Descendants() functionSee also
Finding related members in the same dimension
Getting readyHow to do it...How it works...There's more...Tips and trick related to the EXISTING keywordFilter() versus Exists(), Existing(), and EXISTINGA friendly warningSee also
Finding related members in another dimension
Getting readyHow to do it...How it works...There's more...Leaf and non-leaf calculationsSee also
Calculating various percentages
Getting readyHow to do it...How it works...There's more...Use casesThe alternative syntax for the root memberThe case of the nonexisting [All] levelThe percentage of leaf member valuesSee also
Calculating various averages
Getting readyHow to do it...How it works...There's more...Preserving empty rowsOther specifics of average calculationsSee also
Calculating various ranks
Getting readyHow to do it...How it works...There's more...Tie in ranksPreserving empty rowsRanks in multidimensional setsThe pluses and minuses of named setsSee also
6. MDX for Reporting
Introduction
Creating a picklist
Getting readyHow to do it...How it works...There's more...See also
Using a date calendar
Getting readyHow to do it...How it works...There's more...Alternative - allowing users to select by Date hierarchiesSee also
Passing parameters to an MDX query
Getting readyHow to do it...How it works...There's more...
Getting the summary
Getting readyHow to do it...How it works...There's more...Getting visual totals at multiple levels
Removing empty rows
Getting readyHow to do it...How it works...Checking empty setsThere's more...Trouble with zerosSee also
Getting data on the column
Getting readyHow to do it...How it works...There's more...Named set or DIMENSION PROPERTIES has no effect in the shape of the reportsCreating a column alias in MDX queries can mean data duplicationCreating a column alias is a must with role-playing dimensionsAvoiding using the NON EMPTY keyword on the COLUMNS axisQuery Editor in SSRS only allowing measures dimension in the COLUMNSA few more words...See also
Sorting data by dimensions
Getting readyHow to do it...How it works...There's more...Taking advantage of hierarchical sortingUsing the Date type to sort in a non-hierarchical way"Break hierarchy" - sorting a set in a non-hierarchical waySorting can be done in the frontend reporting toolSee also
7. Business Analyses
Introduction
Forecasting using linear regression
Getting readyHow to do it...How it works...There's more...Tips and tricksWhere to find more informationSee also
Forecasting using periodic cycles
Getting readyHow to do it...How it works...There's more...Other approachesSee also
Allocating non-allocated company expenses to departments
Getting readyHow to do it...How it works...There's more...Choosing a proper allocation scheme
Analyzing the fluctuation of customers
Getting readyHow to do it...How it works...There's more...Identifying loyal customers in a periodMore complex scenarioThe alternative approach
Implementing the ABC analysis
Getting readyHow to do it...How it works...There's more...Tips and tricksSee also
8. When MDX is Not Enough
Introduction
Using a new attribute to separate members on a level
Getting readyHow 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 readyHow to do it...How it works...There's more...See also
Using a dummy dimension to implement histograms over nonexisting hierarchies
Getting readyHow to do it...How it works...There's more...DSV or DW?More calculationsOther examplesSee also
Creating a physical measure as a placeholder for MDX assignments
Getting readyHow to do it...How it works...There's more...Associated measure groupSee also
Using a new dimension to calculate the most frequent price
Getting readyHow to do it...How it works...There's more...
Using a utility dimension to implement flexible display units
Getting readyHow to do it...How it works...There's more...Set-based approachFormat string on a filtered set approach
Using a utility dimension to implement time-based calculations
Getting readyHow to do it...How it works...There's more...Interesting detailsFine-tuning the calculationsOther approachesSee also
9. Metadata - Driven Calculations
Introduction
Setting up the environment
Getting readyHow to do it...How it works...There's more...Additional informationTips and tricksSee also
Creating a reporting dimension
Getting readyHow to do it...How it works...There's more...See also
Implementing custom rollups using MDX formulas
Getting readyHow to do it...How it works...There's more...Why not a built-in feature?Why the Sum() function?More complex formulasSee also
Implementing format string, multiplication factor, and sort order features
Getting readyHow to do it...How it works...There's more...Tips and tricksAdditional informationSee also
Implementing unary operators
Getting readyHow to do it...How it works...There's more...See also
Referencing reporting dimension's members in MDX formulas
Getting readyHow to do it...How it works...There's more...See also
Implementing the MDX dictionary
Getting readyHow to do it...How it works...There's more...Additional informationTips and tricksSee also
Implementing metadata-driven KPIs
Getting readyHow to do it...How it works...There's more...Additional informationTips and tricksSee also
10. On the Edge
Introduction
Clearing the Analysis Services cache
Getting readyHow to do it...How it works...There's more...Objects whose cache can be clearedAdditional informationTips and tricksSee also
Using Analysis Services stored procedures
Getting readyHow to do it...How it works...There's more...Tips and tricksExisting assembliesAdditional informationSee also
Executing MDX queries in T-SQL environments
Getting readyHow to do it...How it works...There's more...Additional informationUseful tipsAccessing Analysis Services 2000 from a 64-bit environmentTroubleshooting the linked serverSee also
Using SSAS Dynamic Management Views (DMVs) to fast-document a cube
Getting readyHow to do it...How it works...There's more...Tips and tricksWarning!More informationSee also
Using SSAS Dynamic Management Views (DMVs) to monitor activity and usage
Getting readyHow to do it...How it works...There's more...See also
Capturing MDX queries generated by SSAS frontends
Getting readyHow to do it...How it works...There's more...Alternative solutionTips and tricksSee also
Performing a custom drillthrough
Getting readyHow to do it...How it works...There's more...Allowed functions and potential problemsMore infoOther examplesSee also

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

Chapter 6. MDX for Reporting

In this chapter, we will cover the following recipes:

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

Introduction

When reporting with multi-dimensional data using MDX queries, there are usually two approaches. One is to use a parameterized MDX query. Another is to use a concatenated dynamic MDX query. In the parameterized MDX query approach, only the values of parameters are passed from the reporting tool, and the MDX query is in the form of parameterized query. In the concatenated dynamic MDX query approach, the entire query is constructed in the reporting tool with string manipulation.

In the SQL Reporting ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Publisher Resources

ISBN: 9781786460998

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by Tomislav Piasevoli, Sherry Li

Chapter 6. MDX for Reporting

Introduction

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.