Book description
Over 70 practical recipes to analyze multidimensional data in SQL Server 2016 Analysis Services cubes
About This Book
Updated for SQL Server 2016, this book helps you take advantage of the new MDX commands and the new features introduced in SSAS
Perform timerelated, contextaware, and business relatedcalculations with ease to enrich your Business Intelligence solutions
Collection of techniques to write flexible and high performing MDX queries in SSAS with carefully structured examples
Who This Book Is For
This book is for anyone who has been involved in working with multidimensional data. If you are a multidimensional cube developer, a multidimensional database administrator, or a report developer who writes MDX queries to access multidimensional cube, this book will help you. If you are a power cube user or an experienced business analyst, you will also find this book invaluable in your data analysis. This book is for you are interested in doing more data analysis so that the management can make timely and accurate business decisions.
What You Will Learn
Grasp the fundamental MDX concepts, features, and techniques
Work with sets
Work with Time dimension and create timeaware calculations
Make analytical reports compact, concise, and efficient
Navigate cubes
Master MDX for reporting with Reporting Services (new)
Perform business analytics
Design efficient cubes and efficient MDX queries
Create metadatadriven calculations (new)
Capture MDX queries and many other techniques
In Detail
If you're often faced with MDX challenges, this is a book for you. It will teach you how to solve various realworld 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 stepbystep 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.
Style and approach
This book is written in a cookbook format, where you can browse through and look for solutions to a particular problem in one place. Each recipe is short, to the point and grouped by relevancy. All the recipes are sequenced in a logical progression; you will be able to build up your understanding of the topic incrementally.
Publisher resources
Table of contents

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
 Preface

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 yeartodate (YTD) value
 Calculating the yearoveryear (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
 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
 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 bubbleup exceptions
 5. Navigation

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
 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 roleplaying 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
 7. Business Analyses

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 timebased 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 metadatadriven KPIs

10. On the Edge
 Introduction
 Clearing the Analysis Services cache
 Using Analysis Services stored procedures
 Executing MDX queries in TSQL environments
 Using SSAS Dynamic Management Views (DMVs) to fastdocument a cube
 Using SSAS Dynamic Management Views (DMVs) to monitor activity and usage
 Capturing MDX queries generated by SSAS frontends
 Performing a custom drillthrough
Product information
 Title: MDX with Microsoft SQL Server 2016 Analysis Services Cookbook  Third Edition
 Author(s):
 Release date: November 2016
 Publisher(s): Packt Publishing
 ISBN: 9781786460998
You might also like
book
Mastering Microsoft Power BI
Design, create and manage robust Power BI solutions to gain meaningful business insights About This Book …
book
SQL Queries for Mere Mortals: A HandsOn Guide to Data Manipulation in SQL, 4th Edition
The #1 Easy, CommonSense Guide to SQL Queries—Updated with More Advanced Techniques and Solutions Foreword by …
book
Microsoft Power BI Cookbook
Get more out of Microsoft Power BI turning your data into actionable insights About This Book …
book
Microsoft Power BI Complete Reference
Design, develop, and master efficient Power BI solutions for impactful business insights Key Features Get to …