MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition E-Book
Tomislav Piasevoli
46,79 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
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.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 656
Veröffentlichungsjahr: 2016
Ähnliche
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
Copyright © 2016 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: August 2011
Second edition: August 2013
Third edition: November 2016
Production reference: 1241116
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-78646-099-8
www.packtpub.com
Credits
Authors
Tomislav Piasevoli
Sherry Li
Copy Editor
Safis Editing
Reviewers
Dave Wentzel
Project Coordinator
Shweta H Birwatkar
Commissioning Editor
Wilson D'souza
Proofreader
Safis Editing
Acquisition Editor
Tushar Gupta
Indexer
Mariammal Chettiyar
Content Development Editor
Sumeet Sawant
Graphics
Disha Haria
Technical Editor
Sneha Hanchate
Production Coordinator
Arvindkumar Gupta
About the Authors
Tomislav Piasevoli is a Business Intelligence (BI) specialist with years of experience working with Microsoft SQL Server Analysis Services (SSAS). He successfully implemented many still-in-use BI solutions, helped numerous people on MSDN forum, achieved the highest certification for SQL Server Analysis Services (SSAS Maestro), and shared his expertise in form of MDX cookbooks.
Tomislav currently works as a consultant at Piasevoli Analytics company (www.piasevoli.com) together with his brother Hrvoje. They specialize in Microsoft SQL Server Business Intelligence platform, SSAS primarily, and offer their BI skills worldwide.
In addition to his regular work, Tomislav manages to find the time to present at local conferences or to write an article or two for local magazines. His contribution to the community has been recognized by Microsoft honoring him with the Most Valuable Professional (MVP) award for six consecutive years (2009-2015).
A large portion of this cookbook is present in all editions, therefore I feel obliged to express my gratitude once again to all the people that influenced its contents or helped making it better. They are: Chris Webb, Greg Galloway, Marco Russo, Darren Gosbell, Deepak Puri, Hrvoje Piasevoli, Willfried Färber, Mosha Pasumansky, Teo Lachev, Jeffrey Wang, Jeremy Kashel, Vidas Matelis, Thomas Kejser, Jeff Moden, Michael Coles, Itzik Ben-Gan, Irina Gorbach, Vincent Rainardi, and my ex-colleagues at SoftPro Tetral company. Next, I appreciate Packt Publishing for giving me a chance to write the first edition of this book. In this third edition, I am thankful to Sumeet Sawant and Tushar Gupta for their help and patience. Dave Wentzel deserves a big thank you for making sure the recipes make sense and that they are laid out in an understandable and clear way. A huge thank you goes to Sherry Li, my dear partner in this project. Her friendly attitude and willingness to help meant a lot to me while I was struggling with some recipes. Speaking of recipes, there were few bright people that took significant part in forcing me to rethink the recipes repeatedly and, in that way, either inspired me or helped me make them better. They are: Snježana Škledar, Aleš Plavčak, Hrvoje Gabelica, and Philipp Trannacher. Thank you, guys! Finally, a thank you goes to my family, close friends, business partners, and clients for understanding why I partially neglected you while working on the book. I dedicate this book to my children, Petra, Matko, and Nina.
Sherry Li is an Analytic Consultant who works for a major financial organization with responsibilities in implementing data warehousing, Business Intelligence, and business reporting solutions. She specializes in automation and optimization of data gathering, storing, analyzing and providing data access for business to gain data-driven insights. She especially enjoys sharing her experience and knowledge in data ETL process, database design, dimensional modeling, and reporting in T-SQL and MDX. She has co-authored two books, the MDX with SSAS 2012 Cookbook and MDX with Microsoft SQL Server 2016 Analysis Services Cookbook, which have helped many data professionals advanced their MDX skill in a very short time. Sherry Li maintains her blog at bisherryli.com.
This book is dedicated to readers who are enthusiastic about Multidimensional modeling and MDX (Multi-Dimensional eXpressions). What I love to do the most is share knowledge, so it is wonderful knowing that the MDX Cookbook is a popular book! Readers who want to become proficient in MDX have given tremendous responses to the first two editions of the book. There is nothing that satisfies me more than knowing that this 2016 edition have even more to share with the readers. I owe tremendous thanks to Packt Publishing for giving me another opportunity to write this edition of the MDX Cookbook. Their first-class professionalism in book designing, editing, publishing and collaboration has impressed me during the entire book project. Special thanks to Sumeet Sawant who is a wonderful content editor, and Tushar Gupta who initiated the project.
Three years ago I was daring enough to take the challenge of working on the second edition of the MDX Cookbook. This third edition has brought me once again working side-by-side with Tomislav Piasevoli, who had this bold idea of adding two new chapters with contents that were never fully presented before in previous MDX books. His dedication to the readers and attention to details left me with a great impression. This 2016 edition would not be possible without his leadership. Thank you Tomislav for your commitment to collaboration, encouragement, and deep knowledge of MDX and cube design. I look forward to future collaboration. To Dave Wentzel, for your insight, helpful questioning, (“Can you give an example or screenshot of this? This may be difficult to conceptually follow for the novice.”) and encouraging comments ("Good explanation. Seems important enough to call out in a tip box or something else to visually note it is important.").
Thanks to all my friends, especially my ACSE (Association of Chinese-American Scientists and Engineers) friends for sharing my sense of accomplishment. To my co-workers, current and past, for their earnest encouragement, enthusiasm, and feedbacks. Last and foremost, I want to thank my husband Jim and daughter Shasha, for all of the support they have given to me. All of the MDX Cookbook work occurred on weekends, nights, and other times inconvenient to my family. To my daughter, for also being my English grammar teacher.
To my dog Atari, for always sitting by my feet while I write late at night.
About the Reviewer
Dave Wentzel is a Data Solutions Architect for Microsoft. He helps customers with their Azure Digital Transformation, focused on data science, big data, and SQL Server. After working with customers, he provides feedback and learnings to the product groups at Microsoft to make better solutions. Dave has been working with SQL Server for many years, and with MDX and SSAS since they were in their infancy. Dave shares his experiences at http://davewentzel.com. He’s always looking for new customers. Would you like to engage?
www.PacktPub.com
For support files and downloads related to your book, please visit www.PacktPub.com.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
https://www.packtpub.com/mapt
Get the most in-demand software skills with Mapt. Mapt gives you full access to all Packt books and video courses, as well as industry-leading tools to help you plan your personal development and advance your career.
Why subscribe?
Preface
Microsoft SQL Server Analysis is one of the keystones of Microsoft's Business Intelligence (BI) product strategy. It is the most widely deployed OLAP server around the world. Many organizations, both large and small, have adopted it to provide secure and high-performance access to complex analytics.
MDX (for Multi-Dimensional eXpressions) is the BI industry standard for multidimensional calculations and queries, and is the most widely accepted software language in multidimensional data warehouse. Proficiency with MDX is essential for any professionals who work with multidimensional cubes. MDX is an elegant and powerful language, but 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 still is and will be the preferred language for both the tabular model and the multi-dimensional model.
SQL Server 2016 is the biggest leap forward in Microsoft’s data platform history. SQL Server 2016 Analysis Services has also come with some great improvements and features for Multidimensional model. The DirectQuery mode can now be used to connect directly to SQL Server, SQL Server Parallel Data Warehouse (Microsoft Analytics Platform System), Oracle and Teradata. The DirectQuery mode has also significantly improved performance compared to the previous version. The SQL Server 2012 Management Studio (SSMS) came with a graphical user interface to configure and manage Extended Events within SQL Server Database Engine. Now this is also available for SQL Server 2016 Analysis Services. The Extended Events support through SSMS GUI allows a simplified way of monitoring your Analysis Services 2016 instances, both Tabular and Multidimensional.
Despite its popularity, very few books are dedicated to MDX. MDX-related books often limit their content to explaining the concepts of multidimensional cubes, the MDX language concept and its functions, and other specifics related to working with Analysis Services.
This book presents MDX solutions for business requirements that can be found in the real business world. You will find best practices, explanations of advanced subjects in full detail, and deep knowledge in every topic. Organized around practical MDX solutions, this book provides full, in-depth treatment of each topic, sequenced in a logical progression from elementary to advanced techniques.
This book is written in a cookbook format. You can browse through the contents and look for solutions to a particular problem. Each recipe is relatively short and grouped by relevancy, so you can find solutions to related issues in one place. Related recipes are sequenced in a logical progression; you will be able to build up your understanding of the topic incrementally.
This book is designed for both beginners and experts in MDX. If you are a beginner, this book is a good place to start. Each recipe provides you with best practices and their underlying rationale, detailed sample scripts, and options you need to know to make good choices. If you are an expert, you will be able to use this book as a reference. Whenever you face a particular challenge, you will be able to find a chapter that is dedicated to the topic.
We hope that you will become confident not only in using the sample MDX queries, but also in creating your own solutions. The moment you start creating your own solutions by combining techniques presented in this book, our goal of teaching through examples is accomplished. We want to hear from you about your journey to MDX proficiency. Feel free to contact us.
What this book covers
We added two new chapters to this edition of MDX cookbook: Chapter 6, MDX for Reporting, and Chapter 9, Metadata - Driven Calculations. We also decided to remove Chapter 8, Advanced MDX Topics due to many overlapping and redundant recipes.
To turn ad-hoc reports into parameterized reports is a challenging task. There are many special considerations associated with the dynamic nature of the reports with dynamic parameters. Through carefully thought-out examples, Chapter 6, MDX for Reporting, introduces new concepts in dynamic reporting, the challenges and the techniques for efficient report writing.
Once a cube is designed and implemented, adding more calculations is a common requirement. These calculations are defined not by the data of the cube, but by expressions that can reference other parts of the cube. MDX calculations that are metadata-driven let us extend the capabilities of a cube, adding flexibility and power to business intelligence solutions. It also comes with challenges, of having instead complex calculations. Chapter 9, Metadata-driven Calculations will cover techniques and best practices that have never been fully documented in any MDX books before.
Here's an overview of chapters and their contents.
Chapter 1, Elementary MDX Techniques, uses simple examples to demonstrate the fundamental MDX concepts, features, and techniques that are the foundations for our further explorations of the MDX language.
Chapter 2, Working with Sets, focuses on the challenges of performing logic operations, NOT, OR and AND, on manipulating sets in general.
Chapter 3, Working with Time, presents various time-related functions in MDX that are designed to work with a special type of dimension called Time and its typed attributes.
Chapter 4, Concise Reporting, focuses on techniques that you can employ in your project to make analytical reports more compact and more concise, and therefore, more efficient.
Chapter 5, Navigation , shows common tasks and techniques related to navigation and data retrieval relative to the current context.
Chapter 6, MDX for Reporting, covers common MDX reporting requirements and techniques using two approaches: parameterized MDX queries and dynamic MDX queries.
Chapter 7, Business Analytics, focuses on performing typical business analyses, such as forecasting, allocating values, and calculating the number of days from the last sales date.
Chapter 8, When MDX is Not Enough, teachers you that MDX calculations are not always the place to look for solutions. It illustrates several techniques to optimize the query response times with a relatively simple change in cube structure.
Chapter 9, Metadata-driven Calculations, explores the concept of storing and maintaining MDX calculations outside the cube by utilizing reporting dimension, custom aggregations, scopes and assignments.
Chapter 10, On the Edge, presents topics that will expand your horizons, such as clearing cache for performance tuning, executing MDX queries in T-SQL environment, using SSAS Dynamic Management Views (DMVs), drill-through, and capturing MDX queries using SQL Server Profiler.
What you need for this book
A Microsoft SQL Server 2016 full installation or at least the following components are required:
We recommend the Developer, Enterprise, or the Trial Edition of Microsoft SQL Server 2016. Standard Edition is not recommended because it does not support all the features and a few examples might not work using the Standard Edition.
The Developer Edition has the full capabilities of the Enterprise Edition and is for development and testing only. The Developer Edition is free if you sign up for the free Visual Studio Dev Essentials program. To download the SQL Server 2016 Developer Edition free, you can start from joining or accessing the Visual Studio Dev Essentials site:
https://www.visualstudio.com/dev-essentials/
You can also access it from this tiny url:
http://tinyurl.com/zzpzdwv
Microsoft SQL Server 2016 Trial Edition is for evaluation only and is valid for 180 days. Use this link to go to Microsoft Evaluation Center:
http://tinyurl.com/joap9rh
Both the relational database file and the multidimensional Adventure Works project files are required:
We recommend the Enterprise Edition of the Multidimensional Model Adventure Works cube project. To download the installation files, use the following link to go to CodePlex:
http://tinyurl.com/AdventureWorks2012
For the 2014 Multidimensional Model Adventure Works cube project, go to Adventure Works 2014 Sample Databases on CodePlex:
http://tinyurl.com/otj8bxf
For instructions on how to install the sample Adventure Works, see Install Sample Data and Projects for the Analysis Services Multidimensional Modeling Tutorial at this link:
http://tinyurl.com/jx6ghbm
Wide World Importers: The new SQL Server sample database
For the magnitude of SQL Server 2016 Microsoft has released a new sample database, the Wide World Importers database.
Both the 2008 and 2012 edition of the MDX Cookbook has been based off Adventure Works, which has been around since the SQL Server 2005 days. For the purpose of demonstrating MDX techniques and Analysis Services features, the Adventure Works sample database has continued to be a good choice for this 2016 edition.
For Creating PivotTable, see this section:
Microsoft Excel 2007 (or newer) with PivotTable is required.
Most of the examples will work with older versions of Microsoft SQL Server (2005 or 2008 or 2008 R2 or 2012). However, some of them will need adjustments because the Date dimension in the older versions of the Adventure Works database has a different set of years. To solve that problem, simply shift the date-specific parts of the queries few years back in time, for example, turn the year 2013 into the year 2002 and Q3 of the year 2013 to Q3 of 2003.
Who this book is for
This is a book for multidimensional cube developers and multidimensional database administrators, for report developers who write MDX queries to access multidimensional cubes, for power users and experienced business analysts. All of the will find this book invaluable.
In other words, this book is for anyone who works with multidimensional cubes, who finds himself or herself in situations feeling difficult to deliver what end users ask for or who are interested in getting more out of their multidimensional cubes. This book is for you if you have found yourself in situations where it is difficult to deliver what your users want and you are interested in getting more information out of your multidimensional cubes.
Sections
In this book, you will find several headings that appear frequently (Getting ready, How to do it, How it works, There's more, and See also).
To give clear instructions on how to complete a recipe, we use these sections as follows:
Getting ready
This section tells you what to expect in the recipe, and describes how to set up any software or any preliminary settings required for the recipe.
How to do it…
This section contains the steps required to follow the recipe.
How it works…
This section usually consists of a detailed explanation of what happened in the previous section.
There's more…
This section consists of additional information about the recipe in order to make the reader more knowledgeable about the recipe.
See also
This section provides helpful links to other useful information for the recipe.
Conventions
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
When shown in text, code words NONEMPTY() will be shown as follows: "Optimizing MDX queries using the NONEMPTY() function."
A block of code is set as follows:
SELECT { [Measures].[Reseller Order Quantity], [Measures].[Reseller Order Count] } ON 0, NON EMPTY { [Date].[Month of Year].MEMBERS } ON 1 FROM [Adventure Works] WHERE ( [Promotion].[Promotion Type].&[New Product] )When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
SELECT { [Measures].[Reseller Sales Amount] } ON 0, { ParallelPeriod( [Geography].[Geography].[Country], 2, [Geography].[Geography].[State-Province].&[CA]&[US] ) } ON 1 FROM [Adventure Works]New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "We can verify this by browsing the Geography user hierarchy in the Geography dimension in SQL Server Management Studio".
Note
Warnings or important notes appear in an information box like this.
Tip
Tips and tricks appear in a tip box like this.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book-what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of.
To send us general feedback, simply e-mail [email protected], and mention the book's title in the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors .
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for this book 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 files e-mailed directly to you.
You can download the code files by following these steps:
You can also download the code files by clicking on the Code Files button on the book's webpage at the Packt Publishing website. This page can be accessed by entering the book's name in the Search box. Please note that you need to be logged in to your Packt account.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/MDX-with-Microsoft-SQL-Server-2016-Analysis-Services-Cookbook. We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Downloading the color images of this book
We also provide you with a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from https://www.packtpub.com/sites/default/files/downloads/MDXwithMicrosoftSQLServer2016AnalysisServicesCookbook_ColorImages.pdf.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books-maybe a mistake in the text or the code-we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.
To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.
Piracy
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at [email protected] with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
Questions
If you have a problem with any aspect of this book, you can contact us at [email protected], and we will do our best to address the problem.
Chapter 1. Elementary MDX Techniques
In this chapter, we will cover the following recipes:
Introduction
MDX is an elegant and powerful language, but also has a steep learning curve.
The goal of this chapter is to use some simple examples to demonstrate the fundamental MDX concepts, features, and techniques that are the foundations for further exploration of the MDX language.
The chapter begins with several basic techniques: putting multi-dimensional data onto query axes, cube space restriction, empty cell removal, and the important concept of unique names for members, tuples, and sets. From there, we shall turn our attention to a few more advanced features, such as using the MDX functions, creating calculations in the cube space, manipulating strings, writing parameterized queries, and conditionally formatting cell properties. This will form the basis for the rest of the chapters in this book.
SSAS 2016 provides a sample Analysis Services database, the Multidimensional Adventure Works DW. All the MDX queries and scripts in this book have been updated for Analysis Services 2016, and verified against the 2016 Enterprise Edition of the Adventure Works DW Analysis Services database. The majority of the MDX queries and scripts should also run and have been tested in SSAS 2008 R2 and also SSAS2012.
The Query Editor in SQL Server Management Studio (SSMS) is our choice for writing and testing MDX queries. SQL Server 2012 and 2016 come with a free tool: SQL Server Data Tools (SSDT) for cube developers. Just as the Business Intelligence Development Studio (BIDS) was the tool that we used for cube design and MDX scripting in SSAS 2008, SSDT is the tool we will use in this cookbook for cube design and MDX scripting for SSAS 2016.
Putting data on x and y axes
Cube space in SSAS is multi-dimensional. MDX allows you to display results on axes from 0, 1, and 2, up to 128. The first five axes have aliases: COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS. However, the frontend tools such as SQL Server Management Studio (SSMS) or other applications that you can use for writing and executing MDX queries only have two axes, the x and y axes, or COLUMNS and ROWS.
As a result, we have two tasks to do when trying to fit the multi-dimensional data onto the limited axes in our frontend tool:
It is fair to say that your job of writing MDX queries is mostly trying to figure out how to project multi-dimensional data onto only two axes, namely, x and y. We will start by putting only one hierarchy on COLUMNS, and one on ROWS. Then we will use the Crossjoin() function to combine more than one hierarchy into COLUMNS and ROWS.
Getting ready
Making a two–by–eight table (that is shown following) in a spreadsheet is quite simple. Writing an MDX query to do that can also be very simple. Putting data on the x and y axes is a matter of finding the right expressions for each axis:
Internet Sales Amount
Australia
$9,061,000.58
Canada
$1,977,844.86
France
$2,644,017.71
Germany
$2,894,312.34
NA
(null)
United Kingdom
$3,391,712.21
United States
$9,389,789.51
All we need are three things from our cube:
Once we have the preceding three things, we are ready to plug them into the following MDX query, and the cube will give us back the two–by–eight table:
SELECT [The Sales Expression] ON COLUMNS, [The Territory Expression] ON ROWS FROM [The Cube Name]The MDX engine will understand it perfectly, if we replace columns with 0 and rows with 1. Throughout this book, we will use the number 0 for columns, which is the x axis, and 1 for rows, which is the y axis.
How to do it...
We are going to use the Adventure Works 2016 Multidimensional Analysis Service database enterprise edition in our cookbook. If you open the Adventure Works cube, and hover your cursor over the Internet Sales Amount measure, you will see the fully qualified expression, [Measures].[Internet Sales Amount]. This is a long expression. Drag and drop in SQL Server Management Studio works perfectly for us in this situation.
Tip
Long expressions are a fact of life in MDX. Although the case does not matter, correct spelling is required, and fully qualified and unique expressions are recommended for MDX queries to work properly.
Follow these two steps to open the Query Editor in SSMS:
Follow these steps to save the time spent for typing the long expressions:
This should be your final query:
SELECT [Measures].[Internet Sales Amount] ON 0, [Sales Territory].[Sales Territory Country].[Sales Territory Country] ON 1 FROM [Adventure Works]Tip
Downloading the example code:
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 files e-mailed directly to you.
When you execute the query, you should get a two–by–eight table, the same as in the following screenshot:
How it works...
We have chosen to put Internet Sales Amount on the AXIS(0), and all members of Sales Territory Country on the AXIS(1). We have fully qualified the measure with the special dimension [Measures], and the sales territory members with dimension [Sales Territory] and hierarchy [Sales Territory Country].
You might have expected an aggregate function such as SUM somewhere in the query. We do not need to have any aggregate function here because the cube understands that when we ask for the sales amount for Canada, we would expect the sales amount to come from all the provinces and territories in Canada.
There's more...
SSAS cubes are perfectly capable of storing data in more than two dimensions. In MDX, we can use the technique called crossjoin to combine multiple hierarchies into one query axis.
Putting more hierarchies on x and y axes with cross join
In an MDX query, we can specify how multi-dimensions from our SSAS cube lay out onto only two x and y axes. Cross–joining allows us to get every possible combination of two lists in both SQL and MDX.
We wish to write an MDX query to produce the following table. On the columns axis, we want to see both Internet Sales Amount and Internet Gross Profit. On the rows axis, we want to see all the sales territory countries, and all the products sold in each country:
Internet Sales Amount
Internet Gross Profit
Australia
Accessories
$138,690.63
$86,820.10
Australia
Bikes
$8,852,050.00
$3,572,267.29
Australia
Clothing
$70,259.95
$26,767.68
Australia
Components
(null)
(null)
Canada
Accessories
$103,377.85
$64,714.37
Canada
Bikes
$1,821,302.39
$741,451.22
Canada
Clothing
$53,164.62
$23,755.91
Canada
Components
(null)
(null)
This query lays two measures on columns from the same dimension [Measures], and two different hierarchies; [Sales Territory Country] and [Product Categories] on rows:
SELECT { [Measures].[Internet Sales Amount], [Measures].[Internet Gross Profit] } ON 0, { [Sales Territory].[Sales Territory Country].[Sales Territory Country] * [Product].[Product Categories].[Category] } ON 1 FROM [Adventure Works]To return the cross–product of two sets, we can use either of the following two syntaxes:
Standard syntax: Crossjoin(Set_Expression1, Set_Expression2) Alternate syntax: Set_Expression1 * Set_Expression2We have chosen to use the alternate syntax for its convenience. The result from the previous query is shown as follows:
Skipping axes
There are situations where we want to display just a list of members with no data associated with them. Naturally, we expect to get that list in rows, so that we can scroll through them vertically instead of horizontally. However, the rules of MDX say that we can't skip the axes. If we want something on rows (which is AXIS(1) by the way), we must use all previous axes as well (columns in this case, which is also known as AXIS(0)).
The reason why we want the list to appear on axis 1 and not axis 0 is because a horizontal list is not as easy to read as a vertical one.
Is there a way to display those members on rows and have nothing on columns? Sure! This recipe shows how.
Getting ready
The notation for an empty set is this: { }. So for the axis 0, we would simply do this:
{ } ON 0How to do it...
Follow these steps to open the Query Editor in SQL Server Management Studio (SSMS):
Follow these steps to get a one-dimensional query result with members on rows:
How it works...
Although we can't skip axes, we are allowed to provide an empty set on them. This trick allows us to get what we need—nothing on columns and a set of members on rows.
There's more...
Skipping the AXIS(0) is a common technique to create a list for report parameters. If we want to create a list of customers whose name contains John, we can modify the preceding base query to use two functions to get only those customers whose name contains the phrase John. These two functions are Filter() and InStr():
SELECT { } ON 0, { Filter( [Customer].[Customer].[Customer].MEMBERS, InStr( [Customer].[Customer].CurrentMember.Name, 'John' ) > 0 ) } ON 1 FROM [Adventure Works]In the final result, you will notice the John phrase in various positions in member names:
The idea behind it
Instead of skipping the AXIS(0), if you put a cube measure or a calculated measure with a non-constant expression on axis 0, you will slow down the query. The slower query time can be noticeable if there are a large number of members from the specified hierarchy. For example, if you put the Sales Amount measure on axis 0, the Sales Amount will have to be evaluated for each member in the rows. Do we need the Sales Amount? No, we don't. The only thing we need is a list of members; hence we have used an empty set {} on AXIS(0). That way, the SSAS engine does not have to go into cube space to evaluate the sales amount for every customer. The SSAS engine will only reside in dimension space, which is much smaller, and the query is therefore more efficient.
Possible workarounds - dummy column
Some client applications might have issues with the MDX statement skipping axes because they expect something on columns, and will not work with an empty set on axis 0. In this case, we can define a constant measure (a measure returning null, 0, 1, or any other constant) and place it on columns. In MDX's terms, this constant measure is a calculated measure. It will act as a dummy column. It might not be as efficient as an empty set, but it is a much better solution than the one with a regular (non-constant) cube measure like the Sales Amount measure.
This query creates a dummy value on columns:
WITH MEMBER [Measures].[Dummy] AS NULL SELECT { [Measures].[Dummy] } ON 0, { [Customer].[Customer].[Customer].MEMBERS } ON 1 FROM [Adventure Works]Using a WHERE clause to filter the data returned
A WHERE clause in MDX works in a similar way as the other query languages. It acts as a filter and restricts the data returned in the result set.
Not surprisingly, however, the WHERE clause in MDX does more than just restricting the result set. It also establishes the query context.
Getting ready
The MDX WHERE clause points to a specific intersection of cube space. We use tuple expressions to represent cells in cube space. Each tuple is made up of one member, and only one member, from each hierarchy.
The following tuple points to one year, 2013 and one measure, the [Internet Sales Amount]:
( [Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2013] )Using a tuple in an MDX WHERE clause is called slicing the cube. This feature gives the WHERE clause another name, slicer. If we put the previous tuple in the WHERE clause, in MDX terms, we are saying, show me some data from the cube sliced by sales and the year 2013.
That is what we are going to do next.
How to do it...
Open the Query Editor in SSMS, and then follow these steps to write a query with a slicer and test it:
You will see the following result:
At this point, we should ask the question, What are the cell values? The cell values are actually the [Measures].[Reseller Sales Amount], which is the default member on the Measures dimension.Add the previous tuple to the query as a slicer. Here is the final query: SELECT { [Customer].[Customer Geography].[Country] } ON 0, { [Product].[Product Categories].[Category] } ON 1 FROM [Adventure Works] WHERE ( [Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2013] ) The result should be as shown in the following screenshot:Ask the question again; What are the cell values? The cell values are now the [Measures].[Internet Sales Amount], and no longer the default measure.How it works...
We can slice the data by pointing to a specific intersection of cube space. We can achieve this by putting a tuple in the WHERE clause.
In the preceding example, the cube space is sliced by sales and the year 2008. The cell values are the Internet Sales Amount for each country and each product category, sliced by the year 2008.
There's more...
Notice that the data returned on the query axes can be completely different from the tuple in the WHERE clause. The tuples in the slicer will only affect the cell values in the intersection of rows and columns, not what are on the column or row axes.
If you need to display sales and year 2008 on the query axes, you would need to move them to the query axes, and not in the WHERE clause.
This query has moved the sales to the columns axis, and the year 2008 to the rows axis. They are both cross joined to the original hierarchies on the two query axes:
SELECT { [Measures].[Internet Sales Amount] * [Customer].[Customer Geography].[Country] } ON 0, { [Date].[Calendar Year].&[2013] * [Product].[Product Categories].[Category] } ON 1 FROM [Adventure Works]Run the query and you will get the following result. The cell values are the same as before, but now we have the year 2013 on the rows axis, and the Internet Sales Amount on the columns axis:
Optimizing MDX queries using the NonEmpty() function
The NonEmpty() function is a very powerful MDX function. It is primarily used to improve query performance by reducing sets before the result is returned.
Both Customer and Date dimensions are relatively large in the Adventure Works DW 2016 database. Putting the cross product of these two dimensions on the query axis can take a long time. In this recipe, we will show how the NonEmpty() function can be used on the Customer and Date dimensions to improve the query performance.
Getting ready
Start a new query in SSMS and make sure that you are working on the Adventure Works DW 2016 database. Then write the following query and execute it:
SELECT { [Measures].[Internet Sales Amount] } ON 0, NON EMPTY Filter( { [Customer].[Customer].[Customer].MEMBERS } * { [Date].[Date].[Date].MEMBERS }, [Measures].[Internet Sales Amount] > 1000 ) ON 1 FROM [Adventure Works]The query shows the sales per customer and dates of their purchases, and isolates only those combinations where the purchase was over 1,000 USD.
On a typical server, it will take more than a minute before the query will return the results.
Now let us see how to improve the execution time by using the NonEmpty() function.
How to do it...
Follow these steps to improve the query performance by adding the NonEmpty() function:
How it works...
Both the Customer and Date dimensions are medium-sized dimensions. The cross product of these two dimensions contains several million combinations. We know that, typically, the cube space is sparse; therefore, many of these combinations are indeed empty. The Filter() operation is not optimized to work in block mode, which means a lot of calculations will have to be performed by the engine to evaluate the set on rows, whether the combinations are empty or not.
This is because the Filter() function needs to iterate over the complete set of data in every cell in order to isolate a single cell. For this reason, the Filter() function can be slow when operating on large dimensions or cross–join result of even medium-sized dimensions.
Tip
The Filter() operation is not optimized to work in block mode. It filters a specified set based on a search condition by iterating through each tuple in the specified set. It's a cell-by-cell operation and can be very slow when operating on large dimensions. For a good explanation of the block mode versus cell-by-cell mode, please see The pluses and minuses of named sets section of Chapter 5, Navigation.
Fortunately, the NonEmpty() function exists. This function can be used to reduce any set, especially multidimensional sets that are the result of a crossjoin operation.
The NonEmpty() function removes the empty combinations of the two sets before the engine starts to evaluate the sets on rows. A reduced set has fewer cells to be calculated, and therefore the query runs much faster.
There's more...
Regardless of the benefits that were shown in this recipe, the NonEmpty() function should be used with caution. Here are some good practices regarding the NonEmpty() function:
