Exercise 2: Performing Analysis with Excel and Excel Services

In this exercise you need to perform a quick analysis of internet sales data. Your analysis can easily be done in Excel using a connection to Analysis Services. Once the analysis tables are completed you will post them to the BI Center SharePoint site so others can view your results.

Task 1 – Create a Pivot Table in Excel

In this task, you will create a new Excel workbook containing a pivot table based on data in Analysis Services.

1. Open Excel 2010 to create a new workbook
2. Add a data connection to the Adventure Works cube in the Adventure Works DW 2008 database
   1. Click the From Other Sources -> From Analysis Services button in the Data ribbon
   2. In the dialog enter demo2010a as the server name and click Next
   3. In the Select Database and Table dialog, choose a database of Adventure Works DW 2008
   4. Choose a cube of Adventure Works and click Next
   5. In the last page of the wizard, click the Authentication Settings button

   6. Choose the None authentication option and click OK

      Note:

      Choosing None as the authentication option instructs Excel Services to use SSS to retrieve the credentials. Excel Services has been preconfigured to use a specific SSS ID when an authentication type of None is selected.

   7. Click Finish to create the data connection
3. Using the Import Data dialog automatically displayed, create a new pivot table in the main worksheet
   1. Verify PivotTable Report is selected
   2. Verify the data is placed in the existing worksheet at =$A$1

   3. Click OK to create the pivot table

      Figure 12

      Import Data Dialog

4. Add a measure of Internet Sales Amount and the Geography and Calendar dimensions to the pivot table
   1. In the PivotTable Field List task pane, choose Internet Sales in the Show fields related to drop down

   2. Find Internet Sales Amount in the check box list and check it

      Figure 13

      Choose Internet Sales Measure

   3. Find CustomerGeography in the check box list and check it

      Figure 14

      Choose Geography Dimension

   4. Find Date.Calendar in the check box list and check it

      Figure 15

      Choose Date Dimension

   5. At this point your pivot table should look like this

      Figure 16

      Complete Pivot Table

Task 2 – Add Sparklines to compliment the Pivot Table

In this task, you will use Sparklines to highlight trends in data and identify min\max values with special formatting.

1. Filter the pivot table to show all months in 2002 only
   1. Click the drop down arrow in the ColumnLabels cell (B1)

   2. In the filter drop down uncheck all years except CY 2002 and click OK

      Figure 17

      Year Dimension Filter

   3. Click the plus by CY 2002 (B2) to drill into the year
   4. Drill into H1 CY 2002 (B3) and H2 CY 2002 (C3)
   5. Drill into Q1 CY 2002 (B4), Q2 CY 2002 (C4), Q2 CY 2002 (E4), and Q2 CY 2002 (F4)

   6. Right click the January 2002 node and select Show/Hide Fields and click:

      1. Calendar Year
      2. Calendar Semester
      3. Calendar Quarter
      Figure 18

      Filtered Pivot Table

2. Add Sparklines for all countries using the data from B3:M8
   1. Copy the country names from cells A3:A8 to A12:A17
   2. Select the cells from B12:B17
   3. In the Insert ribbon click the Line button in the Sparklines group

   4. In the dialog choose a Data range from B3:M8

      Figure 19

      Create Sparklines Dialog

   5. Click OK to create the Sparklines

      Figure 20

      Sparklines

3. Update the formatting of the Sparklines to highlight the min and max points
   1. Make sure cells B12:B17 are selected
   2. In the Sparkline Tools Design tab, click Marker Color -> High Point and choose a color of yellow

   3. In the Sparkline Tools Design tab, click Marker Color -> Low Point and choose a color of green

      Figure 21

      Sparklines with Min/Max

Task 3 – Publishing the Excel Workbook to Excel Services

In this task you will publish the Excel Workbook to SharePoint and viewing it using Excel Services

1. Publish the Excel Workbook to Excel Services
   1. Click the Backstage button (labeled as File) to open backstage
   2. Switch to the Save & Send tab in Backstage
   3. Click the Send to SharePoint option under Save & Send then click the Save As button
   4. Click the Publish Options button
   5. In the drop down choose Sheets and uncheck Sheet2 and Sheet3 to only share Sheet1
   6. Back in the Save As dialog, enter a path of https://intranet.contoso.com/sites/BIApplications/Documents/PivotTable.xlsx
   7. Click Save
2. Open the PivotTable workbook in the browser
   1. In Internet Explorer navigate to https://intranet.contoso.com/sites/BIApplications/Documents

   2. Click the link to PivotTable to view it in the browser

      Figure 22

      Excel Workbook in the Browser