Exercise 3: Building a Dashboard with PerformancePoint Services

In this exercise you will create a dashboard that presents information about reseller sales for Adventure Works. This dashboard will allow users to quickly understand the status of reseller sales across geography and time to better understand the nature of sales for the year to date.

Task 1 – Create the Analysis Service data source

In this task you will create a new data source connected to the Adventure Works analysis services database and tie it to the current year

1. Start the PerformancePointServicesDashboardDesigner
   1. In Internet Explorer navigate to https://intranet.contoso.com/sites/BIApplications
   2. Hover over Monitor Key Performance to show the related content
   3. Click the Start usingPerformancePoint Services link

   4. Click the Run Dashboard Designer button to download and launch the designer

      Figure 23

      PerformancePoint Services Designer

2. Create a new data connection using the Adventure Works DW 2008 analysis services database
   1. Right click on the Data Connections folder and click New Data Source
   2. Choose the Analysis Services template and click the OK button
   3. Enter a server of demo2010a
   4. Choose Adventure Works DW 2008 in the Database drop down

   5. Choose Adventure Works in the Cube drop down

      Figure 24

      Choose a Data Source

   6. Switch to the Properties tab and change the Name to Adventure Works
3. Choose a Time dimension and map it to the first day of the current year
   1. Switch to the Time tab
   2. Choose Date.Date.Calendar in the Time Dimension drop down
   3. Click the Browse button by the Member control

   4. In the Select Member dialog select January 1, 2003

      Figure 25

      Choose Time Reference Member

   5. Choose Day in the Hierarchy Level drop down
   6. Set the Reference Date to the first day of the current year (ex. 1/1/2010 if it is 2010)

   7. Choose the time member associations for each member level

      1. Calendar Year: Year
      2. Calendar Semester: Semester
      3. Calendar Quarter: Quarter
      4. Month: Month
      5. Date: Day
      Figure 26

      Initialize Data Connection Time

4. Save the Data Connection by right clicking it in the Workspace Browser and clicking Save

Task 2 – Create a KPI to track sales performance

In this task, you will create a KPI that calculates the total sales for the month to date, quarter to date and year to date and compares them to last year’s sales plus 25%.

1. Create a new KPI to track reseller sales
   1. Right click on the PerformancePoint Content folder and click New -> KPI
   2. In the Select a KPI Template dialog, choose Blank KPI and click OK
   3. Switch to the Properties tab and name the new KPI Performance
2. Define the actual value as Year to Date (YTD)
   1. Switch to the Editor tab
   2. Click the Name for Actual and rename it YTD
   3. Click the 1 (Fixed values) link in the Data Mappings column for YTD
   4. On the Fixed Values Data Source Mapping dialog, click Change Source
   5. Select the Adventure Works data connection and click OK
   6. Choose Reseller Sales Amount in the Select a measure drop down
   7. Click the New Time Intelligence Filter button

   8. Enter a time formula of YearToDate and click OK

      Figure 27

      YTD Value Definition

   9. Click OK to close the dialog
   10. Click the (Default) link in the Number column for YTD

   11. Choose Currency in the Format drop down list and click OK

       Figure 28

       Completed YTD Value

3. Define the target value as last year’s sales plus 25%
   1. Click the 1 (Fixed values) link in the Data Mappings column for Target
   2. On the Fixed Values Data Source Mapping dialog, click Change Source
   3. Switch to the Calculated Metric tab
   4. Choose Blank Calculation and click OK
   5. In the Calculated Metrics Data Source Mapping dialog, delete Value2
   6. Rename Value1 to LastYearsSales
   7. Click the 1 (Fixed Values) link for LastYearsSales
   8. On the Fixed Values Data Source Mapping click Change Source
   9. Select the Adventure Works data connection and click OK
   10. Choose Reseller Sales Amount in the Select a measure drop down
   11. Click the New Time Intelligence Filter button

   12. Enter a time formula of Year-1 and click OK

       Figure 29

       Target Value Definition

   13. Click OK to close the dialog

   14. Enter a formula of LastYearsSales*1.25

       Figure 30

       Target Value Calculation Definition

   15. Click OK to close the dialog
   16. Click the (Default) link in the Number column for Target

   17. Choose Currency in the Format drop down list and click OK

       Figure 31

       Complete KPI

4. Save the KPI by right clicking it in the Workspace Browser and clicking Save

Task 3 – Create a ScoreCard to contain the Performance KPI

In this task, you will create a new scorecard that will contain the Performance KPI and calculate it for each country.

1. Create a new scorecard to contain the Performance KPI
   1. Right click on the PerformancePoint Content folder and click New -> Scorecard
   2. Choose the Blank Scorecard template in the Standard category then click OK
   3. Switch to the Properties tab and name the new scorecard Performance By Country
2. Place the Performance KPI in the scorecard
   1. Switch back to the Editor tab
   2. Expand the KPIs and PerformancePoint Content nodes in the Details task pane
   3. Drag the Performance into the left side Drop items here block

   4. Click the Update button in the Edit tab

      Figure 32

      Scorecard with KPI

      Note:

      Your values will be different. They will be current to the day the Update button is clicked.

3. Add the Geography dimension to the scorecard
   1. Expand the Dimensions node in the Details task pane
   2. Drag the Geography dimension to the right hand side of the Performance cell

   3. In the Select Members dialog right click All Geography and select Autoselect Members -> Select “Country”

      Figure 33

      Select Geography Members

   4. Click the Update button in the Edit tab

   5. Right click the Performance cell and select Hide

      Figure 34

      Complete Scorecard

4. Save the scorecard by right clicking it in the Workspace Browser and clicking Save

Task 4 – Create a Report to display sales by year

In this task, you will create a report showing reseller sales by reseller type. This report will connect to the scorecard and display data only for the selected country.

1. Create a new report to display reseller sales by reseller type
   1. Right click on the PerformancePoint Content folder and click New -> Report
   2. Choose a report template of Analytic Chart and click OK
   3. Select a data connection of Adventure Works and click Finish
   4. Switch to the Properties tab and name the new report Sales By Reseller Type
2. Define the report to display reseller sales grouped by reseller business types
   1. Switch to the Design tab
   2. Expand the Measures node in the Details task pane
   3. Drag the Reseller Sales Amount item into the Bottom Axis section
   4. Expand the Dimensions then Reseller nodes in the Details task pane

   5. Drag the Business Type item into the Series section

      Figure 35

      Complete Report

3. Save the report by right clicking it in the Workspace Browser and clicking Save

Task 5 – Create a Dashboard to connect the content

In this task, you will display the scorecard and report and connect them. This connection will allow filtering of the report using the currently selected country from the scorecard.

1. Create a new dashboard for displaying the performance scorecard and report
   1. Right click on the PerformancePoint Content folder and click New -> Dashboard
   2. Choose the 2 Columns page template and click OK
   3. Switch to the Properties tab and name the new dashboard Performance Dashboard
2. Add the scorecard and the report to the new dashboard
   1. Switch to the Editor tab
   2. Expand the Scorecards and PerformancePoint Content nodes in the Details task pane
   3. Drag the Performance By Country scorecard into the Left Column
   4. Expand the Reports and PerformancePointContent nodes in the Details task pane

   5. Drag the Sales by Reseller Type report into the Right Column

      Figure 36

      Complete Dashboard

3. Connect the selected country in the scorecard to the report
   1. Drag the Row Member -> Member Unique Name item in the scorecard into the report

   2. Verify the Connection dialog shows Member Row : Member Unique Name in the Source value drop down list and click OK

      Figure 37

      Dashboard Connection

4. Save the dashboard by right clicking it in the Workspace Browser and clicking Save
5. Deploy the new dashboard
   1. Right click it in the Workspace Browser and select Deploy to SharePoint
   2. Select the Dashboards folder and click OK

Exercise 3 Verification

In order to verify that you have correctly performed all steps of exercise 1, proceed as follows:

Test the Add-in

Test your add-in to confirm that it will retrieve contacts from a SharePoint List and insert a selected contact’s information into the document. Then confirm that the ribbon buttons work correctly for exporting the document to XPS and PDF format.

1. View the dashboard in the browser
   1. In Internet Explorer navigate to https://intranet.contoso.com/sites/BIApplications/Dashboards/Performance Dashboard/Page 1.aspx

   2. Select Canada in the scorecard and verify the report is updated

      Figure 38

      Completed Dashboard

2. Analyze sales in Canada using the decomposition tree
   1. Right click Warehouse bar on the report and choose Decomposition Tree

   2. Click Warehouse and choose Date -> Calendar Year to show sales by year

      Figure 39

      Decomposition Tree

   3. Explore the decomposition tree to explore the data