Employing the Sandbox Feature in Oracle EPM Cloud
Updated: Jul 28
What are Sandboxes in Oracle EPM Cloud?
Scenario-based planning is vital today when building out a financial plan. Planners need an easy way to apply assumptions which impact the plan creating different scenarios such as base, worst, and best cases. Sandboxes in Oracle EPM Cloud support this requirement.
Sandboxes are a feature that allows planners to create private plans in their planning application. Sandboxes provided planners with the ability to work privately on their plan before it is submitted for review. They eliminate the need for a planner to work offline in a tool such as Excel. While using sandboxes, planners can test various outcomes and analyze their private plan to assess their impacts. Planners maintain their plan privately online and chose when to publish it to the aggregate (source) plan. Sandboxes accommodate the types of tasks that answer the question "What-if?".
Key Benefits of Sandboxes
Planners carve out a personal area in the Oracle EPM Cloud and work privately.
There is no impact on the aggregate (source) plan until the planner chooses to publish the sandbox plan.
Sandboxes eliminate the duplication of effort. There is no need to reenter data from an external spreadsheet.
Once a planner completes his/her plan in the sandbox, he/she can choose to publish it to the aggregate plan at the push of a button.
The Sandbox feature for most part is maintenance free. Oracle EPM Cloud performs a sandbox cleanup after the publishing step.
How do Sandboxes work in Oracle EPM Cloud?
Sandboxes are native functionality in Oracle EPM Cloud. There is no need to install or deploy a separate module or add-on to make sandboxes available to planners in your application. Figure 1 describes how sandboxes work.
On the left side of the figure is a visual representation of an Oracle EPM Plan Type (Cube) that was enabled for sandboxes. It stores all of the plan data that users input during a planning cycle.
On the right side of the figure is our planner who will be using sandboxes to perform scenario-based planning.
During the plan type configuration, we identified a member called ‘Working’ in the Version dimension that supports sandboxes.
In this example the intersection that represents the scenario ‘Plan’ and the version ‘Working’ is where planners enter their plan data.
This intersection is available to planners to create sandboxes.
As planners work in the plan type, they can create sandboxes that are only available to the user who creates the sandboxes.
In this example the planner created three sandboxes: Base Case, Best Case, Worst Case.
After performing his/her analysis, the planner chooses to publish the ‘Best Case’ sandbox.
The ‘Best Case’ sandbox is copied to the ‘Plan’->’Working’ intersection.
When this happens, the 'Best Case' sandbox is removed from the plan type. The remaining sandboxes are preserved and can be removed by the planner or an administrator.
Tips for Using Sandboxes in Oracle EPM Cloud
This section covers select tips and recommendations for configuring and deploying sandboxes to planners in the Oracle EPM Cloud.
Sandboxes can only be enabled in standard applications and custom BSO plan types in enterprise applications. You can enable sandboxes for BSO plan types, but CANNOT disable sandboxes once enabled. The HSP_View dimension is added to plan types where sandboxes are enabled. Figure 2 displays a plan type with sandboxes enabled and the 'HSP_View' dimension added to the application. Be sure to enable the specific members in the version dimension that will be enabled for sandboxes (see the next section).
To manage sandboxes, an administrator navigates to the ‘Application’ view in Oracle EPM Cloud and can view sandboxes for all planners. The administrator can also delete selected sandboxes from this view. See Figure 2 above.
In the Version dimension, a ‘Sandboxes’ hierarchy is created when it is enabled for the plan type (see Figure 3). For each member in the Version dimension where the sandbox feature is enabled, a corresponding parent roll-up will be created in the 'Sandboxes' hierarchy. A child member will be created under the parent roll-up for each sandbox created by a planner. Security for the new member is inherited from the source version. The Version member is removed when the sandbox is published or deleted by the planner or administrator. Duplicate sandbox names are not allowed. Planners should apply unique names to identify their sandboxes.
As mentioned earlier, the HSP_View dimension is added to a plan type where sandboxes are enabled (see Figure 4). The 'BaseData' member is where all base data for a plan type is loaded and stored (non-sandbox data). The 'SandboxData' member is where all sandbox data is stored. The 'ConsolidatedData' member is a dynamically calculated member that retrieves data from the 'SandboxData' member if it exists, otherwise, it retrieves data from the 'BaseData' member of the base Version. Remember to consider impacts on member formulas in order to properly calculate data in base and sandbox views.
Using Sandboxes with Smart View
The sandbox functionality is available in Smart View, but planners cannot create, delete, or publish sandboxes from Smart View (see Figure 6). The HSP_View' members are available when performing ad hoc analysis in Smart View. It is important that you consider the correct intersections when retrieving sandbox data using Smart View. Sandbox data is stored at the 'SandboxData' member in the HSP_View dimension and the corresponding sandbox member in the Version dimension. It is recommended to use the 'ConsolidatedData' member in the HSP_View dimension to ensure a successful retrieve.
Form Views When Using Sandboxes
A planner will only see the sandboxes that he/she created when using a form where sandboxes are enabled. A user with the administrator role will be able to see the sandboxes created by all planners. The administrator is the only user that can view/edit sandboxes created by all users (see Figure 5).
Sandboxes in Oracle EPM Cloud is another great feature for planners who need to perform scenario-based planning and model their plan before submitting it to the aggregate plan.
We hope you enjoyed this blog post and please feel free to leave your comments and share it with your colleagues.
About the Author
RJ is a founding partner of Innovus Partners. He is an EPM/BI/Data Warehouse Specialist with over twenty years of consulting experience.