Reading OLAP Cube using MDX: Part 1

Reading OLAP Cube using MDX: Part 1
Download demo project - 883 B

• Download source - 662.1 KB

Introduction

This article explains learning of MDX query in detail. When OLAP cube is designed and deployed on SSAS engine, we use MDX to read data. OLAP cube contains data in Cube database. This cube database is different from OLTP relational database. Cube database contains Datasources, Datasource views, cubes, dimension and mining structure. Each cube in cube list contains Measure Groups. these measure groups consist of different measures on which multiple dimension can be applied. I am assuming you already have available OLAP cube in SSAS engine. If it is not available, then first download it from Download source section of this article and deploy the cube. I have already covered OLAP Cube deployment steps in my previous article Designing SSAS cube. You can also download sample datawarehouse database from my previous article, Designing SSAS cube.

Background

OLTP database can be queried using T-SQL. T-SQL uses select statement to read data from table. Similarly, OLAP cube reads data from measures and dimension using MDX query. MDX has separate syntax to specify multiple dimension and measures in query. This article covers these syntax and functions in detail. MDX allows to specify multiple dimensions in single query and can generate result dataset. MDX is managed query expression which covers different approach to apply dimension on any measures.

Using the Code

Connect with SSAS service engine.

Open database and click on New Query. MDXquery1.mdx file will be available to write MDX query.

Expand cube to view all available measures and dimensions.

Start writing first MDX query.

Hide   Copy Code

SELECT FROM
[ADVENTURE WORKS]

The above query contains Select statement reading data from OLAP cube [Adventure works]. The result value is showing $80,450,596.98 value. It is default assign measure value read by MDX. You can verify it by checking the below SQL query.

Hide   Copy Code

SELECT SUM(S.SalesAmount) FROM FactResellerSales S

Now, it clearly displays that default measure is ResellerSales.

FactResellerSales table is available in AdventureWorksDW2008R2 database. This database can be downloaded from my previous article, Designing SSAS cube.

MDX query to read from specific measures

Hide   Copy Code

SELECT FROM [ADVENTURE WORKS]
WHERE [MEASURES].[RESELLER SALES AMOUNT]

The above MDX query will read cube data from [RESELLER SALES AMOUNT] measure where clause specifies measure/dimension area of cube to which Set is to be slice.

Slice Dice and Filter in MDX query

Specify measure value in column

Hide   Copy Code

SELECT
[MEASURES].[RESELLER FREIGHT COST] ON COLUMNS
FROM [ADVENTURE WORKS]

The above query will show measure [RESELLER FREIGHT COST] data into column.

Specify measure in where clause and dimension in column

Hide   Copy Code

--SPECIFYING MEASURES IN WHERE CLAUSE

--WHERE CLAUSE TO GET PRODUCT WISE SALE OF SPECIFIC MEASURE
Select
{
[product].[product categories].[category],
[product].[product categories]
} on columns
from [adventure works]
where
[measures].[reseller tax amount]

The above query will get product dimension wise reseller tax measure data.

Specify non measure member in where clause

Hide   Copy Code

Select
{
[product].[product categories].[category],
[product].[product categories]
} on columns
from [adventure works]
where [geography].[geography].[country].[canada]

The above query will get product wise reseller sales for specific country Canada. [geography].[geography].[country].[canada] is dimension and not a measure specified in where clause so that where clause accepts dimension as well as measures.

Specify multiple member in where Slicer

Hide   Copy Code

 Select
{
[product].[product categories].[category],
[product].[product categories]
} on columns
from [adventure works]
where
(
[geography].[geography].[country].[canada],
[measures].[internet sales amount]
) 

The above MDX query has multiple members in where Slicer. It reads internet sales of specific country Canada.

Two non measure dimension in same slicer

Hide   Copy Code

Select
{
[product].[product categories].[category],
[product].[product categories]
} on columns
from [adventure works]
where
(
{
[customer].[customer geography].[country].[canada],
[customer].[customer geography].[country].[australia]
},
[measures].[internet sales amount]
)

The above MDX query specified two non measures in slicer. It display internet sales amount of two countries, Canada and Australia.

Except and Minus operation in MDX

Hide   Copy Code

Select
{
[product].[product categories].[category],
[product].[product categories]
}
on columns
from [adventure works]
where
(
{
[Sales Territory].[Sales Territory].[country]
-
[Sales Territory].[Sales Territory].[country].[United Kingdom]
},
[measures].[internet sales amount]
)

You can also use the below query for except operation.

Hide   Copy Code

Select
{
[product].[product categories].[category],
[product].[product categories]
}
on columns
from [adventure works]
where
(
[measures].[internet sales amount],
{
Except(
[Sales Territory].[Sales Territory].[country],
[Sales Territory].[Sales Territory].[country].[United Kingdom]
)
}
)

The above query uses Except and - operator to slice from query. It will show internet sales amount of all sales territory except United Kingdom.

Show dimension in rows and measures in column in MDX query

Hide   Copy Code

Select
{
[measures].[internet sales amount]
} on columns,
{
[product].[product categories].[category]
} on rows
from
[adventure works]

The above query will display product wise internet sales amount. Product category will be available in rows and internet sales will be available in columns.

Filter in MDX query is applied through Filter function.

Hide   Copy Code

--apply filter using filter function
Select
{
[measures].[internet sales amount]
} on columns,
{
filter([product].[product categories].[category],
[measures].[internet sales amount] >0)
} on rows
from
[adventure works]

The above query will display product wise internet sales amount having sales amount greater than 0.

Row level filter can be apply using Having clause in MDX

Hide   Copy Code

Select
{
[measures].[internet sales amount]
} on columns,
{
[product].[product categories].[category]
}
having [measures].[internet sales amount] > 0
on rows
from
[adventure works]

Boolean operator used in MDX query to apply row level filter

Hide   Copy Code

Select
{
[measures].[internet sales amount]
} on columns,
{
filter([product].[product categories].[category],
[measures].[internet sales amount] >500000 AND [measures].[internet sales amount] <750000)
} on rows
from
[adventure works]

The above query will show internet sales of all products which have sales amount greater than 500000 and less than 750000. The condition is applied using And operator in Filter function.

Comparing between two measures value in MDX

Hide   Copy Code

Select
{
[measures].[internet sales amount]
} on columns,
{
filter([product].[product categories].[category],
[measures].[internet sales amount] > [measures].[reseller sales amount])
} on rows
from
[adventure works]

The above query will compare two measures and show all rows where measure value of internet sales is greater than reseller sales.

Using of IS operator in MDX query

Hide   Copy Code

Select
{
[measures].[internet sales amount],
[measures].[reseller sales amount]
} on columns,
{
filter([product].[product categories].[category],
([product].[product categories].CURRENTMEMBER IS
[product].[product categories].[category].[accessories])
OR
([product].[product categories].CURRENTMEMBER IS
[product].[product categories].[category].[Bikes])
)
} on rows
from
[adventure works]

The above query will display sales detail of product category Bikes and Accessories only. The condition in MDX is specified using IS operator.

Non Empty to slice NULL rows from MDX result set

Hide   Copy Code

Select
{
[measures].[internet sales amount],
[measures].[reseller sales amount]
} on columns,
non empty filter([product].[product categories].[category],
[measures].[internet sales amount] >0
)
on rows
from [adventure works]

Tops and Bottoms row accessing using MDX

Show internet sales amount of all product subcategory.

Hide   Copy Code

Select
[measures].[internet sales amount] on columns,
[product].[product categories].[subcategory] on rows
from [adventure works]

Show top 5 rows of product subcategory.

Hide   Copy Code

Select
[measures].[internet sales amount] on columns,
topcount([product].[product categories].[subcategory],5) on rows
from [adventure works]

Show internet sales of top 5 product subcategories using Topcount in MDX.

Hide   Copy Code

Select
[measures].[internet sales amount] on columns,
topcount([product].[product categories].[subcategory],5,[measures].[internet sales amount]) on rows
from [adventure works]

Show internet sales of bottom 10 product subcategories excluding NULL or 0 values using Bottomcount in MDX.

Hide   Copy Code

Select
{[measures].[internet sales amount] ,
[measures].[reseller sales amount]}
on columns,
bottomcount(
filter([product].[product categories].[subcategory],[measures].[internet sales amount]>0)
,10,
[measures].[internet sales amount]) on rows
from [adventure works]

Topsum in MDX query to show all top rows actually formed sum of given value.

Hide   Copy Code

Select
[measures].[internet sales amount] on columns,
topsum([product].[product categories].[subcategory],25000000,[measures].[internet sales amount]) on rows
from [adventure works] 

The above query shows all product subcategories internet sales which is forming sum of 25000000 amount.

Bottomsum in MDX query to show least rows require to form sum of specific value.

Hide   Copy Code

Select
[measures].[internet sales amount] on columns,
bottomsum([product].[product categories].[subcategory],1000000,[measures].[internet sales amount]) on rows
from [adventure works]

The above query shows all product subcategories internet sales which is forming sum of 1000000 amount.

Points of Interest

This article is very interesting for learning MDX query. It explains different features available in MDX query to read data from OLAP Cube. This is the first part of the article for MDX query. The next part article will cover calculation in MDX query.