Sources of Data that Do Not Need a Load Rule

To load data to an Essbase cube, you typically need a load rule, unless the source data maps perfectly to the cube. However, if a source of data contains all of the information required, you can load the data source directly in a free-form data load with no rule.

If you are performing a dimension build, skip this section. Dimension builds always require load rules.

To load a data value successfully, Essbase must encounter one member from each dimension before encountering the data value. For example, Essbase loads the data value 42 into the database with the members Texas, 100-10, Jan, Sales, and Actual.

This image illustrates the concept of records and fields in a source of data, as described in the text preceding the image.

If Essbase encounters a data value before a member of each dimension is specified, it stops loading the data.

To map perfectly, 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 (&)
  
  If you are performing a data load without a rule file, when Essbase encounters an invalid member field, it stops the data load even if the Abort on Error flag is not set to true. Essbase loads all fields read before the invalid field into the database, resulting in a partial load of the data values.
One or more valid data values. See Valid Data Fields.

If the data source contains blank fields for data values, replace the blank fields with #MI or #MISSING. Otherwise, the data values may not load correctly.
Valid delimiters. See Valid Delimiters.

The fields in the data source must be formatted in an order that Essbase understands. The simplest way to format a record is to include a member from each dimension and 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

An incorrectly formatted data source will not load. You can edit the data source using a text editor and fix the problem. If you must perform many edits (such as moving several fields and records), use a load rule.

The following sections describe more complicated ways to format free-form sources.

Ranges of Member Fields in the Source Data

You can express Essbase member names as ranges within a dimension. A range is a series of values. A source of data may contain ranges from multiple dimensions at a time. Learn how Essbase processes ranges during a data load, if no load rule is used.

If you are performing a dimension build, this information is not applicable, as dimension builds always require a rule. If you are performing a data load, this information applies when no rule is used (but it is also helpful to know about ranges when you design a data load rule).

You can express member names as ranges within a dimension. For example, Sales and COGS form a range in the Measures dimension. Ranges of member names can handle a series of values.

A source of data may contain ranges from multiple dimensions at a time. In the example below, Jan and Feb form a range in the Year dimension and Sales and COGS form a range in the Measures dimension.


Actual   Texas   Sales   COGS
           Jan   Feb   Jan   Feb
"100-10"   98    89    26    19
"100-20"   87    78    23    32

Notice that Sales is defined for the first two columns and COGS for the last two columns.

Automatic Ranges

When Essbase encounters multiple members from the same dimension with no intervening data fields, it sets up a range for that dimension. The range stays in effect until Essbase encounters another member name from the same dimension, at which point Essbase replaces the range with the new member or new member range.

The following example contains a range of Jan to Feb in the Year dimension. It remains in effect until Essbase encounters another member name, such as Mar. When Essbase encounters Mar, the range changes to Jan, Feb, Mar.

Texas   Sales
                    Jan   Feb   Mar
Actual   “100-10"   98    89    58
         “100-20”   87    78    115

Out of Range Data Values

When Essbase encounters a member range, it assumes that there is a corresponding range of data values. If the data values are not in the member range, the data load stops. Essbase loads any data fields read before the invalid field into the cube, resulting in a partial data load.

The following example contains more data fields than member fields in the defined range of members. The data load stops when it reaches the 10 data field. Essbase loads the 100 and 120 data fields into the cube.

Cola   Actual   East
        Jan   Feb
Sales   100   120   10
COGS    30    34    32

Repeated Members in a Range

Structure ranges in the source data so that Essbase interprets them correctly. If a member appears more than once in a range, Essbase ignores the repeated members.

The first table below shows repeated members for Actual, Budget, Sales, and COGS and two ranges: Actual to Budget and Sales to COGS. Essbase ignores the repeated instances of Actual, Budget, Sales, and COGS (for example, in the second line, the repeated Actual and Budget are ignored):


Cola   East
      Actual   Budget   Actual   Budget
      Sales   Sales   COGS   COGS
Jan   108     110     49     50
Feb   102     120     57     60

For Actual, the first member of the first range, Essbase maps data values to each member of the second range (Sales and COGS). Essbase then proceeds to the next value of the first range, Budget, similarly mapping values to each member of the second range. As a result, Essbase interprets the file as shown below:

Cola   East
      Actual         Budget
      Sales   COGS   Sales   COGS
Jan   108     110    49      50
Feb   102     120    57      60

Columns in the Source Data

Learn how Essbase processes columns during a data load, if no load rule is used. Columns of source data you load to the cube may be symmetric or asymmetric.

Sources of data typically contain columns of fields. Essbase supports loading data from symmetric or asymmetric columns.

Symmetric columns

Symmetric columns have the same number of members under them. In the following example, each dimension column has one column of members under it. For example, Product has one column under it (100-10 and 100-10) and Market has one column under it (Texas and Ohio).


Product    Measures   Market    Year   Scenario   
"100-10"   Sales      Texas    Jan    Actual     112
"100-10"   Sales      Ohio     Jan    Actual     145

The columns in the following file are also symmetric, because Jan and Feb have the same number of members under them:

                                 Jan               Feb
                           Actual   Budget   Actual   Budget
"100-10"   Sales   Texas   112      110      243      215
"100-10"   Sales   Ohio    145      120      81       102

Asymmetric columns

Asymmetric columns have different numbers of members under them. In the following example, the Jan and Feb columns are asymmetric because Jan has two columns under it (Actual and Budget) and Feb has one column under it (Budget):

                           Jan      Jan      Feb
                           Actual   Budget   Budget
"100-10"   Sales   Texas   112      110      243
"100-10"   Sales   Ohio    145      120      81

If a file contains asymmetric columns, label each column with the appropriate member name.

The example above is valid because the Jan label is now over Actual and Budget. It is clear to Essbase that both columns map to Jan.

The following example is not valid because the column labels are incomplete. The Jan label must appear over the Actual and Budget columns.

                           Jan               Feb
                           Actual   Budget   Budget
"100-10"   Sales   Texas   112      110      243
"100-10"   Sales   Ohio    145      120      81