Pivottable data connection models

The way a datasource connects to a PivotTable depends on the datasource. In this post an overview of the six connection models that you can create by means of Excel’s userinterface.

The figure below presents the six basic connection models between a PivotTable and it’s datasource.

In fig 1 most dashed rectangles serve as caches, and are often invisible.
A PivotTable is never directly connected to a datasource. Every PivotTable gets its data from a PivotCache, an object that is automatically created when you add a PivotTable to a worksheet. PivotTables often share one PivotCache. When you create another PivotTable from the same datasource, Excel will usually connect the PivotTable to the existing PivotCache. This explains why a refresh of one PivotTable often refreshes other PivotTables as well.

SourceType and QueryType

The ListObject has a property SourceType and the QueryTable a property QueryType; the PivotCache object has both properties. The values of these properties for each connection model are shown in fig 1 above and explained in the next table:

QueryType	ListObject.SourceType	PivotCache.SourceType
1 = ODBC Query	0 = Sharepoint	1 = Excel database
2 = DAO Recordset	1 = Range	2 = External
4 = Webquery	2 = XML	3 = Consolidation
5 = OLEDB Query	3 = Query	4 = Scenario
6 = Text import		-4148 = PivotTable
7 = ADO Recordset

For details see the VBA help.

Model A: Internal connection

Create a table on a worksheet, add some rows with data and/or formula’s, and create a PivotTable with the table as source. This is a typical model A, where PivotCache.SourceType = Database.
In other model A situations SourceType is Consolidation or Scenario.

Model B: Database connection to table

In the ribbon select tab ‘Data’ and then the first button, ‘From Access’:

In the dialogue ‘Select Data Source’ select an Access database, or change the filetype to ‘All Files (*.*)’ and select an Excel workbook:

In the dialogue ‘Import Data’ select the first option ‘Table’, and choose a location:

Remarks (see fig 1):

• The external datasource is cached in a QueryTable which is tightly connected to a ListObject. In fact, the QueryTable is a kind of hidden (Activesheet.QueryTables.Count = 0) and can only be reached by property ListObject.QueryTable;
• All the usual properties and methods of this QueryTable object can be used. For example, to add a column with unique row numbers to the ListObject you can set QueryTable.RowNumbers to True. The header of this column will be named ‘_RowNum’; you can change this name, but after refreshing the data it will be changed back to ‘_RowNum’;
• You can refresh the data by ListObject.Refresh or by QueryTable.Refresh. However, the parameter BackgroundQuery=True/False can only be used with the QueryTable.Refresh method. An alternative is to set property QueryTable.BackgroundQuery to True or False before refreshing;
• The value of PivotCache.SourceType is Database, and not External as you perhaps might have expected. Indeed, from the perspective of the PivotCache this object has nothing to do with the external datasource;
• A drawback of a model B connection is that data is cached twice. A benefit is that you can enrich the sourcedata in an easy way by means of calculated columns in the ListObject. An alternative for calculated columns is to import data by a SQL-command.

Model C: Database connection to PivotTable

For a model C connection follow the steps for model B, but in the dialogueue ‘Import Data’ (Fig 4) select the second option, ‘PivotTable Report’.
Remarks:

• In this connection model the PivotCache takes over the role of the QueryTable. In fact, a PivotCache is basically a QueryTable;
• You can refresh the data by PivotTable.RefreshTable or by PivotCache.Refresh. None of these methods support the Backgroundquery parameter, so the only way to control this is to set property PivotCache.BackgroundQuery;
• The property PivotCache.SourceType is now set to External, as expected;
• A model B connection is efficient since data is not cached twice. A drawback is that you cannot easily enrich the sourcedata, as in model B. However, in a model C connection the PivotCache is able to import data by a SQL-command.

Model D: Webquery or Textimport

In this connection model the QueryTable is not only cache but also responsible for showing the data on the worksheet. (I assume this was the setup for model B as well, until Microsoft introduced the ListObject in Excel 2003). It is not possible to manually add a ListObject to the QueryTable range. Fortunately, you can add calculated columns to the QueryTable itself by setting property QueryTable.FillAdjacentFormulas to True. Enter a new column header on the right side of the QueryTable range, and enter your formula in the first row. After a refresh Excel will fill the whole column with this formula automatically. Unfortunately it is not possible to use SQL commands with a webquery or a textimport;

Model E: XML import

Xml data are not cached but imported directly into the ListObject. When you import Xml-data an XmlMap object must be present or will be created. The XmlMap object knows if/where to map each element of the Xml-file schema. Data from an Xml-file can be mapped to one or more worksheets, to ListObjects and/or to single cells. The ListObject (of which property SourceType has value SrcXml) is sometimes called ‘Xml table’.

Model F: Datamodel

To create this connection model you need Excel 2010+ and PowerPivot. After importing data into the datamodel you can insert a PivotTable from the PowerPivot tab. To do this from the Insert tab you must select ‘Use an external data source’ and choose the connection with PowerPivot Data:

Although the datamodel is ’embedded’ it behaves by all means as an external source, and indeed PivotCache.SourceType = External. The datamodel is an SSAS OLAP cube: PivotCache.OLAP = True. Basically, a model F connection is the same as model C.

Final notes

By means of Excel’s userinterface you can create connections from other sources as well. For example from a SQL Server table/query, or from a SSAS Cube. In most cases you will be able to choose between a model B or a model C connection.
By using VBA many other connection models can be created, in which – for example – the QueryTable is replaced by a cache of your choice: an Ado recordset (connected to a database table or query), a Html DOM document (webquery), or an Xml DOM document (Xml-import).

I hope this pixcel helps. Excel!