O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

Book Description

Over 70 practical recipes to analyze multi-dimensional 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 time-related, context-aware, and business related-calculations 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 time-aware 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 metadata-driven 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 real-world 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 step-by-step 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.

Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the code file.

Table of Contents

  1. MDX with Microsoft SQL Server 2016 Analysis Services Cookbook Third Edition
    1. MDX with Microsoft SQL Server 2016 Analysis Services Cookbook Third Edition
    2. Credits
    3. About the Authors
    4. About the Reviewer
    5. www.PacktPub.com
      1. Why subscribe?
    6. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Sections
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more…
        5. See also
      5. Conventions
      6. Reader feedback
      7. Customer support
        1. Downloading the example code
        2. Downloading the color images of this book 
        3. Errata
        4. Piracy
        5. Questions
    7. 1. Elementary MDX Techniques
      1. Introduction
      2. Putting data on x and y axes
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Putting more hierarchies on x and y axes with cross join
      3. Skipping axes
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. The idea behind it
          2. Possible workarounds - dummy column
      4. Using a WHERE clause to filter the data returned
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Optimizing MDX queries using the NonEmpty() function
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. NonEmpty() versus NON EMPTY
          2. Common mistakes and useful tips
      6. Using the Properties() function to retrieve data from attribute relationships
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. Basic sorting and ranking
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      8. Handling division by zero errors
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Earlier versions of SSAS
      9. Setting a default member of a hierarchy in the MDX script
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Helpful tips
    8. 2. Working with Sets
      1. Introduction
      2. Implementing the NOT IN set logic
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      3. Implementing the logical OR on members from different hierarchies
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. A special case of a non-aggregatable dimension
          2. A very complex scenario
        5. See also
      4. Iterating on a set to reduce it
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Hints for query improvements
        5. See also
      5. Iterating on a set to create a new one
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. Did you know?
        6. See also
      6. Iterating on a set using recursion
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Earlier versions of SSAS
        5. See also
      7. Performing complex sorts
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Things to be extra careful about
          2. A costly operation
        5. See also
      8. Dissecting and debugging MDX queries
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Useful string functions
        5. See also
      9. Implementing the logical AND on members from the same hierarchy
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Where to put what?
          2. A very complex scenario
        5. See also
    9. 3. Working with Time
      1. Introduction
      2. Calculating the year-to-date (YTD) value
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Inception-To-Date calculation
          2. Using the argument in the YTD() function
          3. Common problems and how to avoid them
          4. YTD() and future dates
        5. See also
      3. Calculating the year-over-year (YoY) growth (parallel periods)
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. ParallelPeriod is not a time-aware function
        5. See also
      4. Calculating moving averages
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Other ways to calculate the moving averages
          2. Moving averages and the future dates
      5. Finding the last date with data
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      6. Getting values on the last date with data
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Formatting members on the Date dimension properly
          2. Optimizing time-non-sensitive calculations
      7. Calculating today's date using the string functions
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Relative periods
          2. Potential problems
        5. See also
      8. Calculating today's date using the MemberValue function
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Using the ValueColumn property in the Date dimension
        5. See also
      9. Calculating today's date using an attribute hierarchy
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. The Yes member as a default member?
          2. Other approaches
        5. See also
      10. Calculating the difference between two dates
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Dates in other scenarios
          2. The problem of non-consecutive dates
        5. See also
      11. Calculating the difference between two times
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Formatting the duration
          2. Examples of formatting the duration on the Web
          3. Counting working days only
        5. See also
      12. Calculating parallel periods for multiple dates in a set
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Parameters
          2. Reporting covered by design
        5. See also
      13. Calculating parallel periods for multiple dates in a slicer
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
    10. 4. Concise Reporting
      1. Introduction
      2. Isolating the best N members in a set
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. The top N members is evaluated in All Periods, not in the context of the opposite query axis
          2. The top N members will be evaluated in the context of the slicer
          3. Using a tuple in the third argument of the TopCount() function to overwrite the member on the slicer
          4. Testing the correctness of the result
          5. Multidimensional sets
          6. TopPercent() and TopSum() functions
        5. See also
      3. Isolating the worst N members in a set
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      4. Identifying the best/worst members for each member of another hierarchy
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Support for the relative context and multidimensional sets in SSAS frontends
        5. See also
      5. Displaying a few important members, with the others as a single row, and the total at the end
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Making the query even more generic
        5. See also
      6. Combining two hierarchies into one
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Use it, but don't abuse it
          2. Limitations
      7. Finding the name of a child with the best/worst value
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Variations on a theme
          2. Displaying more than one member's caption
        5. See also
      8. Highlighting siblings with the best/worst values
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Troubleshooting
        5. See also
      9. Implementing bubble-up exceptions
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Practical value of bubble-up exceptions
          2. Potential problems
        5. See also
    11. 5. Navigation
      1. Introduction
      2. Detecting a particular member in a hierarchy
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Important remarks
          2. Comparing members versus comparing values
          3. Detecting complex combinations of members
        5. See also
      3. Detecting the root member
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. The scope-based solution
        5. See also
      4. Detecting members on the same branch
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. The query-based alternative
          2. Children() will return empty sets when out of boundaries
          3. Various options of the Descendants() function
        5. See also
      5. Finding related members in the same dimension
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Tips and trick related to the EXISTING keyword
          2. Filter() versus Exists(), Existing(), and EXISTING
          3. A friendly warning
        5. See also
      6. Finding related members in another dimension
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Leaf and non-leaf calculations
        5. See also
      7. Calculating various percentages
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Use cases
          2. The alternative syntax for the root member
          3. The case of the nonexisting [All] level
          4. The percentage of leaf member values
        5. See also
      8. Calculating various averages
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Preserving empty rows
          2. Other specifics of average calculations
        5. See also
      9. Calculating various ranks
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Tie in ranks
          2. Preserving empty rows
          3. Ranks in multidimensional sets
          4. The pluses and minuses of named sets
        5. See also
    12. 6. MDX for Reporting
      1. Introduction
      2. Creating a picklist
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      3. Using a date calendar
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Alternative - allowing users to select by Date hierarchies
        5. See also
      4. Passing parameters to an MDX query
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Getting the summary
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Getting visual totals at multiple levels
      6. Removing empty rows
        1. Getting ready
        2. How to do it...
        3. How it works...
          1. Checking empty sets
        4. There's more...
          1. Trouble with zeros
        5. See also
      7. Getting data on the column
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Named set or DIMENSION PROPERTIES has no effect in the shape of the reports
          2. Creating a column alias in MDX queries can mean data duplication
          3. Creating a column alias is a must with role-playing dimensions
          4. Avoiding using the NON EMPTY keyword on the COLUMNS axis
          5. Query Editor in SSRS only allowing measures dimension in the COLUMNS
          6. A few more words...
        5. See also
      8. Sorting data by dimensions
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Taking advantage of hierarchical sorting
          2. Using the Date type to sort in a non-hierarchical way
          3. "Break hierarchy" - sorting a set in a non-hierarchical way
          4. Sorting can be done in the frontend reporting tool
        5. See also
    13. 7. Business Analyses
      1. Introduction
      2. Forecasting using linear regression
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Tips and tricks
          2. Where to find more information
        5. See also
      3. Forecasting using periodic cycles
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Other approaches
        5. See also
      4. Allocating non-allocated company expenses to departments
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Choosing a proper allocation scheme
      5. Analyzing the fluctuation of customers
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Identifying loyal customers in a period
          2. More complex scenario
          3. The alternative approach
      6. Implementing the ABC analysis
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Tips and tricks
        5. See also
    14. 8. When MDX is Not Enough
      1. Introduction
      2. Using a new attribute to separate members on a level
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. So, where's the MDX?
          2. Typical scenarios
      3. Using a distinct count measure to implement histograms over existing hierarchies
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      4. Using a dummy dimension to implement histograms over nonexisting hierarchies
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. DSV or DW?
          2. More calculations
          3. Other examples
        5. See also
      5. Creating a physical measure as a placeholder for MDX assignments
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Associated measure group
        5. See also
      6. Using a new dimension to calculate the most frequent price
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. Using a utility dimension to implement flexible display units
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Set-based approach
          2. Format string on a filtered set approach
      8. Using a utility dimension to implement time-based calculations
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Interesting details
          2. Fine-tuning the calculations
          3. Other approaches
        5. See also
    15. 9. Metadata - Driven Calculations
      1. Introduction
      2. Setting up the environment
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Additional information
          2. Tips and tricks
        5. See also
      3. Creating a reporting dimension
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      4. Implementing custom rollups using MDX formulas
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Why not a built-in feature?
          2. Why the Sum() function?
          3. More complex formulas
        5. See also
      5. Implementing format string, multiplication factor, and sort order features
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Tips and tricks
          2. Additional information
        5. See also
      6. Implementing unary operators
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      7. Referencing reporting dimension's members in MDX formulas
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      8. Implementing the MDX dictionary
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Additional information
          2. Tips and tricks
        5. See also
      9. Implementing metadata-driven KPIs
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Additional information
          2. Tips and tricks
        5. See also
    16. 10. On the Edge
      1. Introduction
      2. Clearing the Analysis Services cache
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Objects whose cache can be cleared
          2. Additional information
          3. Tips and tricks
        5. See also
      3. Using Analysis Services stored procedures
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Tips and tricks
          2. Existing assemblies
          3. Additional information
        5. See also
      4. Executing MDX queries in T-SQL environments
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Additional information
          2. Useful tips
          3. Accessing Analysis Services 2000 from a 64-bit environment
          4. Troubleshooting the linked server
        5. See also
      5. Using SSAS Dynamic Management Views (DMVs) to fast-document a cube
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Tips and tricks
          2. Warning!
          3. More information
        5. See also
      6. Using SSAS Dynamic Management Views (DMVs) to monitor activity and usage
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      7. Capturing MDX queries generated by SSAS frontends
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Alternative solution
          2. Tips and tricks
        5. See also
      8. Performing a custom drillthrough
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Allowed functions and potential problems
          2. More info
          3. Other examples
        5. See also