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