Microsoft Business Intelligence Tools for Excel Analysts

Chapter 4: Loading External Data into Power Pivot

In This Chapter

• Importing from relational databases
• Importing from flat files
• Loading data from other data sources
• Refreshing and managing external data connections

In Chapter 3, you loaded the data already contained within the workbook in which you're working. But as you discover in this chapter, 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 powerful is its ability to consolidate data from disparate data sources and build relationships among them. This means you can theoretically create a Power Pivot Data Model that contains some data from an SQL Server table, some data from a Microsoft Access database, and even data from a one-off text file.

In this chapter, we show you how to import external data into your Power Pivot Data Models.

Loading Data from Relational Databases

Relational databases are one of the more common data sources used by Excel analysts. It’s not difficult to find an analyst who frequently uses data from Microsoft Access, SQL Server, or Oracle databases. In this section, we 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 the storing of enterprise-level data. Most SQL Server databases are managed and maintained by the IT ...