Publishing PowerPivot Reports

From XLCubed v7.5, XLCubed Web Edition allows you to take PowerPivot reports that you have created in Excel and share them with other people in your organisation - without any need for SharePoint, but still keeping control over who is allowed to share, and with whom.

Requirements

• XLCubed Web Edition v7.5 Enterprise Edition with the scheduling feature set up
• Microsoft Analysis Services 2012 in Tabular mode

Overview

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

Configuration

A new configuration file must be created in the XLCubed Web Edition repository:

\Repository\__xlcubed__\__scheduling__\monitor.xml

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:

folder

The folder to watch.

includeSubfolders

Whether to also watch subfolders of the main folder.

server

The Analysis Services 2012 Vertipaq server on which to create the database.

uploadShare

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.

serverLocalPath

The same directory as uploadShare, but from the point of view of the server.

allowOverwrite

Defines whether any existing database should be overwritten. Can be set to true or false.

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 scheduling error log file.