Import SQL Data from Multiple Data Sources into PowerPivot for Excel

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).

In this demonstration, we will import the data from the following three different sources:

  • Tables from AdventureWorksDW2012 SQL Database.
  • Query/Result Set from AdventureWorksDW2012 SQL Database.
  • AdventureWorksDW2008R2 CSV Data File.

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

    F1DateKey
    F2FullDateAlternateKey
    F3DayNumberOfWeek
    F4EnglishDayNameOfWeek
    F5SpanishDayNameOfWeek
    F6FrenchDayNameOfWeek
    F7DayNumberOfMonth
    F8DayNumberOfYear
    F9WeekNumberOfYear
    F10Month
    F11SpanishMonthName
    F12FrenchMonthName
    F13MonthNumber
    F14CalendarQuarter
    F15Year
    F16CalendarSemester
    F17FiscalQuarter
    F18FiscalYear
    F19FiscalSemester
  • 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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *