OLAP cube reading through MDX :part 2
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 OLAP cube reading through MDX : part 1. Calculation process in MDX includes artihmetical 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 mesaures. these measures can be query through different available dimension. 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 basic of MDX query, Please do read my article OLAP cube reading through MDX : 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 comfort 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 familiar with syntax of MDX we can start for complex MDX query. till now we have used existing measures and dimension of OLAP cube in MDX query. we may require some time calculated measure or calculated dimension for our business process. this can be achive through With Member clause in MDX
With Member clause allow to create scope member in MDX
With member [Measures].[My Member] as "Datawarehouse Consultant"
Select
{
[measures].[My Member]
} ON Columns
From [Adventure works]
above MDX query will create scope member [My Member] in current scope.
Create alias of existing member using With Member
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]
above query creates alias [Customer Sales] and [Retailer Sales] for existing member. This steps actually help 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
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 above MDX query example, [Total sales] is a new member calculated using With Member clause.
Formatting of existing member using With Member
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 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.
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 above example, I have create [Not 2008] as named set. this named set contains all clendar year from [CY 2001] to [CY 2007]
Create Member in MDX to create member or set for entire session
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 above example, I have created three member for entire session. now we can use these member in entire session. Note: we must require to provide cube name before specifiying member name in Create Member.
Select
{
[measures].[customer sales],
[measures].[retailer sales],
[measures].[total sales]
} on columns,
{
[Date].[Calendar].[Calendar Year]
} on rows
from [adventure works]
In above example, I have consumed all new member created in previous exaple through Create Member clause.
Drop member created using Create Member
drop member [adventure works].[measures].[customer sales]
go
drop member [adventure works].[measures].[retailer sales]
go
drop member [adventure works].[measures].[Total Sales]
In above example, I have dropped all member created using Create Member clause.
Working with time dimension in MDX query
Select
{
[Date].[Fiscal].[Fiscal Year].[FY 2007]
} ON Columns
From [Adventure Works]
Where [Measures].[Internet Sales Amount]
above query will display Sales figure of financial year 2007.
Previous and Next member access in MDX
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]
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.
Select
{
ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].[Fiscal Year].[FY 2007])
} ON Columns
From [Adventure Works]
Where [Measures].[Internet Sales Amount]
above example return previuos year member of [FY 2007]. because parameter value given to ParallelPeriod function is 1.
OpeningPeriod function and ClosingPeriod function.
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
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]
above example is using : operator to specify begining 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
Select
{
NULL:
ClosingPeriod([Date].[Fiscal].[Fiscal Year])
} ON Columns,
{
[Product].[Category].[Category]
}ON rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]
in above example, initial range is specified using value NULL.
PeriodsToDate function to specify range in member
--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 first example, PeriodtoDate function display 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
--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]
above example display 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.
--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]
above example display date range specification using YTD,QTD and MTD function.
Points of Interest
This article is very interesting in learning of MDX query. once basic of MDX is 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.
History
No updates available