A load rule defines which build method to use, whether data values or members are sorted or in random order, and how to transform data values or members before loading them.

Essbase reads the data values or members in the source, changes them based on the rules, and loads the changed data values into the cube and the changed members into the outline. Essbase does not change the source data. You can reuse a rule file with any source of data that requires the same set of rules.

Load Rule Operations

Rule files enable you to make the following adjustments as you load data or members to your cube:

• Pull from the external source using a SQL query

• Add dimensions and members to the outline

• Change existing dimensions and members in the outline

• Ignore some fields or strings in the source data

• Change the order of fields by moving, joining, splitting, or creating fields

• Map the data in the source to the cube by changing strings

• Change the data values in the source by scaling data values or by adding data values to existing data values in the data source

• Set header records for missing values

• Reject an invalid record and continue the data load

Data Load/Dimension Build Success Criteria

When building dimensions, it is best to create a separate rule file for each dimension.

To load a data value successfully, Essbase must know its dimensionality; in other words, Essbase must encounter one member from each dimension before encountering the data value. For example, in Figure 11-2, Essbase loads the data value 42 into the database with the members Texas, 100-10, Jan, Sales, and Actual. If Essbase encounters a data value before a member of each dimension is specified, it stops loading the data.

The simplest way to format a record (which is analogous to a row in the source data) is to include a member from each dimension, followed by a data field, as illustrated below:

Sales "100-10" Ohio Jan Actual 25
Sales "100-20" Ohio Jan Actual 25
Sales "100-30" Ohio Jan Actual 25

The number of fields in each record in a rule file must match (note how each record above has 6 fields).

A source of data must contain all of the following and nothing else.

• One or more valid members from each dimension. A member name must be enclosed in quotation marks if it contains any of the following:

  • Spaces

  • Numeric characters (0–9)

  • Dashes (minus signs, hyphens)

  • Plus signs

  • Ampersands (&)

• One or more valid data values. See Valid Data Fields.

• Valid delimiters. See Valid Delimiters.

Use the load rule to help you format the source data as you load it (there is no need to edit the source manually). If the source data is already perfectly formed (for example, if it is an unaltered data export from Essbase), then you may not need a load rule, but such situations are rare. Dimension builds always require a load rule.