Thomas.I Microsoft BI & Analytics

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

Product Volatility in Adventure Works

Posted by thomasivarssonmalmo on July 22, 2008

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

2 Responses to “Product Volatility in Adventure Works”

  1. Miky Schreiber said

    Hey Thomas,

    Great MDX post you got there. Just a little comment – when you write long SQL & MDX posts please show the query results. This will help the readers understand what you did.

    Miky.

  2. Thomas said

    Hi Miky,
     
    Thanks for the comment.
     
    You have a point and I usually present the results with pictures. In this case the result is long because it is on the month level and I cannot show all records. I totally agree that pictures will help readers to understand the objectives of the blog post. The result is simply the number of products with increasing, decreasing and constant sales between months.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: