Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

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

MSSQLTips author Dattatrey Sindol By:   |   Read Comments (10)   |   Related Tips: More > 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


Last Update: 12/26/2012


About the author
MSSQLTips author Dattatrey Sindol
Datta Sindol has over 5 years of experience in the IT industry working with SQL Server, SSIS, SSAS, SSRS.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, December 27, 2012 - 2:12:14 AM - Abdallah Read The Tip

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


Thursday, December 27, 2012 - 6:48:43 AM - Dattatrey Sindol Read The Tip

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 - 8:30:01 AM - Abdallah Read The Tip

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 - 12:06:43 PM - Dattatrey Sindol Read The Tip

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)


Friday, December 28, 2012 - 1:49:16 AM - Abdallah Read The Tip

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


Friday, December 28, 2012 - 9:49:07 AM - Dattatrey Sindol Read The Tip

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)


Wednesday, January 30, 2013 - 1:23:56 AM - Rupesh Read The Tip

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.

 


Thursday, January 31, 2013 - 9:33:09 AM - Dattatrey Sindol Read The Tip

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)


Friday, February 01, 2013 - 12:56:11 AM - Rupesh Read The Tip

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, February 28, 2013 - 2:26:15 PM - Dattatrey Sindol (Datta) Read The Tip

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)



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.