Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Archive for the ‘MDX for everyone’ Category

DAX Code Snippet: Year To Date in Charts

Posted by thomasivarssonmalmo on June 3, 2015

This short blog post tries to show the different ways to solve the same problem in DAX and in MDX.

If you look at the picture you can see two ways of presenting a year to date value. The last picture continues with the last value for all empty months. The example is from measuring database file sizes over time. The charts are made in Power BI Designer.

 

Two diagrams with accumulated values

The last chart is simple to create in DAX:

TotalYTDSize = TOTALYTD(SUM(‘DW AssesmentDatabaseFileSizes'[size]),’DW DimDateExt'[DateID])

What about the first chart? I had to do a search and found the result in the MSDN new group about DAX but not in DAX books.

TotalYTDSize2 = IF(ISBLANK(SUMX(‘DW AssesmentDatabaseFileSizes’,’DW AssesmentDatabaseFileSizes'[size])),BLANK(),TOTALYTD(SUM(‘DW AssesmentDatabaseFileSizes'[size]),’DW DimDateExt'[DateID]))

The interesting part is that you need a DAX table function, SUMX, because SUM will not work.

What about the same problem in MDX? I am using the know Adventure Works cube instead and show the result in a grid.

With Member Measures.YTDOrderQty AS
SUM(YTD([Date].[Calendar]),[Measures].[Internet Order Quantity])

Member  Measures.YTDOrderQtyAdj AS
IIF(ISEMPTY([Measures].[Internet Order Quantity]),NULL,SUM(YTD([Date].[Calendar]),[Measures].[Internet Order Quantity]))

Select {[Measures].[Internet Order Quantity],Measures.YTDOrderQty,Measures.YTDOrderQtyAdj} On 0,
Descendants([Date].[Calendar],[Date].[Calendar].[Month]) on 1
FROM [Adventure Works]

And here is the result in the grid. It is the last column that works like the first chart above.

MDX Result

Posted in DAX Time Calculations, MDX for everyone | Tagged: | Leave a Comment »

Tomislavs MDX book

Posted by thomasivarssonmalmo on October 23, 2011

Tomislav appeared on the SSAS forum around 2007, if I am not totally wrong, and he soon became an authority and seem to have impressed even Mosha. Since he worked  with the client Softpro Cube player it was obvious that this guy knew about MDX inside and out.

Now he has written a book about MDX that I can recommend. Actually a few years ago I thought that we would only have two MDX books, since it is not a block buster in its topic. I like Tomislavs book since it is focused on the practical analytical challanges and problems and show how to solve that in 480 pages. This book is obviously the main MDX point of reference for all SSAS developers. It is not a book about the SSAS engine internals and performance tuning of MDX.

You can buy it from here: https://www.amazon.co.uk/Microsoft-Server-Analysis-Services-Cookbook/dp/1849681309/ref=sr_1_3?ie=UTF8&qid=1319373673&sr=8-3

Posted in MDX for everyone | Leave a Comment »

MDX for Report Time Periods

Posted by thomasivarssonmalmo on November 11, 2010

Time period aggregates combined with measure types like actual, budget and forecast are normal day to day reporting. This MDX snippet is one way how you can build these aggregates that are not symmetric. End users would like to see actual values for a single month together with the accumulated values for the pervious months.

I do not handle this in a single step but in three like in this way:

  • Build the set for the time periods, like months
  • Build the set that combines the first set with the measure type
  • Build a calculated member that use the Aggregate()-function, the second set and the appropriate measure

I use the Adventure Works demo cube and the scenario dimension in that cube that consist of Actual, Budget and Forecast. There is only one measure that can be used for that dimension, amount in the Financial Reporting measure group.

Here is the first example that constructs two calculated members on the scenario dimension.

WITH
Set MonthJanMarch2002 As [Date].[Calendar].[Month].&[2002]&[1]:[Date].[Calendar].[Month].&[2002]&[3]
Set ActMonthJanMarch2002 As  CrossJoin(MonthJanMarch2002,{[Scenario].[Scenario].&[1]})
Set BudMonthJanMarch2002 As  CrossJoin(MonthJanMarch2002,{[Scenario].[Scenario].&[2]})
Member Scenario.Scenario.ActAmountJanMarch2002 As Aggregate(ActMonthJanMarch2002,[Measures].Currentmember)
Member Scenario.Scenario.BudAmountJanMarch2002 As Aggregate(BudMonthJanMarch2002,[Measures].CurrentMember)

Select {Scenario.Scenario.ActAmountJanMarch2002, Scenario.Scenario.BudAmountJanMarch2002} On 0,
[Measures].[Amount] on 1
From [Adventure Works];

If I have more than one measure you can add them on rows in the select part and the calculated members will work on all measures being put there. The result look like this:

First Query

In the second example I have a little more complex scenario. Lets add two scenarios in one calculated measure, actuals for January 2002 to February 2002 and budget for March 2002. This is a common scenario when you are working with forecasts.

WITH
Set MonthJanMarch2002 As [Date].[Calendar].[Month].&[2002]&[1]:[Date].[Calendar].[Month].&[2002]&[3]
Set MonthJanFeb2002 As [Date].[Calendar].[Month].&[2002]&[1]:[Date].[Calendar].[Month].&[2002]&[2]
Set ActMonthJanMarch2002 As  CrossJoin(MonthJanMarch2002,{[Scenario].[Scenario].&[1]})
Set ActMonthJanFeb2002 As  CrossJoin(MonthJanFeb2002,{[Scenario].[Scenario].&[1]})
Set BudMonthJanMarch2002 As  CrossJoin(MonthJanMarch2002,{[Scenario].[Scenario].&[2]})
Member Scenario.Scenario.ActAmountJanMarch2002 As Aggregate(ActMonthJanMarch2002,[Measures].Currentmember)
Member Scenario.Scenario.BudAmountJanMarch2002 As Aggregate(BudMonthJanMarch2002,[Measures].CurrentMember)

Member Scenario.Scenario.ActAmountJanFebBudMarch2002
As Aggregate({(MonthJanFeb2002,[Scenario].[Scenario].&[1]),([Date].[Calendar].[Month].&[2002]&[3],[Scenario].[Scenario].&[2])})

Select {Scenario.Scenario.ActAmountJanMarch2002, Scenario.Scenario.BudAmountJanMarch2002, Scenario.Scenario.ActAmountJanFebBudMarch2002} On 0,
[Measures].[Amount] on 1
From [Adventure Works];

The result looks like this:

Second Query

The definition of the calculate member and the aggregate() function in this last example came with a little help from the SSAS team at Microsoft.  These calculated members and named sets can be built in the MDX editor for Reporting Services in the 2008 version and later.

Posted in MDX for everyone | Leave a Comment »

Named Set Based on String Filter

Posted by thomasivarssonmalmo on October 21, 2010

This came up as a scenario in real life with a dimension consisting of collection of codes like (00:11:22:33) and where it is important to get all the collections that consist of one code like (11).

I will use the postal code attribute in the customer dimension of the Adventure Works demo cube as an example. I have used VBA functions supported in SSAS 2005 and later but I have tried it on SSAS 2008.

My example use both the LEFT() and the MID() VBA functions.

WITH Set PostalCode3 as Filter([Customer].[Postal Code].members,
LEFT([Customer].[Postal Code].CurrentMember.Name, 1) = “3”)

Set PostalCode30 as Filter([Customer].[Postal Code].members,
MID([Customer].[Postal Code].CurrentMember.Name, 1,2) = “30”)

Select {[Measures].[Internet Order Quantity]} On 0,
–PostalCode3 On 1
PostalCode30 On 1
From [Adventure Works];

I always try to add features like this in the source dimension tables but sometimes you need to help end users quickly with reports and they cannot wait until you add this in a cube as attributes.

For some of you it might be interesting to see that you can use the  MDX FILTER() function without a measure and that you can filter on  strings i a named set. Without a string filter in a named set end users would have to mark all combinations of a single code in a collection that could be several hundreds of members to maintain.

Be careful with string search in SSAS though.

Posted in MDX for everyone | 1 Comment »

MDX code snippet Existing

Posted by thomasivarssonmalmo on April 30, 2010

If you would like to see how many cities that have sold a certain bike model you can start trying to write something like this on the Adventure Works demo cube.
 
With Member Measures.ProdSold As
Count(Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0))
Select {Measures.ProdSold} On 0,
Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
From [Adventure Works];
 
You will then get a result like this.
 
 
You can also use Count like this with the same result:
 
With Member Measures.ProdSold As
Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0).Count
 
Select {Measures.ProdSold} On 0,
Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
From [Adventure Works];
 
The same value, 102 cities, will be repeated for each country.  What you would like to see is the number of cities for each country. The explantion for this behaviour can be found in the SQL Server 2008 MDX Step By Step book page 116. There is a link to this book up to the right in this blog. Also, the solution to this problem I found in the same book.
 
Before we proceed to the solution lets see how we can move the filter function from the calculated measure and actually use it on rows to see the actual cities that did sell this Bike. I have changed the query to this.
Select {[Measures].[Internet Order Quantity]} on 0,
(Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0)) On 1
From [Adventure Works];
 
You will get the result below.
 
 
 All the 102 cities will appear on the rows axis with it’s total internet order quantity.
 
Finally the solution is to add the Existing operator to the calculated measure like this:
 
With Member Measures.ProdSold As
Count( Existing Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0))
 
Select {Measures.ProdSold} On 0,
Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
From [Adventure Works];
 
Finally we will get what we were looking for. Each country will have the number of cities that sold one unit or more of the selected mountain bike.
 

Posted in MDX for everyone | 2 Comments »

The ITEM() function with MDX code examples

Posted by thomasivarssonmalmo on May 20, 2009

I usually write MDX blog posts with a business problem as the start point and seldom posts about how MDX functions might work.  Here is one exception, the Item function that is useful in many analytic scenarios,  will be discussed with examples but only in an attempt to explain how it works.  I thank Chris Webb for helping me on some questions that I had.
 
We will start with a query to get a base result set to use with the Item function in the later examples. Run this MDX in management studio:
 
 

Select {[Measures].[Internet Sales Amount]} on 0,

CrossJoin (Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]),

Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])) On 1

From [Adventure Works];

 
 

 

 

Internet Sales Amount

CY 2001

Accessories

 

CY 2001

Bikes

3 266 373,66

CY 2001

Clothing

 

CY 2001

Components

 

CY 2002

Accessories

 

CY 2002

Bikes

6 530 343,53

CY 2002

Clothing

 

CY 2002

Components

 

CY 2003

Accessories

293 709,71

CY 2003

Bikes

9 359 102,62

CY 2003

Clothing

138 247,97

CY 2003

Components

 

CY 2004

Accessories

407 050,25

CY 2004

Bikes

9 162 324,85

CY 2004

Clothing

201 524,64

CY 2004

Components

 

CY 2006

Accessories

 

CY 2006

Bikes

 

CY 2006

Clothing

 

CY 2006

Components

 

 

Now let us continue with a simple Item example on the same MDX select that we have started with.

 

Select {[Measures].[Internet Sales Amount]} on 0,

 CrossJoin (Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]),

Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])).Item(0) On 1

From [Adventure Works];

 

The result is the first tuple in the set that you have seen in the starting query. The index that you enter into the Item() function starts with zero so this is a clear result.

 

 

Internet Sales Amount

CY 2001

Accessories

(null)

 
If you change the index in the Item() function in the pervious query to 1 you will get the second tuple in the starting query.
 
 

 

 

Internet Sales Amount

CY 2001

Bikes

3 266 373,66

 

 If you increase the index one by one you will walk down the first result set tuple by tuple, so there is nothing unclear about how the Item() function works with one Index.

 

Now we will add a second argument to the Item() function like Item().Item() and se how this works. Remember to refer to my first example at the top of this blog post.

 

Select {[Measures].[Internet Sales Amount]} on 0,

 CrossJoin (Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]),

Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])).Item(0).Item(1) On 1

From [Adventure Works];

 

 

Internet Sales Amount

Accessories

700 759,96

 

 What you get as a result might require an explanation.  The query asks for the second member from the first tuple. That means that from the tuple (2001, Accessories) we are asking for Accessories.  What we get as a result is the sum of all Accessories in all the years in the first  query.

 

We can repeat the same behaviour for the second tuple with this query.

 

Select {[Measures].[Internet Sales Amount]} on 0,

 CrossJoin (Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]),

Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])).Item(1).Item(1) On 1

From [Adventure Works];

 

 

Internet Sales Amount

Bikes

28 318 144,65

 Like in the previous example we will get a sum of all bike sales for all years.

 
Finally if we execute this statement we will see the total for the components product category that is null.
 
 

Select {[Measures].[Internet Sales Amount]} on 0,

 CrossJoin (Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]),

Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])).Item(3).Item(1) On 1

From [Adventure Works];

 

 

Internet Sales Amount

Components

(null)

 
I hope that these code examples can help with understanding how the Item functions works. This function is easy to understand as long as you use one argument or index but gets a little harder with two arguments or indexes.

Posted in MDX for everyone | 2 Comments »

Empty values in MDX

Posted by thomasivarssonmalmo on March 8, 2009

Empty values in an SSAS cube can sometimes be valuable information but according to my experience, but in most scenarios, you would like to somethng about them. The scenarios I will discuss assumes that you do not have entered zeros for non existing measures in the source fact table.
 
Let us start with the first query:
 
Select Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],[Measures].[Internet Sales Amount])
 
It will show customers in Florida(USA) and what they have bought during the calendar years.
 
 
The first obvious tool in the MDX toolbox is to use the NON EMPTY key word to remove empty rows and columns from the result.
 
 
So far nothing new. Most client tools use the NON EMPTY option to remove empty rows and columns.
 
Update: I forgot the NonEmpty function. Run this MDX and it will give you the same result as in the previous picture, but probably much faster.
 
Select  NON EMPTY Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
NONEMPTY(CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])),
[Measures].[Internet Sales Amount]) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],[Measures].[Internet Sales Amount]);
 
Lets have a look at some other MDX keywords and functions that can be useful. What can the EXISTS function do? Run the MDX below and have a look at the result.
 
Select  NON EMPTY Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
EXISTS(CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])),
[Measures].[Internet Sales Amount]) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],[Measures].[Internet Sales Amount]);
 
 
 
The empty rows appears again but that is because EXISTS will use the members from the first set, Customers in Florida, that have bought products but only show the members from the Customers(in Florida) dimension. The products these customers have bought will not be shown.
 
Using the EXISTING key word will return the same result as EXIST.
 
Select  NON EMPTY Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
EXISTING CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],[Measures].[Internet Sales Amount]);
 
Another option is to use the FILTER function. It will return the same result as the NON EMPTY key word.
 
Select NON EMPTY Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
Filter(CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])),
[Measures].[Internet Sales Amount] > 0) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],[Measures].[Internet Sales Amount]);
 
Finally you can see what  the COALESCEEMPTY function can do with null values. This function do not remove nulls or zeros but change their values.
 
WITH Member Measures.InternetSalesX as
CoalesceEmpty([Measures].[Internet Sales Amount],"x")
Select Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],Measures.InternetSalesX)
 
 
These examples have been written without paying any attention to performance issues. They are simple examples of how to change or remove empty values from a MDX query.
 

Posted in MDX for everyone | Leave a Comment »

A simple MDX ratio problem

Posted by thomasivarssonmalmo on January 18, 2009

"Ratio to parent" in MDX is a frequent discussion in the SSAS newsgroup and on the blogs. Mosha’s and Darren Gosbell’s thoughts are linked here and involves the MDX Axis function. This blog entry is about a less advanced problem in the "ratio to parent scenario". It is about how you can get a total percentage distribution of members from two crossjoined dimensions on rows in a MDX select statement. I think it can be help if your boss demands a quick solution and do not accept the default behaviour in a SSAS client.
 
The default behaviour can be seen if you run this MDX in Management Studio.
 

Select {[Measures].[Internet Sales Amount],[Measures].[Internet Ratio to All Products]} On Columns,
NON EMPTY CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])) On Rows
FROM [Adventure Works]
WHERE ([Date].[Calendar Year].&[2004]); 

The Internet Ratio to all products is defined like this in the Adventure Works cube.

[Measures].[Internet Sales Amount]
    /
    (
      Root( [Product] ),
      [Measures].[Internet Sales Amount] )

The result will look like this:

 

For each country you will get the percentage distribution of [Internet Sales Amount] in the product categories for each country.  A client will accept the dimension you have crossjoined and calculate the percentages according to this.

And now to the simple problem that might not have an obvious simple solution. If I would like to to see the percentage distribution for all the countries and the product categories as a ratio to total.  What was the percentage contribution of [Internet Sales Amount] for Bikes in Australia to the total sales for all markets? This analysis is an issue for the SSAS clients that I know about.

My solution is to build a calculated measure that references All Products and All Customers members like this.

WITH Member Measures.AllInternetSales2004 As
([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004],[Customer].[Customer Geography].[All Customers]
,[Product].[Product Categories].[All Products])

Member Measures.RatioInternetSales2004AllGeo As
([Measures].[Internet Sales Amount]/Measures.AllInternetSales2004),Format_String = "#0.0%"

Select {[Measures].[Internet Sales Amount],[Measures].[Internet Ratio to All Products],
Measures.RatioInternetSales2004AllGeo} On Columns,
NON EMPTY CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])) On Rows
FROM [Adventure Works]
WHERE ([Date].[Calendar Year].&[2004]);

The result will look like this.

 

In order to solve this simple ratio to parent problem you will not need to use the MDX Axis()-function but build a calculated measure with the two top members for each dimension on rows. The All-member is useful in other scenarios as well and I will return to that later.  

The problem with my approach is that you will have to make this ratio for each measure that the ratio applies to. If this takes less or more time than learning about the Axis function is up to you to decide.

Edit: The pictures are updated. To validate the percentages you copy the resultset in Management Studio with a right click in the upper left corner. Paste it in to Excel but you might have to change the decimal sign from (.) to (,). I have only one decimal so you will see a minor gap. Add decimals in the format_string property of the calculated measure. 

Posted in MDX for everyone | 4 Comments »

SSAS clients and TopCount

Posted by thomasivarssonmalmo on November 23, 2008

Last week I met an end user that struggled with building a simple TopCount report in ProClarity. At the first look I thought it would be a simple problem that the client should handle with standard functionality, but that was not true. The end user requested a report with the top ten symptoms and all the related detailed symptoms to each top 10 item.
 
We can easily build the same scenario with the Adventure Works cube. Give me the top 10 customers in 2004 according to Internet Sales Amount and show me all the products they have bought. In ProClarity you would place customers and products on the rows and Internet Sales Amount and the Calendar Year 2004 on columns. You then expand the customers and products dimensions to their leaf levels and put a TopCount 10 filter on that. I will start with the top ten customers for 2004.
 
You set the filter like this.

TopCount10Customers2004

 
The result is like the next picture.
 
MyTop10Cust2004
 
If you add the product dimension leaf members to the right of the customers you will get an unexpected result.
 
 
 Strange result
 
You will get another set of customers and one product per customer. This is not what my end user requested. In the next picture you will see the requested result on the Adventure works cube. You will only see part of the result.
 
This is what I want
 
The MDX that ProClarity generates will reveal what happens.
 
SELECT {[Measures].[Internet Sales Amount]  } ON COLUMNS ,
{ TOPCOUNT( { { { DESCENDANTS( [Customer].[Customer Geography].[All Customers], [Customer].[Customer Geography].[Customer] ) } *
{ DESCENDANTS( [Product].[Product Categories].[All Products], [Product].[Product Categories].[Product] ) } } }, 10,
( [Date].[Calendar].[Calendar Year].&[2004], [Measures].[Internet Sales Amount] ) ) } ON ROWS 
FROM [Adventure Works]
Where ([Date].[Calendar].[Calendar Year].&[2004] )
 
ProClarity crossJoins customers and products first and does the topcount on that result.
 
How can you solve this problem? Here is how you do it in and MDX statement.
 
WITH SET [TopTenCustomer2004Sales] As
TopCount(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),10,
([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))

Select {[Measures].[Internet Sales Amount]} On Columns,
NON EMPTY CrossJoin([TopTenCustomer2004Sales],Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])) On Rows
From [Adventure Works]
Where([Date].[Calendar].[Calendar Year].&[2004]);
 
I create the TopCount set first and later do a crossjoin of this set and the product dimension leaf members. It will also work if you copy the With Set statement into a ProClarity named set.
 
What I assumed was a simple task was more complicated and I am not happy about, that this simple analysis requirement, is not supported in the client.

Posted in MDX for everyone | Leave a Comment »

Product Sales, Top and BottomCount in the same report

Posted by thomasivarssonmalmo on August 18, 2008

Here is a MDX code snippet that will show you how to combine named set based on TopCount and BottomCount. This type of report is sometimes refered to as "winners and loosers". This means that the report will show the best selling products and the worst for a single year.
 
It is also easy to use this code in others types of analytics like the products with most and least errors.
 
I will start this time with showing the final result.

Top and bottomCount

I have the top 10 product sales for 2003 as the first ten members followed by the bottom 10 product sales. On the columns I have Internet Sales Amount for 2003 , the market share for each product and finally the ranking of these products over all sold products in 2003.

Here is the code to produce this result. I am sure that there are many other ways to do the same. This time I have checked the query in MDX Studio and the number of Storage Engine queries was 7.

WITH Member Measures.[Market Share] As
(Measures.[Internet Sales Amount],[Product].[Product Categories].CurrentMember)/
(Measures.[Internet Sales Amount], [Product].[Product Categories].[All Products]), Format_String = "0.0#%"

Set Top10Products2003 as TopCount([Product].[Product Categories].[Product].members,10,(Measures.[Internet Sales Amount],[Date].[Calendar Year].&[2003]))

Set Bottom10Products2003 as
BottomCount(NonEmpty([Product].[Product Categories].[Product].members,([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2003])),10,
(Measures.[Internet Sales Amount],[Date].[Calendar Year].&[2003]))

Set Top10Bottom10Products as Union(Top10Products2003,Bottom10Products2003)

Set AllSoldProducts2003 as NonEmpty([Product].[Product Categories].[Product].members,([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2003]))

Member Measures.[RankTopBotton10Products] as
RANK([Product].[Product Categories].CurrentMember,  AllSoldProducts2003,[Measures].[Internet Sales Amount])

Select {Measures.[Internet Sales Amount],Measures.[Market Share],Measures.[RankTopBotton10Products]} On 0,
Order(Top10Bottom10Products,Measures.[Internet Sales Amount],BDESC) On 1
From [Adventure Works]
Where([Date].[Calendar Year].&[2003]);

A few comments regaring this code. The BottomCount function needs the help of the NonEmpty function to not pick the products with null sales in 2003.  The AllSoldProducts2003 is used to rank these 20 products over all sold products. I have also checked that the number of sold products was 133 in 2003. The ranking of products is tied to Internet Sales Amount but it will also work with the market share calculated measure.

You should compare the amount of MDX code required to build this report with a SQL based solution.

 

Posted in MDX for everyone | Leave a Comment »