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.
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
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.
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.
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.
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 |