PowerPivot and Linked Table Names

When PowerPivot creates a linked table, it appears to assign an arbitrary name to the import. The table name in powerpivot (Table1, Table2 etc) is then usually renamed as part of the design process. However, this practice is sloppy because the table name in excel is different to that of powerpivot and the name in excel is poorly defined (which may confuse anyone updating data at a later stage). This post looks at methods of managing linked tables in powerpivot and excel in order to provide more robust models.

Import (without name)

The standard way of creating a linked table in powerpivot is simply to select the data range, click on the powerpivot ribbon and select ‘Create Linked Table’. Once this is done the ‘Create Table’ dialog displays (usually the user indicates the table has headers) and the table is imported into powerpivot.

The imported table is then renamed in powerpivot with a double click (or right click à rename) so that table name has meaning in the pivot table.

In excel, we can see that this operation has also created an excel table by selecting the ‘Name Manager’ button from the Formulas ribbon. A dialog showing all tables and ranges in the workbook is displayed. Note that Table1 has been created.

The creation of the linked table in powerpivot has defined a table in excel and then created a connection for that table in powerpivot. We can see this in powerpivot by the definition of the linked table.

While this may achieve the outcome of creating a usable powerpivot model, it can become frustrating for the user (or anyone updating excel data) because the definitions in excel (ie Table1) are not the same as the table names in powerpivot (eg Dates).

Importing a Named Table

One way around this is to define the excel table and its name before the excel data is imported into powerpivot. This is easily achieved by selecting a formatting style from the ‘Format as Table’ button in the Home ribbon. The name of the table can be edited using the name manager (Formulas à Name Manager à Edit (after the appropriate table has been selected)).

Now, when the powerpivot linked table is created, it is automatically imported with the same name as the excel table.

Rename the Table and Manage the Import

Where the powerpivot table is created first and then the name of the table is changed in excel, the definition of the table in powerpivot will be in error. This does not present as a problem until the data in powerpivot is refreshed. When the data is refreshed, an ‘Errors in Linked Tables’ dialog is shown (as below) so that the connection can be managed.

The options for management (as below) are rather self-explanatory and allow for;

  • The selection of another excel table. Note that this is any excel table (defined table) that is not linked to a power pivot table.
  • Materialising the table (that is, removing the link to excel)
  • Deleting the powerpivot table from the model

The selection of an existing excel table completely redefines the import so that the refresh will alter the columns and data in the table so that the powerpivot table will be exactly the same as the excel table. While this may seem intuitive, the implication for the model is enormous because the linked table in powerpivot can change structure (adding columns etc).

Other Linked Table Management

Alternatively, a linked table can be managed by the ‘table definition’ button in the ‘Linked Table’ ribbon in powerpivot (as highlighted below). When selected, the dropdown shows all tables that have been defined in the excel workbook (regardless of their usage as linked tables), and, upon table change, imports the new definition and data.

If the excel table is already used as a linked table in the powerpivot model, the user is warned that the table is already used in a link and continuing will break the link (as below). If the user continues, the ‘old linked table’ is materialised and cannot be updated.

Conclusion

The use of linked tables in powerpivot provides a flexible way of importing, managing and using data in the powerpivot model. However, the automatic creation of linked tables without good naming conventions between powerpivot and excel may be confusing as the model is further developed, changed and updated. In-fact, every excel workbook model regardless of the use of powerpivot should implement good naming conventions. However, this can be managed with excel and powerpivot in various ways at various stages of model development.

SQL Saturdays in Australia

As part of a teaser campaign, it is good to hint that SQL Saturdays will run in Australia this year.  This is a great opportunity to get in on some free training on SQL Server and the BI stack and catch up with your or just catch up with your peers.

 The events will run across most Australian capital cities from mid to end April .  Can’t make it on the week end?  Don’t worry, they will be run on week days.

BISM : Importing Text Files into Power Pivot

A recent post has shown an issue with the import of some text files into powerpivot. It would appear that, for some fields the default import for text files assumes integer data types regardless of the underlying type. Because the import utility cannot be configured to specify the data type for fields, the data in columns are dropped from the import.

Text files are imported into via the [From Text] button of the ‘Get External Data’ section of the powerpivot ribbon. The import options are very limited and only specify the file name and generic file properties (delimiter and first row field headings). The advanced option only specifies text encoding.

 

This works where the fields to be imported are numeric types (as above) or capable of numeric conversion but not when the field contains a character type. For example; the following pictures show the outcome of text imports over a non-numeric field. Here, the first record (NA) is not imported and the other records are converted to numeric data.

 

After the import, specifying and reimporting the field as a text data type converts the field but does not import solve the dropping of non-numeric data.

To import the file with the expected type (and all data), we need to provide a data file with quoted data. For example, the following file will import correctly.