Calculations for BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

On-sheet calculations

Spreadsheet calculations are well understood by most IWs who use Microsoft Excel to perform their calculation needs. For planning scenarios, the core calculations can still be done in Excel via additional sheets against data that was stored in the centralized data model. Through the use of writable PivotTables, sheet calculations can then be copied and pasted into their respective regions for data update on the sheet. Publishing of the changes will then persist back to the cube (data model) all the raw calculated values in the writable region of the PivotTable.

Cube calculations

MdxScript is used to define calculations on the cubes. These calculations are defined once and are consumed by all IWs who query against the cube. The benefit of having the calculation stored in the data model versus the on sheet approach is the ability to standardize and maintain a single copy of the truth. Any updates that you make to the MdxScript rules will be immediately available to the users, whereas using a spreadsheet to update rules will be manual, cumbersome, and error prone.

For examples of calculations that use MDX script to drive the HR model, see the “Model Cube Calculation Example” section in Planning and budgeting calculation examples for BI planning solutions and scenarios.

Stored procedures

Stored procedures are a great way for performing financial calculations. These can be defined on the data store of the planning solution. Stored procedures can also be invoked via SSIS packages that can also be scheduled for run by using SQL Server Agent. For more information, see About SQL Server Agent.

For our solution, an ideal business rule to implement as a stored procedure is the currency conversion rule. Since we have all the currency translation values that are stored in the writeback table of our Exchange Rate model, we can do simple T-SQL joins to obtain a single combined currency value for use in reporting. (For examples of stored procedure calculations, see Planning and budgeting calculation examples for BI planning solutions and scenarios.)

SharePoint list calculations

By using SharePoint lists for a method of submitting planning data, we can use the calculation functions available that are available within Lists. First, we would create a new column within the list view and set the column’s data type to Calculated. This will let us to input our own calculations based on the previous column values, together with data format.

In this example, we have created a calculation to average the pay rates for the next four fiscal years. The added column, Avg Salary Cost, will be automatically calculated when the user has entered their values.

Calculations for BI planning solutions and scenarios

On-sheet calculations

Cube calculations

Stored procedures

SharePoint list calculations

See Also

Concepts

Additional resources