Write-back Using PowerPivot

In a discussion about PowerPivot yesterday, one of my friends stated that it wasn’t really useful since you couldn’t perform write-back using PowerPivot. To him this is a very important piece in the Excel budgeting process. Now, I agree that PowerPivot doesn’t give you write-back to a database, but this got me thinking; we have linked tables, so why couldn’t we create a write-back loop for a model that was built entirely in Excel? Well, we can!

To be clear here, this only works if your entire model is built in Excel and PowerPivot. You can source data from elsewhere to supplement it, but the key is that the information will be written into the PowerPivot cube as the ultimate database. I am certainly not advising anyone to toss a database in favour of a PowerPivot file, but if you don’t have a database, and want to user PowerPivot as your DB, then this could work.

Here’s how I generated a writeback scenario;
To summarize this:

• The initial step is to create a table of data in Excel, format it as a Table.
• Next, we need to link the Excel table into PowerPivot. (Create Linked Table.) This action will upload the structure and data into PowerPivot, forming the beginning of the PowerPivot database.
• The next step is that we create a PivotTable in Excel, based off the data. At this point we will have a data table in Excel, and a PivotTable based off that data in another sheet. While I haven’t tested this, I don’t see any reason why you couldn’t avoid the PivotTable and just use cube functions based off the PowerPivot cube instead if you prefer. At any rate, here’s the PivotTable I set up:

• I then create a working sheet.
  
  • The purpose of this worksheet is to pull in the values from the PivotTable (or be populated with OLAP formulas), then allow the user to “override” the key sections. In my test, I used a bunch of GETPIVOTDATA functions to pull the values from the PivotTable (although I could easily have one with an Index/Match combination or something else too.) This section looks like as follows (notice the override cell):
  
  

• Depending on the complexity of the model, I might also create a summary section to re-summarize all the inputs. This would also give the user a place to review for reasonableness, as well as give a nice range to use for an INDEX(MATCH(),MATCH()) combination. You can see that the summary version uses my override, not the original PivotTable value here:

• Finally, we go back to the original data table we uploaded to PowerPivot. At this point, we need to have it read the data from our adjusted data tables. In the case of the example here, I used an Index/Match setup to read from the data table immediately above.

And now we have the ability to generate our writeback. We start at the top of the image below, where the blue circles are manual steps and the red circles are automatic.

You can download a sample of the setup I used to test this. It’s fairly simple, but it does demonstrate that it works.
Hi Warwick,

Unfortunately PowerPivot doesn’t have a security story at the present time. You can’t even hide the PowerPivot tab through the UI.

So with the data stored inside the PowerPivot file, pretty much the only security options you have are to put on a Workbook_Open password. We don’t even have a VBA object model to work with on PowerPivot stuff.

The only other way I can think of at present is that I think Sharepoint has some security measures. Not having any Sharepoint experience though, I could certainly be wrong, so hopefully one of my Sharepoint familiar readers will chip in to clarify.
Thanks Christopher, I’m glad you enjoyed it!