Creating Trend Charts in M&A

Trend charts use Data Mining algorithms in SQL Server Analysis Services 2005 to predict future values of KPIs in your scorecard. KPIs can be sourced from any data source, Excel, SSAS, SQL, etc. Because the trend charts utilize the Time Series Data Mining algorithm in SSAS 2005, in order to use the trend charts in PerformancePoint Monitoring, you need to have an SSAS 2005 server available. Additionally, you need to ensure that the setting on your Analysis Services 2005 server is set to allow session mining models. To verify this, follow the following steps:

1. Open SQL Server Management Studio.

2. Connect to your Analysis Services server.

3. Right click on the server name and select Properties.

4. Ensure that the DataMining\AllowSessionMiningModels is set to True. Click OK.

image

We also need to set the Analysis Services Server used for Data Mining in the Dashboard Designer. This is the server that will be used to run the Time Series Data Mining algorithm against the scorecard data. To do this, follow the below steps:

1. From Dashboard Designer, click the Windows button in the upper left corner. Choose Options.

2. Click the Server tab.

3. Click Connect to connect to the Server.

4. Click the Server Options button.

5. Set the Microsoft Analysis Services Server name (for Data Mining) to your server.

image

6. Click OK.

7. Click OK to exit the dialog.

Next, create a scorecard with KPIs from the Dashboard Designer - the KPIs can be sourced from either SSAS or any of the other available data sources. Publish the scorecard to your server. Now, we can create the trend chart. Note that trend charts currently utilize the Office Web Components from Office 2003. In order for you to create them and for your users to view them, the OWC components must be installed on your server machine and all client machines. To create a trend chart follow the below steps:

1. In the Workspace Browser, right click Reports and select New Report.

2. Choose Trend Analysis Chart, click OK.

3. Assign a name, optionally assign a display folder and permissions. Click Next.

4. Select the scorecard to base your trend chart on. Click Next.

5. Select each of the KPIs you want to trend - note a separate trend chart will be created for each KPI. Click Finish. Click Close.

image

6. The base chart will be created showing the same time periods as the time periods that were selected in the scorecard. The scorecard this view was based on has 4 time period selections on the columns, so the base chart plotted these 4 time periods. No forecasting has been done yet. See the below scorecard which the trend chart was based on:

image

7. Now adjust the Forecast period value (currently 0) to the number of periods that you wish to trend. Hit Enter. Note that the data source you are sourcing your KPIs from must include the future members you wish to forecast values for as dimension members in your source data.

image

8. Notice that I now have one future time period with predicted values shown in Red on the trend chart. The above example is based on the Adventure Works cube, because the Adventure Works cube only contains time dimension member up through Q1 FY 2005, I can only forecast this time dimension member.

9. You can optionally change the time period to use the last trailing periods as well, I can change the chart to use the last 5 periods rather than the 4 that were specified in my scorecard. Note that I can still only forecast for the one future time member that is currently not populated. This would yield a trend chart as follows:

image

10. Now the chart has the last 5 quarters selected and the one future time period predicted.

Alyson Powell Erwin (alysonp@microsoft.com)