29 May 2007

LinRegPoint() MDX Function Sample in AdventureWorks


Thanks to Mosha Pasumansky (no surprise) for the real workings of this sample. Mosha provides a sample of the LinRegPoint() MDX function on the SQL 2000 Foodmart sample database at this link: http://sqljunkies.com/WebLog/mosha/archive/2004/12/21/5689.aspx
More importantly, Mosha explains the usage of the parameters and why the use of the MDX RANK() function really helps in arguments to LinRegpoint. Please see the above link for a detailed explanation on how the sample included here works.

This sample works against the 2005 AdventureWorks sample. It shows the [Internet Sales Amount] compared to the values produced from the LinRegPoint function for the months in calendar year 2003. Here's the MDX:

WITH
MEMBER Measures.[Internet Sales Forecast] AS
LinRegPoint(
Rank([Date].[Calendar].CurrentMember, [Date].[Calendar].CurrentMember.LEVEL.MEMBERS),
Descendants([Date].[Calendar].[Calendar Year].&[2003],[Date].[Calendar].CurrentMember.LEVEL),
[Measures].[Internet Sales Amount],
Rank([Date].[Calendar].CurrentMember, [Date].[Calendar].CurrentMember.LEVEL.MEMBERS))
,FORMAT_STRING="$#,0.00"

SELECT DESCENDANTS([Date].[Calendar].[Calendar Year].&[2003], [Date].[Calendar].[Month]) ON COLUMNS,
{[Measures].[Internet Sales Amount], Measures.[Internet Sales Forecast]} ON ROWS
FROM [Adventure Works]

The graph at the top shows the results in Excel 2007. An image any business savvy individual can value when interested in forecasting: