Thomas.I Microsoft BI & Analytics

 

Sales for products are going up and down. In this blog post I will simply show how you can count the number of products with increasing and decreasing sales between each month in the Adventure Works cube. Volatility is simply about how many changes in sales you have for products. Sometimes you do this by comparing current month with the previous month, as in this example. Sometimes you compare this month with the same month one year ago.

 

This is a MDX snippet written for beginners and medium experienced users. I will also use some TSQL to make you sure of that the starting point for this analysis is correct.

 

I will start in the relational world and the source for the Adventure Works cube. If you run:

 

Select * From DimProduct

 

It will return 606 products. Not all of these are final products sold to customer. If you run this select and relate the product table to the product subcategory table:

 

Select Count(*) From DimProduct dp
Join DimProductSubCategory ds on
dp.ProductSubCategoryKey = ds. ProductSubCategoryKey

 

You will see 397 products.

 

But how many products have been sold each month in this data base? Run this TSQL:

 

Select Distinct Year(t.FulldateAlternateKey) * 100 + Month(t.FulldateAlternateKey) as YearMonth, Count(Distinct ProductKey) as NoProducts
From DimTime t Join dbo.FactInternetSales f
On t.TimeKey = f.OrderDateKey
Group By Year(t.FulldateAlternateKey) * 100 + Month(t.FulldateAlternateKey)
Order By Year(t.FulldateAlternateKey) * 100 + Month(t.FulldateAlternateKey)

 

 What I am doing with dates is simply to create a integer version of year and month. Remember this resultset when we move to the MDX-world.

 

 I want to see the total number of products and the number of sold products in the Adventure Works cube. This MDX will help you with that and show you the same result, for the number of sold products as above:

 

WITH
  MEMBER Measures.TotNumberOfProducts AS
    Count(
      Descendants
      (
        [Product].[Product Categories]
       ,[Product].[Product Categories].[Product]
      ))
  MEMBER Measures.TotNumberOfSoldProducts AS
    Count(
      NonEmpty
      (
        Descendants
        (
          [Product].[Product Categories]
         ,[Product].[Product Categories].[Product]
        )
       ,[Measures].[Internet Sales Amount]
      ))
SELECT
  {
    Measures.TotNumberOfProducts
   ,Measures.TotNumberOfSoldProducts
  } ON 0
 ,[Date].[Calendar].[Month] ON 1
FROM [Adventure Works];

 

You will see exacly the same resultset as in the TSQL query above.

 

 The nice thing with MDX is that it is not that hard, as in TSQL, to compare what happend the month before the current one in the resultset. Here is the final snippet of how many products that increased, decreased and had the same sales as the previous month in the Adventure Works cube.

 

 WITH
  MEMBER Measures.TotNumberOfProducts AS
    Count(
      Descendants
      (
        [Product].[Product Categories]
       ,[Product].[Product Categories].[Product]
      ))
  MEMBER Measures.TotNumberOfSoldProducts AS
    Count(
      NonEmpty
      (
        Descendants
        (
          [Product].[Product Categories]
         ,[Product].[Product Categories].[Product]
        )
       ,[Measures].[Internet Sales Amount]
      ))
     
      MEMBER Measures.NumberOfSoldProductsIncreasing AS
    Count(Filter(
      NonEmpty
      (
        Descendants
        (
          [Product].[Product Categories]
         ,[Product].[Product Categories].[Product]
        )
       ,[Measures].[Internet Sales Amount]
      ),([Date].[Calendar].CurrentMember,[Measures].[Internet Sales Amount]) > ([Date].[Calendar].PrevMember,[Measures].[Internet Sales Amount])) )
     
      MEMBER Measures.NumberOfSoldProductsDecreasing AS
    Count(Filter(
      NonEmpty
      (
        Descendants
        (
          [Product].[Product Categories]
         ,[Product].[Product Categories].[Product]
        )
       ,[Measures].[Internet Sales Amount]
      ),([Date].[Calendar].CurrentMember,[Measures].[Internet Sales Amount]) < ([Date].[Calendar].PrevMember,[Measures].[Internet Sales Amount])) )
     
      MEMBER Measures.NumberOfSoldProductsNonChanging AS
    Count(Filter(
      NonEmpty
      (
        Descendants
        (
          [Product].[Product Categories]
         ,[Product].[Product Categories].[Product]
        )
       ,[Measures].[Internet Sales Amount]
      ),([Date].[Calendar].CurrentMember,[Measures].[Internet Sales Amount]) = ([Date].[Calendar].PrevMember,[Measures].[Internet Sales Amount])) )
     
SELECT
  {
    Measures.TotNumberOfProducts,
 Measures.TotNumberOfSoldProducts,
 Measures.NumberOfSoldProductsIncreasing,
 Measures.NumberOfSoldProductsDecreasing,
 Measures.NumberOfSoldProductsNonChanging
  } ON 0
 ,[Date].[Calendar].[Month] ON 1
FROM [Adventure Works];

 

 

The basic idea is the same for the calculated measures in the last example. Use NonEmpty to get the products that were sold and the combination of Count and Filter to get the number of products that increased, decreased and stayed stable between months. It should not be that hard to add ratios for each of them compared with the number of sold products.

 

Another perspective might be to return the names of the products that had the highest number of increases, decreases or both during a time period. I will return with that later.

 

Edit: Mosha Pasumansky kindly asked me if he could use this blog post as an example for optimizing the query with MDX studio. Since I have focused on the business problem part of the problem I was happy to get the help from the best MDX mentor to tune this query. You should always think about performance as the second part in this process. Have a look at the performance angle of this query at Mosha’s blog.