Home > BISM, DAX, PowerPivot > Managing Connections In Power Pivot

Managing Connections In Power Pivot

Demonstrations that show importing data into Power Pivot do this from database icon in the home window (as identified below). However, once data is in your model and you wish to add new tables it is wiser to reuse the existing connection rather than create a new connection for each import. Each time that table(s) are added by this dialogue, a new connection is created within the model with multiple connections possible referring to the same source. For model simplicity, it is better to have a single connection referring to a single data source.

Standards for Import

The standard method importing data into Power Pivot is to use the import from database icon as shown in the diagram below (when a database is being used). This utility has features that allow you to detect relationships between tables and may suffice from two models. That is, when there are no existing tables and the model.

What this utility does is create a new connection to the data source each time the Wizard is invoked. Consider for example, an existing model that has the product table from Adventure Works imported. The initial import did not include the ProductCategory and ProductSubCateogory table. Naturally, we would want to include this in the model.

If we use this Wizard to reimport the tables, a new connection is set up. This connection is associated with the two new tables. We can see this by selecting the “Existing Connections” icon in the design.

Note that there are two connections in the model (as below) which was caused by the “re-import”of data once the initial population of the model had occurred.

Altering an Existing Connection

We can add data to the existing model and reuse an existing connection at the same time. If we wish to do this you simply reopen the existing connections properties and append tables (or add a query). Click the ‘Open’ button from the dialogue and select either the ‘Select from a list of tables…’ or ‘Write a query that will….’.


This will append any new tables to the model utilising existing connection.

Perhaps the main reason for reusing the connection is that it keeps the model succinct and manageable. Once a table has been imported through a connection the connection properties for that table cannot be changed without first deleting all the tables using the connection. Naturally this would increase the amount of rework required to the model should consolidation of connections be required at a later stage.

Finally, the connections are retained within the model even after all the tables using connection have been deleted. The connection must manually be deleted from the ‘Existing Connections’ dialogue.




About these ads
Categories: BISM, DAX, PowerPivot Tags: ,
  1. April 12, 2013 at 4:39 pm | #1

    Is there a way to delete the connections after the initial load of data, so you load from MS Access – don’t need to refresh ever again. Doesn’t seem to let me delete the connection. I thought info stored in worksheet, just don’t want spreadsheet to ever try to link to connection again.

    • April 12, 2013 at 4:47 pm | #2

      Hi Jim,

      Not in Excel, although if you are sure that the data will not change, then perhaps just paste it in (this will not create a connection). If you are going to deploy the model to the server as an SSAS DB (with linked s/s), you’ll lose the connection anyway.

      HTH, Paul

  2. April 12, 2013 at 6:02 pm | #3

    I cut/pasted the data into a blank excel sheet, re-did me slicers, etc. I made sure there were no connections listed. When I open the spreadsheet up in Excel 2010 with and without powerpivot add-in get error stating Initialization of the data source failed.

  3. April 12, 2013 at 6:07 pm | #4

    Can you open the PPVT widow and see the data model?

    • Jim `Hutch` Hutcheson
      April 12, 2013 at 6:20 pm | #5

      Very strange, have to have version 2 of Powerpivot for Excel 2010 add-in installed and everything works fine, If version different – issues, no add-in issues. Any good write-up talking about restrictions between versions?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: