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