Reading OLAP Cube using MDX: Part 2

Reading OLAP Cube using MDX: Part 2
Download demo project - 1.2 KB

Introduction

This article contains calculation process used in MDX. I have already covered basic MDX query, Filter process in MDX query and different Slicing feature in MDX query in my previous article Reading OLAP cube using MDX query Part 1. Calculation process in MDX includes arithmetical and logical values process with existing measures values, specify range based calculation with measure value and date wise calculation with measure value.

Background

OLAP cube contains multiple different measures. These measures can be queried through different available dimensions. Dimensions available in OLAP cube are collection of attributes. OLAP cube contains definition of attribute relationship of dimension. This article will cover calculation based on attribute relationship available in dimension. If you require basics of MDX query, please do read my article Reading OLAP cube using MDX query Part 1. I personally assume, strength in MDX query makes you more comfortable to work on OLAP cube. More different way, you will write MDX query more comfortable you will be on OLAP cube. Writing complex OLAP cube helps you to prepare business solution easily.

Calculation in MDX Query

After writing basic MDX query and getting familiar with syntax of MDX, we can start for complex MDX query. Till now, we have used existing measures and dimensions of OLAP cube in MDX query. We may require some time calculated measure or calculated dimension for our business process. This can be achieved through With Member clause in MDX.

With Member clause allow to create scope member in MDX

Hide   Copy Code

With member [Measures].[My Member] as "Datawarehouse Consultant"
Select 
{
[measures].[My Member] 
} ON Columns
From [Adventure works]

The above MDX query will create scope member [My Member] in current scope.

Create alias of existing member using With Member

Hide   Copy Code

With 
Member [measures].[customer sales] as [measures].[internet sales amount]
Member [measures].[retailer sales] as [measures].[reseller sales amount]
Select 
{
[measures].[customer sales],
[measures].[retailer sales]
} on columns,
{
[date].[calendar].[calendar year]
} on rows
from [adventure works]

The above query creates alias [Customer Sales] and [Retailer Sales] for existing member. This step actually helps when required member is available in multiple level of measure group. Alias helps to access this long name with some small name.

Calculation using With Member

Hide   Copy Code

With 
Member [measures].[customer sales] as [measures].[internet sales amount]
Member [measures].[retailer sales] as [measures].[reseller sales amount]
Member [measures].[Total Sales] as [measures].[internet sales amount]+ [measures].[reseller sales amount]
Select 
{
[measures].[customer sales],
[measures].[retailer sales],
[measures].[total sales]
} on columns,
{
[date].[calendar].[calendar year]
} on rows
from [adventure works]

In the above MDX query example, [Total sales] is a new member calculated using With Member clause.

Formatting of existing member using With Member

Hide   Copy Code

With 
Member [measures].[customer sales] as [measures].[internet sales amount],format_string="#,###.00€"
Member [measures].[retailer sales] as [measures].[reseller sales amount],format_string="#,###.00€"
Member [measures].[Total Sales] as [measures].[internet sales amount]+ _
    [measures].[reseller sales amount],format_string="#,###.00€"
Select 
{
[measures].[customer sales],
[measures].[retailer sales],
[measures].[total sales]
} on columns,
{
[date].[calendar].[calendar year]
} on rows
from [adventure works]

In the above example, all sales figure will display in € format.

Named set create using With Set clause

Long MDX expression can alias with Set name using With Set clause.

Hide   Copy Code

With 
Set [Not 2008] as [Date].[Calendar].[Calendar Year].[CY 2001] : [Date].[Calendar].[Calendar Year].[CY 2007]
Member [measures].[customer sales] as [measures].[internet sales amount],format_string="#,###.00€"
Member [measures].[retailer sales] as [measures].[reseller sales amount],format_string="#,###.00€"
Member [measures].[Total Sales] as [measures].[internet sales amount]+ _
    [measures].[reseller sales amount],format_string="#,###.00€"
Select 
{
[measures].[customer sales],
[measures].[retailer sales],
[measures].[total sales]
} on columns,
{
[Not 2008]
} on rows
from [adventure works]

In the above example, I have create [Not 2008] as named set. This named set contains all calendar years from [CY 2001] to [CY 2007].

Create Member in MDX to create member or set for entire session

Hide   Copy Code

Create 
Member [adventure works].[measures].[customer sales] as [measures].[internet sales amount],format_string="#,###.00€"
Member [adventure works].[measures].[retailer sales] as [measures].[reseller sales amount],format_string="#,###.00€"
Member [adventure works].[measures].[Total Sales] as [measures].[internet sales amount]+ _
[measures].[reseller sales amount],format_string="#,###.00€"

In the above example, I have created three members for the entire session. Now, we can use these members in the entire session.
Note: We must require to provide cube name before specifying member name in Create Member.

Hide   Copy Code

Select 
{
[measures].[customer sales],
[measures].[retailer sales],
[measures].[total sales]
} on columns,
{
[Date].[Calendar].[Calendar Year]
} on rows
from [adventure works]

In the above example, I have consumed all new members created in the previous example through Create Member clause.

Drop member created using Create Member

Hide   Copy Code

drop member [adventure works].[measures].[customer sales]
go
drop member [adventure works].[measures].[retailer sales]
go
drop member [adventure works].[measures].[Total Sales]

In the above example, I have dropped all members created using Create Member clause.

Working with time dimension in MDX query

Hide   Copy Code

Select
{
[Date].[Fiscal].[Fiscal Year].[FY 2007]
} ON Columns
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

The above query will display Sales figure of financial year 2007.

Previous and Next member access in MDX

Hide   Copy Code

Select
{
[Date].[Fiscal].[Fiscal Year].[FY 2007].Prevmember,
[Date].[Fiscal].[Fiscal Year].[FY 2007],
[Date].[Fiscal].[Fiscal Year].[FY 2007].Nextmember
} ON Columns
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

The above example is using PrevMember and NextMember property to access previous and next member of any dimension.

ParallelPeriod function in MDX also returns prev/next member based on given parameter.

Hide   Copy Code

Select
{
ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].[Fiscal Year].[FY 2007])
} ON Columns
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

The above example returns previous year member of [FY 2007] because parameter value given to ParallelPeriod function is 1.

OpeningPeriod function and ClosingPeriod function.

Hide   Copy Code

Select
{
OpeningPeriod([Date].[Fiscal].[Fiscal Year]),
ClosingPeriod([Date].[Fiscal].[Fiscal Year])
} ON Columns
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

Openingperiod function returns first member of given dimension and Closingperiod function returns last member of given dimension.

Specify range in member using : (colon) operator

Hide   Copy Code

Select
{
OpeningPeriod([Date].[Fiscal].[Fiscal Year]):
ClosingPeriod([Date].[Fiscal].[Fiscal Year])
} ON Columns,
{
[Product].[Category].[Category]
}ON rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

The above example is using : operator to specify beginning member to end member of dimension. I have used Openingperiod function for first member and Closingperiod function for last member.

Starting Range in MDX can be specify using NULL value

Hide   Copy Code

Select
{
NULL:
ClosingPeriod([Date].[Fiscal].[Fiscal Year])
} ON Columns,
{
[Product].[Category].[Category]
}ON rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

In the above example, initial range is specified using value NULL.

PeriodsToDate function to specify range in member

Hide   Copy Code

--display financial year wise sales
Select
{
PeriodsToDate([Date].[Fiscal].[(All)],[Date].[Fiscal].[Fiscal Year].[FY 2008])
} ON Columns,
{
[Product].[Category].[Category]
}ON rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]
--display month wise sales of given financial year
Select
{
PeriodsToDate([Date].[Fiscal].[Fiscal Year],[Date].[Fiscal].[Month].[DECEMBER 2006])
} ON Columns,
{
[Product].[Category].[Category]
}ON rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

In the first example, PeriodtoDate function displays sales value from first available financial year to financial year 2008. While in second example when we have provided financial month in range, it will show sales of first financial month of year upto given financial month of year.

Specify range in MDX using LastPeriod function

Hide   Copy Code

--display previous 3 member
Select
{
LastPeriods(3,[Date].[Fiscal].[Fiscal Year].[FY 2008])
} ON Columns,
{
[Product].[Category].[Category]
}ON rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]
--display next 3 member
Select
{
LastPeriods(-3,[Date].[Fiscal].[Fiscal Year].[FY 2008])
} ON Columns,
{
[Product].[Category].[Category]
}ON rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

The above example displays that positive value in LastPeriods function shows last member and negative parameter value in LastPeriods function shows next member in given dimension.

Specify date range in dimension using YTD,QTD and MTD function. YTD covers Year till date members of dimension, QTD covers Quarter till date members and MTD covers Month till date members of given dimension.

Hide   Shrink    Copy Code

--YTD for Year till date
Select
{
YTD([Date].[Calendar].[Calendar Year].[CY 2008])
} ON Columns,
{
[Product].[Category].[Category]
}ON rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]
--QTD for quarter till date
Select
{
QTD([Date].[Calendar].[Month].[DECEMBER 2006])
} ON Columns,
{
[Product].[Category].[Category]
}ON rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]
--MTD for month till date
Select
{
MTD([Date].[Calendar].[Date].[July 2,2006])
} ON Columns,
{
[Product].[Category].[Category]
}ON rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

The above example displays date range specification using YTD, QTD and MTD function.

Points of Interest

This article is very interesting in learning of MDX query. Once the basics of MDX are known, this article will help you to extend your knowledge in calculation using MDX. My next article will cover MDX advance calculation process, decision making in MDX and KPI defining using MDX.

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.