An important step when you are learning Data Warehouse is connecting to external database. You can import data from many data sources, including relational databases, multidimensional sources, data feeds, and text files. The real power of this broad data-source support is that you can import data from multiple data sources in Power Pivot, and then either combine the data sources between them. This enables you to perform an analysis on the data as if it was from a unified source. Importing relational data through the Power Pivot add-in is often a faster and more efficient alternative to importing in Excel.

CREATING

Today, I created a simple database in Microsoft Access and I put some information to manipulate. It is something nice to do because you will start to see how the database works and where you can find the information, but if you are familiar with any kind of database you will get it easily!

Connecting to external database

CONNECTING

I created a new Excel file that connects with the external database. In this case is Microsoft Access, but you can also connect with a SQL Server database.

Connecting to external database

Connecting to external database

TIP: If is there any update in the Access database, how will it appears in the Power Pivot? There’s a button to refresh the connection and get all the updates!

Connecting to external database

IMPORTANT: Check with a database administrator to get database connection information and to verify you have permission to access the data when you work with a real database, this one was created to learn Power Pivot.

Leave a Comment

Your email address will not be published. Required fields are marked *