Load Data to Power Pivot from Relational Database Systems

By Michael Alexander

Whether your data lives in Oracle, Dbase, or MySQL, you can load data from virtually any relational database system. As long as you have the appropriate database drivers installed, you have a way to connect Power Pivot to your data.

Open the Power Pivot window and click the From Other Sources command button on the Home tab. This opens the Table Import Wizard dialog box, shown. The idea is to select the appropriate relational database system. If you need to import data from Oracle, select Oracle. If you need to import data from Sybase, select Sybase.

From-Other-Sources
Open the Table Import Wizard and select your target relational database system.

Connecting to any of these relational systems takes you through roughly the same steps as importing SQL Server data, earlier in this chapter. You may see some alternative dialog boxes based on the needs of the database system you select.

Understandably, Microsoft cannot possibly create a named connection option for every database system out there. So you may not find your database system listed. In this case, simply select the Others option (OLEDB/ODBC). Selecting this option opens the Table Import Wizard, starting with a screen asking you to enter or paste the connection string for your database system.

connection-string
Enter the connection string for your database system.

You may be able to get this connection string from your IT department. If you’re having trouble finding the correct syntax for your connection string, you can use the Build button to create the string via a set of dialog boxes. Pressing the Build button opens the Data Link Properties dialog box.

Data-Link-Properties
Use the Data Link Properties dialog box to configure a custom connection string to your relational database system.

Start with the Provider tab, selecting the appropriate driver for your database system. The list you see on your computer will be different from the list shown. Your list will reflect the drivers you have installed on your own machine.

After selecting a driver, move through each tab on the Data Link Properties dialog box and enter the necessary information. When it’s complete, click OK to return to the Table Import Wizard, where you see the connection string input box populated with the connection string needed to connect to your database system.

final-syntax
The Table Import Wizard displays the final syntax for your connection string.

To connect to any database system, you must have that system’s drivers installed on your PC. Because SQL Server and Access are Microsoft products, their drivers are virtually guaranteed to be installed on any PC with Windows installed. The drivers for other database systems, however, need to be explicitly installed — typically, by the IT department either at the time the machine is loaded with corporate software or upon demand. If you don’t see the needed drivers for your database system, contact your IT department.