Book Image

Instant Creating Data Models with PowerPivot How-to

By : Taehyung Lee
Book Image

Instant Creating Data Models with PowerPivot How-to

By: Taehyung Lee

Overview of this book

Microsoft PowerPivot is a free software designed to enhance Microsoft Excel. It allows the user to make extensive use of his/her computer's powers to draw data from various sources, do analysis across millions of rows of data, and present results in a very unique format. Instant Creating Data Models with PowerPivot How-to is a concise and to-the-point guide that helps you get a jump start on using this strong business intelligence tool, while still working in an environment similar to Excel. You will begin with data generation and manipulation, learning a new feature at each step by building onto the old file, and ultimately creating a comprehensive file. Instant Creating Data Models with PowerPivot How-to will guide the user through database installation, importing data from various sources, creating pivot charts and tables, utilizing a unique feature of PowerPivot called slicers,adding custom columns, and setting custom relationships between data to generate the ultimate customized dataset for analysis. By the end of the book and all the sections of Microsoft PowerPivot for Excel, the reader will be fully experienced and ready to utilize this powerful software.
Table of Contents (7 chapters)

Importing data from the database (Advanced)

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.

Getting ready

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.

How to do it...

  1. Download and install the small version of Microsoft SQL Server 2008 R2 SP2 – Express with Tools (SQLEXPRWT_x64_ENU.exe or SQLEXPRWT_x86_ENU.exe), which is sufficient for our purpose. It can be downloaded from

  2. Download the sample database AdventureWorks by selecting AdventureWorks2008R2 Data File at

  3. Open SQL Server Management Studio as an administrator. Click on Attach... as shown in the following screenshot:

  4. 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.

  5. 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.

  6. 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.

  7. 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.

How it works...

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.

There's more...

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.

Excel 2013

PowerPivot is a built-in add-in for Excel 2013. To enable, please refer to for instructions on how to enable the PowerPivot add-in.