Extracting Connection Information from Pivot Tables
It’s pretty easy to add pivot tables to worksheets – even easier to add them when the source is a cube. However there are a couple of ways to go about adding them which could make your life easier – that’s one of the things we look at in this post.
When you first create a connection to an OLAP source (and I am including tabular in that), a connection is created in the work book. So just for fun, let’s go through the process and connect to the cube. On the Data tab chose From Analysis Services from the Other Sources dropdown.
We’re asked for a server (not shown) so let’s put in the correct details and then click next. Then we get a list of cubes and perspectives to connect to (I’m using adventure works here so the names appearing in the image below would be familiar to most).
We then have the option to specify a few additional details. Perhaps the most important of these are the filename that stores the connection and the friendly name. Most of the time though we probably discard putting any thought into this and just click next. However in the image below, I’ve specified the filename and the friendly name. Finally we can click Finish and get to work on the pivot table.
The Next Pivot Table
Now when it comes to the next pivot table we can run through the process again – if we chose the same names we’ll get a warning but let’s not go into that. Alternatively, we can just insert a new pivot table and select an existing connection.
You can see this in the screen flow below. When we create a new pivot, we can choose the external source and, then the connection.
There’s one big reason why you’d want to insert a new pivot like this – it’s a lot easier to maintain the workbook when you need to understand where data is coming from (or you need to change data sources).
The Detail – Getting Connection and Pivot Info
It would be a pretty ordinary post if that’s all I had to say (no code after all) – but now the fun starts. Workbooks never finish up as simple and elegant as we’d like – NEVER. It’s easy to add new data sources, new pivots and even pivots of data sourced from other pivots. Throw 20-30 of them in a workbook and your left scratching your head as to where the information is coming from and what do I need to update (say when the data source changes). If you only had one connection it would be easy, but life’s not like that and there’s connections flying around everywhere. So I needed to look at each pivot, determine its source and connection string.
Here’s the code:
Dim wks As Worksheet
Dim pt As PivotTable
For Each wks In Worksheets
For Each pt In wks.PivotTables
Debug.Print wks.Name & “:” & pt.Name
Debug.Print “Cache Index : ” & pt.CacheIndex
Debug.Print “— break —“
Function GetCache(Index As Long)
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim pc As PivotCache
Set pc = wb.PivotCaches(Index)
If pc.OLAP Then
Debug.Print “Is OLAP : ” & pc.OLAP
Debug.Print “Connection Name : ” & pc.WorkbookConnection
Debug.Print “Connection String : ” & pc.Connection
Debug.Print “Is OLAP : ” & pc.OLAP
Debug.Print “Data Range : ” & pc.SourceData
My Sub (GetPivotData) loops through each sheet on the workbook, getting each pivot on the sheet and then calls a function (GetCache) to get the cache (or connection) information for that pivot. While I could get connection information by just adapting the function to iterate over the slicer caches (as below), I’d like to associate each pivot table with its connection.
Additionally, I want to identify any pivots that are not based on an external (OLAP) source. To do this, I can use the Cache.OLAP property and retrieve the appropriate connection info.