Li / Piasevoli MDX with Microsoft SQL Server 2016 Analysis Services Cookbook

Over 70 practical recipes to analyze multi-dimensional data in SQL Server 2016 Analysis Services cubes
3. Auflage 2025
ISBN: 978-1-78646-788-1
Verlag: De Gruyter
Format: EPUB
Kopierschutz: Adobe DRM (»Systemvoraussetzungen)

E-Book, Englisch, 586 Seiten

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.

Autoren/Hrsg.

Li, Sherry

Piasevoli, Tomislav

Fachgebiete

• Mathematik | Informatik EDV | Informatik EDV & Informatik Allgemein

Leseproben

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 and 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 multiple hierarchies into one query axis. In MDX and other query language terms, we call it .

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, and . We will start by putting only one hierarchy on COLUMNS, and one on ROWS. Then we will use the  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 and 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 so 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 axis, and 1 for rows, which is the 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 measure, you will see the fully qualified expression, 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:

1. Start SQL Server Management Studio (SSMS) and connect to your SQL Server Analysis Services (SSAS) 2016 instance ( or ).
2. 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:

1. Put your cursor on , and drag and drop it onto .
2. To get the proper expression for the sales territory, put your cursor over the under the Sales Territory | Sales Territory Country. Again, this is a long expression. Drag-and-drop it onto .
3. 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 clause.

               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 on the , and all members of on the . We have fully qualified the measure with the special dimension , and the sales territory members with dimension and hierarchy .

You might have expected an aggregate function such as 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 to 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 and 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 , and two different hierarchies; and 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:...

Über Autor(innen)

Li Sherry :

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).