Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel
By: Dattatrey Sindol | Updated: 2012-12-26 | Comments (11) | Related: More > Microsoft Excel Integration
I have read a tip on Introduction to PowerPivot for Excel and SQL Server, which introduces us to PowerPivot add-in for Excel 2010 and also explains importing data from SQL Server into PowerPivot in brief. I want to know more about importing data into PowerPivot from different sources and how to relate the data imported from different data sources. Check out this tip to learn more.
PowerPivot add-in for Microsoft Excel supports importing data from a variety of data sources. In this tip we will cover the following aspects:
- Importing data from multiple data sources
- Establishing relationship between data imported from different data sources
Note: In this demonstration, we will be working with SQL Server 2012 PowerPivot add-in for Excel 2010, which can be downloaded here. In addition, we will be working with AdventureWorksDW Sample Database (2012 and 2008 R2 Versions), which are available for download on CodePlex.
In this demonstration, we will import the data from following three different sources:
- Tables from AdventureWorksDW2012 SQL Database (Download)
- Query/Result Set from AdventureWorksDW2012 SQL Database (Download)
- AdventureWorksDW2008R2 CSV Data File (Download)
Tables from AdventureWorksDW2012 SQL Server Database
To start with, let's import the data from AdventureWorksDW2012 SQL Database using the following steps:
- Open Microsoft Excel.
- Go to PowerPivot Tab and launch the PowerPivot Window by clicking on the PowerPivot button.
- In the PowerPivot Window, under the "Get External Data" Section of the top ribbon, notice that there are different sources from which data can be imported into PowerPivot like Databases, SSRS Report, Azure DataMarket, Data Feeds, Text Files, and many others. Click on "From Other Sources" to see a full list of supported data sources as shown below.
- Following is the full list of data sources supported by PowerPivot:
- Relational Databases
- Multidimensional Sources
- Data Feeds
- Report (SSRS Report)
- Azure DataMarket Dataset
- Other Feeds
- Text Files
- Excel File
- Text File
- Go to "Other Data Sources" and this will launch the "Table Import Wizard". Select "Microsoft SQL Server" in the "Connect to a Data Source" step (Alternatively, you can click on "From Database" and then "From SQL Server") and Click Next.
- In the "Connect to a Microsoft SQL Server Database" step, enter the Server Name, Authentication, and Database Name. Give the Connection a Friendly Name "SQLServer_Localhost_SQL2012NEW" as shown below and click Next.
- In the "Choose How to Import the Data" step, choose "Select from a list of tables and views to choose the data to import" and click Next.
- In the "Select Tables and Views" step, select the following three tables and click Finish.
Notice in the above screenshot that we can change the default name of the table and give it a friendly name under the "Friendly Name" column. The friendly name will be used to refer to the table inside PowerPivot.
- Once the data import is completed, click Close.
- In the PowerPivot window you can see that, all the above 3 tables have been imported as shown below.
- Now go to Design Tab of the Top Ribbon and click on "Manage Relationships". This will open the "Manage Relationships" window.
- Notice in the above screenshot that PowerPivot has automatically detected the relationship between DimReseller and DimGeography and FactResellerSales and DimReseller. This relationship is detected based on the Foreign Key Constraints defined in the underlying AdventureWorksDW2012 database.
Query and Result Set from AdventureWorksDW2012 SQL Server Database
Next let's get the data from AdventureWorksDW2012 SQL Database by using the following steps:
- Go to the Design Tab of the Top Ribbon and click on "Existing Connections".
- This will open up the "Existing Connections" window displaying the list of existing connections used previously within PowerPivot.
- Select the "SQLServer_Localhost_SQL2012NEW" connection and click on Open. This will start the "Table Import Wizard" and uses the selected connection for importing the data. Notice that, in the "Existing Connections" window, you can edit, delete, or add new connections. Note that, you could use any other sources here, but I have used the same connection to demonstrate that the existing connections can be re-used.
- In the "Choose How to Import the Data" step of "Table Import Wizard" select "Write a query that will specify the data to import" and click Next.
- In the "Specify a SQL Query" window, enter the below query and set the "Friendly Query Name" to "Sales Territory Query", which will be used as the Table Name, for the dataset returned by the query, after data is imported into PowerPivot.
SELECT SalesTerritoryKey , SalesTerritoryRegion , SalesTerritoryCountry , SalesTerritoryGroup FROM dbo.DimSalesTerritory
- Notice that, in this step, we can validate the query entered and alternatively, we can build the query. Click Finish and click Close when the data import is completed.
- Once the data is imported, our PowerPivot windows should have four tables in it including "Sales Territory Query" table as shown below.
- Now go to the Design Tab of the Top Ribbon and select "Manage Relationships" and notice that, the "Manage Relationships" window shows relationship between the first three tables and does not contain any entry for relationship with "Sales Territory Query" since we have imported the data for Sales Territory using a SQL Query and PowerPivot cannot automatically detect any relationship between them.
- Click on Create in the "Manage Relationships" window and define the relationship between "Sales Territory Query" and "DimGeography" tables as shown below.
- After the relationship is defined, the "Manage Relationships" window should look as shown below.
AdventureWorksDW2008R2 CSV Data File
Finally, let's get the data from AdventureWorksDW2008R2 CSV data file by using the following steps:
- Go to "Other Data Sources" and this will launch the "Table Import Wizard". Select "Text File" in the "Connect to a Data Source" step and Click Next.
- In the "Connect to Flat File" step, enter a friendly name for the connection, set the path of the "DimDate.csv" CSV file, set the Column Separator to "Tab (t)". Click on "Advanced" button and set the Encoding "Unicode (UTF-16)" in the "Advanced Settings" dialog box and click OK.
- After making the above settings, the "Table Import Wizard" looks as shown below.
Notice in the above screenshot that, column names have been defaulted to F1, F2, and so on, since the CSV file does not have column headers in it. Click Finish to import the data.
Also note that, at this point, you can filter the data, select/unselect the columns if you wish as shown in the below screenshot. However, for this demonstration, we will leave it to the default selection.
- Once the data is imported into PowerPivot, go to "DimDate" sheet/table, right-click on the "F1" column and select "Rename Column" from context menu. Rename the column to "DateKey".
- Rename all the columns as per the below table.
Old Column Name
New Column Name
F1 DateKey F2 FullDateAlternateKey F3 DayNumberOfWeek F4 EnglishDayNameOfWeek F5 SpanishDayNameOfWeek F6 FrenchDayNameOfWeek F7 DayNumberOfMonth F8 DayNumberOfYear F9 WeekNumberOfYear F10 Month F11 SpanishMonthName F12 FrenchMonthName F13 MonthNumber F14 CalendarQuarter F15 Year F16 CalendarSemester F17 FiscalQuarter F18 FiscalYear F19 FiscalSemester
- After renaming the columns, the "DimDate" worksheet should look as shown below.
- Now go to Design Tab of the Top Ribbon, select "Manage Relationships" and notice that, there is no relationship between the existing tables and the newly added "DimDate" table. Create a new relationship between "DimDate" and "FactResellerSales" based on "ShipDateKey". After creating the relationship, our "Manage Relationships" window should look as shown below.
- Go to the Home Tab of the Top Ribbon and click on "Diagram View" on the top right, and this will display the tables in a diagram format as shown below. Alternatively, you can also click on the Diagram or Grid Icons at the bottom right corner of the PowerPivot Window to switch to Diagram or Grid View respectively.
- Install PowerPivot add-in for Excel and explore the options demonstrated in this tip.
- Check out these tip(s):
- Check out my previous tips
Last Updated: 2012-12-26
About the author
View all my tips