Before I start describing a problem that my PowerShell script helps to resolve, these are a few blog posts of other people that inspired me to look at the whole issue of automating changes for Excel PowerPivot models (especially when you have many such Excel files and their manual change would take significant amount of time):
So here is my problem: we have built several Excel PowerPivot models and we want to use them for different testing environments (DEV, UAT, etc.) after we save those models in SharePoint. The only problem is that for each of the environments those models have to be deployed, each data connections for different data sources have to be changed; and this can be done only manually: by opening each of the Excel files and through a PowerPivot management to make all the changes; however, if you have too many of Excels files, then this manual change would take significant amount of time.
Let's me show my current approach to develop SharePoint PowerView reports based on PowerPivot models:
(A) Excel PowerPivot model deployed to SharePoint (.xlsx file)
(B) BISM data connection to (A) Excel PowerPivot model (.rsds file)
(C) SharePoint PowerView report based on (B) data connection (.rdlx file)
Deployment prerequisites:
- PowerPivot data model sourcing from different databases
- PowerPivot/PowerView files for different environments are to be saved in different SharePoint folders (or SharePoint servers).
Deployment issues:
- Reference to different databases has to be changed for each of the (A) Excel PowerPivot models.
- Once an updated PowerPivot model is deployed to a different SharePoint folder (or server), then a reference to this Excel file has to be updated in the (B) BISM data connection as well
- (C) SharePoint PowerView report contains internal reference to its data source that has be changed via SharePoint user interface.
Could be an issue to update many files at every deployment, because it’s a manual process and it will take time if we have to deploy many reporting items.
We can always do things manually
Required changes for deployment:
- Change of database references in PowerPivot models
- Change a reference to an Excel PowerPivot file at another folder (or server) for BISM data connections
- Change a reference to a BISM data connection file at another folder (or server) for PowerView report
Manual process to perform required changes:
- Each Excel file with PowerPivot model has to be opened in desktop Excel and then through a PowerPivot management each data source connections have to be changed.
- Change connection string via a SharePoint menu “Edit Data Source Definition”.
- Change data source reference via a SharePoint menu “Manage Data Source”.
Let’s try to automate something:
- Currently no automation to update PowerPivot data connections; it still has to be done manually for every single Excel file.
- BISM connection file is an XML file and can be updated programmatically prior its deployment to SharePoint.
- PowerView .rdlx file is an archived file that contains an XML \Reports\report.rdl file and can be updated programmatically prior its deployment to SharePoint
Possible way to change PowerPivot models programmatically:
1) Each Excel (.xlsx) files can be seen as an archive file (.zip) that contains \xl\model\item.data file and this item.data file contains PowerPivot model of that Excel file.
2) Also this item.data file is an archive file of SSAS tabular database and we can restore this item.data (item.abf) file to a tabular SSAS server database.
3) Then we can change settings of the newly restored database (along with connection strings).
4) And save this file back to our Excel file.
PowerShell script steps:
PowerPivot change PowerShell automation:
- Scan through a folder for Excel files
- Rename .xlsx file to .zip file
- Unzip .zip file to a model folder
- Take item.data file and rename it to item.abf file
- Connect to a SSAS Tabular server
- Run XMLA command to restore item.abf archive file to a tabular database on server
- Run XMLA command to update required database connection strings
- Run XMLA command to backup tabular database back to item.abf
- Rename item.abf file to item.data file
- Copy modified item.data file to the model folder
- Archive model folder to .zip file
- Rename .zip file to .xlsx file
PowerView change PowerShell automation:
- Scan through a folder for .rdlx files
- Rename .rdlx file to .zip file
- Unzip .zip file to a model folder
- Update <DataSourceReference> tag in the XML \Reports\report.rdl file
- Replace modified report.rdl file in the model archive
- Rename .zip file to .rdlx file
BISM data connection change PowerShell automation:
- Scan through a folder for .rsds files
- Update <ConnectionString> tag in the XML file
Here is a link to a complete PowerShell script that does update all PowerPivot/PowerView/BISM data connection files.
Some thoughts about the PowerShell script:
1) Main area that shows all key steps:
2) Easy way of updating XML files:
3) Sending XMLA command was an easy step too:
4) There was one issue of rather difference in dealing with zipped RDLX file compared with zipped XLSX file. Both desktop Excel and SharePoint didn't bother to accept .xlsx file that was created from zipping a whole folder; however powerview .rdlx file didn't like this approach and SharePoint didn't want to accept it (incompatibility error). So in case of .rdlx files I had to update an existing .rdlx(.zip) file rather than creating a new one from a folder; and this was the only approach that worked and I was able to see my modified PowerView report in SharePoint after without any issues.
And the end all PowerPivot (.xlsx), PowerView (.rdlx) and BISM data connection (.rsds) files were automatically prepared to be saved to SharePoint; and all worked well.
Happy data adventures!
