Assembling Data
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.
587379c03.indd 57 5/12/10 11:02:26 AM
CHAPTER 3 Assem b ling 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
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.
— This is the relational database management system (RDBMS) product from Oracle
FIGURE 31: The Get External Data section of the
PowerPivot Window ribbon
587379c03.indd 58 5/12/10 11:02:26 AM

Get Professional Microsoft® PowerPivot for Excel® and SharePoint® now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.