Importing PowerPivot data from SQL Server or Access

In order to import data into PowerPivot from SQL server, you need to navigate to "From Database" button of the Home ribbon. This operation will open up a selection of database types you can use for the data import.

 

  1. From SQL Server
  2. From Access
  3. From Analysis Services or PowerPivot

We are going to look at the first option "From SQL Server" in this tutorial. If we click on this option, we are going to be presented with the Table Import Wizard. This wizard takes us through Server Name as well as Database selections. Make sure you run Test connection option after all of the required data has been provided.

It is important to know authentication that is allowed by your SQL Server. It may use Kerberos authentication and then your Windows Login will be automatically recognized by the SQL Server. However, DB administrators prefer to use SQL Server user id. If this is the case in your organization, you may need to provide user id and password provisioned to you by the DB Administrators.

 

Some description
Pic 1: Connect to a Microsoft SQL Server Database

Next step is designed to determine a method in which you will retrieve data from the database. You are presented with two different radio buttons. Each represents its own way of extracting data from the underlying database tables.

 

  1. Select from a list of tables and views to choose the data to import
  2. Write a query that will specify the data to import

If you decided to select first option or “Select from a list of tables or views”, you will be presented with the schema view of your DBMS. There are two additional configurations you may apply prior to the data export. You can name table names with more user friendly names. In addition, you can apply filters for each of the tables you are selecting data from.

 

Some description
Pic 2: Select Tables and Views

You may also preview data that you are about to extract into PowerPivot by clicking on “Preview & Filter” button. Clicking on “Finish” button will kick start the process and produce detailed status of each table or view importing process.

Importing data into PowerPivot from Access is fairly similar to importing from SQL Server. The only difference in how you connect to a data source. MS Access is a network based file database. All you need to access this file, is to point to a network file location.

 

Some description
Pic 3: Connecting to MS Access.

The rest of the process for obtaining data is very similar to SQL Server steps outline above.

Featured pages

Sharepoint

Learn SharePoint integration with PowerPivot and start utilizing enterprise grade software for comp…

Excel

Learn how to use PowerPivot in Excel and gain insights into your data. PowerPivot for Excel helps …

SQL

Learn about little known PowerPivot tips and tricks and make your Excel PowerPivot Business Intell…

Intelligence

Learn PowerPivot Business Intelligence capabilities and perform BI tasks on large data sets using …