Dev/UAT/Production Publishing for PowerPivot within Sharepoint


  • We are publishing PowerPivot workbooks to a SharePoint Development environment.  After development, we would like to copy them to a UAT environment and easily be able to change the database connection.  It appears as if this is not easy to do and we need to recreate the workbook from scratch.  Does anyone have a good solution for publishing from Dev to UAT to Prod, while changing the database connections?
    Tuesday, May 24, 2011 9:22 PM


  • Here's a way that I've seen implemented to switch sources amongst workbooks:

    1. Create a "master file" Excel workbook that contains only the PowerPivot data model, and a blank Excel worksheet.  Upload this to SharePoint and create a BISM connection in the PowerPivot Gallery that points at this workbook url.  Here's a link describing a BISM connection:
    2. If SharePoint is configured correctly, you can click on the icon for an Excel workbook from the BISM connection in the gallery.  This will open a thin client Excel workbook that has a data connection to the BISM.
    3. Develop your reports in the thin client Excel workbooks.  The reports will be smaller files since the entire data model is not in them.  Also, all reports will be pointing to the same data model hosted on SharePoint (via the BISM connection) to keep things maintainable.
    4. Treat the "master file" Excel workbook containing the PowerPivot data model in the same way that you would treat an OLAP cube.  Keep Dev, UAT, and Prod versions in each environment.
    5. When migrating a new report from Dev to UAT, just modify the data connection to point at the UAT BISM instead of the Dev BISM.
    6. Long term, if a decision is made to make the solution an SSAS Tabular instance, the BISM connection can be "re-pointed" at the SSAS instance and the reports will still work.  In this way, your reports can have Dev/UAT/Prod environments, the ability to promote the solution to SSAS, and there should not be a need for rework if the design of the underlying model is maintained.

    Wednesday, December 11, 2013 5:11 PM

All replies