Write-back Using PowerPivot

I think this is kind of neat…

In a discussion about PowerPivot yesterday, one of my friends stated that it wasn't really useful since you couldn't perform writeback 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 tha 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. J

9 thoughts on “Write-back Using PowerPivot

  1. Hi Deb,

    Thanks for the heads up. It worked when I tested it last night, so I'm not sure what's up with that. I've replaced the link with one that downloads a zip version. Hopefully that's better.

  2. Go figure... while the WebApp aspect seems to work fine, the download of a zip file isn't so stable...

    Jeff, I repointed the link to a file hosted on my site. Have a go now and see if you can get it.

    Cheers,

    Ken

  3. Hi Ken,

    It is good to see someone finally coming up with a solution to this!

    One thing I am not sure about is how to manage security. Do you have any ideas on that? We often want people only to access their data and update their data in their departments.
    Cheers,
    Warwick

  4. 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.

  5. One of the things that I really like about this post is the use of graphics. The graphics really help to get the message accross.

    PowerPivot being so new, there is not a lot of quality info on the web.

    Thank you for postign\\ng this.

  6. Check out power-planner.com They offer a complete PowerPivot Budgeting solution for a low price.

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *