You are previewing MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook.
O'Reilly logo
MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook

Book Description

More than 80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries in this book and eBook

  • Enrich your BI solutions by implementing best practice MDX calculations

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

  • Enhance your solutions by combining MDX with utility dimensions

  • Become skilled in making reports concise

  • Learn how to optimize, dissect, and debug your MDX calculations

  • Maximize your learning with detailed explanations following each solution

  • Packed with practical, hands-on cookbook recipes, illustrating the techniques to enrich your Business Intelligence solutions

  • In Detail

    Microsoft SQL Server is an enterprise database platform that contains a multitude of technologies, Analysis Services being one of them. SQL Server Analysis Services (SSAS) provides OLAP and data mining capabilities and allows users to analyze multidimensional data stored in cubes using the MDX query language.

    This cookbook contains over 80 practical, task-based recipes that show how Microsoft SQL Server 2008 R2 Analysis Services solutions can be taken further by enriching them with high-performance MDX calculations and flexible MDX queries.

    Packed with immediately usable, real-world recipes, the book starts with elementary techniques that lay the foundation for designing further MDX calculations and queries. Here you will find topics such as iterations on a set, Boolean logic, and dissecting and optimizing MDX calculations.

    In the first half of the book you will learn how to efficiently work with time, strings, metadata, calculated members and sets in general, and how to implement MDX solutions that are appropriate in a particular context: a time-aware calculation, a concise report, a calculation relative to another. You will also learn how to implement various types of conditional formatting, how to perform typical MDX calculations like ranks, percentages and averages, and year-to-date calculations.

    The book then deep dives into topics such as enhancing cube design with utility dimensions, context-aware calculations, and other advanced topics. In this part you will learn how a utility dimension can be of great help, for example when you want to calculate histograms or implement time-based calculations. The advanced topics also cover parent-child hierchies, recursion, random values, and complex sorts.

    Enrich your Business Intelligence solutions with over 80 recipes for high-performance MDX calculations and flexible MDX queries

    Table of Contents

    1. MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook
      1. MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook
      2. Credits
      3. About the Author
      4. Acknowledgement
      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
        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. Skipping axis
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. The idea behind
            2. Possible workarounds
        3. 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
        4. Setting special format for negative, zero and null values
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Tips and tricks
            2. A friendly warning
            3. Troubleshooting formatted values
            4. Formatting options in detail
          5. See also
        5. Applying conditional formatting on calculations
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Tips and tricks
            2. Warning
          5. See also
        6. Setting default member of a hierarchy in MDX script
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Helpful tips
        7. Implementing NOT IN set logic
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
          5. See also
        8. Implementing 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
        9. 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
        10. 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…
            1. Did you know
          5. See also
        11. 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
        12. 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
        13. Using NON_EMPTY_BEHAVIOR
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Did you know?
          5. See also
        14. Optimizing MDX queries using the NonEmpty() function
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Common mistakes and useful tips
            2. NonEmpty() versus NON EMPTY
          5. See also
        15. Implementing 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. 2. 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. What to be careful about
            3. Common problems and how to avoid them
            4. YTD() and future dates
            5. The Aggregate() function
          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...
          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. See also
        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. How to know which format to use?
            2. Optimizing time-non-sensitive calculation
          5. See also
        7. Hiding calculation values on future dates
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        8. 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. Today or Yesterday?
            3. Alternative to shifting years
            4. Potential problems
          5. See also
        9. 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 Date dimension
          5. See also
        10. 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
        11. 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
        12. 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
        13. 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
        14. 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. 3. 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. Testing the correctness of the result
            2. Multidimensional sets
            3. 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...
            1. BottomPercent() and BottomSum() functions
          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 front-ends
          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. Tips and tricks
            2. 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. 4. 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. An indicator versus the final calculation
            3. Comparing members versus comparing values
            4. 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. What to look for
            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() vs. 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 parent member
            3. The case of non-existing root member
            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. 5. 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 non-allocated company expenses to departments
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. How to choose a proper allocation scheme?
        5. Calculating the number of days from the last sales to identify the slow-moving goods
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. What's missing here?
            2. Don't mix TopCount and Max functions
          5. See also
        6. 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. Did you know?
            3. More complex scenario
            4. The alternative approach
        7. 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
      13. 6. 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 non-existing 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
          5. See also
        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
      14. 7. Context-aware Calculations
        1. Introduction
        2. Identifying the number of columns and rows a query will return
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Visibility of the calculations
          5. See also
        3. Identifying the axis with measures
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Visibility of the calculations
            2. Additional axes
          5. See also
        4. Identifying the axis without measures
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Visibility of the calculations
            2. Additional axes
            3. More information on the behavior of OWC
          5. See also
        5. Adjusting the number of columns and rows for OWC and Excel
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Other SSAS front-ends
          5. See also
        6. Identifying the content of axes
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. What about the other axis?
            2. Potential problems
            3. More info
          5. See also
        7. Calculating row numbers
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Performance of the calculation
            2. Related calculations
            3. Visibility of the calculations
            4. Other SSAS front-ends
          5. See also
        8. Calculating the bit-string for hierarchies on an axis
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Visibility of the calculations
            2. Other SSAS front-ends
            3. More info
        9. Preserving empty rows
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Multiple measures
            2. When the measure is not known in advance
        10. Implementing utility dimension with context-aware calculations
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Performance of the calculation
            2. Visibility of the calculations
            3. In case of problems
            4. More info
      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. What happens when optional arguments are not used?
            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. Implementing the Tally table utility dimension
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        5. Displaying random values
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        6. 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
        7. Displaying a sample from a random hierarchy
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. The N/A sign
            2. How to use another measure?
          5. See also
        8. 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
        9. 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. Which type of calculation to choose?
          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
          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 (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 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 front-ends
          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 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. A. Conclusion
      18. B. Glossary of Terms
        1. Parts of an MDX query
          1. Regular members
          2. Calculated members
          3. Tuples
          4. Named sets
          5. Set alias
          6. Axis
          7. Slicer
          8. Subquery
          9. Cell properties
          10. Dimension properties
        2. MDX query in action
          1. Explicit members
          2. Implicit members
          3. Data members
          4. Current context
          5. Dimensionality
          6. Attribute overwrites
          7. Visual Totals
          8. Iteration
          9. Recursion
          10. Context-aware calculations
          11. Set-based operations
          12. Errors
        3. Cube and dimension design
          1. Default member
          2. Attribute relationships
          3. Natural versus unnatural hierarchies
          4. Parent-child hierarchies
          5. Utility dimension
          6. Dummy dimension
          7. Granularity
          8. Deployment versus processing
        4. MDX script
          1. Calculate statement
          2. Scopes
          3. Assignments
          4. Dynamic versus static sets
        5. Query optimization
          1. Block-computation versus cell-by-cell evaluation mode
          2. Arbitrary shaped sets
          3. Varying attribute
          4. Static versus dynamic calculation
          5. Late binding functions
          6. Sparse versus dense expressions
          7. Cache
        6. Types of query
          1. MDX query
          2. XMLA query
          3. Drillthrough
          4. DMVs
          5. Stored procedures