A database is an effective method to store and use data for large businesses, but its structure is very different from anything else. We will install a sample database and use its data throughout the rest of the book. If you are well aware of a database or do not plan on using a database at all, then this section is optional.
Before importing data from the database, we need to install the database client and the sample data itself. Then, we will import large amount of data from a database into PowerPivot. Please ensure that you have administrative rights to install on your computer.
Download and install the small version of Microsoft SQL Server 2008 R2 SP2 – Express with Tools (
SQLEXPRWT_x64_ENU.exeorSQLEXPRWT_x86_ENU.exe), which is sufficient for our purpose. It can be downloaded from http://www.microsoft.com/en-ca/download/details.aspx?id=30438.Download the sample database AdventureWorks by selecting AdventureWorks2008R2 Data File at http://msftdbprodsamples.codeplex.com/releases/view/59211.
Open SQL Server Management Studio as an administrator. Click on Attach... as shown in the following screenshot:
In the next screen, select Log in the database details section and click on Remove as shown in the following screenshot. This will remove the logs. Now you can press the OK button.
Once the sample database has successfully been installed, it will be visible on the left-hand side pane of the SQL Server Management Studio. After confirming, open Excel 2010 with PowerPivot.
In Excel 2010, open up the PowerPivot window. Select From Database | From SQL Server. Select the details as shown in the following screenshot. If the server name is not available, type in
PC-NAME\SQLEXPRESS.
Then, select 13 tables with Person in the Schema column and click on Finish. It will import the tables into PowerPivot.
Congratulations! You have successfully imported tens of thousands of rows from the database in just a few seconds. Browse through a few sheets and confirm that we have successfully imported data from SQL database, and then save it as an Excel file named DatabaseData.xlsx and close. It should be roughly a 16 MB file.
Through the installation of SQL database and the sample database, a database with sample data was created on your personal computer. Then, we have simply accessed and imported all data into PowerPivot for Excel. If the data is updated in the database, we can get the latest data simply by refreshing PowerPivot as the data are linked.
The data may not make much sense at the moment if the user is unfamiliar with the database structure, but it will make more sense in later recipes.
If you have problems attaching a database, it is most likely an access error. Try to move the AdventureWorks file to another location, and ensure that SQL Server Management Studio was opened as an administrator.
The PowerPivot data that was imported from the database can be updated by simply pressing the Refresh button, just a few buttons to the right of the From Database button.
PowerPivot is a built-in add-in for Excel 2013. To enable, please refer to http://office.microsoft.com/en-001/excel-help/start-powerpivot-in-excel-2013-add-in-HA102837097.aspx for instructions on how to enable the PowerPivot add-in.