Chapter 7. PerformancePoint Services Development

IN THIS CHAPTER

• Data Connections

• Building Key Performance Indicators

• Developing Scorecards

• Creating Reports

• Filtering Data

• Constructing Dashboards

• User Interaction with the Decomposition Tree

PerformancePoint Services is a highly visual, interactive, analytical component of Microsoft’s business intelligence (BI) stack. Its main purpose is to surface data from established data sources to allow insight into how a company is performing using different metrics that are deemed valuable for the targeted audience. Through the use of dashboard development, managers and analysts can analyze trends, performance indicators, and analytical reports all in one page and then have the option to drill into a specific area of the data to ask and answer questions about company performance.

With PerformancePoint Services embedded into SharePoint 2010 Enterprise, all development is done from within a SharePoint site using Dashboard Designer. In addition, new items have been added to the toolset (for example, the Decomposition Tree and KPI Details report) as has improved functionality to existing objects such as scorecards, analytic charts, and analytic reports. With the latest release of PerformancePoint Services, the toolset can easily be seen as a robust analytical tool that is a must-have for any company in any industry.

This chapter details the development of the different PerformancePoint Services content using Dashboard Designer.

Using Dashboard Designer

Developing PerformancePoint Services content is all done from Dashboard Designer, which is installed as a component of SharePoint 2010 Enterprise. From Dashboard Designer, data connections are established, content is developed, and the content is then saved to a SharePoint list (and in the case of dashboards, they are then deployed to a SharePoint library). In addition, content that has been developed and saved to the SharePoint web application can be reused for new development.

To open Dashboard Designer, you just navigate from a menu off of a Business Intelligence Center site or create a new item in a PerformancePoint content list. To bring up Dashboard Designer from a Business Intelligence Center site, complete the following steps:

  1. Put the cursor over the Monitor Key Performance, Build and Share Reports, or Create Dashboards menu on the Business Intelligence Center default home page, and then click the Start Using PerformancePoint Services link.
  2. On the PerformancePoint Services Samples page, click the Run Dashboard Designer button.
  3. For the initial run, Dashboard Designer needs to install, and a prompt appears asking to install the program. Click the Install button.

After the installation completes, Dashboard Designer starts up, as shown in Figure 7.1.

Figure 7.1. Dashboard Designer main screen.

image

To redirect to Dashboard Designer from a PerformancePoint content list, complete the following steps:

  1. Navigate to the Business Intelligence Center site and click the PerformancePoint Content menu on the left.
  2. Click the List ToolsItems menu.
  3. Click the New Item button on the menu or click the Add New Item link on the list.

A Dashboard Designer Redirect screen appears with a message stating Loading Dashboard Designer and Dashboard Designer then starts up, as shown in Figure 7.1.

Dashboard Designer is broken down via objects that have been created and saved in the SharePoint web application or objects that are local development, which is considered the workspace. Dashboard Designer is made up of three menus and two categories. The menus (Home, Edit, and Create) manipulate the item within the category (data connections or PerformancePoint content), in the workspace. After the workspace content has been developed, saving the item publishes the item to the SharePoint site in a PerformancePoint Services content-enabled list, which is then available for reuse in multiple dashboards.

PerformancePoint content can be organized via created display folders from within Dashboard Designer. After the item is saved to a SharePoint list, the items are organized by content type within the SharePoint list itself. For all Dashboard Designer objects, the managing of the item’s Display Folder is done in the Properties tab of the item. In the Properties tab, there is a textbox where you can type in the name of the display folder, which if it does not exist will create a new folder. You can also click on the Browse button next to the textbox and then select an existing folder or click the Create button to create the new folder. After creating or selecting the display folder, the name of the folder will appear in the Display Folder textbox. In addition, the folder will also appear in the menu on the left under either the Data Connections or PerformancePoint Content category, depending on the item being updated.

For the Data Connections category, the following items are available to be selected and used for a PerformancePoint content object:

• Analysis Services: Connects to a SQL Server Analysis Services cube

• Excel Services: Connects to an Excel Services file in SharePoint

• Import from Excel Workbook: Connects to a local Excel file that has static numeric data

• SQL Server Table: Connects to a SQL Server database table

For the PerformancePoint Content category, the following objects are available to be developed:

• KPI: Creates a key performance indicator to be used on a scorecard

• Filter: Creates an object that will limit data in other objects within a SharePoint page

• Report: Creates a graphical or columnar representation of the data

• Dashboard: Creates a SharePoint page that displays a single or multiple PerformancePoint content objects and can combine those content objects with other SharePoint objects, such as an Excel Services file or Reporting Services report

• Indicator: Creates a series of object icons to be used in KPIs

• Scorecard: Creates a form for displaying KPIs

The remainder of the chapter explains how to develop the different PerformancePoint Services content using Dashboard Designer.

Creating Data Connections

For all PerformancePoint content, a data connection is required to be used for the content to perform. PerformancePoint Services has many different types of data connections available to use, and certain content objects can use only a specific data connection object. You can create a data connection by right-clicking Data Connections and selecting New Data Source or clicking the Dashboard Designer Create menu on the ribbon and clicking the Data Source button.

When the Select a Data Source Template screen appears, you have your choice of which data connection type to create. The following sections explain how to create each data connection type.

Analysis Services Data Connection

An Analysis Services Data Connection provides access to a SQL Server Analysis Services (SSAS) cube for consumption in a dashboard. An SSAS cube is made up of many measures and dimensions which allow for quick analytical processing of data via different aggregations and drill downs. The cube is developed via SQL Server Business Intelligence Studio (BIDS) and processed onto a server that is running SSAS. To create an Analysis Services data connection to an existing SSAS cube, complete the following steps:

  1. Click the Analysis Services template.
  2. Click the Editor tab; the default selection is the Use Standard Connection. To directly supply the connection string, skip steps 3–5.
  3. Supply the server name.
  4. Select the database on the supplied server.
  5. Supply the SSAS cube role that the connection is going to impersonate. This is optional and can be left blank.
  6. If supplying the connection string manually, click the Use the Following Connection option and supply the connection string.
  7. Select the cube.
  8. In the Authentication menu

    • Leave the unattended service account; the default uses the Secure StoreUnattended Service Account configuration as set up in the Central Administration.

    • Clicking the Unattended Service Account and Add Authenticate User Name in Connection String option results in the same configuration being used but adds an extra custom property for the username

    • Clicking the Per-User Identity requires a Kerberos setup of SharePoint and SSAS to authenticate the user and retrieve only privileged data.

  9. Click the Properties tab.
  10. Supply the name of the data connection object.
  11. Enter a display folder name, example Data Sources, for organizing the Dashboard Designer workspace file.
  12. Fill in the description of the connection (optional).
  13. Add any custom properties to pass to the SSAS cube by clicking the New Property button, selecting the type, and supplying the value.
  14. Click the Time tab to associate the time dimension within the cube to the Analysis Services data source.
  15. Select the time dimension member from the time dimension in the cube.
  16. Select the beginning of the year for the selected time dimension. If a calendar year, this value is the 1/1/2010 value, for example. If using a fiscal year, this value represents the first day of the fiscal year.
  17. Pick the hierarchy level that the data represents.
  18. In the reference date, enter a date that represents the selected date.
  19. From the Time Member Associations menu, select the time aggregation for each member of the time hierarchy available.

    Figure 7.2 shows an example of the completed Time tab.

    Figure 7.2. Analysis Services Time tab.

    image

  20. After finishing adding the properties of the connection, click the Editor tab, and then click Test Data Source.
  21. Click the Save button.

    Figure 7.3 shows a completed Analysis Services connection.

    Figure 7.3. Analysis Services data connection.

    image

Excel Services Data Connection

An Excel Services Data Connection provides an Excel workbook as a data source for building PerformancePoint Services content items. The Excel workbook is managed by SharePoint and is available to other authorized users on the SharePoint site where the Excel workbook is hosted. To create an Excel Services data connection for connecting to an existing Excel Services file, complete the following steps.

  1. Click the Excel Services template.
  2. Supply the SharePoint site to connect.
  3. Select the library within the SharePoint site containing the Excel Services file.
  4. Select the Excel workbook from the document library.
  5. Either fill in the item name or, if available, select the item name from the drop-down.

    Note

    The item name is going to map to a named range or table within the Excel Services file. If you are connecting to an Excel 2007 workbook, type in the item name; it will not appear in the Item Name drop-down.

  6. Click the Properties tab.
  7. Supply a name of the data source.
  8. Enter a display folder name, example Data Sources, for organizing the Dashboard Designer workspace file.
  9. Fill in a description of the data source (optional).
  10. Click the Time tab.
  11. If applicable, supply the time dimensional data.
  12. Click the View tab to see the data from the Excel file.
  13. Click the Editor tab.
  14. Click the Test Data Source button.
  15. Click the Save button.

Figure 7.4 shows a completed Excel Services connection.

Figure 7.4. Excel Services data connection.

image

Import from Excel Workbook Data Connection

The Import from Excel Workbook data connection allows for loading of a local Excel into PerformancePoint to then be used by a PerformancePoint Services content item. The Import from Excel Workbook connection differs from the Excel Services data connection in that the imported Excel file is not managed by SharePoint, changes to the local file are not sent back to the connection after the import succeeds, and PerformancePoint stores the imported data. To create an Import from Excel Workbook data connection, complete the following steps:

  1. Click the Import from Excel Workbook template.
  2. Click the Import button, navigate to the local Excel workbook to import the data, and then click Open.

    Note

    After the workbook is imported, a local copy is created within Dashboard Designer and can be modified only from the Edit tab of the data source. The original workbook can change its data, and the data modification will have no effect on the imported data.

  3. On the Edit Data in Excel pop-up screen, uncheck the default Headers in First Row check box or leave checked and click Accept Changes.
  4. On the View tab, review the fields and make any modifications in the Properties menu for each column:

    • Add a column name.

    • Add a unique column name.

    • Select a column type to state whether a column is a dimension, fact, key, time dimension, or to ignore the column.

    • If the column type is fact, select the aggregation method.

    • If the column is a dimension or time dimension, select the column that is of the type key that represents the key for the column.

  5. Click the Properties tab.
  6. Supply a name of the data source.
  7. Enter a display folder name, example Data Sources, for organizing the Dashboard Designer workspace file.
  8. Fill in a description of the data source (optional).
  9. Click the Save button.

Figure 7.5 shows a completed Import from Excel Workbook connection.

Figure 7.5. Import from Excel Workbook data connection.

image

SharePoint List Data Connection

The SharePoint List data connection allows content that exists in a SharePoint List to be manipulated via a PerformancePoint content item. The data itself is seen as a Tabular reference so Analytical reports cannot use the data source. However, the data is available for KPIs and other types of reports. To create a SharePoint List data connection, complete the following steps:

  1. Click the SharePoint List template.
  2. Supply the SharePoint site to connect.
  3. Select the SharePoint site list.
  4. Select the SharePoint list from the list collection.
  5. Click the View tab and update the property for each column.
  6. Click the Preview Data button to review the list data.
  7. Click the Editor tab and click the Test Data Source button.
  8. Click the Properties tab.
  9. Supply a name of the data source.
  10. Enter a display folder name, example Data Sources, for organizing the Dashboard Designer workspace file.
  11. Fill in a description of the data source (optional).
  12. Click the Save button.

Figure 7.6 shows a completed SharePoint list connection.

Figure 7.6. SharePoint list data connection.

image

SQL Server Table Data Connection

A SQL Server Table data connection enables a SQL Server table or view to be consumed by a PerformancePoint content item. To create a SQL Server Table data connection, complete the following steps:

  1. Click the SQL Server Table template.
  2. If using the Use Standard Connection option, supply the server name and click the database on the Database drop-down.
  3. If using the Use the Following Connection option, supply the connection string.
  4. Select the table from the Table drop-down.
  5. Click the Properties tab.
  6. Supply a name of the data source.
  7. Enter a display folder name, example Data Sources, for organizing the Dashboard Designer workspace file.
  8. Fill in a description of the data source (optional).
  9. Click the View tab and update the property for each column.
  10. Click the Preview Data button to test the data.
  11. Click the Editor tab and keep the Unattended Service Account option selected or click the Per-User Identity button if a Kerberos configuration is in place.
  12. Click the Test Data Source button.
  13. Click the Save button.

Figure 7.7 shows a completed SQL Server table connection.

Figure 7.7. SQL Server table data connection.

image

Building Key Performance Indicators

Key performance indicators (KPIs) are graphical representations of the “health of a company” tracking metric. KPIs are designed individually for each metric and contain properties for how the graph should represent the metric based on a comparison of an actual value to a target value. In Dashboard Designer, two types of KPIs can be created: Blank KPI and Objective. After the KPI has been developed, the KPI is placed onto a scorecard for display in a dashboard.

Blank KPI

A Blank KPI is for comparing an actual measurement to a target value for determining the indicator of the measurement. To create a Blank KPI, complete the following steps:

  1. In the PerformancePoint Content category, either click the Create menu and click the KPI button or right-click the PerformancePoint Content menu and select NewKPI.
  2. On the Select a KPI Template screen, click Blank KPI and click OK.
  3. For the Actual row, the Compare To column is left blank. The Target row’s Compare To column defaults to the name of the Actual row.
  4. Click the Default link in the Number Format for both the Actual and Target row and supply the property values on the Format Numbers screen.
  5. In the Data Mappings column for the Actual and Target row, click the original 1 (Fixed Values) link. The default of 1 for a Fixed Value data mapping is displayed. To hard code a different value of what the row should represent, change the value to the appropriate value and click OK and skip to step 12. For KPIs that tie the row item to data source values, click the Change Source button.
  6. On the Select a Data Source screen, click the data connection to use either within the workspace or on the SharePoint site. In addition, a calculated metric can be used as the source.

    Calculated metrics are new to PerformancePoint Services. Clicking the Calculated Metric tab brings up templates for commonly used metrics such as gross profit margin and customer retention rate. Within the metric, a data source is chosen for the source of the fields that are being used in the calculation.

  7. Select a measure that represents the Actual and Target value from the data source for the KPI. If there are any filters to be placed on the KPI via a dimensional value, click the New Dimension Filter button.
  8. Click the dimension to filter by on the Select Dimension screen.
  9. Click the Default All Members link and select the dimension values to filter and then click OK.
  10. If adding a Time Intelligence filter, click the New Time Intelligence Filter button.

    Note

    The data source must set up Time Intelligence for this filter option to be configured correctly.

  11. Supply the time formula. The formula is set via the following syntax:

    [(]<Period>[<Offset>[)][.<Function>[<Offset>]]]

    Examples:

    Previous Day: day-1

    Last 10 days including today: day:day-9

    Parallel year: (year-1).day

  12. Click the Preview button for validation of the formula. Figure 7.8 shows a sample time formula.

    Figure 7.8. Time Formula Editor example.

    image

  13. Click the OK button.
  14. Select the aggregation method in the Aggregate Members By drop-down.
  15. To supply an MDX tuple formula, click the Use MDX Tuple Formula check box and add the MDX statement.
  16. Click the OK button.
  17. For the Target row, click the Indicators column and click the Set Scoring Pattern and Indicator button on the Thresholds screen.
  18. On the Select Scoring Pattern screen, click the Scoring Pattern from the three types:

    • Increasing Is Better: The higher the number is from the worst score (0 for sales, for example), the better.

    • Decreasing Is Better: The lower away the number is from the worst score (1 million returns, for example), the better.

    • Closer to Target Is Better: The closer to the target number (daily machine uptime hours, for example), the better.

  19. Select the banding method from the three types:

    • Band by Normalized Value of Actual/Target: Depending on the scoring pattern, the ratio of distance from the actual to the worst divided by distance from the target to the worst. If decreasing is better, subtract the ratio from 1.

    • Band by Numeric Value of Actual: Using the actual value to determine the bands.

    • Band by Stated Score: Using a stated score, such as a variance, to determine the banding.

  20. Click Next.
  21. Select the indicator and click Next.

    The third screen will vary based on the banding method:

    • If the banding method is Band by Normalized Value of Actual/Target, enter the worst score and click Finish.

    • If the banding method is Band by numeric value of Actual, enough information is present; click Finish.

    • If the banding method is Band by Stated Score, click the Specify Data Mapping button, enter a fixed value for the stated score, or click the Change Source button and select the data source, click OK, and then select the measure that will be used as the stated score.

  22. Based on the scoring pattern and banding method, fill out the BestWorstThresholds for the KPI.
  23. In the Calculation column for the Actual and Target row, click the Default Value link.
  24. For a KPI developed using an Analysis Services data source, select the Data Value calculation and click OK. The Data Value calculation allows the Decomposition Tree to be enabled when the KPI is used in a scorecard.
  25. New to PerformancePoint Services is the ability to add multiple actuals and targets to the KPI and relate the new row back to other existing target and actual values. To add a new actual or target, click the New Actual or New Target button and develop the row as needed.
  26. Enter a display folder name, example KPIs, for organizing the Dashboard Designer workspace file.
  27. Click the Save button.

Figure 7.9 shows a completed KPI.

Figure 7.9. Key performance indicator.

image

Objective

An Objective is for rolling up multiple KPIs for an overall combined score. Objectives can roll up to a higher objective level as well. Making use of objects allows for clean interface of management of lower level KPIs and provides the user with a quick view of how a subject is performing. Follow these steps to create an objective:

  1. In the PerformancePoint Content category, either click the Create menu and click the KPI button or right-click the PerformancePoint Content menu and select NewKPI.
  2. On the Select a KPI Template screen, click Objective and click OK.
  3. Click the Properties tab and supply a name for the objective.
  4. Enter a display folder name, example KPIs, for organizing the Dashboard Designer workspace file.
  5. Because the purpose of the Objective is to roll up child KPIs for a combined score, click the Save button. The objective will be a used as a header KPI in scorecard development, and the calculation method will default to a No value.

Developing Scorecards

A scorecard, in its simplest form, is the container for displaying KPIs within a dashboard. However, when developed properly, scorecards group like KPIs into multiple categories to display a bigger picture of the overall status of an entity.

In PerformancePoint Services, new features have been added to allow for drilling into the data via KPI Details reports and viewing Decomposition Trees, flexible layout options, and the inclusion of dynamic hierarchies in the display.

Follow these steps to create a scorecard:

  1. In the PerformancePoint Content category, either click the Create menu and click the Scorecard button or right-click the PerformancePoint Content menu and select NewScorecard.
  2. On the Select a Scorecard Template screen, there are three categories of templates: Microsoft, Standard, and Tabular.

    • The Microsoft category creates a scorecard from an Analysis Services data source. Selecting the Analysis Services scorecard allows for interaction with the Decomposition Tree.

    • The Standard category allows for selecting either a blank scorecard or a fixed values scorecard:

    • A Blank Scorecard template presents a screen where KPIs and Dimensions are dragged and dropped into the scorecard.

    • A Fixed Values Scorecard template is a list of fixed value KPIs.

    • The Tabular category allows for selecting from an Excel Services, Excel workbook, SharePoint list, or a SQL Server table:

    • Excel Services template creates a scorecard from an Excel Services data source. The KPIs are then developed based on the data within the Excel Services file.

    • Excel Workbook template creates a scorecard from an Excel workbook data source. The KPIs are then developed based on the data within the Excel workbook that has been stored within PerformancePoint Services at the time of import.

    • SharePoint List template creates a scorecard from a SharePoint list data source. The KPIs are then developed based on the data within the SharePoint list.

    • SQL Server Table template creates a scorecard from a SQL Server table data source. The KPIs are then developed based on the data within the SQL Server table.

    Click the Microsoft category, the Analysis Services template, and then click OK.

  3. Click the Analysis Services data source to use for the scorecard, and then click Next.
  4. On the Select a KPI Source screen, two options display:

    • Create KPIs from SQL Server Analysis Services Measures

    • Import SQL Server Analysis Services KPIs

    If KPIs have been developed within the Analysis Services cube, click the Import SQL Server Analysis Services KPIs, and then click Next. If creating a new KPI from measures within the cube, keep the default option selected and click Next. To import previously built workspace or SharePoint site KPIs, either option allows the next screen to import the KPIs.

    Note

    It is recommended that KPIs have either been developed within the cube or previously developed in Dashboard Designer before you create the scorecard.

  5. If the Create KPIs from SQL Server Analysis Services measures option was selected, click Add KPIs for each measure to create the new KPI against. If the Import SQL Server Analysis Services KPIs was selected, check each cube KPI to import.
  6. Click the Select KPI button to import KPIs from the workspace or SharePoint site.
  7. Select each KPI and click OK. After all KPIs are createdimported and selected, click the Next button.
  8. On the Add Measure Filters screen, to allow for dashboard filters to limit the data, check the Add Measure Filters check box.
  9. Click Select Dimension in the Member Filters and select the dimension for the filter.
  10. Click the Select Members button for the actual measure filter, check the members to filter the scorecard, and then click OK.
  11. Click the Select Members button for the target measure filter, check the members to filter the scorecard, and then click OK.
  12. To add Time Intelligence, enter a time formula in both the actual and target measure filters.
  13. Click Next.
  14. Check the Add Column Members check box for scorecards that will present a column header.
  15. Click the Select Dimension button, select the dimension for the column header. In PerformancePoint Services, a hierarchy can be selected to allow user interaction with the scorecard.
  16. Click the Select Members button, select the members for the column headers, and click OK.
  17. Click Next.
  18. On the Locations screen, select the SharePoint list where the created KPIs will be stored.
  19. Click Finish.

    The wizard completes and displays the original creation of the scorecard with all KPIs created and selected at the same level, as shown in Figure 7.10.

    Figure 7.10. Wizard scorecard.

    image

  20. For those KPIs that have been created as objectives, move the KPIs in an organized header location. Select the objective KPI and click the up or down arrows on the Edit menu to the desired location.
  21. For those KPIs that are considered child KPIs of the objective KPIs, click the up or down arrows on the Edit menu to the desired location underneath the objective and click the Indent button to associate the child KPI to the objective KPI.
  22. After the scorecard has been adjusted, click the Update button on the Edit menu to display the KPI data.
  23. Click the Properties tab and supply a name for the scorecard.
  24. Enter a display folder name, example Scorecards, for organizing the Dashboard Designer workspace file.
  25. Click the Save button.

Figure 7.11 shows a completed formatted scorecard.

Figure 7.11. Formatted scorecard.

image

Creating Reports

Essential to every organization is the ability to view different metrics and activity to help analysts understand the who, where, what, how, and why an event happened, which can then lead to a business decision to help further growth, stop negative actions, or maintain a profitable situation. Within PerformancePoint Services, several different types of reports can be created to provide users the corporate story for making these business decisions.

Based on the report type, users can build custom views of the report, filter the report, and drill into different slices of the data. In addition, PerformancePoint Services enables users to connect to external websites, view Excel Services workbooks, and display other company reports from other reporting servers that use SQL Server Reporting Services or ProClarity Analytics Server.

The following report types are available in PerformancePoint Services:

• Analytic Chart: Connects to a SQL Server Analysis Server data source to create bar charts, pie charts, and line charts. The Analytic Chart report allows for drilling into the chart to gain deeper insight into the data.

• Analytic Grid: Connects to a SQL Server Analysis Server data source to create reports that display the data in a rows-and-columns grid.

• Strategy Map: Connects a scorecard for interaction with a Microsoft Office Visio 2007 or later file.

• KPI Details: Displays detailed information about a KPI.

• Reporting Services: References a report stored on a SQL Server Report Server.

• Excel Services: Displays a new view of an Excel Services workbook.

• ProClarity Analytics Server Page: References an existing ProClarity Analytics Server page.

• Web Page: References an existing web page.

Analytic Chart

For enhanced user interaction and the display of graphical reports, the Analytic Chart report is the report of choice most common in PerformancePoint Services dashboards. The Analytic Chart allows for user interaction to drill into the data that makes up the chart information from within the SSAS cube. The Analytic Chart also exposes custom drill through actions that have been defined in the SSAS cube. To create an Analytic Chart report, complete the following steps:

  1. In the PerformancePoint Content category, either click the Create menu and click the Analytic Chart button or right-click the PerformancePoint Content menu and select NewReport. In either case, the Select a Report Template screen displays. Click Analytic Chart, and then click OK.
  2. On the Create Analytic Chart Report screen, click the Analysis Services data source to use for the report and click Finish.

    You can supply custom MDX for the report on the Query tab.

    Note

    Adding custom MDX into the query disables the ability for the report to use the Decomposition Tree feature.

  3. On the Design tab, drag the measures and dimensions needed for the report into the Series and Bottom axis.
  4. If any filtering is planned against the report for a dimension value that is not going to have an axis to display data, drag that dimension into the Background.

    Note

    To display dimension values in the Series and Bottom Axis and not have the measure name appear in the legend, move the measure to the Background.

  5. The default chart is a bar chart. Click the Edit menu and click Report Type to change the type.
  6. The default is not to show the information bar. Click Settings on the Edit menu and check the Show Information Bar check box to display it.
  7. To filter out empty items from the Series and Bottom axis, click the Filter button on the Edit menu and click the Filter Empty Series and Filter Empty Axis items.
  8. Click the Properties tab and supply a name.
  9. Enter a display folder name, example Reports, for organizing the Dashboard Designer workspace file.
  10. Click the Save button.

Figure 7.12 shows a completed Analytic Chart report.

Figure 7.12. Analytic Chart report.

image

Analytic Grid

In step with the Analytic Chart report is the Analytic Grid report, in the terms of interchangeability for users by just changing the report type. Instead of displaying the data in a chart format though, the Analytic Grid displays that data in rows in columns. The measurements contained within the chart are then available for drilling as if right-clicking within an Analytic Chart. Follow these steps to create an Analytic Grid report:

  1. In the PerformancePoint Content category, either click the Create menu and click the Analytic Grid button or right-click the PerformancePoint Content menu and select NewReport. In either case, the Select a Report Template screen displays. Click Analytic Grid, and then click OK.
  2. On the Create Analytic Chart Report screen, click the Analysis Services data source to use for the report and click Finish.

    You can supply custom MDX for the report on the Query tab.

    Note

    Adding custom MDX into the Query will disable the ability for the report to use the Decomposition Tree feature.

  3. On the Design tab, drag the measures and dimensions needed for the report into the rows and columns.
  4. If a report is being developed for a Dashboard that has known dimension filters but the grid being developed does not want to display that filtered dimension value, drag the dimension being used in the filter to the Background section of the report. By adding the dimension to the background in the report, the Dashboard filter can then link to the report and pass the filter’s value to the report for limiting the data in the grid.

    Note

    To measure values in the grid and not have the measure appear in a column or row header, move the measure to the Background. However, dragging multiple measures into the grid automatically moves the measures to the column axis.

  5. The default is not to show the information bar. Click Settings on the Edit menu and check the Show Information Bar check box to display it.
  6. To filter out empty items from the rows and columns, click the Filter button on the Edit menu and click the Filter Empty Rows and Filter Empty Columns items.
  7. Click the Properties tab and supply a name.
  8. Enter a display folder name, example Reports, for organizing the Dashboard Designer workspace file.
  9. Click the Save button.

Figure 7.13 shows a completed Analytic Grid report.

Figure 7.13. Analytic Grid report.

image

Strategy Map

A Strategy Map report is a Visio diagram that organizes company goals graphically via a flowchart of steps that overall represent the organization’s strategy. In combination with a scorecard, the strategy map objects connect to KPIs to display how the strategy is performing. Follow these steps to create a Strategy Map report:

  1. In the PerformancePoint Content category, either click the Create menu and click the Other Reports button or right-click the PerformancePoint Content menu and select NewReport. In either case, the Select a Report Template screen displays. Click Strategy Map, and then click OK.
  2. On the Create a Strategy Map screen, select the scorecard to use as the data for the Visio diagram and click Finish.
  3. Click the Edit menu and click Edit Strategy Map.
  4. Click Import Visio File and navigate to the Visio diagram and click Open.
  5. In the Visio diagram, click the shape to connect to a KPI and click the Connect Shape button.
  6. On the Connect Shape screen, click the KPI and click the Connect button and then click Close.
  7. Repeat steps 5 and 6 for each shape in the Visio diagram to tie to a KPI.

    Hint

    If using Visio Professional 2010, you can switch between the Strategy Map Editor and the Connect Shape without having to close the window. You can then repeat steps 5 and 6 for all KPIs by clicking the Connect button and then moving to the next KPI to be mapped.

  8. After connecting all shapes, click Apply.
  9. Click the Properties tab and supply a name for the strategy map.
  10. Enter a display folder name, example Reports, for organizing the Dashboard Designer workspace file.
  11. Click the Save button.

Figure 7.14 shows a completed Strategy Map report.

Figure 7.14. Strategy Map report.

image

KPI Details

New to PerformancePoint Services is the ability to create a KPI Details report. The KPI Details report allows the drilling of information about the KPI from a scorecard. Follow these steps to create a KPI Details report:

  1. In the PerformancePoint Content category, either click the Create menu and click the KPI Details button or right-click the PerformancePoint Content menu and select NewReport. In either case, the Select a Report Template screen displays. Click KPI Details and then click OK.
  2. On the Editor tab, check the KPI sections within the tab for displaying the details about a KPI in reference to the checked category. When a scorecard connects to the KPI Details report in a dashboard, each KPI on the scorecard is then available to be clicked. Clicking on the connected scorecard’s KPI will display the checked KPI Details report category information within the KPI Details report.
  3. Click the Properties tab and supply a name.
  4. Enter a display folder name, example Reports, for organizing the Dashboard Designer workspace file.
  5. Click the Save button.

Figure 7.15 shows a completed KPI Details report.

Figure 7.15. KPI Details report.

image

Reporting Services

Within the Microsoft BI stack, SQL Server Reporting Services is another complementary toolset that brings information to the user community to enhance informed decision making. Because Reporting Services reports could prove useful in combination with other PerformancePoint content items in a dashboard, the ability to view a specific report is created by connecting to the report itself on the Report Server. To link to a Reporting Services report, complete the following steps:

  1. In the PerformancePoint Content category, either click the Create menu and click the Reporting Services button or right-click the PerformancePoint Content menu and select NewReport. In either case, the Select a Report Template screen displays. Click Reporting Services and then click OK.
  2. On the Editor tab, supply the Report Server report settings.

    • Select the server mode: SharePoint Integrated or Report Center.

    • If SharePoint Integrated, supply the Report Server URL and the full path of the report RDL filed as stored in the SharePoint site.

    Note

    The Report Server URL is the path to the actual report server for the Integrated SharePoint site and is usually of the form http://sharepointserver/reportserver. In configuring a site for SharePoint integrated mode, the path supplied in setting up the Reporting Services server is the same information supplied in this step of the creating the report.

    • If Report Center, supply the server name and click Browse to select the report.

    • Check on or off the Show Toolbar, Show Parameters, and Show DocMap check boxes.

    • Click the Zoom percent of the report.

    • Click the Format for presentation of the report.

    • Supply the Report Parameter values.

  3. Click the Properties tab and supply a name.
  4. Enter a display folder name, example Reports, for organizing the Dashboard Designer workspace file.
  5. Click the Save button.

Excel Services

Excel workbooks being maintained by SharePoint within Excel Services allow for the sharing of workbook data amongst a business group. Accessing these Excel Services workbooks and displaying other PerformancePoint content items within a singular dashboard enables users to gain even better insight and analytics. To display an Excel Services Workbook report, complete the following steps:

  1. In the PerformancePoint Content category, either click the Create menu and click the Other Reports button or right-click the PerformancePoint Content menu and select NewReport. In either case, the Select a Report Template screen click is presented. Click Excel Services and click OK.
  2. Supply the SharePoint site containing the Excel Services workbook.
  3. Select the document library.
  4. Select the Excel workbook and click View to display the workbook.
  5. Select an item name of the table or named range to narrow the view of the data from the workbook.
  6. Click the Properties tab and supply a name.
  7. Enter a display folder name, example Reports, for organizing the Dashboard Designer workspace file.
  8. Click the Save button.

ProClarity Analytics Server Page

Before PerformancePoint Services, there was ProClarity and Microsoft Office Business Scorecard Manager. Microsoft has discontinued support for Microsoft Office Business Scorecard Manager because the application was rarely implemented. However, not all companies have removed the ProClarity instance altogether and therefore still contain valuable information to be relayed back to the user community. PerformancePoint Services allows for the inclusion of this information by linking to the ProClarity Analytics Server. Follow these steps to link to a ProClarity Analytics Server page:

  1. In the PerformancePoint Content category, either click the Create menu and click the Other Reports button or right-click the PerformancePoint Content menu and select NewReport. In either case, the Select a Report Template screen click is presented. Click ProClarity Analytics Server Page and click OK.
  2. In the Server URL field, supply the ProClarity server URL.
  3. Click the Browse button.
  4. Leave the Use Windows Credentials check box checked or uncheck it and supply the credentials to connect to the server.
  5. Select the page and click OK.
  6. In the configuration options, select the option to turn off a feature of user interaction (optional):

    • ad; (Disable Decomposition Tree)

    • dd; (Disable Drill to Detail)

    • ad;dd; (Disable Decomposition Tree and Drill to Detail)

    • st; (All Interaction Disabled)

  7. Click the Properties tab and supply the name for the report.
  8. Enter a display folder name, example Reports, for organizing the Dashboard Designer workspace file.
  9. Click the Save button.

Web Page

Outside web pages are useful in providing external information to further enhance the dashboard story being told. PerformancePoint Services provides this capability. The following steps outline how to link to a web page, which is then stored as a report within PerformancePoint Services content:

  1. In the PerformancePoint Content category, either click the Create menu and click the Other Reports button or right-click the PerformancePoint Content menu and select NewReport. In either case, the Select a Report Template screen click is presented. Click Web Page and click OK.
  2. In the URL text box, supply the URL.
  3. Click the Properties tab and supply the name for the report.
  4. Enter a display folder name, example Reports, for organizing the Dashboard Designer workspace file.
  5. Click the Save button.

Filtering Data

Filters within PerformancePoint Services enable users to limit data to one or more PerformancePoint Service content items within a dashboard, provided those content items have been set up to use the filter. In the SQL Server Reporting Services world, you can consider the filter the same as applying a parameter for the report; but for PerformancePoint Services, the filter can alter the data for multiple objects simultaneously.

The following filter types are available in PerformancePoint Services:

• Custom Table: Uses a table of information from a SQL Server, Excel workbook, Excel Services, or SharePoint list data source as its filter values

• MDX Query: Connects to a SQL Server Analysis Server data source and uses custom MDX to supply the filter values

• Member Selection: Connects to any data source that has dimension fields defined to use as the filter values

• Named Set: Connects to a SQL Server Analysis Server data source containing named sets, which are then used as the filter values

• Time Intelligence: Uses a data source that has time intelligence set up and creates a formula to be used as the filter values

• Time Intelligence Connection Formula: Uses a data source that has time intelligence set up and uses a calendar control to be used as the filter values

To create a filter, in the PerformancePoint Content category either click the Create menu and click the Filter button or right-click the PerformancePoint Content menu and select NewFilter. When the Filter Template screen appears, click the type of filter to build. Repeat this step for creating any type of filter to be used in a dashboard.

Custom Table

A Custom Table filter allows for retrieving data values from a tabular data source as an item to limit other PerformancePoint Services content in a dashboard. To create a Custom Table filter, follow these steps:

  1. On the Select a Filter Template screen, select the Custom Table filter and click OK.
  2. Select a data source and click Next.
  3. On the Preview Table screen, review the data and click Next.
  4. On the Choose Key Columns screen

    • Select a key for the filter.

    • Select a parent key for the key item. The parent key is used to generate a filter tree.

    • Select the display value.

    • Select the Is Default column.

  5. Click Next.
  6. Select a display method for the filter values:

    • List: Displays all items as one list, and only one item can be selected

    • Tree: Displays the filter values in a tree structure, but only one item can be selected

    • Multi-Select Tree: Displays the filter values in a tree structure, but allows for multiple selections to be used as the filter value

  7. Click Finish.
  8. Click the Properties tab and supply a name.
  9. Enter a display folder name, example Filters, for organizing the Dashboard Designer workspace file.
  10. Click the Save button.

MDX Query

An MDX Query filter is an MDX Set statement against an SSAS data source for retrieving dimensional members on the dashboard. One major advantage for creating an MDX Query filter is that you can limit specific dimension values based upon a condition without having to update the SSAS cube. To create an MDX Query filter, follow these steps:

  1. On the Select a Filter Template screen, select the MDX Query filter and click OK.
  2. Select a data source and click Next.
  3. Enter the MDX set definition that is to be used as the filter values and click Next.
  4. Select a display method for the filter values.
  5. Click Finish.
  6. Click the Properties tab and supply a name.
  7. Enter a display folder name, example Filters, for organizing the Dashboard Designer workspace file.
  8. Click the Save button.

Member Selection

A Member Selection filter makes dimension members available from an SSAS data source to drill into PerformancePoint content items on a dashboard. When defining the filter, you select the dimension, the specific members of the dimension and the default member for the filter. To create a member selection filter, follow these steps:

  1. On the Select a Filter Template screen, select the Member Selection filter and click OK.
  2. Select a data source and click Next.
  3. Click the Select Dimension button.
  4. On the Select Dimension screen, select the Dimension field and click OK.
  5. Click the Select Members button.
  6. On the Select Members screen, check the members for the filter. To set the member to use as the default, right-click the member and select Set as Default Selection.
  7. Click OK.

    The default member selected on the Select Members screen should appear in the Default Member Selection text box.

  8. Click Next.
  9. Select a display method for the filter values.
  10. Click Finish.
  11. Click the Properties tab and supply a name.
  12. Enter a display folder name, example Filters, for organizing the Dashboard Designer workspace file.
  13. Click the Save button.

Named Set

A Named Set filter makes available an SSAS Named Set that has been defined within the SSAS cube. A named set is a calculated member that creates a limited list of dimension members based upon a condition of the data. An example of a named set would be a Top 10 Salesperson set that is determined by Total Sales. To create a Named Set filter, follow these steps:

  1. On the Select a Filter Template screen, select the Named Set filter and click OK.
  2. Select a data source and click Next.
  3. Select the named set from the cube and click Next.
  4. Select a display method for the filter values.
  5. Click Finish.
  6. Click the Properties tab and supply a name.
  7. Enter a display folder name, example Filters, for organizing the Dashboard Designer workspace file.
  8. Click the Save button.

Time Intelligence

The Time Intelligence filter allows you to enter a Time Intelligence formula expression as the filter to be passed to a PerformancePoint content item in a dashboard. Examples of a Time Intelligence formula are day:day-9 (for range of last ten days) and yeartodate. To create a Time Intelligence filter, complete these steps:

  1. On the Select a Filter Template screen, select the Time Intelligence filter and click OK.
  2. Click the Add Data Source button.
  3. Select a data source and click OK.
  4. Click Next.
  5. Add a time formula expression into the Formula text box and supply a name for the formula.
  6. Click the Preview button to validate the formula.
  7. Repeat steps 5 and 6 for each formula to add for the filter.
  8. Click Next.
  9. Select a display method for the filter values.
  10. Click Finish.
  11. Click the Properties tab and supply a name.
  12. Enter a display folder name, example Filters, for organizing the Dashboard Designer workspace file.
  13. Click the Save button.

Time Intelligence Connection

The Time Intelligence Connection filter presents the user with a calendar to pick a date from and evaluates the Time Intelligence expression against the chosen date. To create a Time Intelligence Connection Formula filter, complete the following steps:

  1. On the Select a Filter Template screen, select the Time Intelligence Connection Formula filter and click OK.
  2. Click the Add Data Source button.
  3. Select a data source and click OK.
  4. Click Next.

    The Time Intelligence Calendar Tool will be highlighted.

  5. Click Finish.
  6. Click the Properties tab and supply a name.
  7. Enter a display folder name, example Filters, for organizing the Dashboard Designer workspace file.
  8. Click the Save button.

Constructing Dashboards

The dashboard is the user interface that connects all the PerformancePoint content into a singular analytic tool for user consumption. The dashboard is made up of filters, reports, and scorecards, which are displayed in zones as the dashboard organization.

To create a dashboard, in the PerformancePoint Content category either click the Create menu and click the Dashboard button or right-click the PerformancePoint Content menu and select NewDashboard. When the Dashboard Page Template screen appears, click the originating page layout template to use. However, after selecting a template, the dashboard can have zones added, split, or deleted by right-clicking in a zone and performing an action. The list of options is displayed in Figure 7.16.

Figure 7.16. Dashboard zone manipulation.

image

The main building of a dashboard is done by dragging content from the Details section into a dashboard zone. After you design the dashboard layout and fill the zones with content items, the following actions are performed in the Editor tab to finish the interactive section of the dashboard development:

• Connecting filters to dashboard content objects

• Linking a KPI Details report to a scorecard

• Creating a multiple pages within the dashboard

• Deploying the dashboard to SharePoint

When you’ve completed the Editor tab, click on the Properties tab and update the name of the dashboard, add the display folder, example Dashboards, for organizing the Dashboard Designer workspace. Select the Document Library that the dashboard will deploy to in the SharePoint site and select a SharePoint site’s master page for formatting the dashboard itself to conform to the rest of the SharePoint site pages.

Connecting Filters to Dashboard Content Objects

Connecting filters to dashboard content items is performed by setting up the content’s connection to the value from the filter. The following steps illustrate how to set up a filter connection:

  1. For each PerformancePoint content item that is going to have its data affected by the filter, on the right side of the item in the zone there is a drop-down arrow. Click the arrow and select Create Connection. The menu is shown in Figure 7.17.

    Figure 7.17. Create Connection menu.

    image

    Hint

    Connecting a filter to a PPS content item in a zone can be achieved by hovering over the filter to display the filter’s list of properties, such as Member Unique Name, and then dragging that property onto the PPS content item’s Drop fields to create connections section (or if a connection already exists, this section is named Connections).

  2. On the Connection screen, select the filter in the Get Values From drop-down, as shown in Figure 7.18.

    Figure 7.18. Filter connection.

    image

  3. Click the Values tab and select the data item from the content that will have the data filtered and the source value from the filter to use as the supplied value. If using an Analysis Services data source as the filtered item, the connection will usually be done on the member unique name, as shown in Figure 7.19.

    Figure 7.19. Filter value mapping.

    image

  4. Click OK.

After connecting, the content item that was set up to connect to the filter will have the filter listed in its Connections.

Linking KPI Details Report to a Scorecard

Similar to setting up a filter on dashboard, a KPI Details report receives its information from a KPI that is stored on a scorecard, of which the scorecard can be set up with a filter to limit the KPIs available. To set up the link for a KPI Details report from a scorecard, complete the following steps:

  1. Click the arrow in the upper-right corner for the KPI Details report and select Create Connection.
  2. On the Items tab of the Connection screen, select the scorecard that will be supplying the KPI.
  3. Click the Values tab, and the Connect To drop-down will have the cell selected.
  4. In the Source value, select Cell: Context, as shown in Figure 7.20.

    Figure 7.20. Filter value mapping.

    image

  5. Click OK.

After connecting, the KPI Details report that was set up to connect to the scorecard has the scorecard listed in its connections.

Creating Multiple Pages in a Dashboard

Dashboard Designer enables you to create multiple pages within the dashboard content. Each page is its own entity, and the connections can share data from other content items on the same page. Follow these steps to add pages to a dashboard:

  1. Build the first page as you would any other dashboard: Lay out the dashboard, drag content items, and link connections for filters and KPIs.
  2. In the Pages section, supply a name.
  3. To add another page, click the New Page button and supply a name for the new page.
  4. Design the new page as you would for the first page.
  5. After all pages have been developed, click the Properties tab for the dashboard content item.
  6. Supply a name for the dashboard. This is the name that will display in the SharePoint library.
  7. In the Deployment properties, select the document library to deploy the dashboard.
  8. Click the Include Page List for Navigation check box. The property ensures that when the dashboard is deployed each page has a link to bring up the dashboard content developed on the page. Figure 7.21 displays a sample dashboard Properties page.

    Figure 7.21. Dashboard Properties page.

    image

  9. Click the Save button.

Deploying the Dashboard to SharePoint

After developing the dashboard, the last step is to deploy the dashboard to SharePoint for users to interact with the dashboard. Within Dashboard Designer, there are two ways to accomplish this task:

• Right-click the dashboard from within the content and select Deploy to SharePoint, as shown in Figure 7.22.

Figure 7.22. Deploy to SharePoint menu.

image

• Click the Dashboard Designer Home icon and select Deploy, as shown in Figure 7.23.

Figure 7.23. Main deploy.

image

After you deploy the dashboard, the dashboard displays within the Dashboard library.

User Interaction with the Decomposition Tree

PerformancePoint Services has made the Decomposition Tree available as another method of drilling into the details to break down other objects that that make up the rolled-up dashboard value being displayed. The Decomposition Tree is not a development option. Instead, it is a menu item made available when drilling from a scorecard KPI or interacting with an analytic chart and grid.

To allow the enabling of the Decomposition Tree for a KPI, the KPI’s calculation method must be set to Data Value, which is explained in the “Building Key Performance Indicators” section of this chapter. For the analytic chart or grid, the report’s query should have been created using the Design tab and not have supplied custom MDX for the report.

Viewing the Decomposition Tree from a KPI, right-click the KPI value to drill into and select Decomposition Tree, as shown in Figure 7.24.

Figure 7.24. Scorecard Decomposition Tree menu.

image

Viewing the Decomposition Tree from an analytic chart or grid, right-click the value to drill into and select Decomposition Tree, as shown in Figure 7.25.

Figure 7.25. Analytic Decomposition Tree menu.

image

After you select the Decomposition Tree from the menu, a new window appears that allows for user interaction with the Decomposition Tree. Clicking a tree item displays a menu that allows you to expand the tree to another dimension or view the properties of the selected tree item. If the tree item is part of a hierarchy, an Expand button is available to the left of the tree item, to drill into the next level of the hierarchy. Figure 7.26 shows an example Decomposition Tree.

Figure 7.26. Decomposition Tree drilling.

image

Summary

PerformancePoint Services integration with SharePoint has made the technology readily available to the analysts and management team who want to manipulate information without having to contact a report developer every time a new scenario is considered. Developers familiar with developing for PerformancePoint 2007 will have little issue getting up to speed to create dashboards for PerformancePoint Services. Those less familiar with the toolset will feel confident with the ease of using Dashboard Designer in building PPS content items and dashboards. PerformancePoint Services for SharePoint 2010 is a powerful business intelligence tool for delivering information to the analytical community, which enhances their ability to make well informed business decisions.

This chapter walked through the process of developing each piece of PerformancePoint content: KPIs, scorecards, report, filters, and dashboards. In addition, the chapter explained how to develop the content to enable other features and thus further enhance the user experience, such as setting up the content for allowing the Decomposition Tree to display. In later chapters, a complete end-to-end solution shows how PerformancePoint Services fits into the overall BI stack and how the components from this chapter are used to build the complete solution.

Best Practices

The following are best practices from this chapter:

• Within Dashboard Designer, organize PerformancePoint content by creating display folders and saving the objects within the folder.

• Setting up the Data Source for Time Intelligence expands the functionality of other PerformancePoint Services content for creating custom time formulas.

• Develop KPIs before creating scorecards, instead of creating the KPI during the scorecard development. KPIs have a vast number of properties that have to be configured, which will either enable or disable other user interaction, such as enabling the Decomposition Tree and creating a KPI Details report.

• Try not to use custom MDX for the Analytic Grids and Charts reports. Using the Design tab and dragging source items into the report will allow for the Decomposition Tree to be visible when viewing the report in a dashboard.

• If building a Strategy Map report, use a Visio diagram that has simple objects and not multilayered diagrams. The diagram objects will be connecting to scorecard elements via a mapping, which cannot be created in a multilayered Visio diagram.

• Organizing the layout of a dashboard is just as important as the content that will be displayed within the dashboard. If the content does not appear user friendly or workable, the user will not want to use the content, which defeats the purpose of bringing the information to the user in the first place.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset