When is a refresh not a refresh?

Ok. This post will be a bit complicated but stick with me. Hopefully, in the end, all will be clear. And the geek in you will love it.

One of the things that users just kind of glance over, but don’t realize the implication, is the fact that PowerPivot is a copy of the data. If you haven’t already, let me suggest that you read my "Where’s the beef?" posting. In that posting I talked about the fact that data itself is pulled into the workbook when you save it. When you click any of these buttons:

The import process runs. From then on, the data can start change and shift away from the values that is stored in memory and ultimately in the workbook. The data is ‘real-time’ only when the import is running; afterwards all calculations, pivot and slice is driven by the stored data. On the client this is clear because we have the ‘Refresh’ button (and its options) that provide refresh on the client. But how about the server?? Well, that is the core of this posting. Let’s take a closer look at it. We will start at the menu items for the Excel Services rendering of the workbook. Notice the options here:

The question is “What does it mean to ‘refresh’ the connection?” The answer to that is that it depends on the data provider. For virtually every OLEDB and ODBC provider that Excel Services uses, ‘refreshing a connection’ means going out to the data source and re-querying the data source for its data. SQL Server RDBMS, Oracle, Teradata, virtually to everyone it means refreshing that Excel Services data. And it means that in PowerPivot also, but in PowerPivot where is the data stored? (You know the answer this already, don’t you). The data is in the workbook. Has the workbook changed since you last opened the .xlsx file? Well, I suppose it might have – and in which case, refreshing the connection might bring in new data. But in the vast, vast number of cases, refreshing the PowerPivot table means just re-reading the data that Excel Services already has. In most cases, it has absolutely no effect at all.

To really drive this home, let’s shift into super-geek mode and drill down into the workbook itself. I will go back to the workbook in the first screen shot and first click on the Connections option in the Data ribbon. Notice that there is a connection that has been defined behind my back in the workbook. It is called “Sandbox” which by the way was the name of our system prior to Gemini and prior to PowerPivot. I didn’t create that connection. It was created for me when the PowerPivot Excel add in was first started. This is the connection which is actually interfacing to the in-memory database. Now let’s drilldown further into the “Sandbox” connection and look at its connection string. WOW! The “Data Source=” property, which would normally point to the server for where the database is stored, instead points to “$Embedded$” – What’s that??

$Embedded$ is the magic tag that tells PowerPivot for SharePoint that the data does not come from some server somewhere – instead the data comes from the workbook itself. One of the new OLEDB interfaces created for PowerPivot is a property that Excel Services sets which contains the URL for the workbook that Excel Services is opening. The msolap OLEDB provider takes that URL and replaces the $Embedded$ string with the URL itself –> and thus the infrastructure will read its data from the workbook itself.

But – and this is the critical “BUT” – notice that the embedded content never changes. After you upload a workbook, that workbook doesn’t change on its own. Thus neither does the data. Remember the data is a copy of the data that is embedded in a workbook. If Excel Services refreshes it, the ECS calc engine gets the same data over and over again. The SSAS database embedded in the workbook hasn’t changed – so the data refresh is a nop – it never changes. Refreshing a connection to an embedded PowerPivot database doesn’t refresh anything. You get the same data over and over again.

So, how does the workbook data get refreshed? After all, there must be some way to do it . . . In fact, there are two ways:

1. Bring the workbook down on the client and refresh the data in the workbook. Then re-publish the workbook back to the same location in SharePoint. New data is automatically given to Excel Services and existing connections.
2. Use the data refresh facility, see the data refresh posting and detailed steps posting for more information. In this case the PowerPivot System Service will reach out and pull in new data into the workbook. A new version of the workbook is created and new data is automatically give to Excel Service and existing connections.

And before you ask, No, PowerPivot V1 has no option to monitor the data in real-time and update its data in-memory as the source data changes. The workbook captures the data at a point in time – and then users work with that data. There are no provisions for real-time access to data while doing analytics / calculations / pivot table operations.