Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

23.2. Forecasting

Now that you have your orders (and accumulated orders) reports to understand the ranking of your products, the next thing you want to do is to expand on this to see if your past orders can help you understand what future demand will entail. Therefore, the following sections focus on some of the MDX calculations needed to provide trend analysis as well as rolling averages and weighted rolling averages. For more information specific to inventory forecasting, suggested reading includes the whitepaper Improving the Accuracy of your Forecasts (Schreibfeder, www.microsoft.com/dynamics/industry/wholesale_distribution_whitepapers.mspx, 2004).

A good reference for calculating rolling averages is Mosha Pasumansky's blog "post" Moving Averages in MDX (sqlblog.com/blogs/mosha/archive/2007/09/04/moving-averages-in-mdx.aspx).

23.2.1. Trend Analysis

In many inventory and data warehousing scenarios, the first thing that needs to be done is to understand your trends over a time period. For example, to obtain a six-month trend of clothing products ordered on the internet from Adventure Works, you can run the following query:

with
member [Measures].[Orders] as
    iif([Measures].[Internet Order Count] = null,
         0,
         [Measures].[Internet Order Count])
select {
    Crossjoin(
        [Measures].[Orders],

[Product].[Product Categories].[Clothing].children
    )
} on columns, {
    [Date].[Fiscal].[Month].[February 2004].Lag(5):
    [Date].[Fiscal].[Month].[February 2004]
} on rows
from [Adventure Works]

Converted ...