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.
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 year-to-date (YTD) value
- Calculating the year-over-year (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 bubble-up 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 role-playing 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 time-based 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 metadata-driven KPIs
-
10. 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 (DMVs) to fast-document 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
SQL Server 2016 Reporting Services Cookbook
Create interactive cross-platform reports and dashboards using SQL Server 2016 Reporting Services About This Book Get …
book
Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX
Understand Microsoft's dramatically updated new release of its premier toolset for business intelligence The first major …
book
SQL Server Analysis Services 2012 Cube Development Cookbook
If you prefer the instructional approach to a lot of theory, this cookbook is for you. …
book
SQL Server 2017 Integration Services Cookbook
Harness the power of SQL Server 2017 Integration Services to build your data integration solutions with …