You are previewing MDX with SSAS 2012 Cookbook.
O'Reilly logo
MDX with SSAS 2012 Cookbook

Book Description

In this book you’ll find 90 clearly written recipes to help developers advance their skills with the demanding but powerful language MDX and SQL Server Analysis Services. All leading to greatly improved business intelligence solutions.

  • A wide range of time-related, context-aware, and business-related calculations

  • Combine MDX with utility dimensions

  • Illustration of techniques to enrich business intelligence solutions, aided by practical, hands-on Cookbook recipes

  • In Detail

    MDX is the BI industry standard for multidimensional calculations and queries. Proficiency with this language is essential for the realization of your Analysis Services’ full potential. MDX is an elegant and powerful language, and also has a steep learning curve. SQL Server 2012 Analysis Services has introduced a new BISM tabular model and a new formula language, Data Analysis Expressions (DAX). However, for the multi-dimensional model, MDX is still the only query and expression language. For many product developers and report developers, MDX is the preferred language for both the tabular model and multi-dimensional model.

    MDX with SSAS 2012 Cookbook is a must-have book for anyone who wants to be proficient in the MDX language and to enhance their business intelligence solutions.

    MDX with SSAS 2012 Cookbook is packed with immediately usable, practical solutions. It starts with elementary techniques that lay the foundation for designing advanced MDX calculations and queries. The discussions after each solution will provide you with a solid foundation and best practices. It covers a broad range of real-world topics and solutions and provides you with learning materials to become proficient in the language.

    This book will guide you through the hands-on and practical MDX solutions, best practices, and many intricacies that hide within the MDX calculations and queries.

    We will start by working with sets, creating time-aware, context-aware calculations, and business analytics solutions, through to the techniques of enhancing the cube design when MDX is not enough. We will then move on to capturing MDX generated by SSAS front-ends and using SSAS stored procedures, and we will explore the whole range of MDX solutions for real-world BI projects.

    Table of Contents

    1. MDX with SSAS 2012 Cookbook
      1. Table of Contents
      2. MDX with SSAS 2012 Cookbook
      3. Credits
      4. About the Authors
      5. About the Reviewers
      6. www.PacktPub.com
        1. Support files, eBooks, discount offers and more
          1. Why Subscribe?
          2. Free Access for Packt account holders
          3. Instant Updates on New Packt Books
      7. Preface
        1. What this book covers
        2. What you need for this book
          1. Database Installation
          2. Sample Adventure Works 2012 Database
          3. For Creating PivotTable
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Downloading the example code
          2. Errata
          3. Piracy
          4. Questions
      8. 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
      9. 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. 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
        5. Iterating on a set in order 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
        6. Iterating on a set in order 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
        7. 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
        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
      10. 3. Working with Time
        1. Introduction
        2. Calculating the YTD (Year-To-Date) 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 YoY (Year-over-Year) 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. Format members on the Date dimension properly
            2. Optimizing time-non-sensitive calculation
        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. Member Yes 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
      11. 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 member is evaluated in All Periods, not in the context of the opposite query axis
            2. The top N member will be evaluated in the context of the slicer
            3. Use 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 few important members, 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
      12. 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 combination 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 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
      13. 6. Business Analytics
        1. Introduction
        2. Forecasting using the 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 the 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 the nonallocated 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 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 particular 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. 7. 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. 8. Advanced MDX Topics
        1. Introduction
        2. Displaying members without children (leaves)
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Second and third argument in Descendant() are optional
            2. A reverse case
            3. Possible problems with ragged hierarchies
          5. See also
        3. Displaying members with data in parent-child hierarchies
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Alternative solution
          5. See also
        4. Displaying random values
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        5. Displaying a random sample of hierarchy members
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Alternative solution
          5. See also
        6. Displaying a sample from a random hierarchy
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          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. Using recursion to calculate cumulative values
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. A simplified version of the solution
            2. Choosing between recursive and cumulative calculations
          5. See also
      16. 9. 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
            4. 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 (DMV) 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 View (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 about them
            2. More info
            3. Other examples
          5. See also
      17. Index