Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel

By:   |   Updated: 2012-12-26   |   Comments (11)   |   Related: > Microsoft Excel Integration


Problem

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.

Solution

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.
    Launch PowerPivot

  • 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.
    Get External Data - Table Import Wizard

  • Following is the full list of data sources supported by PowerPivot:
  • 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.
    Configuring Connection to SQL Server Connection

  • 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.
    • DimGeography
    • DimReseller
    • FactResellerSales

    Table Selection for Data Import

    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.
    Tables Imported into PowerPivot

  • Now go to Design Tab of the Top Ribbon and click on "Manage Relationships". This will open the "Manage Relationships" window.
    Manage Relationships


    Relationships Detected by PowerPivot

  • 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".
    Open Existing Connections

  • This will open up the "Existing Connections" window displaying the list of existing connections used previously within PowerPivot.
    List of Existing Connections

  • 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.
    Sales Territory Query Table Imported into PowerPivot

  • 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.
    Relationship bewteen [Sales Territory Query] Table and [DimGeography] Table

  • After the relationship is defined, the "Manage Relationships" window should look as shown below.
    Newly defined Relationship bewteen [Sales Territory Query] Table and [DimGeography] Table

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.
    Connecting to a CSV File

    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.

    Applying Filters and Columns 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".
    Renaming Columns

  • 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.
    Columns Renamed

  • 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.
    All Table(s) Relationships

  • 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.
    Diagram View of the Tables
This diagrammatic display is similar to the view in Data Source View in SQL Server Analysis Services and is convenient to visualize the relationships between different tables.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dattatrey Sindol Dattatrey Sindol has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-12-26

Comments For This Article




Monday, August 17, 2015 - 12:15:50 PM - Sinan Back To Top (38458)

Hi,

I have an SQL table which has no change in it (last year data) and a dynamic table.

I understand the appanding tables. But since it is very time consuming to retrieve old data, how could I get the old data table once and break the connection?

Regards

Thursday, February 28, 2013 - 2:26:15 PM - Dattatrey Sindol (Datta) Back To Top (22487)

Hi Abdallah,

 

Following tip demonstrates how to combine data from multiple sources tables into one single PowerPivot table (UNION ALL).

http://www.mssqltips.com/sqlservertip/2893/combining-data-from-multiple-relational-data-sources-into-one-table-in-powerpivot-for-excel/

 

Hope that helps!

 

Best Regards,

Dattatrey Sindol (Datta)

Friday, February 1, 2013 - 12:56:11 AM - Rupesh Back To Top (21857)

Hi Datta

 

Thanx a lot for responding to the query.

 

I Have one more issue.

 

Suppose I have a set of tabe in a data base.

I had imported those tables to create a tabular mode and performed the required operations.

Then I deployed that project and created report from that model.

Now if data in the source got changed then how does those changes reflect in the tabular model.

Is there any option to reprocess the model or cube as it is given in SSAS.

 

Thursday, January 31, 2013 - 9:33:09 AM - Dattatrey Sindol Back To Top (21838)

Hi Rupesh,

 

Yes it is possible to give a Friendly Name to a table after importing the table.

Yes Renaming a Table and giving Friendly Name are both one and the same.

Please Rename the Tables to give a Friendly Name to those tables.

 

Hope that helps!

 

Best Regards,

Dattatrey Sindol (Datta)

Wednesday, January 30, 2013 - 1:23:56 AM - Rupesh Back To Top (21784)

Please try to provide solutions to these queries

 

Is it possible to give the 'Friendly Name' to the table after importing the tables ?

Is renaming a table and giving friendly name to tables both are same????

I have already imported some tables in the Data Tool and I want to add 'friendly name' to those tables.

 

Friday, December 28, 2012 - 9:49:07 AM - Dattatrey Sindol Back To Top (21173)

Hi Abdallah,

 

Yes it is possible to combine the data from multiple tables/queries into a Single Table in PowerPivot. I will be covering that in my next tip.

 

Best Regards,

Dattatrey Sindol (Datta)

Friday, December 28, 2012 - 1:49:16 AM - Abdallah Back To Top (21166)

Hi Datta

We are creating new database every year for some reasons. Now, we will would like to have several reports which will retrieve information from previous databases (e.g. 2 years) and include them on the current year. If I want to know Sales for 1 year, 2 year and the current year.

 

Regards,

 

Abdallah

Thursday, December 27, 2012 - 12:06:43 PM - Dattatrey Sindol Back To Top (21161)

Hi Abdallah,

 

Can you please tell me what is your exact requirement? If you could provide me some additional details around your requirement, I can provide you with better inputs, since PowerPivot already has the capability to bring in data from different sources and generate a report with data from different sources.

 

Best Regards,

Dattatrey Sindol (Datta)

Thursday, December 27, 2012 - 8:30:01 AM - Abdallah Back To Top (21156)

Dear Datta

Is it possible and how can I use OPEN QUERY technique which has been used in other languages?

 

Thanks

 

Thursday, December 27, 2012 - 6:48:43 AM - Dattatrey Sindol Back To Top (21152)

Hi Abdallah,

 

In the above scenario, we have extracted data from 3 different sources. In the future posts, we will do reporting using data from all these 3 different sources (Combining/Merging data from all these 3 sources into one report).

 

Hope that answers your question.

Stay Tuned!

 

Best Regards,

Dattatrey Sindol (Datta)

Thursday, December 27, 2012 - 2:12:14 AM - Abdallah Back To Top (21145)

I would like to know if there is a easy way of extracting data from two different databases and merge for reporting














get free sql tips
agree to terms