Additional planning functions for BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

Add new lines

When planning for new spending or headcount, the ability to add new lines becomes a critical functionality required by the IW. For example, the plan calls for an additional two headcount to be allocated for additional sales resources to the Northeast Division. There are two possible approaches that can be taken, one basic and one advanced. Note that these approaches center on having analysis services as the data model. (For more information, see the “Add New Lines” section in Building planning functionalities guide for BI planning solutions and scenarios.)

SharePoint List that uses External Content Type

Use Microsoft SharePoint Designer 2010 to create your list off an existing relational table. You can do this by first defining an External Content Type on your SharePoint site and then creating an instance of a list off of it.

New members can be added to the dimension by simply adding new members to the SharePoint list. Here, we have an example of the required fields that are needed in order to create a new member to employees for use in budgets.

From a Web browser, IWs update the external content type list.
Microsoft SharePoint Server 2010 updates the underlying relational data table.

Spreading

Cube spreading

When working in Excel 2010, IWs can configure the method of spreading for updating cube data in Microsoft SQL Server Analysis Services (SSAS) through write-back enabled PivotTables.

There are many spreading methods that an IW may want to perform. These can be broken down into basic and advanced scenarios:

Spread aggregate value across a defined range of time periods.
Spread an aggregate value evenly down a hierarchy, starting from any parent member of the hierarchy.
Allocate evenly down hierarchy.
Increase or decrease by percentage down a hierarchy.

Spread based on previous year’s actuals.
Spread based on custom allocation percentages stored in relational data tables.
Spread down a parent-child hierarchy.

Sometimes, the default spreading logic available in Excel SSAS might not be enough. In such instances, customization can help. One approach is to leverage Microsoft SQL Server 2008 relational engine to do the spreading via stored procedures. The process for the IW would be:

Update input members that are required by the stored procedure for spreading.
Run the stored procedure from Excel 2010.

You can call stored procedures directly from Excel via macros. (For more information, see Macro Examples Using XLODBC Functions. Spreads that are done at the relational level are ideal when the calculated values are all at the leaf level of a hierarchy, i.e. the lowest level of granularity for a cube. Once the calculations for the spread are complete, the cube will show the correct aggregated values along the hierarchy.

Also, spreading can be achieved via an UPDATE CUBE statement in MDX. For more information, see UPDATE CUBE Statement (MDX). However, this will typically be beyond the skill set of most IWs.

When designing spreading logic, be sure to test out the end user scenarios thoroughly so as to provide the best guidance to end users when they are working on their forwarding looking processes. For instance, parent child hierarchies have known issues with spreads that may be seen by the IW as having unexpected behavior. For example, spreading down 1,000 over the following parent-child hierarchy can result in the following allocation:

A Spread 1000
- B
- C

A – 1000 (aggregate)
- A - 333.333 (data member)
- B - 333.333
- C - 333.333

In general, when designing for spreads by IWs, it is best to give clear guidance from the model designer as to what can and cannot be spread and what is the expected behavior that will result with each kind of spread. This is important as the IW will be unable to tell the difference between a pc hierarchy and a leveled hierarchy, yet the behavior of the spread will be different as called out above.

Spreading performance in Excel 2010 and SQL 2008 R2 is generally very good. We’ve performed spreading from the top member “All” of geography down to all the separate regions, with the value to spread entered by product SKU over the forecast periods. The resulting spread created over 50 thousand rows of fact in the writeback table of the OLAP cube. Publishing the changes back to the cube completed in a few seconds.

Cube contains the following dimension and member count:

Account (77)
Geography (14)
Product (505)
Scenario (11)
Time (66)

Hyper-V Virtual Machine Configuration:

3.5G of RAM
1 Proc 2.67GHz Intel Core i7

PowerPivot spreading

Here we will explore some ideas around updating a PowerPivot data model with spreading values by using native Excel 2010 functionality and PowerPivot linked tables. As called out previously, PowerPivot does not have native support for updates directly from Excel 2010 PivotTables, which makes spreading and other kinds of data updates a challenge. However, we will see that in certain basic scenarios we can achieve Excel level spreading and have the results pushed back into a PowerPivot model.

Let us understand a simple example of spreading that an IW wants to do:

IW wants to create custom spreads over time based on total revenue by product SKU using data from an existing PowerPivot model.
IW wants to incorporate the spread information back to the PowerPivot data model for use in other analyses

Create a PivotTable to bring in aggregates to spread over time
Create a region on the spreadsheet to hold the percentages to allocate over time periods
Create a region on the spreadsheet to hold the currency amounts that are reallocated
On another sheet ‘PowerPivot Partitions’, create structured fact tables that can be linked back to PowerPivot. Each table here can be thought of as a partition table for a SSAS cube.
- For each time period that we use in the spread, we will create a table.
- Create each table with the same dimensionality as that of the data model
- For each table, create relationships to the PowerPivot dimension tables
Create a DAX measure that will sum together the data from the partition tables into a single measure for use in reporting

To the right of the spreading percentage region, we have the calculated spread amounts by time period. These calculated values are then referenced by Excel tables to build the partition tables that will be used to link the table data back into the PowerPivot model. For simplicity of the Excel reference formulas, we will create a partition table for each time period.

When creating the partition tables for ‘linked tables,’ it is important to have the unique keys that will identify the members. In our previous example, we are using the ‘MemberId’ for each dimension when we are constructing the partition table
The previous approach is ideal for situations where the filter selections will not change on the form. For instance, each IW can have their own PivotTable with their own unique filter selection that they use to get the data linked back to the PowerPivot model.
Ensure enough buffer space is created in the Excel table for reference cell formulas to include spread data for new product SKUs.
The more linked tables that are created, the higher the complexity of the workbook leading to less manageability of the PowerPivot model.

PowerPivot is great in bringing multiple sources of structured data together. DAX shines in this area with calculated measures.
Updating data directly in Excel can also update the PowerPivot model
Creating input forms for IWs to update PowerPivot models is not easy.

Additional planning functions for BI planning solutions and scenarios

Add new lines

Spreading

See Also

Concepts

Additional resources