Throughout this book, we have explored how the SharePoint platform can be used to provide a user interface for business intelligence (BI) and data analysis. Up to now, we have examined individual technologies such as Excel Services, Visio Services, and SQL Server Reporting Services as isolated solutions for specific needs. In this chapter, you will discover how PerformancePoint Services allows you to build integrated business intelligence solutions that bring these capabilities together into powerful interactive dashboards.

What Will You Learn?

By the end of the chapter, you’ll learn about

• Introduction to PerformancePoint Services
• The architecture of PerformancePoint Services
• Setting up PerformancePoint Services
• Authoring and publishing PerformancePoint solutions in SharePoint 2013
• Integrating PerformancePoint dashboards with Visio and Excel Services
• Managing PerformancePoint using PowerShell

Software Prerequisites

To get the most out of this chapter, you’ll need the following software:

• SharePoint Server 2013 Enterprise Edition
• Office Excel 2013 x64
• Office Visio 2013 x64
• SQL Server 2012 SP1 qx64
• AdventureWorks Database, which is downloadable at http://msftdbprodsamples.codeplex.com/releases/view/55330
• SQL Server 2012 Client Tools, which is downloadable at http://technet.microsoft.com/en-us/library/ms143219.aspx

Introduction to PerformancePoint Services

In the early years of this century, the “scorecard” became a popular business metaphor for bringing together related pieces of information from throughout the enterprise to provide decision makers with a high-level view of the business. Special-purpose scorecards like the “Balanced Scorecard” became popular, but there were no good tools available to deliver them. After a few attempts at getting Excel and web-based templates deployed, Microsoft published the Office Business Scorecard Manager 2005.

In 2006, Microsoft purchased ProClarity Corporation, thereby acquiring one of the premier business intelligence software companies. Microsoft immediately began integrating features of ProClarity’s rich server-based and client-based data analysis tools into its products.

When MS PerformancePoint Server was released in 2007, it contained two major modules: monitoring and analysis (M&A) and planning. The M&A component combined the features of the Business Scorecard Manager product with new capabilities brought in from ProClarity and saw good adoption. Use of the planning module, however, was sparse and in 2009, Microsoft dropped it due to lack of market interest. What remained of PerformancePoint was then rolled into the enterprise license of Microsoft Office SharePoint Server (MOSS) 2007. In fact, at that point, the MS PerformancePoint Server 2007 product could be loaded onto a MOSS server farm (with Enterprise Client Access Licenses, or CALs) without additional licensing.

In SharePoint 2010, PerformancePoint became a fully integrated service within the SharePoint environment. This has been carried forward to SharePoint 2013 as well. You get all of SharePoint’s administration and content-management tools along with the analytic abilities of PerformancePoint.

PerformancePoint Services Architecture

PerformancePoint Services is implemented using service application framework of SharePoint Server. In the same way that Excel Services and Visio Services run as separate service processes, so does PerformancePoint.

Setting Up PerformancePoint Services

Like the other services we have examined in this book, PerformancePoint Services are configured using Central Administration or PowerShell commands (cmdlets). Let’s take a look at the settings for PerformancePoint Services, along with the procedures for setting up a PPS instance.

The default, wizard-based installation of SharePoint Server 2013 includes an instance of the PerformancePoint Services application, so creating a new instance is not normally necessary. If you do need to create one, however, this can be easily accomplished through SharePoint Central Administration:

1. Open the Central Administration web site using the SharePoint 2013 Central Administration link available in the server’s Start menu.
2. Navigate to the Central Administration Application Management Service Applications Manage Service Applications page. Note the default instance of PPS highlighted in Figure 6-11.

   

   Figure 6-11. The Manage Service Applications page

3. To create a new PPS instance, select PerformancePoint Service Application from the New section of the ribbon, as shown in Figure 6-11. This brings up the New PerformancePoint Service Application dialog (shown in Figure 6-12).

   

   Figure 6-12. New PerformancePoint Service Application dialog

4. Set the name and application pool for the new PPS application instance, or select an existing one. Use a new application pool when you want to isolate PPS processing from other components within IIS.
5. Click Create.
6. After few moments, you will see a dialog box that says “PerformancePoint Service application was successfully created.” Click on OK.

It is now time to configure the application. If you are working with the default PPS instance, you should review the default settings, paying particular attention to the unattended service account, which should have the minimum privileges. See “Securing PerformancePoint Solutions” earlier in this chapter to review why the USA is important.

To begin configuring the service application, click on the name of the service “PPS Application” in our case) to be taken to the Manage PerformancePoint Services page as shown in Figure 6-13.

On the Manage PerformancePoint Services page, there are various settings to configure, which we’ll discuss now. Although most of the settings can be left to their default values, some need to be configured in order to address specific requirements.

Authoring and Publishing PerformancePoint Solutions

In this section, we will create all of the PerformancePoint content objects necessary to implement a typical business intelligence dashboard using data from the AdventureWorks sample database. Our solution will include the KPIs, scorecards, filters, reports, and dashboards necessary to provide the user with a rich data analysis environment. We will deploy this solution to SharePoint and explore the resulting user experience.

In the following section, “Advanced Report Types,” we will then expand on this solution by adding reports to the dashboard based on Excel Services and Strategy Map report types.

Deploying the Business Intelligence Center

We will create a work area for our solution by deploying the PerformancePoint Business Intelligence Center site template. First we must verify that the necessary features are activated at the site-collection level. You will need site-collection administrator rights in order to enable features and create the site:

1. Open the root web site in the site collection to host the solution.
2. Click on the Settings icon from the top right corner to invoke the drop-down menu. (See Figure 6-22.)

   

   Figure 6-22. Click on the settings icon from the top right corner

3. Select “Site Settings” from the drop-down menu. (See Figure 6-23.)

   

   Figure 6-23. Select “Site settings” from the drop-down menu

4. Select Site Collection Administration Site Collection Features. (See Figure 6-24.)

   

   Figure 6-24. Selecting site-collection features

5. Activate the SharePoint Server Enterprise Site Collection Features feature if it’s not already active. (See Figure 6-25.)

   

   Figure 6-25. Activating SharePoint Server Enterprise site-collection features

6. Activate the PerformancePoint Services Site Collection Features feature if it is not already active. (See Figure 6-26.)

   

   Figure 6-26. Activating PerformancePoint Services site-collection features

7. Navigate to the site under which you want to create the BI Center. In the parent site, create the site using the Business Intelligence Center site template. (See Figure 6-27.)

   

   Figure 6-27. Selecting the Business Intelligence Center site template

The site created has all of the lists and libraries needed to deploy a complete PerformancePoint solution. It also contains informational content describing the site’s purpose, which you can delete when you no longer need it.

Creating a Dashboard Designer Workspace

All of the objects we create for our solution will be stored in a Dashboard Designer Workspace file, which is stored on your local desktop with a DDWX file extension. This file acts as an offline store for these objects until you are ready to publish them to SharePoint. The workspace file is similar to a Visual Studio solution file in that it allows you to organize, edit, and manage all of the components of your solution in one place.

Because the Dashboard Designer is a one-click Windows application, there is no executable to download and install on the desktop. The easiest way to install it is simply to use it and then save a file. We will do this as part of the next section.

Creating a Data Source

Now let’s create a data source from which to retrieve business data for our dashboard:

1. Navigate to the BI Center site, and click on the Data Connections library link in the Quick menu to the left. This will take you to the data source library for the solution.
2. From the ribbon menu at the top of the page, select Files New Document PerformancePoint Data Source. (See Figure 6-28.)

   

   Figure 6-28. Adding a PerformancePoint data source

   This will launch the Dashboard Designer. If this is the first time you’ve used it, you’ll see messages and progress bars indicating that it’s installing. The Web browser might ask you to authorize the installation. Finally, the designer will be shown with an empty workspace and the “Select a Data Source Template” dialog displayed, as shown in Figure 6-29.

   

   Figure 6-29. Selecting a data source template

3. Our data will come from the Adventure Works OLAP database, so select the Analysis Services template and click OK, and you’ll see the dialog shown in Figure 6-30.  If you receive an error, follow the steps from http://technet.microsoft.com/en-us/library/ee748644.aspx to resolve the issue.

   

   Figure 6-30. The New Data Source screen

   Take a moment to find your way around the Dashboard Designer application—you’ll spend a lot of time here! At the top is a ribbon menu system that works like any Office 2013/Office 2010 application. The options that appear in the ribbon will change as the context of the central window changes.

   On the left side of the window is the Workspace Browser , which is a tree listing the contents of the workspace. Currently, this list shows one workspace file called Untitled Workspace, one SharePoint list called Data Connections, and one item within that list—a data source currently named New Data Source.

   The editor for the item selected in the list on the left is displayed in the center window where changes can be made. This window will have a set of tabs across the top based on the type of object being edited. The Properties tab is common to all objects and is used to name and organize objects within the workspace.

   To the right of the center window are panels that list the objects within the workspace that are related to the current item:

4. In the center window, under New Data Source Connection Settings Use Standard Connection, enter the name of the SSAS server instance containing the AdventureWorks OLAP cubes in the Server text box.
5. Select the “Adventure Works DW” SSAS database from the Database list.
6. At the bottom of Connection Settings, select the Adventure Works cube from the Cube list.
7. Note the defaults for the Data Source Settings panel, but don’t make any changes.
8. Switch to the Properties tab in the center window.
9. Enter “Adventure Works” for the name of the data source.
10. Switch to the Time tab, where you can specify the cube’s primary time dimension. (See Figure 6-31.)

    

    Figure 6-31. Selecting a time dimension

11. The Time Dimension drop-down menu will list all of the hierarchies available in the cube. Select “Date.Date.Fiscal.” This is a fiscal calendar that begins on the first of July of each year.
12. Under Reference Member, select a member from the dimension that represents the first day of the fiscal year, such as January 1, 2005.
13. For the Reference Date, enter the same date in your regional format. This allows PerformancePoint to understand how years are structured in the date dimension.

     Note  Depending on when you downloaded the AdventureWorks sample databases, the actual dates available in your cube might be different from those shown. The sample database used here has data from FY 2006 to FY 2009.

14. Under Time Member Associations, select the time dimension hierarchy levels as shown in Figure 6-32.

    

    Figure 6-32. The time dimension hierarchy

    At first glance, it might seem strange to enter all of this information about the time dimension in the editor you’re using to create a data source. The reason for doing this is that it allows the use of Time Intelligence filters. These very powerful tools will help when it comes time to present data to the user. See “Creating Filters” later in this chapter for details. Now that the data source is set up, let’s go back and finish setting up the workspace itself:

15. Select Untitled Workspace on the Workspace Browser.
16. Click the Save icon at the top of the window. This will cause the file save dialog to be displayed. (See Figure 6-33.)

    

    Figure 6-33. Saving the workplace

    If you go back to the Data Connections library in your BI Center site, you’ll see that there’s one data source there. Why is it called New Data Source? The answer is that you haven’t yet published the data source you created to SharePoint. You’ve only saved it to the workspace file.

     Tip  You can tell that an item hasn’t been published because there is a pencil superimposed on its icon in the Workspace Browser.

17. Select the Adventure Works data source in the Workspace Browser.
18. Click the Save icon again. The pencil icon will disappear from the item, and the data source should appear correctly in the Data Connections Library. (See Figure 6-34.)

    

    Figure 6-34. Saving the PerformancePoint data source to the Data Connections Library

Now that we have set up our data source and started our workspace file, let’s get ready to retrieve data for analysis.

Creating Key Performance Indicators

Our dashboard will contain one set of KPIs. We will create a set of actual and target metrics that will allow the marketing department to track the effectiveness of their promotions in terms of their effect on Gross Profit Margin.

Our workspace is currently connected to the Data Connections Library, but that area can be used only to store data sources. To create the KPI, you need to connect your workspace to the PerformancePoint Content list in the BI Center site:

1. Open the workspace file Marketing.ddwx that you created in the previous section.
2. In the Dashboard Designer’s ribbon menu, select Home Workspace Add Lists.
3. Select PerformancePoint Content from the BI Center site you created earlier, and click OK. (See Figure 6-35.) The PerformancePoint Content list now appears in the Workspace Browser.

   

   Figure 6-35. Selecting the PerformancePoint content

4. Right-click on the PerformancePoint Content list in the Workspace Browser, and select New KPI. (See Figure 6-36.)

   

   Figure 6-36. Creating a new KPI

5. Select Blank KPI from the “Select a KPI Template” dialog (shown in Figure 6-37), and click OK.

   

   Figure 6-37. Choosing a blank KPI template

6. The KPI will be created with the name selected in the Workspace Browser, so you can immediately type a name for the KPI. Enter “Profit Margin KPI” and press Enter.
7. Select the Properties tab, and set the Display Folder value to “KPIs.” (See Figure 6-38.)

   

   Figure 6-38. Setting the Display Folder

8. Note that the KPI now appears inside a folder in the Workspace Browser. (See Figure 6-39.)

   

   Figure 6-39. The Profit Margin KPI in the Workspace Browser

    Note  The use of display folders within the Dashboard Designer is entirely optional. Their purpose is only to help keep the solution organized. They have no effect on anything other than how items are displayed in the Workspace Browser. Workspace folders do not get reflected in the folder structure of the content list in PerformancePoint or anywhere else outside of Dashboard Designer. Typically, they are used to separate items by type (KPIs, Filters, Dashboards, etc.) or functionally by the part of the solution with which they are associated. Folders can be nested as needed by ­separating folder names with a backslash () character. For the rest of this chapter, items will be placed into such folders but the directions won’t mention it each time from here on out.

9. Switch to the Editor tab (shown in Figure 6-40), which displays the actual and target metrics for the KPI along with their various settings. We will customize the two metrics that were created by default and then add some of our own.

   

   Figure 6-40. Editing the KPI

10. Select the Name cell, which currently contains “Actual,” and change the name to “Gross Profit.”
11. Click on the cell for the same row under Data Mappings to launch the data mapping dialog. Currently, it is set to return a fixed value of 1. Click the Change Source button to bring up the “Select a Data Source” dialog. (See Figure 6-41.)

    

    Figure 6-41. Changing the data source

12. This dialog allows you to select the data source from which you will retrieve the value of the metric you are creating. In this case, the Gross Profit will come from the cube we’ve configured as a data source. Select “Adventure Works” and click OK. The Dimensional Data Source Mapping dialog is displayed. (See Figure 6-42.)

    

    Figure 6-42. Selecting a measure

13. This dialog allows the designer to select a measure from the cube and, optionally, perform filtering on it or enter an MDX query expression to retrieve the value. Select “Gross Profit” from the “Select a measure” drop-down list, and click OK. The result is shown in Figure 6-43.

    

    Figure 6-43. Mapping to the Gross Profit measure

14. Now we will create a target that indicates that we want to break even (GP >= $0) on each promotion. Set the name of the target metric to “Breakeven.” Then change the data mapping value to “0” instead of the default of “1.” Notice that the Compare To field points to the Gross Profit actual metric. This indicates that the target value, 0, will be compared to the actual value, which is the Gross Profit measure returned from the cube. (See Figure 6-44.)

    

    Figure 6-44. Creating a breakeven metric for Gross Profit

15. Now that the actual and target values are set, the Thresholds area at the bottom of the window is activated. The indicator type shown by default is not really appropriate, so we will customize it. Click on the Set Scoring Pattern and Indicator button in the Thresholds panel. This displays a three-step wizard we’ll use to configure the appearance of this target metric. (See Figure 6-45.)

    

    Figure 6-45. Selecting the scoring pattern

16. Select “Increasing is Better” and “Band by numeric value of Actual.” Click Next.
17. Select “Red to Black – Small” under the Miscellaneous category. (See Figure 6-46.) Click Next.

    

    Figure 6-46. Choosing the indicator

18. In this case, there is no selection to be made on the third step of the wizard, so click Finish.
19. The indicator we selected has four possible statuses: black, grey, light red, and red (as shown in Figure 6-47). Thresholds configure the points at which black becomes gray and so on. In this case, set the thresholds to the values shown in Fiqure 6-47. These values will be compared with the Gross Profit amount to show whether or not we are “in the black” or “in the red” for a particular promotion.

    

    Figure 6-47. Threshold values

20. Use the New Actual button to create two more actual metrics named “Total Sales” and “Gross Profit Margin.” Set the data mappings in the same way as for Gross Profit shown earlier. Map the new actual metrics to the “Sales Amount” and “Gross Profit Margin” measures, respectively.
21. On the Gross Profit Margin row, click on (Default) in the Number Format column. Set the value to be displayed as a Percentage using parentheses for negative numbers and 1 decimal place. (See Figure 6-48.)

    

    Figure 6-48. Setting the number format

22. Click on New Target to create a second target metric. In this case, instead of breaking even, our goal is to do better with a promotion than without one. Therefore, we will compare the gross profit margin obtained with a promotion to the margin obtained when the promotion is “No Discount.”
23. Set the name of the new target to “GP% vs. no Discount.”
24. Set the Compare to field to “Gross Profit Margin.”
25. Click on the link to set the data mapping, and select the Gross Profit Margin measure just as you did when setting the actual metric.
26. Instead of finishing the dialog, we will add a filter to find the value of the gross profit margin in a specific case. Start by clicking on the New Dimension Filter button under "Select a dimension". (See Figure 6-49.)

    

    Figure 6-49. Creating a filter

27. The Select Dimension dialog is somewhat misnamed. The list under Dimension isn’t really a list of dimensions. It is a list of dimension hierarchies that can be used to filter the selected measure. Select “Promotion.Promotion” and click OK.
28. Click on the “Default Member (All Promotions)” link. (See Figure 6-50.)

    

    Figure 6-50. Selecting the filter criteria

29. In the Select Members dialog, select only the No Discount member. This will cause the target value for this metric to include only data related to the No Discount promotion. Click OK.
30. Click on the “Set Scoring Pattern and Indicator” button. Review all of the default options but don’t change anything. Click Cancel to close the wizard.
31. Set the thresholds as shown in Figure 6-51.

    

    Figure 6-51. Setting the thresholds

32. Save the KPI and workspace file.

Creating a Scorecard

On our dashboard, we want to display a list of the promotions we’ve run and the values of our KPIs for each promotion. This is called a scorecard (shown in Figure 6-52), which we will create as a separate component in our workspace (inserted using the text markup feature):

1. Open the Marketing.ddwx workspace file.
2. Right-click on the PerformancePoint Content list in the Workspace Browser, and select New Scorecard.
3. From the “Select a Scorecard Template” dialog box, deselect the “Use Wizards to create scorecards” option and click the OK button.
4. Name the scorecard “Promotion Scorecard” (and add it to a display folder if you want).

   

   Figure 6-52. An empty scorecard

   The empty scorecard shown in Figure 6-52 has two main areas. The center window is where you design the scorecard; you will be able to arrange and format items in this window. To the right are panels that provide the elements to go on the scorecard. First, we identify the KPIs to be shown, and then we identify the rows and grouping to use in calculating the KPIs:

5. In the Details panel, open the KPIs branch of the tree until you reach the Profit Margin KPI. (See Figure 6-53.)

   

   Figure 6-53. The Profit Margin KPI

6. Drag the Profit Margin KPI from the Details panel to the header area of the scorecard. (See Figure 6-54.)

   

   Figure 6-54. Adding the KPI to the scorecard

7. Select Adventure Works from the drop-down list under Data Source. (See Figure 6-55.) Note that the Data Source panel is in the bottom right corner of the designer window, under the Details panel.

   

   Figure 6-55. Choosing Adventure Works as the data source

8. Under Details, select Dimensions Promotion Promotions. This is the main hierarchy of promotions that will allow us to roll up or drill down into our KPIs.
9. Drag the Promotions hierarchy onto the row area of the scorecard. This displays the Select Members dialog. (See Figure 6-56.)

   

   Figure 6-56. The Promotions hierarchy

10. Open the All Promotions node.
11. Right-click on the Customer node, and select Autoselect Members Select All Descendants. (See Figure 6-57.)

    

    Figure 6-57. Selecting members

12. Do the same for the Reseller node.
13. Select the All Promotions, Customer, and Reseller nodes, as well as the two “All descendants of” nodes. Do not select No Discount. (See Figure 6-58.)

    

    Figure 6-58. Confirming member selection

14. Click OK. The scorecard should now look like what’s shown in Figure 6-59.

    

    Figure 6-59. The scorecard with added elements

15. Right-click on the Gross Profit column header, and select Delete.
16. Right-click on the Gross Profit Margin column header, and select Delete.
17. Right-click on the Breakeven column header, and select Metric Settings.
18. Select “No Value” for Data Value and “Actual” for Additional Data Value. (See Figure 6-60.)

    

    Figure 6-60. Target settings for Breakeven

19. Review the other settings, but retain the defaults. Click OK.

    At this point, the scorecard will automatically be processed. It should look something like the one in Figure 6-61. The rows show the promotion hierarchy, including rolling up to parent levels. The columns show our KPIs as they are currently configured. Notice that rows are appearing for promotions even though there were no sales for those promotions.

    

    Figure 6-61. The processed scorecard

20. Right-click on the Total Sales column header, and select Filter Empty Rows. Note that the rows that should be filtered are now shown with red labels. (See Figure 6-62.) This indicates they will be filtered at runtime.

    

    Figure 6-62. Empty rows are red, indicating they will be filtered at runtime

21. Right-click on the “GP% vs. no Discount” column header, and select Metric Settings.
22. Select “Actual” for Data Value and Target for Additional Data Value.
23. Click OK.
24. Save the scorecard and workspace file.

Creating Filters

Our dashboard will contain two filters. The first filter will control the date range for the figures displayed. The second will allow us to select from among our sales territories.

1. Open the Marketing.ddwx file in Dashboard Designer.
2. Right-click on the PerformancePoint Content list Node, and select New Filter.
3. Select Time Intelligence from the “Select a Filter Template” dialog as shown in Figure 6-63. Be careful not to select “Time Intelligence with Connection Filter” by mistake.

   

   Figure 6-63. Creating a date filter

4. Click “OK.”
5. Click Add Data Source, select the Adventure Works data source, and click Next. (See Figure 6-64.)

   

   Figure 6-64. Selecting a data source for the filter

   For the “Enter time formula” step, we will define the options the user will have for selecting time periods. The formulas are entered on the left, and the name displayed to the user is on the right. The formulas are designed to output a list of members of the cube’s time dimension as specified in the data source. For a good overview of these formulas, go to blogs.msdn.com/b/performancepoint/archive/2010/01/21/time-intelligence-formula-quick-reference.aspx.

6. Enter the formulas and display names shown in Figure 6-65 and then click Next.

   

   Figure 6-65. Adding date formulas

7. Select List for the Display Method, and click Finish. (See Figure 6-66.)

   

   Figure 6-66. Selecting a display method for the date filter

8. Set the name of the filter to “Date.”
9. Save the filter and workspace.

   Now we’ll create a filter for selecting from the Sales Territory dimension. This filter will display a tree for the territory hierarchy and allow us to select one territory or higher-level region.

10. Right-click on the PerformancePoint Content list, and select New Filter.
11. Select the Member Selection filter template, and click OK.
12. Select the Adventure Works data source and click Next. (See Figure 6-67.)

    

    Figure 6-67. Selecting the data source for the filter

13. This displays the Select Members page of the wizard, where we will specify the members to show in the filter. (See Figure 6-68.)

    

    Figure 6-68. Selecting members for the filter

14. Click the Select Dimension button.
15. Select Sales Territory.Sales Territory and click OK. (See Figure 6-69.)

    

    Figure 6-69. Choosing the dimension

16. Click the Select Members button.
17. Right-click All Sales Territories (shown in Figure 6-70), and select Autoselect Members Select All Descendants.

    

    Figure 6-70. Selecting Sales Territory members

18. Select both “All Sales Territories” and “All descendants of All Sales Territories.” (See Figure 6-71.)

    

    Figure 6-71. Confirming the members

19. Click OK.
20. Select Tree from the Select Display Method page, and click Finish. (See Figure 6-72.)

    

    Figure 6-72. Choosing a display method for the Sales Territory filter

21. Change the name of the filter to “Sales Territory.”
22. Save the filter and workspace file.

Creating Reports

KPIs and scorecards allow you to compare business data against goals. Once you have identified the problem areas in the enterprise, you need to be able to understand the conditions that are causing problems. A “report” in PerformancePoint is a generic term referring to a window of information that has been sorted out based on the filters and scorecard selections made in the dashboard.

The two most common types of reports in PerformancePoint are Analytic Charts and Analytic Grids. We will create one of each for our dashboard.

1. Open the Marketing.ddwx file in Dashboard Designer.
2. Right-click on the PerformancePoint Content list, and select New Report.
3. Select the Analytic Grid template, and click OK. (See Figure 6-73.)

   

   Figure 6-73. Choosing a report template

4. Select the Adventure Works data source, and click Finish. (See Figure 6-74.)

   

   Figure 6-74. Choosing Adventure Works as the data source

5. Set the name of the report to “Margin by Sales Channel.” (See Figure 6-75.)

   

   Figure 6-75. The empty “Margin by Sales Channel” report

   The empty grid report window is divided into several areas. The draft view of the report appears in the center window. Beneath the center window are panels labeled Rows, Columns, and Background that will be used to specify the contents of the report. In the report designer, “background” refers to the ways in which the data in the report can be filtered.

   To the right is a Details tree from which we’ll select items to add to the report by dragging them onto one of the panels at the bottom of the window.

6. From the Details panel, drag Dimensions Sales Channel and drop it on the Columns panel.
7. From the Details panel, drag Measures Sales Amount and drop it on the Columns panel.
8. Drag and drop these measures to Columns as well: Total Product Cost, Gross Profit, and Gross Profit Margin.
9. From the Details panel, drag Dimensions Product Categories and drop it on the Rows panel.

   Now that we have defined the rows and columns for the report (as shown in Figure 6-76), it will automatically display a default view. In our dashboard, we want to see the product categories by default. We also want to be able to filter this report by date, territory, and promotion.

   

   Figure 6-76. Defining the report’s rows and columns

10. Right-click on Product Categories in the Rows panel, and choose Select Members.
11. Right-click on All Products, and select Autoselect Members Select Category.
12. Ensure that All Products and Category descendants of All Products are selected, and click OK. (See Figure 6-77.)

    

    Figure 6-77. Selecting members for the report’s default view

13. From Details, drag Dimensions Date Fiscal Fiscal to the Background panel. (See Figure 6-78.)

    

    Figure 6-78. Adding dimensions to the report’s default view

14. From Details, drag Dimensions Promotion Promotion to the Background panel.
15. From Details, drag Dimensions Sales Territory Sales Territory to the Background panel.
16. Save the report and workspace.

    Now let’s create an Analytic Chart report. This report will compare the gross profit margin across product categories.

17. Right-click on the PerformancePoint Content list, and select New Report.
18. Select the Analytic Chart template, and click OK.
19. Select the Adventure Works data source, and click Finish.

    At this point, the chart report looks almost exactly like the grid report. The difference is that the panels across the bottom are Series, Bottom Axis, and Background. (See Figure 6-79.)

    

    Figure 6-79. The empty analytic chart report

20. From the Details panel, drag Measures Gross Profit Margin and drop it on the Series panel.
21. From the Details panel, drag Dimensions Product Category and drop it on the Bottom Axis panel.
22. From Details, drag Dimensions Date Fiscal Fiscal to the Background panel.
23. From Details, drag Dimensions Promotion Promotion to the Background panel.
24. From Details, drag Dimensions Sales Territory Sales Territory to the Background panel. (See Figure 6-80.)

    

    Figure 6-80. Adding dimensions to the Background panel

25. Right-click anywhere in the center window, and select Format Report Don’t Show.
26. Right-click anywhere in the center window, and select Show Information Bar.

    The Information Bar is a useful feature on reports. When this is turned on, it shows the user exactly how the data in the report is being filtered. In the designer, the bar shows “No background selections exist” because there are no filters applied until the report is embedded in a dashboard. (See Figure 6-81.)

    

    Figure 6-81. The Information Bar turned on

27. Set the name of the report to “Margin by Category.”
28. Save the report and workspace.

Creating a Dashboard

We have created all of the components for our dashboard. Now we’ll put it all together and make it work as an integrated package.

1. Open the Marketing.ddwx file in Dashboard Designer.
2. Right-click on the PerformancePoint Content list, and select New Dashboard.
3. Select the default page template: Header, 2 Columns, as shown in Figure 6-82.

   

   Figure 6-82. The default page template

4. Click OK.
5. Set the name of the dashboard to “Marketing Dashboard.”

   This will create an empty dashboard page with three zones. (See Figure 6-83.) At the top of the center window is a list of the pages in the dashboard. Beneath the pages is the Dashboard Content panel. This is where the selected dashboard page will be assembled from the components we’ve created. At the right, the Details panel contains the components that can be used.

   

   Figure 6-83. The empty dashboard page

6. Set the name of the dashboard’s initial page to “Sales Summary” in the Pages panel.
7. On the Details panel, open the Filters node until you find Date and Sales Territory filters. Drag and drop both filters onto the Header zone of the page.
8. On the Details panel, open the Scorecards node until you find the Promotion Scorecard. Drag and drop it onto the Left Column zone.
9. On the Details panel, open the Reports node until you find the “Margin by Category” and “Margin by Sales Channel” reports. Drag and drop both reports onto the Right Column zone.

   The components are now on the page (as shown in Figure 6-84), but they are not integrated with one another.

   

   Figure 6-84. The dashboard page with components

   By dragging fields from one component to another, data is passed to synchronize or filter data throughout, or between, dashboard pages. The behavior we want is as follows:

   • •  When a date range is selected in the filter, it should be applied to all components.
   • •  When a Sales Territory is selected in the filter, it should be applied to all components.
   • •  When the user clicks on a row or cell in the scorecard on the left, the reports on the right should be filtered to display data only for that promotion.

   Now we will create the connections to create this behavior:

10. Hover over the Date filter. A panel will drop down showing all of the fields that are available from this filter. Drag Member Unique Name, and drop it onto the scorecard.
11. The default connection is to Page, which refers to the scorecard’s overall filter. (See Figure 6-85.) Click OK.

    

    Figure 6-85. Creating connections between dashboard items

12. Drag and drop the same field from the Date filter onto the two report components. The reports will default the connection to the Date Fiscal background field that is specified in the reports.
13. From the Sales Territory filter, drag the Member Unique Name field to the scorecard. Again, Page is the connection field.
14. Drag and drop the same field from the Sales Territory filter onto the two report components. When the connection dialog appears, select Sales Territory for the “Connect to” field. (See Figure 6-86.)

    

    Figure 6-86. Connecting to the Sales Territory filter

15. From the Promotion Scorecard, drag Row Member Member Unique Name onto each report. Verify that the connection dialog shows that the field is connected to the Promotion field. (See Figure 6-87.)

    

    Figure 6-87. Connecting to the Promotion field

16. The dashboard is now complete. (See Figure 6-88.) Save the dashboard and the workspace.

    

    Figure 6-88. The completed dashboard

Deploying the Solution to SharePoint, and Exploring

We are now ready to deploy our dashboard and start analyzing data. Remember that the dashboard we created in the previous section is only the definition for a dashboard. That definition is stored in our PerformancePoint Content list. When we deploy the dashboard, the definition is converted into a folder of ASPX pages that implement our dashboard.

Note that PerformancePoint uses Silverlight controls to render some of the UI controls. Now would be a good time to download and install the latest version of Silverlight from www.microsoft.com/silverlight.

1. Open the Marketing.ddwx file in Dashboard Designer.
2. Right-click on the Marketing Dashboard item, and select Deploy to SharePoint as shown in Figure 6-89.

   

   Figure 6-89. Deploying to SharePoint

3. Since this is the first time we’ve deployed the dashboard, Dashboard Designer will prompt for a destination location for the dashboard.
4. Select the Dashboards document library in the BI Center site (shown in Figure 6-90), and click OK.

   

   Figure 6-90. Putting the dashboard in the Dashboards library

5. After a few seconds, a web browser window will open to the Sales Summary page of the dashboard. (See Figure 6-91.)

   

   Figure 6-91. The Sales Summary page

6. Select a time period in the Date filter that contains data. The scorecard and both reports are filtered accordingly.
7. Select a Sales Territory. Notice the text displayed in the Information Window below the “Margin by Category” chart. (See Figure 6-92.)

   

   Figure 6-92. Text in the information window

8. Close the web browser, and open a new window to view the dashboard. Note that the filter values selected in the previous session are remembered. (See Figure 6-93.)

   

   Figure 6-93. Values from the previous session are remembered

9. Click on a row on the scorecard. Note the changes that occur on the reports to the right. (See Figure 6-94.)

   

   Figure 6-94. Click a row on the scorecard to modify the reports

10. Right-click on one of the cells in the "Margin by Sales Channel" report and select Show Details. A window is displayed containing all of the data that went into that cell. This data can also be exported to Excel for further analysis. (See Figure 6-95.)

    

    Figure 6-95. Exporting data to Excel

11. Right-click on one of the cells in the “Margin by Sales Channel” report, and select Decomposition Tree. A window is displayed that allows the user to decompose the results in the cell in any way needed to discern patterns. (See Figure 6-96.)

    

    Figure 6-96. Decomposing a cell into multiple levels

Create an Excel Services Report

Microsoft Excel is one of the most widely used packages for doing numerical analysis. As a file-based desktop application, however, it is limited in its ability to reach a large audience and integrate with other solutions. As you saw in Chapter 5, SharePoint Server 2013 incorporates Excel Services to address these issues by creating a server environment for storing, processing, and delivering Excel content. With PerformancePoint Services, you have the opportunity to leverage Excel’s analytical abilities and familiar user interface to integrate rich reports into your PPS solutions.

In this section, we will create a simple Excel Services spreadsheet and deploy it to SharePoint. Then, we will integrate the elements of that spreadsheet into our existing dashboard as a pair of new reports on a new page. The new page will allow us to view a grid and a chart with a breakdown of our sales figures by Sales Territory. Be prepared, there ar quite a few steps.

1. Launch Excel 2013, and create a new blank workbook.
2. On the ribbon menu, select Data From Other Sources From Analysis Services.
3. Enter the name of the SSAS server containing the Adventure Works DW database as shown in Figure 6-97.

   

   Figure 6-97. Selecting the database server to connect to

4. Click Next.
5. Select the Adventure Works DW database.
6. Select the Adventure Works cube.
7. Click Next.
8. Enter “AWExcel.odc” for the File Name. (See Figure 6-98.)

   

   Figure 6-98. Entering a name for the data connection file

9. Enter “Adventure Works DW – Excel” for the Friendly Name.
10. Click Finish.
11. Select PivotTable Report (as shown in Figure 6-99), and click OK.

    

    Figure 6-99. Choosing how to view data in the workbook

12. From the Pivot Table Field List (shown in Figure 6-100), drag the fields listed in Table 6-1 into the indicated panel at the lower right of the Excel window.

    

    Figure 6-100. The Pivot Table Field List

    Table 6-1. Pivot Table Fields

    Field	Panel
    Date Fiscal Date.Fiscal	FILTERS
    Promotion Promotions	FILTERS
    Sales Territory Sales Territory	ROWS
    Sales Summary Sales Amount	VALUES

    The spreadsheet should now look like the image in Figure 6-101.

    

    Figure 6-101. The pivot chart

13. Select the Table ,and click on Recommended Charts from the INSERT ribbon menu.
14. Select “Pie” chart from the left Side, and click OK.
15. Right-click on the chart title, and select Delete.
16. From the ribbon menu, select DESIGN and choose Style 6. (See Figure 6-102.)

    

    Figure 6-102. Selecting the Chart type

17. From the ribbon menu, select the ANALYZE option and enter “SalesChart” into the Chart Name box. (See Figure 6-103.)

    

    Figure 6-103. Entering the chart name

18. Click on one of the cells in the PivotTable.
19. From the ribbon menu, select PivotTable Tools Options PivotTable. Enter “SalesTable” into the PivotTable Name box. (See Figure 6-104.)

    

    Figure 6-104. Entering the name of the pivot table

20. Select the “Sales Amount” column cells on the PivotTable.
21. Select Home Conditional Formatting Data Bars, and select one of the options shown. (See Figure 6-105.)

    

    Figure 6-105. Selecting a style for the data bars

22. Click on cell B1. This should be the All Periods value for the Date.Fiscal filter. Be sure to select the value cell (B1), not the label cell (A1).
23. Click ANALYZE from the Ribbon menu, type “Date” into the name box, and press Enter. (See Figure 6-106.)

    

    Figure 6-106. Setting up conditional formatting for the Date filter

24. Click on cell B2. This should be the All Promotions value for the Promotions filter. Be sure to select the value cell (B2), not the label cell (A2).
25. Type “Promotions” into the name box, and press Enter.

    The Excel spreadsheet is now ready to be published to SharePoint. For simplicity, we will store it in the Dashboards library in our BI Center site (though it could be stored in any location trusted by Excel Services). First, we’ll create a copy of the data connection we’re using.

26. From the ribbon menu, select Data Connections Properties.
27. Select the Definition tab. (See Figure 6-107.)

    

    Figure 6-107. Copying the data connection

28. Click the Export Connection File button.
29. In the File Save dialog, navigate to the Data Connections library in the BI Center site.
30. Set the file name to “AWExcel” and click Save. (See Figure 6-108.)

    

    Figure 6-108. Naming the data connection file

31. Select Office Data Connection File as the Content Type, and click OK. (See Figure 6-109.)

    

    Figure 6-109. Selecting the content type of the data connection file

32. Click OK on the Connection Properties dialog.

    Before we publish the spreadsheet to SharePoint, we need to identify the objects and parameters to expose to Excel Services. The objects we’ll use are the SalesChart and SalesTable objects. We will declare the Date and Promotions cells as parameters, which PerformancePoint will use to pass in dashboard filter selections.

33. From the ribbon menu, select File Save As SharePoint. (See Figure 6-110.)

    

    Figure 6-110. Saving to SharePoint

34. Click on Browser View Options. (See Figure 6-111.)

    

    Figure 6-111. Selecting the “Browser View Options”

35. Select “Items in the Workbook” from the drop-down list. (See Figure 6-112.)

    

    Figure 6-112. Choosing the items that will be displayed in the browser

36. Select the SalesChart and SalesTable items.
37. Switch to the Parameters tab. (See Figure 6-113.)

    

    Figure 6-113. Adding parameters

38. Click the Add button.
39. Select both available parameters, and click OK from “Add Parameters” dialogue box.
40. Click OK from “Broswer View Options” dialogue box.
41. If the Dashboards library is not shown under Locations, select Browse for a location to add it.
42. Double-click the location to publish the file to SharePoint.
43. Enter the file name “AWExcelRpt” in the Save As dialog, and click Save.
44. Select Document as the content type, and click OK.
45. Navigate to the SharePoint documents folder, and click on the “AWExcelRpt.”
46. Close the new browser window and Excel.

    Now that we have a spreadsheet published to SharePoint, we will add the PivotTable and PivotChart as reports in a new page on our dashboard.

47. Open the Marketing.ddwx file in Dashboard Designer.
48. Right-click on the PerformancePoint Content list, and select New Report.
49. Select the Excel Services template, and click OK.
50. Set the name of the report to “Sales Table.”
51. Enter the URL for the BI Center site in the “SharePoint site” box on the report editor. (See Figure 6-114.)

    

    Figure 6-114. Adding a new Sales Table report

52. Select the Documents library.
53. Select “AWExcelRpt.xlsx” file for the workbook.
54. Select “SalesTable” for the item name.
55. Note that the Dashboard Designer has already identified the Workbook Parameters: Date and Promotions.
56. Copy and paste the Sales Table report in the Workspace Browser.
57. Rename “Sales Table – Copy” to “Sales Chart.”
58. Select SalesChart from the “Item name” drop-down on the Sales Chart report editor.
59. Save both reports and the workspace file.
60. Right-click on the PerformancePoint Content list, and select New Filter.
61. Select the Member Selection filter template, and click OK.
62. Select Adventure Works for the data source, and click Next.
63. Click Select Dimension, and choose Promotion.Promotions.
64. Click OK.
65. Click Select Members, and select All Promotions and all of its descendants. (See Figure 6-115.)

    

    Figure 6-115. Selecting the All Promotions member

66. Click OK to move to the next screen. (See Figure 6-116.)

    

    Figure 6-116. Creating a filter

67. Click Next.
68. Select Tree, and click Next.
69. Name the new filter Promotion.

    We now have the new filter and reports created for our new page. Next we’ll create the new dashboard page and deploy the dashboard.

70. Select the Marketing Dashboard in the Workspace Browser.
71. Click New Page in the dashboard editor, and select the default template.
72. Name the page “Sales by Promotion”.
73. From Details, drag the Date and Promotion filters into the header of the new page.
74. From Details, drag the Sales Table report into the left column zone.
75. From Details, drag the Sales Chart report into the right column zone.
76. Add the connections shown in Table 6-2 to the page.

    Table 6-2. Connections for the Dashboard

    

    The dashboard page should now resemble the image in Figure 6-117.

    

    Figure 6-117. The dashboard page

77. Right-click the Marketing Dashboard, and select “Deploy to SharePoint.”
78. When the browser window opens, click on the “Sales by Promotion” link.

    Notice that all of the formatting from Excel is displayed on the dashboard, including the pie chart and data bars. (See Figure 6-118.) Now any changes made to the Excel spreadsheet will automatically be reflected in the dashboard. This allows users who are more comfortable with Excel to author reports that can be integrated with the rest of a PerformancePoint solution.

    

    Figure 6-118. Excel reports displayed in the browser

Create a Strategy Map Report

Microsoft Visio is a powerful desktop visualization tool. As you saw in Chapter 2, the Visio Services component in SharePoint Server 2013 allows Visio diagrams to be brought to life with real data. With PerformancePoint Services, you can take that integration one step further. A Visio diagram can be created that visually represents the KPIs on a scorecard. Those KPIs can then be connected to the Visio diagram to create a graphical representation of the status of the enterprise. In PerformancePoint, this type of report is called a “strategy map.”

In this section, we will create a simple Visio diagram that we’ll deploy to PerformancePoint as a Strategy Map report. The term “strategy map” refers to a particular type of diagram often used in conjunction with the “balanced scorecard” methodology. While this feature in PerformancePoint is ideally suited for implementing that type of map, any Visio diagram can be used as long as it contains only simple shapes that don’t involve sets or groupings within Visio.

1. Launch Visio 2013, and create a new blank diagram using the Basic Diagram (US Units) template.
2. Create a diagram with a set of simple shapes that looks something like the image in Figure 6-119. The precise details of the diagram are not important.

   

   Figure 6-119. A simple Visio diagram

3. Save the file with the file name Promotion Tree.vsd anywhere on your local computer.
4. Close Visio.
5. Open the Marketing.ddwx file in Dashboard Designer.
6. Right-click on the PerformancePoint Content list, and select New Report.
7. Select the Strategy Map template from the “Select a Report Template” dialog, and click OK.
8. Select the Promotion Scorecard as the scorecard for the strategy map. (See Figure 6-120.) The Strategy Map report uses a scorecard as a data source instead of a normal data source object.

   

   Figure 6-120. Creating a strategy map report using the Promotion Scorecard

9. Click Finish.
10. Name the new report “Promotion Map.”
11. From the ribbon menu, select Edit Report Editor Edit Strategy Map. (See Figure 6-121.) If your operating system is 64 bit, ensure you have installed a 64-bit version of Visio 2013.

    

    Figure 6-121. The Strategy Map Editor

12. Click the Import Visio File button, and choose Promotion Tree.
13. To connect the Reseller Promotions shape to the scorecard, click on the shape and then on the Connect Shape button as shown in Figure 6-122.

    

    Figure 6-122. Connecting the Reseller Promotions shape to the scorecard

14. The Connect Shape dialog shows a view of the underlying scorecard. Select the cell at the intersection of the Reseller row and the “GP% vs. no Discount” column.
15. Ensure that the “Show KPI name in shape text” check box is not selected, and click the Connect button.
16. Click Close and the Reseller Promotions shape will now be colored the same as the KPI.
17. Repeat steps 14 though 16 for each of the other shapes on the diagram. Your colors might vary depending on the filters that are active in your environment. The important thing is to connect the proper shape to the correct KPI.
18. Drag the cursor over the diagram to select all of the shapes.
19. Right-click one of the shapes, and select Data Edit Data Graphic.
20. Click the New Item button.
21. On the New Item dialog, select the options shown in Figure 6-123.

    

    Figure 6-123. Values for the data graphic

22. Click OK to create the Data Graphic.
23. Select “Below Shape” in the Vertical option, and click OK to save Data Graphics.
24. Answer “Yes” to “Do you want to apply this data graphic to the selected shapes?”
25. Now the map should look something like Figure 6-124.

    

    Figure 6-124. The shapes with the data graphic applied

26. Click Apply.
27. Save the Promotion Map report and the workspace.
28. Select the Marketing Dashboard in the Workspace Browser.
29. Add a new page to the dashboard with the default template.
30. Name the page “Promotion Map.”
31. Add the components in Table 6-3 to the zones of the new page.

    Table 6-3. Components for the New Page

    Component	Zone
    Date Filter	Header
    Sales Territory Filter	Header
    Promotion Scorecard	Left Column
    Promotion Map Report	Right Column

32. Create the connections shown in Table 6-4.

    Table 6-4. Connections for the New Page

    
33. The dashboard should now look like Figure 6-125.

    

    Figure 6-125. The Marketing Dashboard

34. Save the dashboard and workspace.
35. Ensure the “Include page List for navigation” is selected in the Properties tab and then Deploy the dashboard to SharePoint.

    Try selecting different values for the Date and Sales Territory filters. (See Figure 6-126.) Both the scorecard and the map are updated simultaneously.

    

    Figure 6-126. Trying out the strategy map

There are several features of the map to note:

• The end user can zoom in and out as desired by right-clicking on the map.
• By holding Ctrl and clicking on a shape, users can select that shape. Right-clicking a selected shape provides the option to view a detailed list of KPI properties.
• While it is common to display the scorecard associated with a strategy map on the same page, it’s not required. The scorecard is still an active part of the dashboard even when it’s not visible on the current page. Therefore, you could remove the scorecard from this page and the only effect would be to hide it. The strategy map would still function correctly.

There is an important limitation when using strategy maps that can be handled with a little additional planning. In our example, if we select a different date range and sales territory, we might see rows on the scorecard that don’t appear on the map, or we might see rows on the scorecard disappear due to a lack of data. These shapes still appear on the map, but they don’t show a color. When we connect shapes to the scorecard, that connection is static. We can’t change that association as needed to fit how the scorecard is filtered. We also can’t add shapes as new rows appear in the scorecard.

There are different strategies for handling such situations. One way is to include every possible shape on the map. You could also limit the filtering of the page to prevent unwanted rows from appearing in the scorecard. Regardless, the shapes on the map and how they are associated are fixed at runtime.

Strategy maps are a versatile way to add visualization to your solution. They can be used to provide a user-friendly means of interpreting your key performance indicators.

Summary

In this chapter, you explored PerformancePoint Services as outlined in Figure 6-127, including

• The components of the PerformancePoint Services architecture.
• How to configure PerformancePoint Services using both Central Administration and PowerShell commands.
• How to enable the features of PerformancePoint Services within a SharePoint site and deploy the Business Intelligence Center.
• How to use Dashboard Designer to author and deploy a dashboard and its supporting components to SharePoint 2013.
• How to integrate PerformancePoint Dashboards with diagrams using Visio Services and spreadsheets using Excel Services.