Exercise 3: Building a Dashboard with PerformancePoint Services

In this exercise you will use the Dashboard Designer to create a KPI, a scorecard and a report from the same SQL Service Analysis Services cube that you used in the previous exercise. You will then create a dashboard that assembles all these pieces onto a single page and then you will deploy the dashboard to a SharePoint site.

1. You will begin by using Central Administration to make a change to the PerformancePointServices configuration setting.
   1. Launch Central Administration: Start » Program Files » Microsoft SharePoint 2010 Products » SharePoint 2010 Central Administration.
   2. Inside the Application Management section, find and click the Manage service applications link.
   3. Look down the list of managed service applications and click on the one titled PerformancePoint Service Application. This will bring you to the main configuration page for PerformancePoint Services.

   4. Click on the link titled PerformancePoint Service Application Settings.

      Figure 35

      Configure PerformancePoint Service Application Settings

   5. You should now be at the PerformancePoint Services Settings page as shown in the following screenshot. Look down the page to get an idea of the types of settings that are tracked by PerformancePoint Services. The one thing you need to do on this page is to change the User Name setting inside the Secure Store and Unattended Service Account section. If the User Name is already set to CONTOSO\Administrator, you do not have to do anything. If the User Name is set to something else, configure the User Name as CONTOSO\Administrator and use a password of pass@word1.

      Figure 36

      Configure an Unattended Service Account

   6. Click the OK button on the PerformancePoint Services Settings page to save changes.
   7. You are now done with Central Admin.

2. Using the browser, navigate to the site created at https://intranet.contoso.com/sites/Lab12b. Note that this site has been created from the special site template created by the PerformancePoint Services template.

   Figure 37

   The SharePoint site created using the PerformancePoint Services template

3. Click the Run Dashboard Designer button to launch the Dashboard Designer application.(Note: if you receive a security warning about this application click Run)

   Figure 38

   The Dashboard Designer

4. Create a new data source to a SQL Server Analysis cube.
   1. Right-click on the Data Sources folder on the left-hand side of the page and select New Data Source to bring up the Select a Data Source Template dialog.

   2. In the Select a Data Source Template dialog, select the Multidimensional category and then the Analysis Services template and then click OK.

      Figure 39

      The Select a Data Source Template dialog

   3. Once the data source has been created, rename it to AdventureWorks in the left pane.

      Figure 40

      The Workspace browser

5. Now you will configure the connection for this data source. Select the Editor tab in the Workspace on the right-hand side of the screen, go through the following steps to configure the connection for the AdventureWorks data source.
   1. Select the Use Standard Connection option in Connection Settings.
   2. In the Server text box type the server name demo2010a.
   3. Click on the Database drop down box and select Adventure Works DW 2008R2.
   4. Click on the Cube drop down box and select Adventure Works from the list of options.
   5. Under Authentication, leave the default setting of Unattended Service Account.

   6. Click on the Test Data Source button to verify all the connection settings.

      Figure 41

      The AdventureWorks data source settings

   7. Click on the Time tab in the Workspace and follow these steps.

      Figure 41

      The AdventureWorks Time Tab Settings

   8. In the Reference Data Mapping section, click the down arrow of the Time dimension dropdown list and select Date.Date.Calendar.

   9. In the Reference Member section click on the Browse button which will bring up the Select Members dialog. Expand the All Periods node, then the CY 2006 node, then the H1 CY 2006 node, then the Q1 CY 2006 node, then the January 2006 node, and select the January 1, 2006 node and click OK.

      Figure 42

      The Reference Member section

   10. For the Hierarchy level select a value of Day.

   11. In this step you will set a reference date. This is required because the AdventureWorks database does not have data through the current time period. Enter a data of 1/1/2010 so that the first day of 2010 maps to the first day of 2006. In later steps, this will allow you to see values to things such as month-to-date sales figures as if it were 2006.

       Figure 43

       The Reference Data Mapping

6. In the Time Member Associations section, map the appropriate time aggregations by assigning the following Member Level settings to the corresponding Time Aggregation values.
   1. Calendar Year: Year
   2. Calendar Semester: Semester
   3. Calendar Quarter: Quarter
   4. Month: Month

   5. Date: Day

      Figure 44

      The Member Associations

7. Right-click on the AdventureWorks data source in the Workspace Browser and click Save to save your work.
8. Now you will create your first KPI.

   1. Right-click on the Dashboard Content node in the Workspace Browser. Expand the New option and select the KPI option.

      Figure 45

      Configure the Dashboard content

   2. Select Blank KPI and click OK
   3. In the Workspace click on the Editor tab.
   4. Observe that in the Actual and Targets section there are already two indicator rows. The first is indicator row an actual indicator with a default name of Actual and the other is a target indicator with a default name of Target.
   5. Right-click on the new KPI inside the Workspace Browser and click the Save command. Then right-click on the new KPI and rename it to Performance.
9. Modify the Actual indicator to track month-to-date performance.
   1. Click inside the Name column replace with text Actual with the text MTD.
   2. Click in the Data Mappings cell which as a value of "1 (Fixed Values)". The Fixed ValuesData Source Mapping dialog will appear.
   3. Click on the Change Source... button. The Select a Data Source dialog will appear.
   4. Select the Workspace tab inside the Select a Data Source dialog.

   5. Locate and select the AdventureWorks data source.

      Figure 46

      The AdventureWorks data source

   6. Click OK. The Dimensional Data Source Mapping dialog will take focus. In the Select a measure drop down box, select the Reseller Sales Amount measure.

   7. In the Select Dimension section, click the New Time Intelligence Filter button and the Time Formula Editor dialog will appear. In the Time Formula text box, type in the text MonthToDate. Click the Preview button to ensure the formula looks like [Date].[Calendar].[March2006 to Date by Day].

      Figure 47

      Time Formula Editor

   8. Click OK to accept changes and dismiss the Time Formula Editor dialog

   9. Click OK again to accept changes and dismiss the Dimensional Data Source Mapping dialog.

      Figure 48

      The Dimensional Data Source Mapping dialog

   10. In the Number Format column of the MTD indicator, click the "(Default)" text. The Format Numbers dialog will appear. In the Format dropdown box select Currency and then click OK.

       Figure 49

       The workspace

   11. Save your work by right-clicking the Performance KPI and clicking Save.
10. Create a second actual indicator to track quarter-to-date performance. Many of the steps here will be identical to what you did when configuring the first actual indicator.
    1. In the Workspace pane, click on the New Actual button to create a new actual indicator.
    2. Click inside the Name column replace with text Actual with the text QTD.
    3. Click in the Data Mappings cell which as a value of "1 (Fixed Values)". The Fixed Values Data Source Mapping dialog will appear.
    4. Click on the Change Source... button. The Select a Data Source dialog will appear.
    5. Select the Workspace tab inside the Select a Data Source dialog.
    6. Locate and select the AdventureWorks data source.
    7. Click OK. The Dimensional Data Source Mapping dialog will appear.
    8. In the Select a Measure drop down box, select the Reseller Sales Amount measure
    9. In the Select Dimension section, click the New Time Intelligence Filter button and the Time Formula Editor dialog will appear.
    10. In the Time Formula text box, type in the text QuarterToDate. Click the Preview button to ensure the formula looks like [Date].[Calendar].[Q1 CY 2006 to Date by Day].
    11. Click OK to accept changes and dismiss the Time Formula Editor dialog
    12. Click OK again to accept changes and dismiss the Dimensional Data Source Mapping dialog.

    13. In the Number Format column of the QTD indicator, click the "(Default)" text. The Format Numbers dialog will appear. In the Format dropdown box select Currency and then click OK.

        Figure 50

        The result

11. Create a third actual indicator to track year-to-date performance.
    1. In the Workspace pane, click on the New Actual button to create a row for a new actual
    2. Click inside the Name column replace with text Actual with the text YTD.
    3. Click in the Data Mappings cell which as a value of "1 (Fixed Values)". The Fixed Values Data Source Mapping dialog will appear.
    4. Click on the Change Source... button. The Select a Data Source dialog will appear.
    5. Select the Workspace tab inside the Select a Data Source dialog.
    6. Locate and select the AdventureWorks data source.
    7. Click OK. The Dimensional Data Source Mapping dialog will appear.
    8. In the Select a Measure drop down box, select the Reseller Sales Amount measure
    9. In the Select Dimension section, click the New Time Intelligence Filter button and the Time Formula Editor dialog will appear.
    10. In the Time Formula text box, type in the text YearToDate. Click the Preview button to ensure the formula looks like [Date].[Calendar].[CY 2006 to Date by Day].
    11. Click OK to accept changes and dismiss the Time Formula Editor dialog
    12. Click OK again to accept changes and dismiss the Dimensional Data Source Mapping dialog.

    13. In the Number Format column of the QTD indicator, click the "(Default)" text. The Format Numbers dialog will appear. In the Format dropdown box select Currency and then click OK. At this point, your screen should look like this:

        Figure 51

        The result

12. Modify the Target indicator to track target amount for the year.
    1. Click the Name column and change the indicator name from Target to Target for the year.
    2. In the Compare To column click on the drop down box and select the YTD actual indicator.
    3. Click on the Number Format column and choose Currency formatting.
    4. In the Data Mappings column select the “1 (Fixed values)" text. The Fixed Values Data Source Mapping dialog will appear.
    5. Click the Change Source... button. The Select a Data Source dialog will appear.

    6. Select the Calculated Metrics tab and then select the Blank Calculation template. Click OK.

       Figure 52

       The Calculated Metrics Data Source Mapping dialog

    7. Select the Value2 row and click the Delete button to remove it.
    8. Click on the Name Value1 and change its value to LastYearSales.
    9. Click on the Source text "1 (Fixed values)". The Fixed Values Data Source Mapping dialog will appear.
    10. Click Change Source button. The Select a Data Source dialog will appear.
    11. Click on the Workspace tab. Select the AdventureWorks data source and click OK. Now the Dimensional Data Source Mapping dialog will appear.
    12. Expand the Select a Measure drop down box and select Reseller Sales Amount.
    13. In the Select a dimension section, click the New Time Intelligence Filter button. The Time Formula Editor dialog will appear.

    14. In the Time Formula text box type the formula Year-1. Click the Preview button to ensure that a valid formula is returned such as [Date].[Calendar].[Calendar Year].&[2005].

        Figure 53

        The Time Formula editor

    15. Click OK to save your changes and dismiss the Time Formula Editor dialog.
    16. Click OK to save your changes and dismiss the Dimensional Data Source Mapping dialog

    17. At this point, you should be back at the Calculated Metrics Data Source Mapping dialog. Locate the Formula text box and type LastYearsSales*1.25.

        Figure 54

        The Calculated metrics Data Source Mapping dialog

    18. Click OK to save your changes and dismiss the Calculated Metrics Data Source Mapping dialog.

    19. Within the Workspace View, make sure target indicator named Target for the year as shown in the screenshot below. Look below in the Thresholds section and you should see target values for Best, Threshold 2, Threshold 1 and Worst. Change the Best threshold to 135% and change the Threshold 2 threshold to 85%. Leave the other two thresholds at their default values.

        Figure 55

        The Workspace View

    20. Right-click the Performance KPI in the Workspace Browser pane and click Save.
    21. Now, you will move on to create a scorecard to expose your KPI.
13. Right-click on the Dashboard Content node in the Workspace Browser pane. Expand the New node and select Scorecard to create a new scorecard. The Select a Scorecard Template dialog will appear. Uncheck the Usewizards to create scorecards and click OK.
14. Click on the Properties tab in the Workspace pane. In the General Properties section select the Name text box and replace the name New Scorecard with Performance By Country.
15. Right click on the Performance By Country scorecard in the Workspace Browser pane and select Save.

16. Click on the Editor tab in the Workspace pane. Locate the Details pane on the right-hand side of Dashboard Designer. Inside the Details pain, expand the KPIs node until you locate the Performance KPI. Left-click and drag the Performance KPI to the Performance By Country scorecard designer area to the left that is labeled Drop items here.

    Figure 56

    The Dashboard Designer

17. Up in the ribbon, select the Edit tab of Dashboard Designer. Inside the Edit tab within the View group, locate and click the Update button. The Performance KPI should render displaying data in each of the cells.

    Figure 57

    The Dashboard Designer

18. Locate the Data Source pane in the bottom right side of Dashboard Designer and make sure the AdventureWorks data source is selected in the dropdown list.(Note: if the only choice here is No Source, you should go to the Workspace Browser on the left side of the screen and select (i.e. click on) something else and then re-select Performance By Country to fix this situation).

    Figure 58

    Locate the Data Source pane

19. Expand the Dimensions node in the Details pane and locate the Geography dimension.

    Figure 59

    The Details pane

20. Drag the Geography Dimension into the Workspace and release it when the right side of the Performance cell is highlighted (Note: you should see a pop-up box that displays the words “Last child”). The Select Members dialog will appear.

    Figure 60

    The Details pane

21. Right click the All Geographies member. Expand the Autoselect Members menu. Click the menu item with the caption “Select Country” and click OK

    Figure 61

    The Autoselect Members menu

22. Up in the ribbon, select the Edit tab of Dashboard Designer. Inside the Edit tab within the View group, locate and click the Update button. The Performance KPI should render displaying data in each of the cells (Note: you may only have data for a couple of countries depending on the year that was selected (e.g. 2006 data is only available for Canada and the United States).)

    Figure 62

    The Dashboard Designer- Sample data shown here (Note: this may likely not be the same data that you are seeing on your screen)

23. Now you will create a Report which displays a chart.
    1. In the Workspace Browser pane, right click on the Dashboard Content node, expand the New menu and select Report. The Select a Report Template dialog appears.
    2. Select the Analytic Chart Report template and click OK.
    3. In the Select a Data Source dialog box, on the Workspace tab, select AdventureWorks underneath Data Sources and click Finish.
    4. In the Workspace select the Properties tab. In the General Properties section locate the Name text box and type in "Performance By Year".
    5. Right click on the Performance By Year report and click Save.
    6. In the Workspace Browser pane, ensure that the Performance By Year report is selected. Click the Design tab in the Workspace pane.
    7. In the Details pane on the right-hand side of the screen, expand the Measures node.
    8. Locate the Reseller Sales Amount measure and drag and drop in into the Bottom Axis.
    9. Collapse the Measures node in the Details pane and expand the Dimensions node.
    10. Within the Geography dimension, locate the Country dimension. Drag and drop the Country dimension to the Series axis.

    11. Up in the ribbon, select the Edit tab. Drop down the Reporttype button and select the Pie Chart option.

        Figure 63

        Select the Report type button on the Edit tab

    12. Right click on the Performance By Year report and click Save.
24. Now, it’s time to create a dashboard.
    1. In the Workspace Browser pane, right click the Dashboard Content node, expand the New menu and select the Dashboard object. The Dashboard Page Template dialog will appear.

    2. Select the 2 Column template and click OK.

       Figure 64

       The Dashboard Page Template dialog

    3. Using the dashboard’s Properties tab, rename the new dashboard to Performance Dashboard and save your work.
25. Now it’s time to configure the dashboard.
    1. In the Workspace Browser pane, ensure that Performance Dashboard is selected.
    2. In the Workspace, select the Editor tab.
    3. Inside the Pages section you should see a single page named Page1. Rename this page to Performance.
    4. In the Details pane, expand the Scorecards node until you locate the Performance by Country scorecard. Drag and drop the Performance By Country scorecard on the Right Column zone of the dashboard designer.
    5. In the Details pane, collapse the Scorecards node and expand the Reports node.

    6. Locate the Performance By Year report. Drag and drop the Performance By Year report into the Left Column zone.

       Figure 65

       Configure the Performance Dashboard

    7. Right-click on Performance Dashboard and click Save.
26. In the Workspace Browser pane, right click the Untitled Workspace node. Select the Save option. In the File Name text box type in "PPS Lab" and click Save.
27. Now (finally!), it is time to deploy what you have done into SharePoint.

    1. In the Workspace Browser pane, right click Performance Dashboard and select the Deploy to SharePoint... option.

       Figure 66

       The Workspace browser

    2. The Deploy To dialog will open and ask you to select one the of the document libraries in the site at https://intranet.contoso.com/sites/Lab12b. Select the Dashboards document library and ensure that the Master Page selected is "v4". Click OK.

       Figure 67

       The Deploy To dialog

    3. An Internet Explorer window should open and will render the dashboard.

       Figure 68

       The finished Dashboard

       (Note: this is a sample picture and may not accurately represent exactly what you are seeing on your screen (i.e. your actual data may be different).

    Note:

    In this exercise you used the PerformancePoint Dashboard Designer to create some new KPIs and add them to a scorecard in a SharePoint site.