In the above diagram, we have the traditional Co-ordinate system with the positive X and Y planes indicated by straight lines, However when it comes to MDX we have to realize that barring the X and Y co-ordinates we can write queries across multiple axes as indicated by the dotted lines. Hence the term "Multidimensional Expressions".
Coming back to the MDX aspects we divide the MDX into 3 categories-->
1] CellSpace/CellSet/Cell
2] Member
3] Tuple
4] Set
If we go with the standard definitions we could define that the CellSpace/CellSet/Cell (Cell) is the value of intersection between all the axes.
A member is any of the co-ordinate intersections which defines a particular behavior along one of the axes.
Another layer of abstraction over the member is called tuple and it basically is a collection of members from different access but share the same behavior.
A collection of tuples is called a Set.
Now we can get more clarity by reading the article as mentioned below (It is a quick read):
http://msdn.microsoft.com/en-us/library/aa216769(v=sql.80).aspx
Once you have read this article, let us continue our foray into the world of MDX:-
General Syntax-->
With CalculatedMember as CalculatedMember formaula
Select Measure/Dimension/CalculatedMember (Cell/Member/Tuple/Set) as Columns/0/Axes(0),
Measure/Dimension/CalculatedMember (Cell/Member/Tuple/Set) as Columns/0/Axes(1)
from [CubeName] where slicerinformation
Let us take a couple of examples from our AdventureWorks 2008 R2 cube.
In the following example we are taking the Measure amount and verifying it across the different members belonging to the Account dimension:
select [Measures].[Amount] on 0,
{[Account].[Account Number].[Account Number]} on 1
from [Adventure Works]
[This is the basic MDX statement that is normally written by Analysts and Dev to verify information]WITH member x as
CASE WHEN ([Measures].[Reseller Sales Amount] > 45450)
THEN "TRUE"
ELSE "FALSE"
END
select {[Measures].[Reseller Sales Amount],x} on 0,
{[Geography].[Geography].&[United States].Children} on 1
from [Adventure Works]
select [Geography].[Geography].[City].&[Coffs Harbour]&[NSW].PARENT on 0 from [Adventure Works]
Ans: New South Wales
select [Geography].[Geography].[State-Province].&[BC]&[CA].Children on 0
from [Adventure Works]
WITH member Test as
CoalesceEmpty([Measures].[Customer Count],0)
select [Product].[Category].Members on 0,Test on 1
from [Adventure Works]
Wherevr we get a null value, it will be replaced by the 0 digit in this case.
Let us now move onto some Math functions:
Min: Will give us the minimum value in a given Set.
Example- We want to find the minimum discounted amount value for reseller sales based on the country-->
With member test as min(Geography.Geography.Country.allmembers,[Measures].[Discount Amount])
select test on 0
from [Adventure Works]
Max: Will give us the maximum value in a given Set.
Example- We want to find the maximum discounted amount value for reseller sales based on the country-->
With member test as max(Geography.Geography.Country.allmembers,[Measures].[Discount Amount])
select test on 0
from [Adventure Works]
Median: Will give us the median of a defined Set.
Going back to Math 101, the median of a defined set is not the average of a given set of numbers which will be the sum of all the numbers in a particular set divided by the count of numbers. Let us consider the set of numbers --> {12,21,34,45,51},
the average is (12+21+34+45+51)/5 = 32.6 but the median is taken by arranging the numbers in an ascending fashion and taking the number in the middle which in this case is 32.6
Example-We want to find the median of the discounted amount value for reseller sales based on the country-->
With member test as median(Geography.Geography.Country.allmembers,[Measures].[Discount Amount])
select test on 0
from [Adventure Works]
Let us now move onto some Date Related Functions. This is extremely important and is almost the fundamental key principles for business analysts to succeed when querying against Cubes.
Let us start with the highest level which is Year and keep moving down to lower levels like Quarter--> Month --> Week --> Day --> Hour
1. YTD (Year to date) This function will return the values for the specific year upto a specific date within the year.
For example this following MDX query will give us the count of the customers for the entire year:
SELECT ([Measures].[Customer Count],YTD([Date].[Calendar Year].&[2003])) on 0 from [Adventure Works]
2. QTD(Quarter To Date) This function is used to return all the values belonging to a quarter defined within the cube. For example Jan - March might be considered as the first quarter and every 3 months from March can be grouped into a quarter group.
SELECT ([Measures].[Customer Count],QTD([Date].[Calendar].[Calendar Quarter].&[2001]&[3])) ON 0 from [Adventure Works]
3. WTD(Week To Date) This function returns a set of members from the date and time dimension starting with the first day of the week up to the specified member.
In case an invalid grain is given as part of the function then the following error will be raised:
Executing the query ...
Query (1, 37) By default, a week level was expected. No such level was found in the cube.
Execution complete
4. MTD(Month to Date) This function is used to return the required aggregation from a measure based on the number of months(always the beginning of the year) till a specified date in a year
SELECT ([Measures].[Customer Count],MTD([Date].[Calendar].[Date].&[2])) on 0 from [Adventure Works]
5. We have a higher grain greater than the years which is the PeriodsToDate function which basically gives us all the information upto a particular period in time.
The periods to date function utilizes any level within the time hierarchy to find the periodic information. The general syntax for this function is PeriodsToDate(LEVEL, MEMBER).
SELECT
PeriodsToDate([Date].[Calendar].[Month], [Date].[Calendar].[Date].&[28]) on 0 FROM [Adventure Works]
6. Parallel Period is a function that is regularly used. Now parallel period is used to fetch information from a specific time range that occurred but in a different time period. The genral syntax is ParallelPeriod( [ Level_Expression [ ,Index [ , Member_Expression
] ] ] )
Ex: The following will return the values from January 2003 as it is less than 3 quarters away from October 2003.
SELECT ParallelPeriod ([Date].[Calendar].[Calendar Quarter],3,[Date].[Calendar].[Month].[October 2003])ON 0 FROM [Adventure Works]
This is where our MDX function expedition comes
to an end. The next part of the Article will deal with the storage and function engine in SSAS and how MDX queries are executed.