Trend Analysis in Performance Point Server 2007 with Analysis Services KPIs

At BI Training, we glossed over using PPS to create trend charts. I decided to look into this in more detail and blog about it. Below is a basic demo of the steps required to use your existing Analysis Services KPIs within PPS to create trend charts.

Setup environment:

Download the SQL Server Samples from CodePlex.

Open the AdventureWorks sample solution located at: C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Standard\Adventure Works.sln

You’ll notice there are a few KPIs in the Adventure Works cube (Growth in Customer Base, etc…). Deploy and Process the project (by right-clicking and choosing Deploy). FYI, the KPIs should look something like this in BIDS. We will be looking at how to import one or more of these KPIs [from Analysis Services] into Performance Point Server 2007 to use for trend analysis.

Create the datasource

• Open Dashboard Designer and add a new datasource. Set it up as follows:
  • From the “Multidimensional” category, choose the “Analysis Services” template.
  • Name the data source “AdventureWorksDS”
  • Check the checkbox labeled “Grant read permissions to all authenticated users” and click Finish
  • Setup remaining properties per the below screen shot:
    

Create a scorecard

• Add a new Scorecard, and choose the “Analysis Services” template from the “Microsoft” category. Be sure the “Use wizards to create scorecards” box is checked. Click OK.
• Type “TrendAnalysisSC” as the Name, and check the checkbox labeled “Grant read permissions to all authenticated users”. Click Next.
• Select the “AdventureWorksDS” data source and click Next.
• Select the 2^nd radio button labeled “Import SQL Server Analysis Services KPIs.
• Select the “Internet Revenue” KPI and click Next.
• Click Next again to skip the “Add measure filters” step.
• Check “Add column members” and select the “Date.Date.Fiscal” dimension.
• Now we need enough historical data to produce a good forecast. In “select members for columns”, select each quarter for 2002 and 2003. It should look like this:

• Click Finish. Then click Close.
• Now click “Publish All” in the ribbon area. This is very important. If you don’t do this, the scorecard may not show up during the following steps.
• Now we need to create a trend chart based on the TrendAnalysisSC Scorecard.
• To do this, add a new report. When prompted for report type, choose “Trend Analysis Chart”. Click OK.
• Name the report “TrendAnalysisReport”, and check the checkbox labeled “Grant read permissions to all authenticated users”.
• In the “Select a scorecard” step, choose “TrendAnalysisSC”.
• In the “Select the KPIs” step, choose “Internet Revenue”. Click Finish. Then click Close.
• You should see something like this:

• Note: The above is a chart of the actual KPI values, not the forecasted values. To see forecasted values, enter 2 in the “Forecast Period” box and hit Tab to leave the field. The chart will refresh and highlight the forecasted section red. Because we entered a value of 2, and we are at the “Quarter” level, the values for the next two quarters are forecasted. The below graph is how it should looknow:

• Note that three series (lines) are displayed. Click the “Show/hide Legend” button in the toolbar to see what these are. There will be a series for each element of the KPI (Value, Goal/Status, Trend).
  Note: if you don’t see the toolbar, right-click the chart and select “Toolbar”.
• So what if you are asked to hide everything except the “Value” series? Read on…

How to remove one or more series from the trend chart

• In the “Workspace Browser”, click on the “Internet Revenue” KPI you created earlier. You should see a grid with three rows. Delete the “Goal and Status” row and the “Trend” row. The only remaining row is now “Value”.
• Click “Publish All” and then click TrendAnalysisReport. Nothing has changed. Now click the refresh button. Still, nothing changes. Also feel free to go into the scorecard and delete all of the “Goal and Status” and “Trend” columns. The report still shows all three series. Guess what… the report is now a completely separate entity from the KPI and the Scorecard; so modifying these will do nothing to change the report. To remove a series from the report, you have a few options:
  • (Bad solution) Modify the scorecard, delete the report, then recreate the report based on the modified scorecard.
  • (Good solution) Right-click the chart area of the report and choose “Field List…”. You’ll see something like the below. Right-click “Goal and Status” and choose “Delete” to remove that series from the chart.
    

You should now see only the Value series:

FYI: Performance Point Creates Temporary Mining Models

• The forecasted data points are created by Analysis Services, but in this case, a temporary session-scoped Mining Model is created for you (on-the-fly) by PPS. To prove this to yourself, fire up SQL Profiler, then start a trace using Analysis Services and the Adventure Works cube. Now run the report (you may have to change the “Forecast Period” again to see results. From the confusing results of the trace, below are a few highlights / observations:

1: The below MDX Query is generated. I suspect the results of this are used as @InputRowset (keep reading) to train the Mining Model:

WITH

MEMBER [Measures].[CUSTOM_a7ba172a-ce41-4d38-b573-c7bed75a9477_12af2876-ed38-4404-a753-c5abe35331cc] as ‘(KPIGoal(“Internet Revenue”))’

MEMBER [Measures].[a7ba172a-ce41-4d38-b573-c7bed75a9477_12af2876-ed38-4404-a753-c5abe35331cc] as ‘([Measures].[CUSTOM_a7ba172a-ce41-4d38-b573-c7bed75a9477_12af2876-ed38-4404-a753-c5abe35331cc])’

MEMBER [Measures].[CUSTOM_a7ba172a-ce41-4d38-b573-c7bed75a9477_bb983dd8-365c-45ff-846a-b0d4f47bfee9] as ‘(KPIStatus(“Internet Revenue”))’

MEMBER [Measures].[a7ba172a-ce41-4d38-b573-c7bed75a9477_bb983dd8-365c-45ff-846a-b0d4f47bfee9] as ‘([Measures].[CUSTOM_a7ba172a-ce41-4d38-b573-c7bed75a9477_bb983dd8-365c-45ff-846a-b0d4f47bfee9])’

MEMBER [Measures].[CUSTOM_a7ba172a-ce41-4d38-b573-c7bed75a9477_3829f331-b17f-4647-806c-8370d2266c56] as ‘(KPITrend(“Internet Revenue”))’

MEMBER [Measures].[a7ba172a-ce41-4d38-b573-c7bed75a9477_3829f331-b17f-4647-806c-8370d2266c56] as ‘([Measures].[CUSTOM_a7ba172a-ce41-4d38-b573-c7bed75a9477_3829f331-b17f-4647-806c-8370d2266c56])’

MEMBER [Measures].[CUSTOM_a7ba172a-ce41-4d38-b573-c7bed75a9477_26aa5e8c-4b17-44f2-b6f2-fb6adcf6b662] as ‘(KPITrend(“Internet Revenue”))’

MEMBER [Measures].[a7ba172a-ce41-4d38-b573-c7bed75a9477_26aa5e8c-4b17-44f2-b6f2-fb6adcf6b662] as ‘([Measures].[CUSTOM_a7ba172a-ce41-4d38-b573-c7bed75a9477_26aa5e8c-4b17-44f2-b6f2-fb6adcf6b662])’

MEMBER [Measures].[CUSTOM_a7ba172a-ce41-4d38-b573-c7bed75a9477_fc0ccba1-69e1-4ce0-85ba-0dc0fe814c4d] as ‘(KPIValue(“Internet Revenue”))’

MEMBER [Measures].[a7ba172a-ce41-4d38-b573-c7bed75a9477_fc0ccba1-69e1-4ce0-85ba-0dc0fe814c4d] as ‘([Measures].[CUSTOM_a7ba172a-ce41-4d38-b573-c7bed75a9477_fc0ccba1-69e1-4ce0-85ba-0dc0fe814c4d])’

SELECT { [Measures].[a7ba172a-ce41-4d38-b573-c7bed75a9477_12af2876-ed38-4404-a753-c5abe35331cc],

[Measures].[a7ba172a-ce41-4d38-b573-c7bed75a9477_bb983dd8-365c-45ff-846a-b0d4f47bfee9],

[Measures].[a7ba172a-ce41-4d38-b573-c7bed75a9477_3829f331-b17f-4647-806c-8370d2266c56],

[Measures].[a7ba172a-ce41-4d38-b573-c7bed75a9477_26aa5e8c-4b17-44f2-b6f2-fb6adcf6b662],

[Measures].[a7ba172a-ce41-4d38-b573-c7bed75a9477_fc0ccba1-69e1-4ce0-85ba-0dc0fe814c4d]} ON AXIS(0),

{([Date].[Fiscal].[Fiscal Quarter].&[2003]&[1]),

([Date].[Fiscal].[Fiscal Quarter].&[2003]&[2]),

([Date].[Fiscal].[Fiscal Quarter].&[2003]&[3]),

([Date].[Fiscal].[Fiscal Quarter].&[2003]&[4]),

([Date].[Fiscal].[Fiscal Quarter].&[2004]&[1]),

([Date].[Fiscal].[Fiscal Quarter].&[2004]&[2]),

([Date].[Fiscal].[Fiscal Quarter].&[2004]&[3]),

([Date].[Fiscal].[Fiscal Quarter].&[2004]&[4]),

([Date].[Fiscal].[Fiscal Quarter].&[2005]&[1]),

([Date].[Fiscal].[Fiscal Quarter].&[2005]&[2])} ON AXIS(1)

FROM [Adventure Works]

2: PPS creates a mining model in Analysis Services (note the SESSION keyword, which causes the model to be removed when the connection closes):

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

3: Training data is inserted into the model. This causes the model to be processed / trained.

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

4: Results from DMX query are returned. Note that two forecasted periods are requested:

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

That’s it. Hopefully this gives you a good general idea of how to create trend charts in Performance Point Server 2007 using your existing KPIs from Analysis Services, and of what’s going on behind the scenes.

SQL Server Analysis Services Connection Errors

Ever get the below error when trying to connect to Analysis Services?  From the looks of things at forums.microsoft.com, lots of folks do. 

The problem is the account that the “SQL Server Browser Service” uses to authenticate, which by default is the account selected for services during setup.  But this account needs to have administrator rights.  So if the “Network Service” account is used, the quick fix is to change the SQL Server Browser Service to run as “Local System” instead.  Have a look at this Books Online article for details on the minimum rights required for the browser service to work properly – you’ll want this account to have the least permissions required in a production environment.

But according to Microsoft: “To work properly, the SQL Server Browser service must run under a security account that has local administrator rights, such as the local system account.” (Pasted from http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspx)

This seems a bit ambiguous to me.  The books online article recommends using minimum permissions, but the technet article says you have to use an admin account.  On my laptop, the browser service appears to have all of the recommended rights, but will not let me connect until I run as Local System.  Strange but true.

Note: The SQL Server Browser Service in (SQL Server 2005) is only necessary when using named instances or a side-by-side installation with SQL Server 2000.  Otherwise, SQL Server setup does not configure the browser service to start automatically.

Business Intelligence Resources for SQL Server 2005 and PerformancePoint Server 2007

I’m attending an Advanced BI Training course this week.  Below are some books and online resources recommended by the instructor.  

Books:

• The Rational Guide To Monitoring and Analyzing with Microsoft Office PerformancePoint Server 2007
• Business Intelligence with Microsoft® Office PerformancePoint Server 2007
• Information Dashboard Design
• Fast Track to MDX 
• The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset
• MDX Solutions
• Data Mining with SQL Server 2005

Web Sites:

• http://www.TDWI.org (The Data Warehouse Institute)
• http://blogs.msdn.com/performancepoint/
• http://www.perceptualedge.com/ 
• http://www.sqlserverdatamining.com/

Tools:

• BIDS Helper : “A Visual Studio.Net add-ins with features that extend and enhance the functionality of the SQL Server 2005 BI Development Studio. (BIDS)”