Professional SQL Server™ Analysis Services 2005 with MDX

7.5. 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.

7.5.1. 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])

7.5.2. 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, ...