O'Reilly logo

Professional Microsoft® SQL Server® Analysis Services 2008 with MDX by Denny Guang-Yeu Lee, Robert Zare, Sethu Meenakshisundaram, Matt Carroll, Sivakumar Harinath

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required