What If Analysis in Ranet OLAP Pivot Table

Ranet OLAP Pivot Table allows users to not only analyze actual data for reporting, planning, and budgeting but also perform what-if analysis for studying different scenarios by changing values in the pivot table and seeing how they influence the cube data. This feature is available in all Ranet OLAP versions (HTML, WPF).

In order to support this type of analysis, the cube needs to have write-enable partitions, and all measures must use SUM aggregate function. One more requirement would be the absence of multiple filters applied to columns in the pivot table - there should be not more than one element for each column in the Filters settings.

What-if analysis data table

All changes that users make to the values are stored in a separate data table and are not mixed with the factual data. The table is created automatically when the cube is recalculated and can be deleted if required.

It is important to always separate the analysis data from the reporting data, for example, by using different measure groups. Storing all data is the same cube would be a bad idea as it might significantly overload the OLAP server.

Write-back data tables support work with multiple users, so it is also recommended to set up the cube the way that will allow different users to work with different data slices and not interfere with each other’s work. Otherwise, there might be a possibility of one user’s data being overwritten by another one's.

Editing data in the pivot table

Ranet OLAP has two modes for synchronizing the data changes in the pivot table with the OLAP server:

• Automatic (default mode): when data is changed in the table the data on the server is immediately recalculated, and a new result is sent to the client.
• Postpone calculation: altered data is stored in cache locally on the user’s machine, and is recalculated upon user’s command (Calculate with Changes). Discard last Changes discards all cached changes that haven't been calculated.

It is always better, but not required, to change only end member values in the table when you conduct what-if analysis. Ranet OLAP Pivot Table does allow users to change parent members, such as folders and hierarchy nodes, but they must carefully monitor the process of data allocation to all daughter members. When a parent element is changed, all daughter members are updated, so the user has to be very careful and set up required allocation rules. In a case of improper settings, allocation can lead to creation of thousand or even millions of additional rows, which will eventually overload the OLAP server.

The changes can be either published (Publish Changes) or overridden (Rollback Changes). If published, they become available to all users, otherwise, the pivot table restores the values it had before the editing.  After the changes have been published, they cannot be rolled back. Rollback Changes would discard only those changes that were made after the latest publishing.

In order users could set up rules for performing what-if analysis, Ranet OLAP Pivot Table provides UPDATE CUBE instruction (Figure 1). There, users can set up parameters for processing values that are edited in the pivot table and choose required method of allocation.

Below is a video showing how to use what-if analysis in Ranet OLAP for WPF in automatic and postponed modes, edit data in the pivot table, publish and discard the changes etc. The feature works the same in HTML and Silverlight versions - see online demos.

We hope this article was helpful, and you will use what-if analysis feature in Ranet OLAP Pivot Table effectively. Feel free to contact us in case you have any question about this or any other topic.