Perform Operations on Data

Use Essbase data load rule options to define a field as a data field, add to or substract from existing values, extract summarized data from the source, clear or replace data, scale data values, or flip signs.

This section applies to data load only. If you are performing a dimension build, skip this section.

You can perform operations on the data in a field; for example, moving a field to a new position in the record.

• Defining Columns as Data Fields

• Adding to and Subtracting from Existing Values

• Extracting Source Data Using Column Store Options

• Clearing Existing Data Values

• Replacing All Data

• Scaling Data Values

• Flipping Field Signs

Defining Columns as Data Fields

When records in the source data contain columns for dimensions followed by one or more data columns, use field properties in the Essbase data load rule to indicate the data fields.

This section applies to data load only. If you are performing a dimension build, skip this section.

If each record in the source data contains a column for every dimension followed by one or more data columns, as in the following example, use the Field Properties to define the data columns as data fields.

The following example shows how to indicate that a field is a data field.

If all fields are dimensions followed by only a single data field, you must mark the data field as a single data field. The following field is how a single data field should look in the rule editor:

To set the field as a single data field,

1. Click the edit field/member selection tool next to the field.

2. Select Single Data Field (*Data*)

   
   
   
   
   

3. Click OK. Verify and save the rule.

Adding to and Subtracting from Existing Values

If you want the Essbase data load to add to or subtract from existing cube values instead of overwriting them, use global properties in the Essbase data load rule to specify how newly loaded data values should affect existing values. For example, if you load weekly values, you can add them to create monthly values in the cube.

This section is for data load only. If you are performing a dimension build, skip this section.

By default, Essbase overwrites the existing values of the cube with the values of the source data, but you can set up the load rule to instead use incoming data values to add to or subtract from existing cube values.

1. In your data load rule, click Global Properties.

2. Select a load option: Overwrite (the default), Add, or Subtract.

   
   
   
   
   

3. Click OK. Verify and save the rule.

You can use these load options to perform Add, Subtract, or Overwrite operations in data-load rules when you want to make broad changes to all the values you are loading into the Essbase cube. You can only select one of these load options per data load, and the values are treated the same way for all data fields (all are either added, subtracted or overwritten).

Using this option makes recovery more difficult if the a problem occurs during data load, although Essbase lists the number of the last row committed in the application log.

For block storage cubes, in the settings, set the Commit Rows transaction option to 0 to prevent difficult recoveries. This setting causes Essbase to view the entire load as a single transaction and to commit the data only when the data load is complete.

Using the import data (aggregate storage) MaxL statement, you can only add to and substract from existing values in aggregate storage cubes only.

Extracting Source Data Using Column Store Options

You can load data to the Essbase cube using column-level options in the data load rule, to extract the source data the way you want it.

As described in Adding to and Subtracting from Existing Values, you can use load options to perform Add, Subtract, or Overwrite operations in data-load rules when you want to make broad changes to all the values you are loading into the cube. You can only select one of these load options per data load, and the values are treated the same way for all data fields (all are either added, subtracted or overwritten).

If you are loading to a large Essbase cube with many upper level members, you may need to perform more specific rule operations at the level of data columns, using SUM/MIN/MAX/COUNT. These operations help you drill through to the source data (from the load rule) to extract the source data in a more specific way.

As an example use for these operations, consider a retail business with credit card transactions stored in a relational system. You don’t want to load all transactional data into Essbase, but you want to load the sum of all transactions at the end of each day.

To select a column level option in a data load rule,

1. Click the edit field/member selection tool next to the rule field for the data column you want to customize.

2. Ensure the field is marked as a data field.

   
   
   
   
   

3. Select a Storage Type option.

   Available options:

   • Minimum - stores the minimum value of the incoming data, including a comparison with existing cube data.

   • Maximum - stores the maximum value of the incoming data, including a comparison with existing cube data.

   • Sum - behaves the same as the Add global option. Adds the incoming data to existing cube data.

   • Count - stores the count of values present in the incoming data.

   
   
   
   
   

4. Click OK. Verify and save the rule.

Clearing Existing Data Values

When records in the source data will be added to or subtracted from existing Essbase data values, you may need to use to clear some regions of data first to ensure accuracy. You can use the global properties of the data load rule clear selected values.

This section is for data load only. If you are performing a dimension build, skip this section.

You can clear selected existing data values from the cube before you load new values. By default, Essbase overwrites the existing values of the cube with the new values of the source data. If you are adding and subtracting data values, however, Essbase adds or subtracts the new data values to and from the existing values.

Before adding or subtracting new values, make sure that the existing values are correct. Before loading the first set of values into the cube, make sure that there is no existing value.

For example, assume that the Sales figures for January are calculated by adding the values for each week in January:

January Sales = Week 1 Sales + Week 2 Sales + Week 3 Sales + Week 4 Sales

When you load Week 1 Sales, clear the cube value for January Monthly Sales. If there is an existing value, Essbase performs the following calculation:

January Sales = Existing Value + Week 1 Sales + Week 2 Sales + Week 3 Sales + Week 4 Sales

You can also clear data from fields that are not part of the data load. For example, if a source contains data for January, February, and March, and you want to load only the March data, you can clear January and February data.

To clear before loading,

1. In your data load rule, click Global Properties.

2. In the Clear Combinations box, enter the member combinations to clear. For example, "Jan","New York".

   
   
   
   
   

3. Click OK. Verify and save the rule.

Replacing All Data

For data slices in aggregate storage (ASO) cubes that can be reloaded in their entirety while maintaining low latency, Essbase can replace values using the contents of an incremental data load buffer.

This topic applies to loading data into an aggregate storage cube only. If you are loading data into a block storage cube or performing a dimension build, skip this topic.

In an aggregate storage cube, Essbase can remove all of the data in the cube or all of the data in each incremental data slice in a cube, and replace the data with the contents of a specified data load buffer. This functionality is useful when working with data sets that are small enough to reload completely, or when working with data that can be separated into large, static data sets that are never updated and small, volatile data sets in which you need to track changes.

To replace all data, see Replace Data Using Incremental Data Slice Contents.

Scaling Data Values

When data values in the source data are not of the same decimal scale as data values in the Essbase cube, use field properties in the data load rule to set a scaling factor for the incoming data values.

This section is for data load only. If you are performing a dimension build, skip this section.

For example, assume the real value of sales is $5,460. If the Sales source data tracks the values in hundreds, the value is 54.6. If the Essbase cube tracks the real value, you must multiply the value coming in from the Sales source data (54.6) by 100 to have the value display correctly in the Essbase cube (as 5460).

To scale the values,

1. Select a data field.

   
   
   
   
   

2. Click Field Properties.

3. Click Scale, and select a scaling factor.

   
   
   
   
   

4. Click OK. Verify and save the rule.

Flipping Field Signs

When records in source data contain negative values that need to be positive in the Essbase cube, or positive values that need to be negative, you can invert the signs. One case where sign flip can be useful is when loading data from double-entry accounting systems.

This section is for data load only. If you are performing a dimension build, skip this section.

You can reverse, or flip, the value of a data field by flipping its sign. Sign flips are based on the UDAs of the outline. When loading data into the accounts dimension, for example, you can specify that any record whose accounts member has a UDA of Expense change from a plus sign to a minus sign.

To flip signs,

1. In your data load rule, click Global Properties.

2. In Sign Flip Dimension, enter the dimension for which you want to flip signs.

3. In Sign Flip UDA, enter the name of the user defined attribute.

   
   
   
   
   

4. Click OK. Verify and save the rule.

See Also

Creating UDAs