• 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.
    Quote Originally Posted by Warwick Leitch
    on March 20th, 2011 at 8:12 pm

    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.
    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.
    Quote Originally Posted by Christopher
    on August 4th, 2011 at 12:43 pm

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

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

    Thank you for posting this.
    Thanks Christopher, Iím glad you enjoyed it!
    Quote Originally Posted by Istvan Bartalos
    on August 29th, 2011 at 12:12 am

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


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!