OLAP cube reading through MDX : part 1

[アーティクル]
04/04/2015

Introduction

This article explain 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 minining structure. Each cube in cube list contains Measure Groups. these measure groups consist of different measures on which multiple dimension can be apply. I am assuming you have already available of OLAP cube in SSAS engine. if it is not available then first download it from codeplex and deploy the cube. I have already cover OLAP Cube deployment steps in my previous article step by step SSAS cube designing.

Background

OLTP database can be query 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 function in details. MDX allows to specify multiple dimension 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

Connet 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 measure and dimension

Start writing first MDX query

 SELECT FROM 
[ADVENTURE WORKS]

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

 SELECT SUM(S.SalesAmount) FROM FactResellerSales S

Now it clearly display that deafult measure is ResellerSales.

FactResellerSales table is available in AdventureWorksDW2008R2 database. this database can be download from codeplex site.

MDX query to read from specific measures

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

above MDX query will read cube data from [RESELLER SALES AMOUNT] measure.

where clause specify measure/dimeansion area of cube to which Set is to be slice.

Slice Dice and Filter in MDX query

specify measure value in column

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

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

specify measure in where clause and dimension in column

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

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

specify non measure member in where clause

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

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.

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

above MDX query has multiple member in where Slicer. it reads internet sales of specific country Canada.

two non measure dimension in same slicer

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

above MDX query specified two non measure in slicer. it display internet sales amount of two country Canada and Australia.

Except and Minus operation in MDX

 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 below query for except operation

 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]
)
}
)

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

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

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 apply through Filter function

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

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

 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

 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]

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

Comparing between two measures value in MDX

 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]

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

 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]

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

Non Empty to slice NULL rows from MDX result set.

 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

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

Show top 5 rows of product subcategory

 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.

 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.

 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.

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

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.

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

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

Points of Interest

This article is very interesting in learning of MDX query. it explain different feature available in MDX query to read data from OLAP Cube. This is first part article for MDX query. next part article will cover calculation in MDX query.

History

No updates available

Comments

Anonymous
April 04, 2015
Introduction
This article contains calculation process used in MDX. I have already covered basic MDX

次の方法で共有