Trend Analysis Charts with SSAS and PerformancePoint Server 2007

Creating mining models and writing prediction queries has never been easier. In fact, use the Trend Analysis Report type in PerformancePoint Server 2007 and much of the work will be done for you. Below is a brief walkthrough of how to create a simple report to forecast future data points based on prior data points, and a look at what PerformancePoint is doing behind the scenes to serve up its predictions.

First of all...

Download the SQL Server Samples from CodePlex. The AdventureWorks sample solution located at: C:Program FilesMicrosoft SQL Server90ToolsSamplesAdventureWorks Analysis Services ProjectStandardAdventure Works.sln. Deploy the project and process the database.

Creating the report

We'll create a basic report to predict Q1 2003 Revenue based on 2002 Revenue:

Open Dashboard Designer. From the "Workspace Browser" pane, add a new Data Source, pointing it to the "Adventure Works DW Standard Edition" database. Note that the AS database will not appear in the drop-down list unless you've added the account of the logged-in user to a Role that has access to the database.

The Trend Analysis Report type must be based on a Scorecard, so we'll import an existing one from the AdventureWorks Analysis Services sample database.

Create a new Scorecard from the Workspace Browser. When the wizard appears...

• Accept the defaults on the first screen of the wizard.
• On the next screen, name the Scorecard "RevenueCard"
• Next screen, select the "AdventureWorksDW" data source you created.
• Next screen, choose "Import SQL Server Analysis Services KPIs" option.
• Next screen, check the "Revenue" KPI.
• Next screen, do nothing
• Next screen, choose "Date.Date.Fiscal" as the dimension. For the members, expand the nodes and check "Q1 FY 2002", "Q2 FY 2002", "Q3 FY 2002", "Q4 FY 2002".

Now create a new report from Workspace Browser. When the wizard appears, choose "Trend Analysis Chart"

• Name the report "RevenueTrend"
• Next screen, select the "RevenueCard" Scorecard. If it doesn't show up, cancel the wizard and click "Publish All" from the "Home" ribbon.
• Complete the wizard, accepting all defaults.

Forecasting

Open the "RevenueTrend" report and type a number into the "Forecast Period" textbox. Note that the predicted data point for "Q1 FY 2003" is highlighted in red.

Making it pretty

The report looks busy, so we need to remove the unwanted series. Right-click the chart and make sure "Toolbar" is checked. Now from the toolbar, click "Field List". Delete the "Goal and Status" and "Trend" totals from the field list.

The result should look something like this:

Under the covers

Now let's examine a SQL Profiler trace to see how PerformancePoint is querying Analysis Services to serve up the forecasted data. To get good trace data, start the trace, then switch over to PerformancePoint and change the value in the "Forecast Period" textbox to 2. The below is distilled from the trace results:

First, a session (temporary) mining model is created. Note that the Microsoft Time Series Data Mining Algorithm is used, because the forecast is based on a date dimension.

CREATE SESSION MINING MODEL MiningModel_0( [KeyTime] LONG KEY TIME ,[Key] LONG KEY ,[Column_2] DOUBLE CONTINUOUS  PREDICT_ONLY) USING Microsoft_Time_Series

Then the mining model is trained with data.  Note the @InputRowset parameter (will explain below):

INSERT INTO MiningModel_0 (  [KeyTime] ,[Key] ,[Column_2]) @InputRowset

Finally, a prediction query returns the results.  Note the "number of next steps to predict" parameter is set to 1, because we chose to forecast 1 period (Q1 FY 2003):

SELECT FLATTENED (SELECT $TIME, [Column_2] from PredictTimeSeries([Column_2], 1) ) FROM [MiningModel_0]

The @InputRowset parameter above is an in-memory table created by PerformancePoint to train the mining model.  For a good explanation of this technique, see this post by Microsoft's SQL Server Data Mining development team. From the trace, I suspect the below query is used to help build @InputRowset.

WITH 
 MEMBER [Measures].[CUSTOM_eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_836cc47f-a089-4156-8347-81ee360f6acf] as '(KPIGoal("Revenue"))'
 MEMBER [Measures].[eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_836cc47f-a089-4156-8347-81ee360f6acf] as '([Measures].[CUSTOM_eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_836cc47f-a089-4156-8347-81ee360f6acf])'
 MEMBER [Measures].[CUSTOM_eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_5047faae-8899-41d1-946e-3ce4abc4274d] as '(KPIStatus("Revenue"))'
 MEMBER [Measures].[eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_5047faae-8899-41d1-946e-3ce4abc4274d] as '([Measures].[CUSTOM_eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_5047faae-8899-41d1-946e-3ce4abc4274d])'
 MEMBER [Measures].[CUSTOM_eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_46a8b718-4377-4414-a7a3-0b85c838c17c] as '(KPITrend("Revenue"))'
 MEMBER [Measures].[eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_46a8b718-4377-4414-a7a3-0b85c838c17c] as '([Measures].[CUSTOM_eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_46a8b718-4377-4414-a7a3-0b85c838c17c])'
 MEMBER [Measures].[CUSTOM_eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_48a18387-c463-4e52-b324-4df046dbb8ff] as '(KPITrend("Revenue"))'
 MEMBER [Measures].[eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_48a18387-c463-4e52-b324-4df046dbb8ff] as '([Measures].[CUSTOM_eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_48a18387-c463-4e52-b324-4df046dbb8ff])'
 MEMBER [Measures].[CUSTOM_eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_1c950f07-5d39-4d21-8339-440227113f7a] as '(KPIValue("Revenue"))'
 MEMBER [Measures].[eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_1c950f07-5d39-4d21-8339-440227113f7a] as '([Measures].[CUSTOM_eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_1c950f07-5d39-4d21-8339-440227113f7a])'
SELECT { [Measures].[eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_836cc47f-a089-4156-8347-81ee360f6acf],
[Measures].[eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_5047faae-8899-41d1-946e-3ce4abc4274d],
[Measures].[eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_46a8b718-4377-4414-a7a3-0b85c838c17c],
[Measures].[eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_48a18387-c463-4e52-b324-4df046dbb8ff],
[Measures].[eca3a6bc-0bc9-446a-9e2b-da5f05a31e53_1c950f07-5d39-4d21-8339-440227113f7a]} ON AXIS(0),
{([Date].[Fiscal].[Fiscal Quarter].&[2002]&[1]),
([Date].[Fiscal].[Fiscal Quarter].&[2002]&[2]),
([Date].[Fiscal].[Fiscal Quarter].&[2002]&[3]),
([Date].[Fiscal].[Fiscal Quarter].&[2002]&[4]),
([Date].[Fiscal].[Fiscal Quarter].&[2003]&[1])} ON AXIS(1)
FROM [Adventure Works]

Conclusions

PerformancePoint allows you to easily create trend reports from existing KPIs. Using Dashboard Designer, even business users can generate forecast reports with no knowledge of DMX.

There are several features I didn't cover, such as the "Advanced Settings" screen that allows you to specify values for a few mining model parameters (PERIODICITY_HINT, COMPLEXITY_PENALTY, MINIMUM_SUPPORT). Aside from these settings, Dashboard Designer doesn't give you much control of the DMX that drives the report. As a last resort, the entire query could be coded by hand, but hopefully you won't have to go down that road.