Submit plan data for BI planning solutions and scenarios


Applies to: SharePoint Server 2010 Enterprise

Topic Last Modified: 2011-01-28

In Excel 2010, IWs are able to make updates to SSAS cubes via writeback enabled PivotTables. IW updates are strictly controlled by SSAS role security, with data input enabled only to regions that are write-enabled. To set your form to be write-back enabled, select “What-if Analysis” under PivotTable tools, and then select Enable What-If Analysis.

There is also an option for either manual or automatic re-calculation as IWs enter inputs to the form. By selecting automatic re-calculation, each change made on the form will trigger a ‘What-If analysis’ on the cube. This is useful when you want to see the immediate results of changing your values on the form but can have some performance effects. It is recommended that if you have a multi-user plan process that this option should be set to manual so that less re-calculations are performed by the SSAS server, especially when the performance is already noticeably slow.

Changes in the PivotTable are tagged with a default dark red color triangle indicator. The calculated change is tagged with a dark blue triangle indicator. When IWs are finished with their plan updates they can then publish their changes directly into the cube for writeback. Publishing the changes to the cube will result in storing the changes to a relational writeback table on the server.

To publish the changes, select What-if Analysis under Pivot-table tools, and then select Publish.

1) Excel PivotTable publishes data by submitting updates to the underlying SSAS cubes.

2) SSAS will transform the updates into fact rows and store them to the writeback table.

Planning models built using Excel PowerPivot can have data models updated by using the feature of Linked Tables. Linked Tables can be thought of as that relate to partition tables in SSAS cube. Thus, updates to the PowerPivot model can be performed by either directly updating an existing Linked Table or by creating a new one and linking it to the PowerPivot model.

The key advantage to using linked tables is that IWs can create forms and conduct analysis off multiple heterogeneous, refreshable data sources and still be able to extend that data model with adjustments that can be managed all inside a workbook.

In order to set up the correct environment for this, we would have to build a separate table to feed the data to our PowerPivot model. The following is an example of where we have created a table within a separate sheet and used it to update our PowerPivot model.

  • Note that linked tables often resemble fact tables and therefore is not always the most intuitive structure for making adjustments to data models

  • PowerPivot PivotTables do not support writeback. This makes it more difficult for an IW to update a PowerPivot model

With the table setup, highlight the section of data within the table and then click on the Create Linked Table button under PowerPivot. Here we can select Automatic Update Mode for updates within the Excel table to automatically update the PowerPivot Model. By using the data linked to a table within PowerPivot, we now have additional values that can be used in our form PowerPivot form authoring.

1) Updates to the Linked Tables are made via copy/paste or through cell references.

2) Changes made to the Linked Tables automatically processed into PowerPivot model.

3) Updated PowerPivot model can be consumed in forms or reports.

4) Other data sources update to PowerPivot as normal.

Once the planning forms are created, set up a document library within the SharePoint 2010 site to publish the forms to the IWs. The organization for the document library depends on user requirements (that is by Business Unit, by Department, by Geography).

For PowerPivot reports, we recommend that you publish these reports to SharePoint’s PowerPivot Gallery where the report viewer has more interaction with the report. By using SharePoint 2010, the following multiuser collaboration scenarios can be supported:

  • Multi-user Check-in / Check-out process for data submissions and approvals.

  • Copies of or changes to the PowerPivot data model can be done within Excel 2010.

  • Share interactive reports and analysis through PowerPivot Gallery.

An alternative to submitting plan data is with SharePoint List function. SharePoint list enables the capture of data via web browsers from multiple IWs across the organization. Since the lists are maintained in the SharePoint environment, Excel is not needed for its creation or maintenance. Although this allows for straightforward setup and functionality, there are some drawbacks to using SharePoint Lists vs. OLAP cubes as the data capture mechanism.


Advantage of lists Drawbacks Versus OLAPS

Quick Setup & Access

No Aggregation of Data

Straightforward Data Entry

No Hierarchy Display

Basic Calculations

Limited Calculation Functions

Workflow and Security (SharePoint)

Less Business Rule Support


Limited Data Security


Limited Filter Selection

To set up a SharePoint List for a sample planning scenario, see Building planning functionalities guide for BI planning solutions and scenarios SharePoint Lists section.

For planning scenarios, lists can represent input forms for Finance to collect data from line managers in different departments and business units. SharePoint lists can also integrate with the rest of the BI tools available on the SharePoint platform when you build dashboards, scorecards and analytics.