Professional Microsoft® PowerPivot for Excel® and SharePoint®

Assembling Data

WHAT’S IN THIS CHAPTER?

Importing data into PowerPivot



Understanding other ways of bringing data into PowerPivot



Introducing the healthcare audit application



Assembling data for the healthcare audit application



BI applications (including self-service BI applications) are all about making sense of data. In

order to make sense of it, you must start with the data. This chapter is about this aspect of

building PowerPivot applications — assembling the data you will analyze and/or report on.

Unlike the common corporate BI approach, where you assemble all your data into a data ware-

house and then import it into your online analytical processing (OLAP) database, PowerPivot

strives to make it easy to bring data into the application directly from many different data

sources without bringing it first into an intermediate staging area. This allows a more interac-

tive, exploratory approach to BI that is one of the hallmarks of self-service BI.

Keep in mind that self-service BI is an alternative to, not a replacement for, corporate BI.

There are contexts where the traditional corporate BI approach is the right one. Two exam-

ples are analyzing massive quantities of data or using the full power of Multidimensional

Expressions (MDX) if the calculation requirements of your application require it.

This doesn’t mean that the data that lives in data warehouses or in corporate BI databases

can’t be used in self-service BI applications. Data from these types of applications is included

in the diverse types of data that can be brought into your self-service BI applications with

PowerPivot. This chapter looks at how to bring data into PowerPivot applications from those

and other data sources.

3

587379c03.indd 57 5/12/10 11:02:26 AM

58



CHAPTER 3 Assem b ling DAtA

IMPORTING DATA

The primary way of bringing data into

your PowerPivot application is by importing it.

PowerPivot for Excel includes a tool for doing

this called the Table Import Wizard. The Table

Import Wizard is started from the Get External

Data section of the Home tab of the ribbon in

the PowerPivot Window. Figure 3-1 shows this

section of the PowerPivot ribbon.

As you can see, there are multiple drop-downs and buttons corresponding to the multiple data

source types that PowerPivot supports:

The From Database drop-down enables you to import data from various Microsoft database



types — SQL Server, Access, and Analysis Services/PowerPivot.

The From Report button enables you to import data from Microsoft SQL Server 2008 R2



and later Reporting Services reports.

The From Data Feeds button helps you to import data from data feeds (a new technology for



making available updatable data sources that will be described later in this chapter).

The From Text button enables you to import data from text files.



The From Other Sources button enables you to import data from every type of data source



that PowerPivot supports, including those that aren’t available from the other buttons.

The following sections describe the different types of data sources from which you can import data

into PowerPivot.

Relational Databases

In Chapter 2, you learned about importing data from Microsoft SQL Server. But, using PowerPivot,

you can import data from a variety of different relational databases in addition to Microsoft SQL

Server. Figure 3-2 shows a list of the supported relational data sources from PowerPivot’s Table

Import Wizard.

You can see that, in addition to Microsoft SQL Server, PowerPivot supports importing data from the

following:

Microsoft SQL Azure



— This is a cloud-based relational database service built on Microsoft

SQL Server technologies.

Microsoft SQL Server Parallel Data Warehouse



— This is a highly scalable system for data

warehousing that employs massively parallel processing.

Microsoft Access



— This is the desktop database component of Microsoft Office.

Oracle



— This is the relational database management system (RDBMS) product from Oracle

Corporation.

FIGURE 31: The Get External Data section of the

PowerPivot Window ribbon

587379c03.indd 58 5/12/10 11:02:26 AM