Ranking and Sorting

Ranking and Sorting are pretty common features in most business analysis. MDX provides several MDX functions such as TOPCOUNT, BOTTOMCOUNT, TOPPERCENT, BOTTOMPERCENT, and RANK that help you stack rank information for better business decisions. You saw an example of RANK in Chapter 3. This section uses the Adventure Works DW sample database to show a few examples of some common business questions in the retail industry that business analysts might be looking at.

Example 1

If you want to get an overview of the various products sold across various countries and through internet sales, the following MDX query will provide you the results. You can set a Sales Quota for subsequent years to improve revenue on specific countries or specific products to have an overall impact for the company.

     SELECT [Customer].[Customer Geography].[Country] on 0,     [Product].[Category].members on 1     from [Adventure Works]     WHERE (Measures.[Internet Sales Amount]) 

Example 2

In the case of companies which manufacture and sell products, it is desirable to take a look at how various products are performing on a periodic basis. If you are looking for the top N product categories or subcategories based on the sales in all the countries, the following queries will provide you the answer. Based on the results you can invest more in marketing campaigns and other initiatives to further boost the sales and revenue for the company.

     SELECT Measures.[Internet Sales Amount] on COLUMNS,     TOPCOUNT ([Product].[Product Categories].[Category].Members,     3, Measures.[Internet Sales Amount]) ON ROWS from [Adventure Works]     SELECT Measures.[Internet Sales Amount] on COLUMNS,     TOPCOUNT ([Product].[Product Categories].[SubCategory].Members,     10, Measures.[Internet Sales Amount]) ON ROWS     from [Adventure Works] 

Example 3

If you want to drill down further on the top 10 product categories within the United States, you can use the following query. Based on this information you can improve sales on products that are doing well or try to boost sales on the remaining products after some market research on why they are not doing well.

     SELECT Measures.[Internet Sales Amount] on COLUMNS,     TOPCOUNT ([Product].[Product Categories].[SubCategory].Members,     10, Measures.[Internet Sales Amount]) ON ROWS     from [Adventure Works]     WHERE ([Customer].[Customer Geography].[Country].&[United States]) 

Example 4

If you want to see growth figures for the top 5 products in the last 4 quarters, you can use the following MDX query. This query provides you with the trend information that allows you to see if the top 5 products have been consistently increasing in sales. If you see that some products do not show a positive trend, you do need to drill down further for details and take appropriate action.

     WITH SET [Top5Products] as 'TopCount ( [Product].[Product     Categories].[SubCategory].Members,         5,Measures.[Internet Sales Amount] )'     SET [CurrentQuarter] as 'Tail (Filter ([Date].[Fiscal].[Fiscal Quarter].Members,             Not IsEmpty ([Date].[Fiscal].CurrentMember)),1)'     SET [Previous4Quarters] as ' [CurrentQuarter].item (0).item (0).Lag (4) :     [CurrentQuarter].item (0).item (0).Lag (1) '     MEMBER Measures.Growth AS     ([Date].[Fiscal].currentmember ,[Measures].[Internet Sales Amount])     -     ([Date].[Fiscal].currentmember.prevmember, [Measures].[Internet Sales Amount])/     ([Date].[Fiscal].currentmember, [Measures].[Internet Sales Amount])* 100     select [Top5Products] on COLUMNS,      [Previous4Quarters] on ROWS     from [Adventure Works]     WHERE [Growth] 

Example 5

In order to maximize your business you might discontinue products that are not providing your best sales. Typically companies might cut their work force by 10% to increase the company's bottom line. Assume you want to analyze the products to see the bottom 10% of the products in terms of Internet sales. You can send the following MDX queries from SSMS. You can see that there are 159 products that contribute toward internet sales and out of these, 95 products contribute to the bottom 10% of the overall sales. Now you can further drill down at each product and identify the cost of selling them over the internet and see if it really makes sense to keep selling these products.

     //Total number of products contributing towards internet sales - 159 products     select {[Measures].[Internet Sales Amount]} on COLUMNS,      Non Empty[Product].[Product Categories].[Product Name].Members on ROWS     from [Adventure Works]     //Bottom 10% (Sales) of the products sold through the internet - 95 products     select {[Measures].[Internet Sales Amount]} on COLUMNS,      Non Empty BottomPercent ([Product].[Product Categories].[Product Name].Members, 10,      [Measures].[Internet Sales Amount]) on ROWS     from [Adventure Works]