Load Data to Power Pivot 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 department. To connect to a SQL Server database, you have to work with your IT department to obtain Read access to the database you're trying to pull from.

After you have access to the database, 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 here. There, select the Microsoft SQL Server option and then click the Next button.

The Table Import Wizard now asks for all the information it needs to connect to your database. On this screen, you need to provide the information for the options described in this list:

• Friendly Connection Name: The Friendly Name field allows you to specify your own name for the external source. You typically enter a name that is descriptive and easy to read.
• Server Name: This is the name of the server that contains the database you're trying to connect to. You get this from your IT department when you gain access. (Your server name will be different from the one shown.)
• Log On to the Server: These are your login credentials. Depending on how your IT department gives you access, you select either Windows Authentication or SQL Server Authentication. Windows Authentication essentially means that the server recognizes you by your windows login. SQL Server Authentication means that the IT department created a distinct username and password for you. If you're using SQL Server Authentication, you need to provide a username and password.
• Save My Password: You can select the check box next to Save My Password if you want your username and password to be stored in the workbook. Your connections can then remain refreshable when being used by other people. This option obviously has security considerations, because anyone can view the connection properties and see your username and password. You should use this option only if your IT department has set you up with an application account (an account created specifically to be used by multiple people).
• Database Name: Every SQL Server can contain multiple databases. Enter the name of the database you're connecting to. You can get it from your IT department whenever someone gives you access.

After you enter all the pertinent information, click the Next button to see the next screen, shown in the following figure. You have the choice of selecting from a list of tables and views or writing your own custom query using SQL syntax. In most cases, you choose the option to select from a list of tables.

The Table Import Wizard reads the database and shows you a list of all available tables and views. Tables have an icon that looks like a grid, and views have an icon that looks like a box on top of another box.

The idea is to place a check mark next to the tables and views you want to import. Note the check mark next to the FactInternetSales table. The Friendly Name column allows you to enter a new name that will be used to reference the table in Power Pivot.

You see the Select Related Tables button. After you select one or more tables, you can click this button to tell Power Pivot to scan for, and automatically select, any other tables that have a relationship with the table(s) you've already selected. This feature is handy to have when sourcing large databases with dozens of tables.

Importing a table imports all columns and records for that table. This can have an impact on the size and performance of your Power Pivot data model. You will often find that you need only a handful of the columns from the tables you import. In these cases, you can use the Preview & Filter button.

Click the table name to highlight it in blue, and then click the Preview & Filter button. The Table Import Wizard opens the Preview Selected Table screen, shown here. You can see all columns available in the table, with a sampling of rows.

Each column header has a check box next to it, indicating that the column will be imported with the table. Removing the check mark tells Power Pivot to not include that column in the data model.

You also have the option to filter out certain records. The following figure demonstrates that clicking on the drop-down arrow for any of the columns opens a Filter menu that allows you to specify criterion to filter out unwanted records.

This works just like the standard filtering in Excel. You can select and deselect the data items in the filtered list, or, if there are too many choices, you can apply a broader criteria by clicking Date Filters above the list. (If you're filtering a textual column, it's Text Filters.)

After you finish selecting your data and applying any needed filters, you can click the Finish button on the Table Import Wizard to start the import process. The import log, shown here, shows the progress of the import and summarizes the import actions taken after completion.

The final step in loading data from SQL Server is to review and create any needed relationships. Open the Power Pivot window and click the Diagram View command button on the Home tab. Power Pivot opens the diagram screen, where you can view and edit relationships as needed.

Don't panic if you feel like you've botched the column-and-record filtering on your imported Power Pivot table. Simply select the worrisome table in the Power Pivot window and open the Edit Table Properties dialog box (choose Design-->Table Properties). Note that this dialog box is basically the same Preview & Filter screen you encounter in the Import Table Wizard. From here, you can select columns you originally filtered out, edit record filters, clear filters, or even use a different table/view.