Keeping a workbook ‘pinned’ in memory

One of the special considerations that arise when demonstrating PowerPivot is that you would like to ensure the workbooks that you are showing are kept in memory all of the time. PowerPivot does not have a built-in ‘pin’ feature to do this, but there is nice trick that you can use. Here is what I do:

1. Turn on SQL Agent.
2. The SQL Agent service account needs access to the content in SharePoint, i.e. the workbooks being demo’ed
3. Create a job with a schedule that runs every n minutes (I use every 5 minutes) and a single “SQL Server Analysis Services Query” step with the following characteristics:
   1. Set the “Server” to be the URL to the workbook you are trying to keep loaded
   2. Enter a command that you know will fail, or if you know one that would be OK, I typically use “SELECT * FROM SANDBOX” (which will fail) – we don’t need the query to run – all we need is a good connection and the embedded data will be loaded.

A valid question might be: “So tell me, Dave, why every 5 minutes? That seems way too often. After all this is a demo system and there isn’t memory pressure, so why running it so often??” Yes, that is true. The other reason for why the workbook data would not be loaded is the inactivity timeout @48 hours for detaching. But that also does not seem to apply. The reason why I use 5 minutes is data refresh. Since I typically demonstrate setting and running a data refresh, this means that new content is always being generated. When new content arrives in SharePoint, the PowerPivot cleaner thread checks every 10 minutes and it will delete unused old databases immediately. Thus you would have the ‘first access’ loading the new content – and with it, the built-in delay would happen. Running the job every 5 minutes means a forced load of the new data quickly. Your mileage may vary.