Exploring MDX (Multi Dimensional Expression) in SQL Server Analysis Services

Introduction to MDX

Multi Dimensional eXpression (MDX) is a language created to allow the users to work with multi dimensional data in Microsoft SQL Server Analysis Services. It works as a query language with OLAP (Online Analytical Processing) cubes. We can also use it to create calculated members or use the various functions provided which can be used to query data.

Differences in SQL and MDX

If you are a little familiar with SQL (Structured Query Language), you may feel there is some similarity with MDX in some aspects. The SELECT clause, FROM clause and WHILE clause are similar with SQL and MDX. MDX provides various ways with which a cube can be queried - it provides different functions.

With MDX we can even create, modify and delete cubes if required.

MDX infrastructure

MDX works with multi dimensional data where we mostly have more than 2 dimensions. A dimension is a collection of related objects. These objects are called as attributes which provide information about fact data. For example when we consider attributes for a customer dimension they can be customer name, type of customer (low, ordinary or priority), location for customer etc. If we need detailed information like – ‘in a specific month all the transactions for a particular customer’ ,we need the details coming from the fact tables. Cubes contain different dimensions on which users base the queries. For example the dimension can be the type of customer, location of customer or time period.

How MDX queries data

To identify data, MDX uses reference system. This reference system is based on tuples. A tuple can identify a cell uniquely. Like we reference a cell in a spreadsheet by giving the column followed by the row number, we can also reference the tuple. The similarity ends here as there can be multiple dimensions in a cube as against to only rows and columns in a spreadsheet. A tuple can also be referenced by considering a default member. This default member is [All] member for every hierarchy in a cube.

We will start by creating a cube on adventureworksDW database.

1. Create Analysis Services project and name it MDXDemo

2. Add a new data source in it, which directs to Adventure Works DW database namely ‘AdventureWorksDW’

3. Add a new Data Source View (named Adventure Works DW) which consists of tables DimProduct, DimProducSubCategory, DimProducCaetgory, DimCustomer and FactInternetSales. Change friendly names for tables to as shown in figure:

4. Create a cube with default settings but selective measures as Order Quantity, Unit Price, Sales Amount and Internet Sales Count. Give cube name as DemoCube.

5. Go to the Properties of the project (MDXDemo) > select Deployment tab and enter server name. (if you are working with default instance, you can keep the name provided by default i.e. localhost)

6. After successful deployment, open SSMS and connect to Analysis Services to work with MDX queries.

7. Create a new query, connect to MDXDemo database

Enter following MDX query:

SELECT FROM DemoCube

In this case default member for Order Quantity is displayed as 60938

Enter the query as

SELECT [Measures].[Order Quantity] ON 0
FROM DemoCube

will also give the same result. (0 can also be replaced by COLUMNS) which is the axis.

8. Let us add one more measure as follows:

In this case we see two measures Order Quantity and Sales Amount on Columns

Let us change the default measure. Go to Cube Structure tab in MDXDemo project in BIDS (Business Intelligence Development Studio). Right click on Sales Amount and click Move Up till it becomes first measure. Redeploy the cube and again enter the query

SELECT FROM DemoCube

Now the value shown is different. Let us format this to show only 2 digits after decimal. Enter the FormatString as #,###,##.## and see the difference now.

The default measure can also be changed by MDX as follows

ALTER CUBE DemoCube
UPDATE DIMENSION
Measures, DEFAULT_MEMBER=[Measures].[Order Quantity]

In this case we are again changing the default measure back to Order Quantity

9. Go to DSV (Data Source View), right click on Customers and add a named calculation with name as FullName and expression as FirstName + ‘ ‘ + LastName. Double click on Customers dimensions and specify FullName as NameColumn (so as to display name and not number).

10. Specify similarly EnglishProductName for Product key, EnglishProductCategoryName for product category key and EnglishProductSubcategoryName for product subcategory key in Products dimension.

11. Let us give query for a specific product category Bikes as follows

SELECT [Product].[Product Category Key].[Bikes]
ON COLUMNS
FROM DemoCube
OR
SELECT [Product].[Product Category Key].&[1]
ON COLUMNS
FROM DemoCube

12. Let us add rows now (add another axis)

SELECT [Product].[Product Category Key].&[1]
ON COLUMNS,
Customer].[Customer Key].MEMBERS ON ROWS
FROM DemoCube

With this we get Sales Amount for Bikes for all different customers

This can also be achieved by using ordinals as 0 and 1 instead or COLUMNS and ROWS

13.

SELECT NON EMPTY [Product].[Product Category Key].MEMBERS
ON COLUMNS,
[Customer].[Customer Key].MEMBERS ON ROWS
FROM DemoCube

to get all categories (we added NON EMPTY to get rid of null values)
The result is as follows:

14. Try following query to see Order Quantity for various categories

SELECT [Measures].[Order Quantity]
ON COLUMNS,
[Product].[Product Category Key].MEMBERS ON ROWS
FROM DemoCube

15. Let us see how to add a calculated member

WITH MEMBER Measures.[Calculated Member] AS
([Measures].[Order Quantity] * [Measures].[Unit Price])
SELECT NON EMPTY {Measures.[Calculated Member],
[Measures].[Sales Amount]} ON COLUMNS,
NON EMPTY [Product].[Product Category Key].MEMBERS on ROWS
FROM DemoCube

First we calculate the member and use it with select to view the result

16. If we replace Members after Produt Category Key with CHILDREN we get the result as follows

SELECT NON EMPTY {Measures.[Calculated Member],
[Measures].[Sales Amount]} ON COLUMNS,
NON EMPTY [Product].[Product Category Key].CHILDREN on ROWS
FROM DemoCube

We get only the (non empty) children for categories, all categories are not displayed.

17. Let us see how to display results in order

For this we will first create of products as follows : Within category, Product sub category and within it product name.

Select Product Dimension, drag and drop Product Category key on hierarchies, drag sub category within it and product key within it, name this hierarchy as Product-Category. Deploy the changes to analysis server.

Let us give query without order

select NON EMPTY
{[Measures].[Order Quantity],[Measures].[Sales Amount]} ON 0,
NON EMPTY DESCENDANTS
([Product].[Product-Category].[Product Subcategory Key].[gloves],
,after)
on 1 from democube

The result looks as follows

With descendants function we can use SELF_AND_AFTER clause instead of AFTER clause to get the current descendants member including the specified member
Now let us give query with Order function:

select NON EMPTY
{[Measures].[Order Quantity],[Measures].[Sales Amount]} ON 0,
NON EMPTY ORDER(
DESCENDANTS
([Product].[Product-Category].[Product Subcategory Key].[gloves],
,after),
[Measures].[Sales Amount]
,asc) on 1
from democube

The result will be orderd on Sales Amout as follows

18. Let us add one more sub category and increase a bit of complexity

select NON EMPTY
{[Measures].[Order Quantity],[Measures].[Sales Amount]}
ON 0,
NON EMPTY ORDER(
{DESCENDANTS
([Product].[Product-Category].[Product Subcategory Key].[gloves],
,after),
DESCENDANTS
([Product].[Product-Category].[Product Subcategory Key].[vests],
,after)},
[Measures].[Sales Amount]
,asc) on 1
from democube