Publishing PowerPivot Reports
From XLCubed v7.5 users can take XLCubed workbooks which connect to PowerPivot data, and publish these to XLCubed Web Edition. This enables sharing of the reports or dashboards with non-PowerPivot users through XLCubed Web Edition and also provides increased scalability and security options.
The publication process moves the data model directly onto the specified Tabular Analysis Services instance, and dynamically changes the data connection on the published report. The publication process does not require SharePoint or Excel services.
- XLCubed Web Edition v7.5 or higher Enterprise Edition with the scheduling feature set up
- Microsoft Analysis Services 2012 or higher in Tabular mode
XLCubed can monitor one or more directories on the Web Edition server, usually in the Web repository. When an appropriate set of files is created or updated in that directory, the PowerPivot connections are published to Analysis Services:
- The connection file is uploaded to a common network share
- The information is copied to an Analysis Services database
- The XLCubed report is modified to point to the newly created database
A new configuration file must be created in the XLCubed Web Edition repository:
This configuration file is an XML file containing one or more powerPivot elements, enclosed in a single monitor element:
<monitor> <powerPivot folder="C:\inetpub\wwwroot\XLCubedWeb\Repository\PowerPivot Reports" includeSubfolders="true" server="as2012server" uploadShare="\\as2012server\powerPivotFiles\" serverLocalPath="C:\powerPivotFiles" allowOverwrite="true" /> </monitor>
Each powerPivot element defines a repository folder to watch, and information about what to do with new files:
- The folder to watch.
- Whether to also watch subfolders of the main folder.
- The Analysis Services 2012 Vertipaq server on which to create the database.
- A shared directory on the Analysis Services server, which can be used for temporarily holding the connection information. The user running the scheduler service must have write access to this folder.
- The same directory as uploadShare, but from the point of view of the server.
- Defines whether any existing database should be overwritten. Can be set to true or false.
- A semicolon delimited set of usernames and groups to give access to the new database.
- An email address to send the notification to when a new database is uploaded. Optional; uses the first available SMTP server.
If the server is on the same machine as XLCubed Web Edition, then uploadShare and serverLocalPath can both be left blank, and the scheduler will just use the workbook where it is.
Note that the scheduler user must have permissions on the Analysis Services server to restore a database. Any messages, success or failure, will be written to the standard error log (See this article for file-based repositories, and this article for the SQL repository).