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














































Creating Linked Tables in PowerPivot for Excel

MSSQLTips author Dattatrey Sindol By:   |   Read Comments (3)   |   Related Tips: More > Microsoft Excel Integration
Problem

I have read a tip on Importing Data from Multiple Data Sources into PowerPivot for Excel. I have data in Excel which is frequently updated and I want to know if I can consume that data from Excel in PowerPivot, while I continue to modify the data in Excel and when I need it. Is this possible in PowerPivot? If yes, how it can be achieved?

Solution

In this tip, we will see how we can consume data from Excel in PowerPivot so that the updates in Excel are readily reflected in PowerPivot. PowerPivot has a feature called "Linked Tables" which allows us to address this requirement.

Note: In this demonstration, we will be working with SQL Server 2012 PowerPivot add-in for Excel 2010, which can be downloaded here. And we will be working with AdventureWorksDW Sample Database (2012 & 2008 R2 Versions), which are available for download on CodePlex.

For this demonstration, let us copy the data from a SQL Server Table and paste it into the Excel file which we created in our previous tip on Importing Data from Multiple Data Sources into PowerPivot for Excel. We are doing this just for continuity purposes and also this updated Excel/PowerPivot data file(s) will be used in the subsequent tips. You may start from a brand new excel file if you wish to do so.

Open the Excel file which we created in our previous tip. Notice that, if PowerPivot window is also opened, you will find two windows - One is an Excel Window and another One is a PowerPivot Window as shown below.

Excel and PowerPivot Windows

Click on Excel file and ensure that, it is the active window. Open SQL Server Management Studio (SSMS), connect to AdventureWorksDW2012 database, and execute the below query.

SELECT 
    EmployeeKey
    , ParentEmployeeKey
    , SalesTerritoryKey
    , FirstName
    , LastName
    , Title
    , DepartmentName
    , HireDate
    , Phone
    , Gender
FROM [dbo].[DimEmployee]

Once the above query is executed, copy the results along with headers into the clipboard.

Copy query results

Add a new tab in the excel file which you opened in the beginning and name it as "DimEmployee" (You can use an existing sheet if you wish and you can either rename it to something meaningful like "DimEmployee" or any other name of your choice) and paste the data copied in the previous step into the "DimEmployee" sheet as shown below.

Query results copied into Excel

Click on the first cell in "DimEmployee" sheet in the excel, go to Home Tab in the Top Ribbon, select "Format as Table" and choose one of the formats of your choice. You will be presented with a dialog box asking "Where is the data for your table?", ensure that the entire range of data in the current sheet is set in the "Range" and "My table has headers" checkbox is selected and click OK.

Format Excel data as Table

Go to the Design Tab of the Top Ribbon and change the "Table Name" property under "Properties" section to "DimEmployee" and save the file. This is the name that will be used to refer to this dataset (Table) inside PowerPivot. Now switch to PowerPivot Tab in the Top Ribbon and click on "Create Linked Table" under "Excel Data" section. This will add a table named "DimEmployee" to PowerPivot.

Next switch to PowerPivot window and go to Design Tab of the Top Ribbon, click on "Manage Relationships", and create relationship between "DimEmployee" and "FactResellerSales" tables. To keep the things simple, we are not creating any relationship between "DimEmployee" and existing "Sales Territory Query" tables. After creating the relationship, the Manage Relationships window should look as shown below.

Manage Relationships window

After creating the linked table and the required relationships, the Diagram View of tables in PowerPivot window looks as shown below.

PowerPivot Window after Adding DimEmployee table and Creating Required Relationships

After adding Linked Table to PowerPivot, a New Tab is available on the Top Ribbon in PowerPivot named "Linked Table" under "Table Tools" as shown below (You get to see this tab only when DimEmployee Table is selected).

Linked Table Tab in PowerPivot Top Ribbon

Notice that the following options are available in the "Linked Table" Tab of Top Ribbon:

  • Update All: Clicking this button will update all the linked tables in PowerPivot (Those tables in PowerPivot which are linked to Tables in Excel).
  • Update Selected: Clicking this button will update the selected table in PowerPivot which is created as a Linked Table linked to a Table in Excel.
  • Excel Table: Click this button to see a list of Tables available in Excel Workbook.
  • Go to Excel Table: Clicking this button will switch to an Excel Window and take you to the corresponding Table in Excel.
  • Update Mode: This drop-down has two options - Automatic and Manual. Automatic and Manual options are used to indicate whether any changes in Excel Table are automatically reflected in PowerPivot or need to be manually refreshed in PowerPivot respectively. Keep this option set to "Automatic" for the purpose of this demonstration.

Now go to the "DimEmployee" table in Excel and add a new column after "LastName" and before "Title". Name it "FullName", enter the below formula, and hit "Enter".

=[@FirstName] & " " & [@LastName]

Updated Table in Excel looks as shown below.

Newly added column in Excel Table

Now switch back to PowerPivot window and notice that the "FullName" column which we added to Excel Table is showing up in "DimEmployee" table in PowerPivot since we have set the "Update Mode" to "Automatic". The Diagram View in PowerPivot should look as shown below.

Newly added column in Excel Table reflecting in PowerPivot Table

As we can see from this demonstration, consuming data from Excel in PowerPivot is simple and convenient. This capability of PowerPivot allows the users to continue to update the data in Excel while consuming that data for analysis in PowerPivot at the same time.

Next Steps


Last Update: 12/31/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




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Monday, December 31, 2012 - 9:18:54 AM - GeriH Read The Tip

Thanks - Very helpful


Monday, December 31, 2012 - 11:14:35 AM - Brent Read The Tip

It has been my experience that the linked table in PowerPivot does not get updated 'automatically'. If I modify the table in Excel, then 'refresh all' pivot tables in Excel, the updates do not flow to the pivot tables. However, if I just go to the linked table in PowerPivot, as soon as the table is displayed - it updates. Then I return to Excel & 'refresh all' pivot tables - they are updated with the changes. Anyone else observe this???


Wednesday, April 17, 2013 - 3:54:25 AM - OZBSB.com Read The Tip

Thanks a lot.. this has helped me to understand basics of PP..



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

Signup for our newsletter


Comments
*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 | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.