MDX with Microsoft SQL Server 2016 Analysis Services Cookbook
Description
Key Features
[*]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
Book DescriptionIf 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.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
Who this book is forThis 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.
More details
Other editions
Additional editions
Persons
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.Piasevoli Tomislav :
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).
Content
Elementary MDX Techniques
Working with Sets
Working with Time
Concise Reporting
Navigation
MDX for Reporting
Business Analyses
When MDX is Not Enough
Metadata-driven Calculations
On the Edge
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:
- We must always explicitly specify a display axis for all elements in the SELECT list. We can use aliases for the first five axes: COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS. We are also allowed to use integers, 0, 1, 2, 3, and so on but we are not allowed to skip axes. For example, the first axis must be COLUMNS (or 0). ROWS (or 1) cannot be specified unless COLUMNS (or 0) has been specified first.
- Since we only have two display axes to show our data, we must be able to combine multiple hierarchies into one query axis. In MDX and other query language terms, we call it crossjoin.
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:
- The name of the cube
- The correct expression for the
Internet Sales Amountso we can put it on the columns - The correct expression of the sales territory so we can put it on the rows
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:
- Start SQL Server Management Studio (SSMS) and connect to your SQL Server Analysis Services (SSAS) 2016 instance (
localhostorservername\instancename). - Click on the target database Adventure Works DW 2016, and then right-click on the New Query button.
Follow these steps to save the time spent for typing the long expressions:
- Put your cursor on
[Measures] [Internet Sales Amount], and drag and drop it ontoAXIS(0). - To get the proper expression for the sales territory, put your cursor over the
[Sales Territory Country]under the Sales Territory | Sales Territory Country. Again, this is a long expression. Drag-and-drop it ontoAXIS(1). - For the name of the cube, the drag-and-drop should work too. Just point your cursor to the cube name, and drag-and-drop it in your
FROMclause.
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:
To return the cross-product of two sets, we can use either of the following two syntaxes:
Standard syntax:...