Share via

Hands On Lab: Building and Sharing Your First SQL Server 2008 R2 PowerPivot for Excel Workbook

Version: 1.0

Description

This Hands On Lab will show you how to build your first SQL Server 2008 R2 PowerPivot for Excel workbook to perform self-service analysis, and how to share it with SQL Server 2008 R2 PowerPivot Server for SharePoint.

Overview

This lab will create a PowerPivot workbook that will import data from a SQL Server database and a data feed. The PowerPivot model will be refined by defining relationships and calculated columns. Next, a PivotTable report will be created and measures defined. Finally, the PowerPivot workbook will be published to SharePoint.

Objectives

The objectives of this exercise are to:

  • Import data into PowerPivot
  • Create table relationships
  • Create calculated columns
  • Create a PivotTable report
  • Create measures
  • Format the PivotTable report
  • Publish the PowerPivot workbook to SharePoint

System Requirements

You must have installed the following items to complete this lab:

  • Microsoft Excel 2010
  • SQL Server 2008 R2 PowerPivot Add-in for Excel
  • Microsoft SQL Server 2008 R2:
    • Database Engine
    • Analysis Services (SharePoint integrated mode)
  • SQL Server AdventureWorks2008 R2 sample databases
    • AdventureWorks2008R2
  • Microsoft Visual Studio 2010 SP1
    • Visual C#
    • Visual Web Designer
  • Microsoft SharePoint 2010 (Enterprise Edition)
    • Excel Services
  • SQL Server 2008 R2 PowerPivot Add-in for SharePoint

Setup

All the requisites for this lab are verified using the Configuration Wizard. To make sure that everything is correctly configured, follow these steps.

Note:
To perform the setup steps you need to run the scripts in a command window with administrator privileges.

  1. Launch the Configuration Wizard for this lab by double-clicking the Dependencies.dep file located under the Source\Setup folder of this lab. Install any pre-requisites that are missing (rescanning if necessary) and complete the wizard.

Cleanup

  1. To restore the original state of the AdventureWorks2008R2 SQL Server database, execute the Cleanup.cmd script located under the Setup folder in the Source folder of this lab.
  2. Manually delete the workbook saved to the Source folder, and the PowerPivotLab PowerPivot site.

Exercises

This Hands-On Lab comprises the following exercise:

  1. Creating a PowerPivot Solution

Estimated time to complete this lab: 30 minutes.

next >

Downloads

Download The Offline Training Kit
Download Hands-on Lab Document (DOCX)
Download Hands-on Lab Source Files

Contents