Creating Linked Tables in PowerPivot for Excel

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

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.

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.

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.

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.

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.

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

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

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.

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.

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

• Read Importing Data from Multiple Data Sources into PowerPivot for Excel tip and build the initial Excel/PowerPivot workbook
• Apply and explore the options demonstrated in this tip.
• Check out these tip(s):
  • Understanding the SQL Server 2012 BI Semantic Model (BISM)
• Check out my previous tips