A Peek Inside: Unloading PowerPivot data

Ok folks. Here comes a cool one.

As I am sure that everyone is aware by now, one of the most powerful new features of PowerPivot is that we do on-demand loading of embedded data. Well, if you are going to load it, then you had better at least think about unload it at some time. There is probably some law of thermodynamics that applies here.

First rule for deleting data is that we don’t touch data if there are users currently connected to it. But other than that, any databases loaded or cached by the PSS (i.e. their database folder is contained in the SSAS Backup folder tree) are candidates for being unloaded.

There are two ways that databases can be unloaded in PowerPivot:

a)  Inactivity

If a database has not been accessed for a long time, then it is a good idea to start to free up resources for it. However being too aggressive means that you may have to re-read content which places a larger load on SharePoint. One of a farm’s most critical resources is the amount of traffic that has to be moved to and from the content database. So we want to minimize its load as much as possible. This is particularly true if the content is large – which is likely for PowerPivot workbooks. We have designed PowerPivot so that inactivity checking is a two stage algorithm. Every 30 minutes the PowerPivot System Service (PSS) scans the databases in the instance map assigned to the server’s service application looking for:

1. A loaded database with its ‘last accessed’ timestamp longer than 48 hours (2 days). If it finds one, the PSS detaches the database from the SSAS instance. The database remains in the cache (a collection of detached databases on a server) so it is available for quickly coming back if a user accesses the database. If this happens, all the PSS has to do is to issue an Attach XMLA command – it does not have to go back to the SharePoint content database and re-read the workbook.
2. A cached database with its ‘last accessed’ timestamp longer than 120 hours (5 days). If it finds one, the PSS deletes the database from the cache by deleting the database folder (and all its files) from the hard disk. If a user accesses the database after it was deleted from the cache then the data has to be re-read from the workbook (which is stored in the SharePoint content database).

b)  New Content has arrived

If a file is updated in SharePoint (whether or not file versioning is enabled or not), then the any databases loaded or cached for that file can be unloaded from memory and deleted from the server cache immediately. Ordinarily you would think that it would be over aggressive to delete all old databases.

Every 30 minutes the PSS scans the databases in the instance map assigned to the server’s service application looking for content that is newer than the content loaded or cached. The instance map contains the SPFileVersion.Created property of the file version that is loaded. If the ‘latest version’ of the file has a SPFileVersion.Created that is later than the instance map then we know that new content has arrived. If the database is loaded, then we delete the database from the SSAS instance by sending an XMLA Delete command. If the database is cached, then the PSS deletes the database from the cache by deleting the database folder (and all its files) from the hard disk.

Note that we cannot use the SharePoint versioning system to determine if a version has changed. We have to use this timestamp method because the SharePoint versioning system can be disabled. In fact it is disabled by default. When it is disabled all files look like version 1.0. We are able to use the file version “Created” property because files in SharePoint are read-only. There is no in-place editing. If you edit a file, then behind the scenes you are creating a new version and it has the “Created” timestamp of the save operation.

—————————-

Being a geek, you are probably asking if you can change any of these thresholds or behaviors. The answer is “Sorry, No. Maybe next version.”

Enjoy!