Chapter 35: Introducing Power Pivot
35
Loading Data from Other Data Sources
As you’ll discover in this section, you’re not limited to using only the data that already
exists in your Excel workbook. Power Pivot has the ability to reach outside the workbook
and import data found in external data sources. Indeed, what makes Power Pivot so pow-
erful is its ability to consolidate data from disparate data sources and build relationships
between them. This means you can theoretically create a Power Pivot data model that con-
tains some data from a SQL Server table, some data from a Microsoft Access database, and
even data from a one-off text file.
Loading data from relational databases
One of the more common data sources used by Excel analysts are relational databases. It’s
not difficult to find an analyst who frequently uses data from Microsoft Access, SQL Server,
or Oracle databases. In this section, we’ll walk through the steps for loading data from
external database systems.
Loading data from SQL Server
SQL Server databases are some of the most commonly used for storing enterprise-level data.
Most SQL Server databases are managed and maintained by the IT department. To connect
to a SQL Server database, you’ll have to work with your IT department to obtain read access
to the database from which you’re trying to pull data.
Once you have access to the database, open Excel and activate the Power Pivot window by
selecting Power Pivot ➪ Manage.
Once activated, select the From Other Sources command button on the Home tab. This
will activate the Table Import Wizard dialog box shown in Figure 35.13. Here, select the
Microsoft SQL Server option and then click the Next button.
The Table Import Wizard will now ask for all the information it needs to connect to your
chosen data source. This includes things such as server address, login credentials, and any
other database name. The wizard will show you different fields based on the type of data
source you select. The more common fields you’ll see when connecting to an external data
source are as follows:
Friendly Connection Name The Friendly Connection Name field allows you to specify
your own name for the external source. You typically enter a name that is descriptive and
easy to read.
Server Name This is the name of the server that contains the database to which you are
trying to connect. You will get this from your IT department when they give you access.
Log on to the Server These are your login credentials. Depending on how your IT depart-
ment gives you access, you will select either Use Windows Authentication or Use SQL Server
Authentication. The Use Windows Authentication option essentially means that the server
will recognize you by your Windows login. The Use SQL Server Authentication option means
that the IT department created a distinct username and password for you. If you select Use
SQL Server Authentication, you will need to provide a username and password. Note that
you will need at least READ privileges for the target database to pull the needed data.