Ranking results from MDX queries
This post explains how you can create a ranking of data from an OLAP MDX query. This will take the results from the query, and assign a ranking to each row. i.e. 1st, 2nd, 3rd best rows etc.
The first thing to do is to decide two things.
1) What measure do you want to rank by
2) What data set are you returning
Let’s assume we want to rank all stores by sales value. The basic non-ranked MDX query would be something like this
SELECT
{[Measures].[Sales Value]} ON 0,
{[Store].[Store Name].members} ON 1
FROM
[SalesCube]
So our measure is Sales Value, and our data set (granularity) is Store Name. We now want to create an ordered set of this data, ordered by Sales Value. We do this with the ORDER() function, which takes a set, a measure and either ascending or descending as its parameters. Note that by specifying the attribute twice we remove the [All] member from the set.
WITH SET [OrderedSet] AS
ORDER([Store].[Store Name].[Store Name].MEMBERS,
[Measures].[Sales Value],
BDESC)
SELECT
{[Measures].[Sales Value]} ON 0,
{[OrderedSet]} ON 1
FROM
[SalesCube]
The next stage is to apply a ranking to this ordered set. Helpfully, MDX provides us with a Rank() function, which takes a member and a set as its parameters. All it does is locate the member within the set and return its position. Because we have ordered the set, it will give us the ranking.
WITH SET [OrderedSet] AS
ORDER([Store].[Store Name].[Store Name].MEMBERS,
[Measures].[Sales Value],
BDESC)
MEMBER [Measures].[Rank] AS
RANK([Store].[Store Name].CurrentMember,
[OrderedSet])
SELECT
{[Measures].[Rank], [Measures].[Sales Value]} ON 0,
{[OrderedSet]} ON 1
FROM
[SalesCube]
You can now easily expand this to only show you the top x records, by using the Head() function on the ordered set. In this example we’re only showing the top 10. You could also use Tail() to find the bottom x records. Other functions you can use include TopPercent(), TopSum(), BottomPercent() and BottomSum().
WITH SET [OrderedSet] AS
ORDER([Store].[Store Name].[Store Name].MEMBERS,
[Measures].[Sales Value],
BDESC)
MEMBER [Measures].[Rank] AS
RANK([Store].[Store Name].CurrentMember,
[OrderedSet])
SELECT
{[Measures].[Rank], [Measures].[Sales Value]} ON 0,
{HEAD([OrderedSet], 10)} ON 1
FROM
[SalesCube]
Tags: Analysis Services, MDX, OLAP, Ranking
May 21st, 2008 at 10:07 pm
Trackback. Link to this post was added to our site in the [SSAS Articles]/[MDX] section:
http://www.ssas-info.com/analysis-services-articles/50-mdx/878-ranking-results-from-mdx-queries
July 28th, 2008 at 7:14 pm
Dear Alex,
Write more fantastic posts as usually!!
cheers!!
Pedro
July 28th, 2008 at 7:48 pm
Hi Pedro, thanks for stopping by.
Sorry about the lack of activity here at the moment – blame my pesky customers
too much work to do!
I’ll try and get another frog-blog-top-tip up here soon!
Alex
September 26th, 2008 at 2:44 pm
I always read your blog in high spirits. Thanks
September 29th, 2008 at 8:31 am
hi,
i’m trying to find out the Top10 products on retailers
WITH SET [OrderedSet] AS
ORDER ( [Retailer].[Retailer Name].[Retailer Name].MEMBERS,
[Measures].[Sales Value Gross RRP Excluding Taxin Local Currency] ,
BDESC)
MEMBER [Measures].[Rank] AS
RANK([Retailer].[Retailer Name].CurrentMember,
[OrderedSet])
SELECT
{[Measures].[Rank], [Measures].[Sales Value Gross RRP Excluding Taxin Local Currency]} ON 0,
{HEAD([OrderedSet], 10)} ON 1
FROM
[CubeClientAdmin]
it gives the error:
Error 1 MdxScript(CubeClientAdmin) (69, 1) Parser: The syntax for ‘WITH’ is incorrect. 0 0
and i couldn’t trace out the error in ‘with’,
can you tell me what is the error in the above code?
Thanks,
Regards
Viji
October 2nd, 2008 at 10:53 am
Hi Viji
Syntactically, I can’t find anything wrong with your query. What database are you running it against? And what query tool are you using?
My example was using SQL 2005, querying using Management Studio.
Alex
October 2nd, 2008 at 2:33 pm
Hi Alex,
thanks for your reply. I’m using SQL 2005 and i queried using analysis services.
Regards,
Viji
October 2nd, 2008 at 2:36 pm
Hi Viji
What happens when you run it as a query using SQL Server Management Studio?
Alex
February 26th, 2009 at 3:53 am
what about ties? Your use of the ‘order’ method does not account for ties correctly.
February 26th, 2009 at 9:28 am
Hi Gene
Very good point. However you can get around this by nesting multiple ORDER statements.
ORDER(ORDER(xxx,xxx,xxx),xxx,xxx)
The Outer statement will be the primary order, the second one in willl be the decidor if there are duplicate primary sort values causing a tie.
Thanks for stopping by!
Alex
October 8th, 2009 at 11:17 pm
Thanks, your example of the rank function is very clear. I was having trouble with someone else’s explanation.
December 14th, 2009 at 4:49 am
Thanks a lot Alex.. even after so long time you can still see how many of us make use of this valuable piece of code. Its so simple and very clear… Thanx again.
January 23rd, 2010 at 10:26 am
I’ve been looking for that for so long! Thanks a lot for for this great explanation…a appreciate it!!