Modifying Essbase Data

In this section:

The Adapter for Essbase supports Write access to cubes using the MODIFY command. By using MODIFY in conjunction with the MATCH or NOMATCH commands, you can change the values of a measure cell.

To ensure the cube's calculation consistency, the measure cells must be fully defined dimension coordinates of Essbase members that do not have any children.


Top of page

Example: Updating the Value of a Measure Cell

This example illustrates the affect of a MODIFY request run against an Essbase data source. It shows the report output before and after the MODIFY procedure is run.

To establish a base point for the comparison, suppose that the following report request is run against the Essbase data source BASIC. To create the synonym for this example, select the application Sample and the database Basic, then select Measures in the Select Measure drop-down box on the synonym creation panes:

TABLE FILE BASIC
PRINT SKU SALES 
WHERE MONTH EQ 'Dec'
WHERE STATE EQ 'Oregon'
WHERE GEN2_SCENARIO EQ 'Actual'
WHERE SKU EQ '400-30'
END

The output is:

SKU      Sales
-----    -----
400-30  320.00

If you were to run the following MODIFY request and then rerun the TABLE request, you would see updated output for the measure cell SALES.

MODIFY FILE BASIC
  FREEFORM MONTH STATE GEN2_SCENARIO SKU SALES
  MATCH MONTH
  MATCH STATE
  MATCH GEN2_SCENARIO
  MATCH SKU
  ON MATCH UPDATE SALES
DATA
MONTH=Dec, STATE=Oregon, GEN2_SCENARIO=Actual, SKU=400-30,SALES=325.00,$
END

The MATCH conditions trigger an update of the SALES number, producing the following report when the TABLE request is run again:

SKU      Sales
-----    -----
400-30  325.00

Top of page

Example: Updating the Values of Multiple Measure Cells

In this example, the MODIFY command is used in conjunction with two COMPUTE commands to replace values of two measure cells: ACTUAL_SALES and BUDGET_SALES. To create the synonym for this example, select the application Sample and the database Basic, then select Measures in the Select Measure drop-down box and Scenario in the Select Measure Group drop-down box on the synonym creation panes.

For comparison, suppose that you run the following report request:

TABLE FILE BASIC
PRINT SKU ACTUAL_SALES BUDGET_SALES
WHERE MONTH EQ 'Dec'
WHERE STATE EQ 'Oregon'
WHERE FAMILY EQ '100'
END

The output is:

SKU            Actual_Sales       Budget_Sales
--------   ----------------   ----------------
100-10                98.00             110.00
100-20               101.00             120.00
100-30               112.00             130.00

Suppose now that you want to compute new values for ACTUAL_SALES and BUDGET_SALES (rather than replacing an existing value with a fixed number as illustrated in Updating the Value of a Measure Cell.

In the following example, the COMPUTE command derives the new values for ACTUAL_SALES and BUDGET_SALES. However, due to the complexity of the Essbase API, when a COMPUTE command is used, the report request (TABLE command) READs and WRITEs the computed values to a HOLD file:

TABLE FILE BASIC 
ON TABLE SET HOLDLIST PRINTONLY 
PRINT MONTH STATE SKU ACTUAL_SALES NOPRINT BUDGET_SALES NOPRINT 
COMPUTE ACTUAL_SALES/D20.2 = ACTUAL_SALES/2;
COMPUTE BUDGET_SALES/D20.2 = BUDGET_SALES/2; 
ON TABLE HOLD FORMAT ALPHA AS ESS3AB
WHERE MONTH EQ 'Dec'
WHERE STATE EQ 'Oregon'
WHERE FAMILY EQ '100'
END
-RUN

The values from the HOLD file are then used to update the measure cells in the data source during the execution of the MODIFY procedure:

-M:
MODIFY FILE BASIC 
  FIXFORM FROM ESS3AB
  MATCH MONTH
  MATCH STATE
  MATCH SKU
  ON MATCH UPDATE ACTUAL_SALES BUDGET_SALES 
  DATA ON ESS3AB
END
-RUN

The MATCH command evaluates MONTH, STATE, and SKU. When matches occur, the values of ACTUAL_SALES and BUDGET_SALES are updated, producing the following output when the report request is run:

SKU            Actual_Sales       Budget_Sales
--------   ----------------   ----------------
100-10               49.00               55.00
100-20               50.50               60.00
100-30               56.00               65.00

Note: When Essbase member values are changed, it may be wise to recalculate the Essbase database.


Top of page

x
Modifying the Essbase Outline

You can use the MODIFY command to add and delete members from an Essbase outline.

Note: When you add or delete a member in the Essbase outline, it is not always necessary to recreate the synonym. However, if a member is added or deleted in an Accounts Tagged Dimension, you must recreate the synonym before you can reference the new member in a request.

For related information about Accounts Tagged Dimensions, see Using the SET MEASURE Command.



Example: Deleting Members in the Essbase Outline

This example uses a MODIFY command to delete members in the dimension hierarchy. If a parent member is selected, Essbase will delete the parent and its children.

The following image shows the Time dimension in an Essbase outline:

The MODIFY command deletes Qtr2, and its children, from the outline:

MODIFY FILE BASIC
  FREEFORM QUARTER
  MATCH QUARTER
  ON MATCH DELETE QUARTER 
DATA 
QUARTER=Qtr2, $ 
END

The updated outline now looks as follows:

Notice all of Qtr2 is gone, along with the months Apr, May, and Jun.



Example: Adding Members to the Essbase Outline

In this example, a MODIFY command is used to add members to the dimension hierarchy. This outline looks as follows before the MODIFY request is run:

The following MODIFY request adds two members (in this case states) to the Essbase hierarchy: Georgia in the East region and Arizona in the West region.

MODIFY FILE BASIC
FREEFORM REGION STATE
MATCH REGION 
ON MATCH UPDATE STATE 
DATA 
REGION=East, STATE=Georgia,$
REGION=West, STATE=Arizona,$ 
END
-RUN

Note: When you add new members to the Essbase outline, the length of the member name cannot exceed the ACTUAL and USAGE attributes in the existing Master File, where the ACTUAL and USAGE values for a field are determined by the longest member name in the original outline. For example, in the following Essbase outline Massachusetts is the longest member name, with 13 characters. Therefore, the Master File contains the following entry for the STATE field:

FIELDNAME=STATE, ALIAS=State, USAGE=A13, ACTUAL=A13,$

Trying to add a state whose name exceeds 13 characters will cause an error.

Neither Georgia nor Arizona exceed the limit.

The Essbase outline now looks as follows:


iWay Software