Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Article

Reading OLAP Cube using MDX: Part 1

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
29 Jul 2014CPOL5 min read 24.1K   802   8  
This article explains the step by step process to learn MDX query. It will help user to read data from OLAP cube.

Sample Image - maximum width is 600 pixels

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.

Image 2

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

Image 3

Expand cube to view all available measures and dimensions.

Image 4

Start writing first MDX query.

SQL
SELECT FROM
[ADVENTURE WORKS]

Image 5

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.

SQL
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

SQL
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

SQL
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

SQL
--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

SQL
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

SQL
 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

SQL
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

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

SQL
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

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

SQL
--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

SQL
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

SQL
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

SQL
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

SQL
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

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

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

Show top 5 rows of product subcategory.

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

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

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

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

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

History

  • 28th July, 2014: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Developer Microsoft
India India
I am Ajit Kumar Thakur. I am Consultant in Microsoft Global Delivery India. I work on Database (SQL Server), BI (SSAS, SSRS,SSIS),and Windows Powershell. I have 8 years of experience in Database and BI project development.

Comments and Discussions

 
-- There are no messages in this forum --