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.
- From SQL Server
- From Access
- 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.
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.
- Select from a list of tables and views to choose the data to import
- 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.
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.
Pic 3: Connecting to MS Access.
The rest of the process for obtaining data is very similar to SQL Server steps outline above.