Essbase - Essbase-friendly/free-form Data Sources (Don't Need a Rules File during data load)

About

If a Essbase - Data Source contains all of the information required to load the data values in it into the database, you can load the data source directly in a free-form data load (without rule file)

To load a data value successfully, Essbase must encounter one member from each dimension before encountering the data value.

For example, in the figure below, 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 source.

To map perfectly, a data source 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 rules file, when Essbase encounters an invalid member field, it stops the data load. Essbase loads all fields read before the invalid field into the database, resulting in a partial load of the data values. See Loading Dimension Build and Data Load Error Logs.
  • One or more valid data values. See Valid Essbase - Data fieldss. 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 Essbase - Delimiters.

The data in the file would also need to be ordered in the same order as the members in the outline from top to bottom.

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), consider using a Essbase - Data Load Rules Files.

When your data source file is created from an Essbase database data export, you get a free-form data source

More complicated ways to format free-form data sources

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

Formatting Ranges of Member Fields

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 data source can contain ranges from more than one dimension 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.

The following sections describe additional types of ranges.

Setting Ranges Automatically

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

Handling 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 database, 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 database.

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

For information on restarting the load, see Loading Dimension Build and Data Load Error Logs.

Interpreting Duplicate 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 duplicates.

The following example shows duplicate members for Actual, Budget, Sales, and Budget and two ranges: Actual to Budget and Sales to COGS. Essbase ignores the duplicate instances of Actual, Budget, Sales, and COGs (as shown in bold text).

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

Reading Multiple Ranges

As Essbase scans a file, it processes the most recently encountered range first when identifying a range of data values. The example above contains two ranges: Actual and Budget and Sales and COGS. While reading the file from left to right and top to bottom, Essbase encounters the Actual and Budget range first and the Sales and COGS range second. Because the Sales and COGS range is encountered second, Essbase puts data fields in the Sales and COGS part of the database first.

Formatting Columns

Files can contain columns of fields. Essbase supports loading data from symmetric columns 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

Documentation / Reference


Powered by ComboStrap