Professional Microsoft® PowerPivot for Excel® and SharePoint®

A

Setting Up the

SDR Healthcare Application

In Chapters 3 through 6 of this book, you work through an extended example of how to

create and publish a PowerPivot workbook. This example involves a fictional healthcare

company, SDR Healthcare, and involves doing analysis and reporting on the data in that com-

pany’s audit database, and other external data sources.

To simulate importing data from multiple data sources, some setup of those data sources is

needed before starting the application. In the real world, those data sources would already be

there. But, for this example, they are not. This appendix provides instructions on how to set up

the initial environment before starting to work on the example. These instructions assume that

PowerPivot for SharePoint is already set up. Instructions for a simple single-machine setup can

be found in Chapter 2. Instructions for a more detailed multi-machine setup can be found in

Chapter 8.

SETTING UP THE SQL SERVER AUDIT DATABASE

The main analytical data for the application comes from a SQL Server database. This data is

included in the

SDRAudit.bak file included in this book’s downloadable files.

To restore the backup onto a SQL Server 2008 R2 instance, follow these steps:

1. Start SQL Server Management Studio.

2. Connect to the server on which you want to restore the database.

3. Right-click on the Databases folder for that server in the Object Explorer window,

and select Restore Database.

4. In the “Source for restore” section of the dialog, select From Device. Click the edit field

button (the button with the “…” caption).

5. In the Specify Backup dialog, click the Add button.

6. Specify the full name (including the path) of the SDRAudit.bak file in the “File name”

field and click the OK button.

587379bapp01.indd 339 5/12/10 10:58:34 AM

340



APPENDIX A Setting Up the SDR healthcaRe application

7. Click OK in the Specify Backup dialog.

8. Click on the Restore checkbox in the “Select the backup sets to restore” section of the dialog.

9. In the “To database” drop-down in the “Destination for restore” section of the dialog, select

“SDRAudit” and click OK. SQL Server Management Studio will restore the database. Once

the restore is completed, click OK on the completion message box.

SETTING UP THE DATABASE GROUP NAME SHAREPOINT LIST

The Database Group Name table in the SDR Healthcare application comes from a SharePoint 2010

list that is imported as a data feed. In order for the SharePoint functionality of exporting a list as a

data feed to work, you must install the Data Services Update for .NET Framework 3.5 SP1 on your

SharePoint server. You can find this update at the following locations:

For Windows Server 2008, go to the following:



http://www.microsoft.com/downloads/details.aspx?familyid=

4B710B89-8576-46CF-A4BF-331A9306D555&displaylang=en

For Windows Server 2008 R2, go to the following:



http://www.microsoft.com/downloads/details.aspx?familyid=

79d7f6f8-d6e9-4b8c-8640-17f89452148e&displaylang=en

This book’s download files include a DatabaseGroupName.xlsx file that contains a worksheet

page with the data needed for the Database Group Name SharePoint list. To import that data into

SharePoint, follow these steps:

1. In your SharePoint 2010 installation, navigate to the Lists section of the site, as shown in

Figure A-1.

FIGURE A1: SharePoint Lists page

587379bapp01.indd 340 5/12/10 10:58:34 AM

Setting Up the Database Group Name SharePoint List



341

2. Click on the Create button.

3. In the Create dialog, navigate to the Import Spreadsheet item and click on it. On the right

side of the dialog, click the Create button.

4. Fill in the name and, optionally, the description of the new list. In this case, enter

DatabaseGroupName.

5. Click on the Browse button and navigate to the location of the DatabaseGroupName.xlsx file.

6. Click the Import button.

7. In the Import to Windows SharePoint Services list, select Range of Cells. In the Select Range

field, ensure that all the cells with data on the

DatabaseGroupName sheet are selected (that is,

$A$1:$C$23). Click on the Import button.

After the import completes successfully, you will have a new SharePoint list called

DatabaseGroupName (Figure A-2) that can be used as a data feed with PowerPivot.

FIGURE A2: The DatabaseGroupName SharePoint list

587379bapp01.indd 341 5/12/10 10:58:34 AM