mdx 2 tier ranking
DESCRIPTION
Ranking within ranking built step wise. An excellent introduction to basic MDX syntax and usage, but also showing the power of advanced MDX.TRANSCRIPT
MDX 2 Tier Ranking
Basics to Advanced Functions
Table of Contents
MDX 2 Tier RankingIntroduction 3 Getting the top 5 months 12
Specification 4 Adding the set and rank 13
Core of Query 5 Adding the top 5 cities 14
Base Query and Results 6 Ranking the cities 15
Adding Prior Period Sales 7 Pulling it all together 16
Improving appearance 8 The final results set 17
Adding Percentage Change 9 Conclusion 18
Immediate IF (IIF) 10 The complete script 19
With 3 basic values 11
2
Introduction
The steps used in this presentation provide an explanation and demonstration of basic MDX functions and syntax along with some advanced functions that demonstrate the power of MDX. The process will attempt to demonstrate best practices.
The query built will:
Compare and Rank the % increase over the prior month for:- the top 5 months - and top 5 cities within those months
The result must showInternet Sales Amount, Sales Amount for the prior month, the percentage increase
the Month Rank and the City Rank both from highest to lowestBy Month Name and City Name
3
Start with the specification
As a first step the specification is copied into the Query window. . .
. . . And edited slightly and turned into a comment by selecting and . It then looks like:
Notice that it picks up the intelli-sence marking
The double slash // marks this as a comment and turns the text green.
4
Core of query
The query revolves around Internet Sales Amount and Months, so first these two components are brought in as a baseline.
This provides a list of the months in orderand the Internet sales for that month.
5
Exploring base query and result
6
The next step is to enable a presentation one months internet sales and the previous month’s sales on the same line.
The resulting output should resemble the original output adjacent a copy of that output slipped down by one row.
An additional column which shows the internet sales for the previous month. This is added as a member in the first opening declaration prior to the primary query:
The new member, [Internet Sales Amount Prior PD] is added to the primary query.
Adding the Prior Sales Period
7
The query now looks like this,
and the output is showing the October 2001 sales next to November 2001.
The output needs to be formatted in dollars.
This is accomplished with
The designation for the PrevMemberpoints to the sales for the prior month.
Improving appearance
8
This is added as part of the member declaration.
The output is looks better.
Next we will add another member to bring in percentage as a calculated member using
The calculation syntax is based on ordinary mathematical notation.
For this the format is set to percent.
Adding the Percentage Change
9
Notice the WITH is only listed before the vary first declared item.
A default setting to handle a null denumerator is needed. A special IF function is used.
Immediate IF (IIF)
This is the immediate if function which has 3 parts: A parameter that is checked. A setting used when that condition is true A setting used when the condition is false
The basic syntax is
Parameter 1
Parameter 2
Parameter 3
10
The 3 parts used are:1. If the prior period sales amount is not null2. Then calculate the % change.3. Else set the change to 0 (We don’t want “start ups.”)
With the 3 basic values
11
At this point the query returns the three basic values.
The next step is to determine the top 5 months and to rank them in order.
Getting the top 5 months
12
The first step is to retrieve the top 5 month based on the % change. Conveniently, MDX does this for us using a TOPCOUNT Function which has three parameters.
So the set is declared as
It needs to be declared as a named set before the query
These are to be ranked, highest to lowest.
The Rank is declared as a member
Adding top 5 cities
14
The next step is to identify the top 5 cities within each of those months. In essence, 5 sets of cities, one for each of the months are needed. The GENERATE function is used to find these:
These will be
Like the top 5 Months these must be declared as a named set
The cities still need to be ranked.
Inner Parameter 1 must be CurrentMember.Inner Parameter 2 must be Children.
Ranking the Cities
15
The Rank for the cities is more complex because the ranking needs to be within a specific month and we only want to include groupings in those top five months.
Conclusion
Building the most complex query typically starts with a relatively simple query and works toward the necessary complexity. I hope you have found this presentation useful or interesting.
Please feel free to direct questions or comments to me at my e-mail address.
Watch my linked in page for other T-SQL and MDX Query examples.
18
