13 Model Data in Private Scenarios

Using scenario management, scenario participants can perform what-if analysis to model data in their own private work areas. These scenarios can optionally be subject to an approval workflow which includes a scenario owner and one or more approvers. In the workflow, scenario owners merge scenario data with the final cube data only after it is approved.

Understand Scenarios

Scenarios are private work areas in which users can model different assumptions within the data and see the effect on aggregated results, without affecting the existing data.

Each scenario is a virtual slice of a cube in which one or more users can model data and then commit or discard the changes.

Scenario-enabled cubes have a special dimension called Sandbox. The sandbox dimension is flat, with one member called Base and up to 1000 other members, commonly referred to as sandbox members. All members in the sandbox dimension are level-0. Sandbox members are named sb0, sb1, and so on. Each sandbox is a separate work area, whereas the Base holds the data currently contained in the cube. A specific scenario is associated with exactly one sandbox member.

Sandbox
	Base
	sb0
	sb1
	sb2

Base data is the starting point before you use the sandbox to model possible changes. Sandbox data (also known as scenario data) is not committed unless the scenario owner applies it, at which point it overwrites the Base data.

When first created, sandbox member intersections are all virtual and have no physical storage. The physical data from the cube is stored in the Base member slice. Querying new sandbox members dynamically reflects the values stored in the Base.

Only after you update any of the values in a sandbox are your changes stored physically in the sandbox. After you update some values in a sandbox member, queries against the sandbox reflect a mixture of stored sandbox values and values inherited dynamically from the Base.

Changes made in a sandbox are not committed to the Base until you do so explicitly, generally after an approval workflow. See Understand Scenario User Roles and Workflow.

After you’re finished with the sandbox, you can put the sandbox through the approval workflow, or you can skip the workflow and commit the updated values to the Base, or reject and discard the sandbox changes.

You must enable hybrid mode for scenario management to work. For queries, it is enabled by default. Do not disable it. For calculations, you also need to enable the HYBRIDBSOINCALCSCRIPT application configuration. See HYBRIDBSOINCALCSCRIPT (or use the SET HYBRIDBSOINCALCSCRIPT calculation command to control it on a per-calculation basis).

Security and filters apply to the Sandbox dimension.

Scenario enabled cubes have a CellProperties dimension that you should ignore, as it is for internal processes. You do not need to modify it nor account for it in calculations, queries, or load rules, and it shouldn’t be included in any calculations or other operations.

View and Work with Scenario Data

There are two entry points for viewing and working with scenario data in Smart View.

You can use the Essbase web interface to launch a scenario in Smart View, or you can use a Smart View private connection and work with the scenario data that way.

To analyze data in a scenario, you must have all of the following permissions:
  • Be a user provisioned to the application.
  • Have a minimum of database access permission for the application (and have a write filter if you want to change data in the sandbox).
  • Be a participant in the scenario (created by a user with higher privilege).
View and Work With Scenario Data From the Essbase Web Interface

You can launch Smart View from a scenario in the web interface.

When you do this, because you enter from the scenario, you can only work in Smart View in the sandbox member associated with the scenario from which you entered. The sandbox member is implicit. You will not see it in the Smart View grid.

  1. In Essbase, click Scenarios.

  2. Click the Excel icon Image of the Excel icon. next to the scenario you want to view.

  3. Select to open the file.

  4. This launches Excel with a Smart View connection to the scenario.

When you do this, the slice of data for that specific scenario is in the worksheet. You can query data only in that scenario. If you have minimum database update permission on the application, you can submit data to the scenario. (When you submit data to a scenario, you are submitting data to one sandbox member).

You can launch a scenario in Smart View from the web interface only on Windows using Firefox, Internet Explorer, or Chrome browsers.

View and Work With Scenario Data From a Smart View Private Connection

You can open Excel and make a private connection to your cube, without starting from the web interface.

When you do this, the sandbox dimension will be in the worksheet, so you can submit data to any sandbox member to which you have access. This is helpful when you are a participant in more than one scenario, but you must explicitly know which sandbox you want to work in.

To see which sandbox member is associated with a scenario, go to the web interface, click on Scenarios, click on the scenario name, and view the General Information tab.

  1. Open Excel.

  2. Make a private connection to your scenario-enabled cube.

  3. Do an ad hoc analysis.

  4. Drill into the Sandbox dimension to view the sandbox members.

Examples

This is a Smart View grid including the Base member and a sandbox member. Sandbox values have not been updated, so they reflect the Base values. Those values are stored only in the Base, not in the sandbox members:


Image of a Smart View grid showing a base member and one sandbox member.

The changed sandbox value below, 500, is stored in a sandbox member. The remaining sandbox value, 271 that was not updated is stored only in the Base:


Image of a Smart View grid showing a base member and a sandbox member, with a changed value in the sandbox member.

Below is a grid with multiple sandbox members. If you have the Database Access user role and the appropriate write filter, you can submit data within multiple scenarios simultaneously:


An image showing a Smart View grid with multiple sandbox members.

About Scenario Calculations

By default, Essbase calculates all members from a dimension unless a fix statement is used to limit the scope of the calculation to a specific member or group of members from the dimension.

The sandbox dimension is an exception to this behavior; if members from the sandbox dimension are not included in the fix for a calculation, only the base member from the sandbox dimension is calculated by default. To calculate non-base members from the sandbox dimension, include them in the fix statement, optionally along with the base member.

When you specify non-base sandbox members in a fix statement, base is excluded from the calculation unless explicitly added into the fix.

This behavior is different from calculations on non-sandbox dimensions excluded from the fix; if you exclude a dimension from your fix statement, Essbase calculates all members from the implied dimension. Sandbox dimensions are calculated differently, as the intent is usually to calculate either Base or specific sandboxes at a given time. Essbase calculates the Base member values, rather than the working sandbox values, except:

  • When the calculation fixes on particular sandbox members.

  • When the calculation is executed from a sheet launched from a scenario in the web interface (this is called a scenario-launched sheet). See View and Work With Scenario Data From the Essbase Web Interface.

  • When a sandbox cell value is selected in a private connection Smart View sheet and a calculation script is launched.

If you execute a calculation script from a scenario-launched sheet, the calculation runs in the sandbox associated with the scenario as long as no sandbox is explicitly mentioned in the script.

If you're in a sheet opened using a Smart View private connection and you're displaying sandbox and base values, if you highlight any data cell from the sandbox and launch a calculation script without explicit sandbox fix, the sandbox will implicitly be calculated and Smart View will indicate that the sandbox was calculated. If you highlight a cell from the base member (or highlight no cell), then the base will be calculated when you launch your calc script and Smart View will indicate that the base was calculated.

You can calculate sandbox members using your pre-existing MAXL scripts by using the reserved runtime substitution variable name: ess_sandbox_mbr.

This statement can be implemented (for your sandbox) in any MAXL script without creating any substitution variable on the server or application.

execute calculation appname.dbname.calcname with runtimesubvars ‘ess_sandbox_mbr=sb0;’;

About Data Loads to Scenario-enabled Cubes

You can load scenario-enabled cubes using data exports taken before enabling the cube for scenarios. The data will load to the base sandbox member.

If you didn’t use column export, then you can’t have outline member changes that would invalidate your data load. If you used column export but your outline has changed, you may need a .rul file to load the data.

About Data Exports from Scenario-enabled Cubes

Scenario-enabled cubes have a CellProperties dimension that is for internal purposes, nonetheless this dimension is included in data exports and must be considered when loading exported data. Also, it is important to understand the behavior of the sandbox dimension when working with exported data.

The following are considerations when exporting data from scenario-enabled cubes:

  • If you use the web interface Jobs page to export data from a scenario-enabled cube, the resulting data file contains all three members from the CellProperties dimension (EssValue, EssStatus, and EssTID). Do not eliminate any of these columns.
  • The data file from the export includes data physically stored in the cube, based on the selection you make: level zero data, all data, or input data.
  • If values have been changed in sandboxes, then sandbox values will be in your export.
  • In order to load exported data into sandboxes, values for all three CellProperties members (EssValue, EssStatus, and EssTID) must be in the data file.

About Transparent and Replicated Partitions in Scenario-enabled Cubes

Transparent and Replicated partitions connect slices from two Essbase cubes together. This is the case when neither, one, or both cubes are scenario-enabled.

Sandboxes come into use when scenarios are created. However, there is no guarantee that scenarios on partitioned cubes will map to the same sandbox number. The same user may not be a participant in sandboxes in multiple cubes. Introducing scenarios imposes the following limitations:

  • If source of a transparent partition is scenario-enabled, target queries will always pull data from the source base sandbox member.
  • Write-back between scenario-enabled source and target cubes is only allowed between base members in the cubes, target cube base to source cube base.

    Example: Write-back to source, which is normally enabled from transparent partition target cubes, is disabled for non-base sandbox members of scenario-enabled target cubes. It is a violation of permissions to allow a remote sandbox user to write directly into the base of the source cube.

  • For replicated partitions, replication is only possible between source cube base and target cube base.

See Understand Transparent and Replicated Partitions.

About XREF/XWRITE in Scenario-enabled Cubes

In scenario-enabled cubes, you can use XREF and XWRITE to reference or write to data in another cube.

XREF queries a remote cube from a local cube (the cube containing the XREF statement). If the remote cube is scenario-enabled, XREF only pulls base data from the remote cube.

XWRITE updates a remote cube from a local cube (the cube containing the XWRITE statement). Because XWRITE writes data into the remote cube, the scope of the XWRITE statement matters.

For different combinations of scenario-enabled and non-scenario-enabled cubes, XWRITE behaves in the following ways:

When a scenario-enabled local cube references a non-scenario-enabled remote cube,
  • A Fix on the base member in the local cube with an XWRITE to the remote cube writes the local cube base into the remote cube.
  • No Fix on any sandbox member in the local cube with XWRITE to the remote cube writes the local cube base into the remote cube. If you don't include a sandbox member in the Fix, base is included automatically.
  • A Fix on sandbox in the local cube with an XWRITE to the remote cube returns an error. Writing from a non-base sandbox member into a remote cube is not supported.
When a scenario-enabled local cube references a scenario-enabled remote cube,
  • A Fix on the base member in the local cube with an XWRITE to the remote cube writes the local cube base into the remote cube base.
  • No Fix on any sandbox member in the local cube with an XWRITE to the remote cube writes the local cube base into the remote cube base. If you don't include a sandbox member, base is included automatically.
  • A Fix on sandbox in the local cube with an XWRITE to the remote cube returns an error. Writing from a non-base sandbox member into a remote cube is not supported.

When a non-scenario-enabled local cube references a scenario-enabled remote cube, XWRITE always updates the remote cube base member.

See Understand XREF/XWRITE.

About Audit Trail in Scenario-enabled Cubes

Data audit trail tracks updates made to data in a cube. To work with audit trail in scenario-enabled cubes you should understand what defines "old" and "new" data values, and the two different entry points for working with sandbox data in Smart View.

This topic assumes you are familiar with the different entry points for viewing scenario data. See:

If you consider the latest data update committed to a cell to be “new” data, and all prior data values for that cell to be “old,” it can help you understand how audit trail works in scenario-enabled cubes.

A new or unused sandbox in a scenario-enabled cube contains no stored values. The values shown to users, such as the values displayed in a spreadsheet, reflect the values stored in the base.

If you use data audit trail on a new scenario-enabled cube, the base values that display in the spreadsheet for the sandbox are considered the “old” values.

When you update values in a sandbox, those values are stored in the sandbox (not in the base). For the purposes of data audit trail, these values are the “new” values.

If you later update these “new” values, audit trail will track the latest changes. It will treat the previous values as “old” and the updated values as “new.”

In summary,
  • Old values are the base values reflected in a new sandbox.
  • Initially, new values are the updated, stored values in the sandbox.
  • Subsequently, updated values are new, and the values they replace are old.
There are two possible entry points for working with data in Smart View:
  • Where you open Excel and make a private connection to your cube, without starting from the Essbase web interface.
  • Where you launch Smart View from a scenario in the web interface.

When you start by opening Excel and making a private connection to your cube, audit trail works as you would expect with any other data set.

When you launch Smart View from a scenario in the Essbase web interface, audit trail works differently.
  • When you export logs to a sheet, the sheet does not show the implicit sandbox member.
  • When you launch a new sheet using the Ad hoc button below the Audit Trail pane, the new sheet does not show the implicit sandbox member, and any changes in that sheet affect the data values for that sandbox member.

About Scenario Limitations

These limitations apply to scenarios and sandbox dimensions.

  • Scenarios are not supported on aggregate storage cubes.

  • The DATAEXPORT calculation command isn’t supported on sandbox members. It is only supported on the Base member.

  • When you connect to a scenario from a scenario-launched sheet, MDX queries, MDX inserts, and MDX exports will work with the base instead of working with the sandbox for that scenario.

  • Runtime substitution variables with the svLaunch parameter are not supported when you launch the scenario in Smart View from the Essbase web interface. See View and Work With Scenario Data From the Essbase Web Interface.

    Runtime substitution variables with the svLaunch parameter work correctly when you connect to the scenario directly from a private connection. This is because the sandbox member is included in the sheet.

There are a limited number of functions that are not supported in hybrid mode, which is used with scenario-enabled cubes. See Functions Supported in Hybrid Mode.

Scenario Workflow

You can review a scenario using an optional approval workflow. Alternatively, when working with a scenario, you can change data values in the scenario and commit data changes to the cube (or reject them), without going through an approval process.

Scenario status changes and workflow are affected by the number of participants and approvers for a given scenario. With participants, but no approvers, participants do not have the option to submit the scenario for approval, and there is no option to approve or reject the scenario. With no participants and no approvers, the scenario owner makes the changes and applies them. Again, there is no approval process.

  • Scenario with participants but no approvers:
    1. Scenario owner creates the scenario (Status = In Progress)
    2. Scenario owner and participants make changes in Smart View or the web interface
    3. Scenario owner applies changes to base (Status = Applied)
  • Scenario with no approvers and no participants
    1. Scenario owner creates the scenario (Status = In Progress)
    2. Scenario owner makes changes in Smart View or the web interface
    3. Scenario owner applies changes to base (Status = Applied)
  • Scenario with participants and approvers
    1. Scenario is created by owner (Status = In Progress)
    2. Scenario owner, participants and approvers can make changes in Smart View or the web interface
    3. Scenario owner submits the scenario for approval (Status = Submitted)
    4. Scenario is either approved by all approvers or rejected by one or more approvers (Status = Approved or Status=Rejected)

      Rejected status is the same as In Progress status, in that all participants can make changes to reach approved status.

    5. After the scenario reaches approved status (all approvers have approved the scenario), then the scenario owner applies the changes to the base (Status=Applied).

Enable Email Notifications for Scenario Status Changes

If the system administrator has enabled outgoing emails from Essbase, then the appropriate scenario participants receive email notifications for scenario changes.

To set up SMTP email notifications:

  1. Log in to Essbase as a system administrator.
  2. Click Console.
  3. Select Email Configuration.
  4. Select the SMTP Configuration tab.

    SMTP controls outgoing email.

  5. Enter your company’s SMTP host and port.
  6. Enter your company email address and password, of the sender of the notification email.
  7. Click Save.

When SMTP mail is set up, scenario participants begin receiving emails when their scenarios change status, ownership, priority, or due date.

When users are added to the system, email is an optional field. If it has not been filled out, then that user cannot receive emails even if they participate in scenarios.

Scenario State Email to Email Cc Email Subject
Create scenario Participant, approver Owner You are invited to participate in scenario <scenario name>
Submit Approver Owner, participant Scenario <scenario name> is submitted for approval
Approve Owner Participant, approver Scenario <scenario name> is approved
Reject Owner Participant, approver Scenario <scenario name> is rejected by <user>
Apply Participant Owner, approver Scenario <scenario name> is updated
Delete Participant, approver, owner Deleting user Scenario <scenario name> is deleted
Update action

Can be a change in ownership, priority, or due date.

Participant, approver Owner Scenario <scenario name> is updated

An existing scenario can be updated (see Update action in the table) to change the owner, the priority, or the due date. If, for example, the scenario’s due date is changed, then the participants will receive an email indicating the new due date. The old due date will appear in strike through text, so that it is clear what information about the scenario was updated.

Create a Scenario

To create a scenario, you specify general information about your scenario, including creating a scenario name, selecting a due date, selecting an application and cube, and choosing whether to use calculated values. Then you add users and define whether each user is a participant or an approver.

To create a scenario you must:
  • Be a user provisioned to the application or be the application’s owner.
  • Have database update permission.
  1. In Essbase, login as a user with database update (or higher) permission to at least one application.
  2. Click Scenarios.
  3. Click Create Scenario.
  4. On the General Information tab, enter a scenario name and select a Priority (optional), Due Date, Application, and Database (cube). You will only see applications for which you have minimum database update permission.
  5. Turn on Use Calculated Values if you want to merge calculated values to base values when running calculation scripts on scenarios.
  6. (Optional) Enter a description.
  7. On the Users tab, click Add Image of the Add User icon. for a list of users.
  8. Add the users that you want.
  9. Close the Add Users dialog box.
  10. For each user, keep the default (Participant), or select Approver.
    Scenario user roles determine the workflow for the scenario.
  11. Save your changes.

Model Data

As a scenario user, you can model data slices in your own scenario.

  1. In Essbase, click Scenario.
  2. On the Scenarios page, locate the scenario in which you want to model data.
    • You can search for the scenario by name in the Search field.
    • You can select your application from the All Applications drop-down list and search within that application.
    • After selecting the application, you can further narrow your search by selecting the database (cube) from the All Databases drop-down list and searching within that specific cube.
  3. Launch Smart View by clicking the Excel Image of the show changes in Smart View Excel icon. icon before the scenario name.
  4. Make data changes and perform your what-if analysis in Smart View.

    If you change and submit values and decide you want to go back to the base values, you can revert to the base by typing #Revert in the changed cells and choosing Submit Data on the Smart View Essbase ribbon.

    If a cell in the base has a value, and you want the corresponding cell in the scenario to be #Missing, you can send #Missing to the scenario or you can delete the value in Smart View and select Submit Data on the Smart View Essbase ribbon.

  5. Continue this process until you’re ready to submit data for approval.
If a calculation has been run on a sandbox and the changes are not acceptable, request from your application designer a calc script to revert the changes, or request a new sandbox.

Submit a Scenario for Approval

After you submit a scenario for approval, no one will be able to write to that scenario.

  1. In the Essbase web interface, log in as the application owner or the scenario owner.
  2. Click Scenarios.
  3. Click the Submit Image of the scenario submit arrow. arrow under Actions.
  4. (Optional) Enter a comment.
  5. Click OK.
After a scenario is submitted for approval, the scenario approver can approve or reject the data changes.

Approve or Reject Scenario Changes

After the owner of the scenario submits for approval, the approver has the option to approve or reject scenario changes, and the scenario owner is notified of the action. You must be logged in as an approver to have the options to approve or reject a scenario.

  1. In the Essbase web interface, click Scenarios.
  2. Next to the submitted scenario, under Actions, click Approve Image of the approve scenario icon. or Reject Image of the reject scenario icon..
  3. Enter a comment on the Approve or Reject dialog box.

After a scenario is approved, the scenario owner can apply the changes to the cube.

Apply or Discard Data Changes

When you apply data changes, the changes stored within the scenario overwrite the base data.

You can apply or discard changes from the Scenario page.

  1. In the Essbase web interface, click Scenarios.

  2. Next to the approved scenario, under Actions, click Apply or Discard.

  3. When prompted, confirm your selection.
  • You can also apply data changes using the DATAMERGE calculation command.
  • After a scenario is applied or discarded, you can delete the scenario to reuse the sandbox for that scenario.
  • Database managers and higher can execute a calculation script to perform a DATAMERGE. They do not need to be designated as scenario approvers in order to do so.
  • After a scenario is applied, it can be re-applied, but it cannot be changed.

Copy a Scenario

If you have the service administrator role, or if you are a scenario user (participant, approver, or owner), you can copy a scenario. You can copy scenarios at any point in the scenario workflow, prior to Delete Scenario. The approval state of the copied scenario is reset to In Progress.

  1. In the Essbase web interface, click Scenarios.
  2. Click the Actions menu for the scenario you want to copy, and click Copy.
  3. Enter the scenario name and select what scenario components to copy from Approvers, Participants, Comments, and Data.
  4. Click OK.

Delete the Scenario

Since there are a fixed number of available sandboxes in a cube, you may need to free up sandboxes from inactive scenarios. After the associated scenario is deleted, the sandbox is empty and is automatically returned to the pool of available sandboxes.

To reuse a sandbox associated with a scenario, you need to delete the scenario.

  1. In the Essbase web interface, click Scenarios.
  2. Click the Actions menu for the scenario you want to delete, and click Delete.

Understand Scenario User Roles and Workflow

You can review a scenario using an optional approval workflow.

Scenario user role assignments determine the workflow for scenarios. You must have at least one approver to enable the scenario workflow. Without an approver, participants do not have the option to submit the scenario for approval, for example, and there is no option to approve or reject the scenario.

The only action for scenarios without at least one approver is Apply. Without an approver, the scenario owner can still change data values in the scenario and apply data changes to the cube (or reject them), without going through an approval process.

Participants can participate in a what-if analysis. They must have Database Update or Database Access user role. Adding participants is not mandatory.

Approvers monitor the process, and approve or reject scenarios. They must have Database Access or higher role. Scenarios can have multiple approvers, in which case each one must approve the scenario before it can be submitted.

Participants and approvers with the Database Access user role cannot write to a scenario until they are granted write access through a filter.

Participants and approvers are not mandatory. The scenario owner can change data values in the scenario and commit data changes to the cube (or reject them) without designating participants or approvers.


Image of a flowchart showing the workflow for working with scenarios.

Enable Scenario Modeling

Enabling scenario modeling as part of the cube creation process is as easy as selecting a check box in the user interface or populating the right fields in an application workbook.

You can create or enable a cube for scenario modeling using one of the following methods:

Data Audit Trail is not supported on scenario-enabled cubes.

Create a Scenario-Enabled Cube

Scenario-enabled cubes have specialized dimensions required to use scenario management. These include the Sandbox dimension and the CellProperties dimension. CellProperties is considered a hidden dimension in that you do not need to interact with it in any way when performing Essbase tasks such as building cubes, loading data, or calculating cubes.

  1. On the Applications home page, click Create Application.

  2. On the Create Application dialog box, enter an Application Name and a Database Name (cube name) and expand Advanced Options.

  3. Ensure that in Database Type, Block Storage (BSO) is selected.

  4. Select Enable Scenarios.

  5. Click OK.

Create a Scenario-Enabled Sample Cube

You can create a scenario-enabled cube by importing the scenario-enabled sample application workbook.

  1. In the Essbase web interface, click Import.
  2. Click Catalog.
  3. Drill down into gallery > Applications > Demo Samples > Block Storage.
  4. Select Sample_Basic_Scenario.xlsx and click Select.
  5. Provide a unique name and click OK.

Enable an Existing Cube for Scenario Management

If you have the application manager role, you can enable an existing cube to use scenario modeling. It is best to do so on a copy of the original cube. Existing scripts, rules, and queries will work as before, on the base member. If you need to run them on a sandbox member, you can run them from a scenario-launched sheet.

A scenario launched sheet is an Excel sheet launched from a scenario in the web interface. See View and Work With Scenario Data From the Essbase Web Interface .

  1. On the Applications page, expand the application.
  2. Click the Actions menu to the right of the cube name and select Inspect.
  3. On the General tab, for Scenarios, click Not Enabled.
  4. Adjust the number of scenarios members (non-base sandbox members) you want to create and click Ok.

Create Additional Sandbox Members

By default, a new scenario-enabled cube has 100 sandbox members. You can create additional sandbox members (up to 1000).

  1. On the Applications page, expand the application.
  2. Click the Actions menu to the right of the cube name, and select Inspect.
  3. On the General tab, click the plus sign next to Scenarios.
    Image of the cube inspector, general tab, with scenarios highlighted.
  4. Enter the number of sandbox members you want to create.
  5. Click OK.

Work with Scenarios

After you create a scenario-enabled cube, you can create scenarios and follow a workflow that includes modeling data, approving or rejecting changes, applying or discarding changes, and submitting the scenario for approval.

View Base Member Data

From the web user interface, you can launch an Excel sheet showing base data for a scenario.

  1. In Essbase, click Scenarios.
  2. Click the Actions menu for the scenario you want to view, and click Show Base Data.
  3. Click on the downloaded link to launch Smart View.
The Excel sheet that is launched shows base data for the cube. It does not show sandbox data.

Compare Scenario Values to Base Values

If you are the owner, approver or participant for a given scenario, you can view scenario and base values in a spreadsheet or in the web user interface to compare models.

Compare Values in Excel

  1. In the Essbase web interface, click Scenarios.

  2. From the Actions menu, select Show Changes in Excel.

  3. Click on the downloaded link to open the Smart View link.

  4. You can view values for both the scenario and base members in the spreadsheet.
    Image of an Excel spreadsheet showing base and scenario values.

    • In column G, sb10 is the scenario (or sandbox) member.

    • In column F, Base shows the base values.

    • In the scenario, values for sb10 on rows 2 through 6 have been changed, and you can see the aggregated result in row 7.

Compare Values in the Web User Interface

  1. In the Essbase web interface, click Scenarios.

  2. From the Actions menu, select Show Changes.

    The Data Changes dialog box is empty if no data changes have been made.

Compare the scenario to the base in order to determine your next steps. For example, you might choose to change the status of the scenario to approved based on this information.

Set Scenario Cells to #Missing

You can set scenario cells to #Missing even if the corresponding base cells have values.

To set a scenario cell to #Missing:
  1. Type #Missing in the cell or delete the cell contents.
  2. Select Submit Data on the Smart View ribbon.
Example
  1. Initially, the value in sb1 is an exact mirror of the value in the base.
    Image of a grid showing sb1 and Base, both with a value of 678.

  2. Enter #Missing in sb1 (or delete the cell contents) and submit data.
    Image of a grid with #Missing in sb1.

  3. Refresh the sheet. See that sb1 is #Missing.
    Image of a grid with #Missing in sb1.

Revert Scenario Values Back to Base Values

Initially, scenario values are not stored and they are an exact mirror of the base values. After you change the scenario values in Excel and submit the changes to the cube, the scenario values are stored, and they are different from the base. You can revert the scenario values back to the base by typing #Revert in the changed cells and clicking Submit Data on the Smart View ribbon.

To revert scenario values back to the base:
  1. In Excel, type #Revert in the scenario cells you want to revert to the base.
  2. Click Submit Data on the Smart View ribbon.
The selected scenario values are updated to the base values.

Example

  1. Initially, the value in sb1 is an exact mirror of the value in base.
    Image of a grid with a base member and a scenario member showing the same value.

  2. Submit a new value, 100, to sb1.
    Image of a grid with the scenario member showing a value of 100.

  3. Submit #Revert to sb1.
    Image of a grid showing #Revert in the sb1 cell.

  4. Refresh the sheet. See that sb1 again reflects the base value of 678.
    Image of a grid with a base member and a scenario member showing the same value.

Understand When to Aggregate Sandbox Dimensions

As you model in scenarios, you will need to determine whether or not to calculate within each sandbox.

Submit data changes to the sandbox and calculate as little other data as possible, just enough data to allow users to validate their work. This preserves the storage efficiency of the sandbox design.

For example, when all upper level members in a cube are dynamic calc, aggregations in the form of calculation script are not needed.

If you have stored upper level members, limit the scope of any sandbox calculation to the minimum needed for users to do their work.

Example: Calculate Scenarios with Dynamic Upper Level Members

Dynamic hierarchies (both dense and sparse) aggregate automatically, and users making changes in sandboxes see their changes immediately.

Let’s look at an example from the Sample_Scenario.Basic block storage demo application.

Assume that Product and Market are dynamic hierarchies with data stored only at level zero, and that a scenario is created using sandbox dimension member sb0.

When the sandbox is newly created, values for sb0 are the same as the values for Base. This is because sandbox members are virtual, reflecting base values until users submit changes to them.


Image of an Excel spreadsheet, showing values for the Base and sb0 members of the Sandbox dimension. The values for both members are the same.

After modifying Sales->Budget->Jan->Cola data in member sb0, we immediately see that the dynamic sandbox member, West (in D10) aggregates to the correct total by using a combination of stored members from Base and sb0.

Values for Oregon, Utah and Nevada are stored in the Base sandbox member. Values for California and Washington have been submitted by scenario participants and are stored in the sb0 sandbox member. The total for West->Cola->sb0 aggregates dynamically using these stored values.


Image of an Excel spreadsheet showing values for the Base and sb0 members of the Sandbox dimension. The values are the same, except for California and Washington, which have been changed.

You can also use calculation scripts in sandboxes. Assume that Oregon is meant to be budgeted as 80% of California. The following calculation script can do this:

FIX("Jan", "Budget", "Cola", "Sales")
"Oregon"="California"*.8;
ENDFIX

When a scenario participant launches an Excel worksheet from the web interface and runs this calculation, sb0 is the default sandbox member calculated and the value for member Oregon is updated:


Image of an Excel spreadsheet showing values for the Base and sb0 members of the Sandbox dimension. The values are the same, except for California and Washington, and Oregon which have been changed. The value for Oregon is 720 and reflects the results of the calculation script.

This view is not from a scenario-launched sheet, but rather from a Smart View private view, where Base and sb0 can both be represented on the sheet.

Example: Calculate Scenarios with Stored Upper Level Members

In some cases, a sparse or dense hierarchy may have stored upper level members, and aggregations on level- or generation-based calculations could be required.

Continuing from the last grid of the previous example, assume now that upper level members in the Market dimension are stored, rather than dynamic.

If we change the value for Oregon to 250, the West member will need to be re-calculated before we can see correct results:


Image of an Excel spreadsheet showing values for the Base and sb0 members of the Sandbox dimension. The values are the same, except for California and Washington, and Oregon which have been changed. The value for Oregon is 250. The total value for sb0 for the West region is 2070.

The following calc script can be used to aggregate the Market dimension in the sandbox, when executed from a scenario-launched excel sheet:

AGG("Market");
Image of an Excel spreadsheet showing values for the Base and sb0 members of the Sandbox dimension. The values are the same, except for California and Washington, and Oregon which have been changed. The value for Oregon is 250. The total value for sb0 for the West region is 1600.