Table of Contents
Preface
Chapter 1: Elementary MDX Techniques
Chapter 2: Working with Time
Chapter 3: Concise Reporting
Chapter 4: Navigation
Chapter 5: Business Analytics
Chapter 6: When MDX is Not Enough
Chapter 7: Context-aware Calculations
Chapter 8: Advanced MDX Topics
Chapter 9: On the Edge
Conclusion
Appendix: Glossary of Terms
Index
- Chapter 1: Elementary MDX Techniques
- Introduction
- Skipping axis
- Handling division by zero errors
- Setting special format for negative, zero and null values
- Applying conditional formatting on calculations
- Setting default member of a hierarchy in MDX script
- Implementing NOT IN set logic
- Implementing logical OR on members from different hierarchies
- Iterating on a set in order to reduce it
- Iterating on a set in order to create a new one
- Iterating on a set using recursion
- Dissecting and debugging MDX queries
- Using NON_EMPTY_BEHAVIOR
- Optimizing MDX queries using the NonEmpty() function
- Implementing logical AND on members from the same hierarchy
- Chapter 2: Working with Time
- Introduction
- Calculating the YTD (Year-To-Date) value
- Calculating the YoY (Year-over-Year) growth (parallel periods)
- Calculating moving averages
- Finding the last date with data
- Getting values on the last date with data
- Hiding calculation values on future dates
- 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
- Chapter 3: Concise Reporting
- Introduction
- Isolating the best N members in a set
- Isolating the worst N members in a set
- Identifying the best/worst members for each member of another hierarchy
- Displaying few important members, 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 bubble-up exceptions
- Chapter 4: Navigation
- Introduction
- Detecting a particular member in a hierarchy
- Detecting the root member
- Detecting members on the same branch
- Finding related members in the same dimension
- Finding related members in another dimension
- Calculating various percentages
- Calculating various averages
- Calculating various ranks
- Chapter 5: Business Analytics
- Introduction
- Forecasting using the linear regression
- Forecasting using the periodic cycles
- Allocating the non-allocated company expenses to departments
- Calculating the number of days from the last sales to identify the slow-moving goods
- Analyzing fluctuation of customers
- Implementing the ABC analysis
- Chapter 6: 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 non-existing 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 time-based calculations
- Chapter 7: Context-aware Calculations
- Introduction
- Identifying the number of columns and rows a query will return
- Identifying the axis with measures
- Identifying the axis without measures
- Adjusting the number of columns and rows for OWC and Excel
- Identifying the content of axes
- Calculating row numbers
- Calculating the bit-string for hierarchies on an axis
- Preserving empty rows
- Implementing utility dimension with context-aware calculations
- Chapter 8: Advanced MDX Topics
- Introduction
- Displaying members without children (leaves)
- Displaying members with data in parent-child hierarchies
- Implementing the Tally table utility dimension
- Displaying random values
- Displaying a random sample of hierarchy members
- Displaying a sample from a random hierarchy
- Performing complex sorts
- Using recursion to calculate cumulative values
- Chapter 9: On the Edge
- Introduction
- Clearing the Analysis Services cache
- Using Analysis Services stored procedures
- Executing MDX queries in T-SQL environments
- Using SSAS Dynamic Management Views (DMV) to fast-document a cube
- Using SSAS Dynamic Management Views (DMVs) to monitor activity and usage
- Capturing MDX queries generated by SSAS front-ends
- Performing custom drillthrough
- Appendix: Glossary of Terms
- Parts of an MDX query
- MDX query in action
- Cube and dimension design
- MDX script
- Query optimization
- Types of query
Book backreference:
