Sources of Data
During a data load or dimension build, Essbase reads the source of data from the top, proceeding left to right through records, fields, and delimiters. Sources of data may contain data values and metadata, and can be text files, relational tables, or export files from cubes.
Source data contains the information that you want to load into the Essbase cube. A source of data can contain data values as well as metadata. Metadata is information about members, such as names, aliases, formulas, and other properties.
Items in a Source of Data
During a data load, Essbase reads the source of data starting at the top and proceeding from left to right. A source of data contains records, fields, and field delimiters.
-
A record is a structured row of related fields.
-
A field is an individual value.
-
A delimiter indicates that a field is complete and that the next character in the record starts another field. In the illustration, the default delimiter appears as a space.
Figure 11-1 Records and Fields
Sources of data can contain dimension fields, member fields, member combination fields, and data fields.
Figure 11-2 Kinds of Fields
-
Dimension fields identify the dimensions of the cube, such as Market. Use dimension fields to tell Essbase the order of the dimensions in the source. In the illustration above, the dimension fields are Market, Product, Year, Measures, and Scenario. Fields in the Market column, such as Texas, are members of the Market dimension, and fields in the Product column, such as 100-10, are members of the Product dimension. If the dimension fields are not present in the source, you can identify them using the load rule.
-
Member fields identify the members or member combinations of the specified dimensions. Use member fields to tell Essbase to which members to map new data values, or which members to add to the outline. In the illustration above, Texas, 100-10, Jan, Sales, and Actual are member fields.
-
Data fields contain the numeric data values that are loaded into the intersections of the members of the cube. Each data value must map to a dimension member. In the illustration above, for example, 42 is the data value that corresponds to the intersection of Texas, 100-10, Jan, Sales, and Actual.
You can specify information in the header and in an individual record. One member from every standard dimension on the outline must be associated with every data value being loaded into Essbase. In the following example, 100 is the data value associated with Jan, Actual, Cola, East, Sales, and 200 is the data value associated with Jan, Actual, Cola, West, Sales.
Jan, Actual Cola East Sales 100 Cola West Sales 200 Cola South Sales 300
Data fields are used only for data loading; dimension builds ignore data fields.
Valid Dimension Fields
In an Essbase data load, if the source does not identify every dimension in the outline, the load rule must identify the missing dimensions.
For example, the Sample.Basic cube has a dimension for Year (where Level 0 of year is Month). If several sources of data arrive with monthly numbers from different regions, the month itself might not be specified in the sources. (Here, all sources contain the same month’s data, and we have only one load rule file.) You must specify the month in the header of the load rule.
A dimension field must contain a valid dimension name. If you are not performing a dimension build, the dimension must already exist in the outline. If you are performing a dimension build, the dimension name can be new, but the new name must be specified in the load rule.
Valid Member Fields
In sources of data to load to Essbase, member fields are either blank, or contain a valid member name or alias. A blank member field inherits the member name from the previous record.
Essbase must know how to map each member field of the source to a member of the outline.
In Figure 11-2, for example, Texas and Ohio are valid members of the Market dimension.
To be valid, a member field must meet the following criteria:
-
Contains or inherits a valid member name or member property. See Use the Source Data to Work with Member Properties. If you are not performing a dimension build, the member must already exist in the outline. If you are performing a dimension build, the member can be new.
-
Maps to a dimension. Either the source or the rules file must specify which dimension each member field maps to.
-
Maps either to a single member name, such as Jan (of the Year dimension) or to a member combination, such as Jan, Actual (of the Year and Scenario dimensions).
-
Is within quotation marks as needed. This is necessary for member names that contain the same character as the file delimiter.
When a rule file is not used (for example, if loading data from an Essbase export file), blank dimension and member fields are OK. When Essbase encounters a blank field in such cases, it uses the last dimension or member name encountered for that column.
Note:
As it processes each data load record, Essbase does not check to ensure that a member specified in a member field belongs to the dimension specified for the dimension field. Essbase loads the data value to the data cell identified by the member combination in the record.
In Figure 11-2, for example, if the second record reversed Jan and Sales (Texas, ‘100-10’, Sales, Jan, Actual, 42), Essbase would load 42 to the correct data cell.
Valid Data Fields
If you are performing a dimension build, skip this section. Data fields are ignored during a dimension build.
In a data field, Essbase accepts numbers and their modifiers, with no spaces or separators between them, and the text strings #MI and #MISSING, as listed in the table below.
Table 11-1 Valid Data Field Modifiers
| Valid Modifiers | Examples |
|---|---|
|
Currency symbols:
|
$12 is a valid value. $ 12 is not a valid value because there is a space between the dollar sign and the 12. |
|
Parentheses around numbers to indicate a negative number |
(12) |
|
Minus sign before numbers. Minus signs after numbers are not valid. |
-12 |
|
Decimal point |
12.3 |
|
Large numbers with or without commas |
1,345,218 and 1345218 are valid values. |
|
#MI or #MISSING to represent missing or unknown values |
#MI |
If the source contains a member field for every dimension and one field that contains data values, you must define the field that contains data values as a data field in the rule file. To read the following source into the Sample Basic cube, for example, define the last field as a data field.
Jan Cola East Sales Actual 100
Feb Cola East Sales Actual 200If the source contains blank fields for data values, replace them with #MI or #MISSING. If there is no value in the data field (or the value is #MISSING), Essbase won't replace current values in the cube with empty values.
Valid Delimiters
In sources of data to be loaded to Essbase, you must separate fields from each other with delimiters. If you are loading data without a rule file, you must use spaces to delimit fields.
If you are using a rule file, delimiters can be any of the following:
-
Tabs (default)
-
Spaces
-
New lines
-
Carriage returns
-
Commas
Extra Delimiters Without a Rule File
In sources of data that are loaded without a rule file, Essbase ignores extra delimiters. In the following example, the fields are separated by spaces. Essbase ignores the extra spaces between the fields.
East Cola Actual Jan Sales 10
East Cola Actual Feb Sales 21
East Cola Actual Mar Sales 30Extra Delimiters with a Rule File
In sources of data that are loaded with a rule file, Essbase reads extra delimiters as empty fields.
For example, if you try to use a rule file to load the file below into the Sample Basic cube, the load fails. Essbase reads the extra comma between East and Cola in the first record as an extra field. Essbase then puts Cola into Field 3. In the next record, however, Cola is in Field 2. Essbase expects Cola to be in Field 3 and stops the data load.
East,,Cola,Actual,Jan,Sales,10
East,Cola,Actual,Feb,Sales,21
East,Cola,Actual,Mar,Sales,30To resolve the problem, delete the extra delimiter from the source.
Valid Formatting Characters
In sources of data to be loaded to Essbase, some characters in the source data are ignored and considered to be formatting characters only.
When processing the source file, Essbase ignores the characters listed below:
Table 11-2 Valid Formatting Characters
| Formatting Character | Description |
|---|---|
|
|
Multiple equal signs, such as for double underlining |
|
|
Multiple minus signs, such as for single underlining |
|
|
Multiple underscores |
|
|
Multiple IBM PC graphic double underlines (ASCII character 205) |
|
|
Multiple IBM PC graphic single underlines (ASCII character 196) |
Ignored fields do not affect the data load or dimension build.
For example, Essbase ignores the equal signs in the following source file, and loads the other fields normally.
East Actual "100-10"
Sales Marketing
===== =========
Jan 10 8
Feb 21 16Supported Source Data Types
Sources of data you can use for Essbase data load and dimension build include text files (flat files), relational tables or databases such as Oracle Database, exported files from Essbase cubes, and other Essbase cubes.
Essbase supports many types of source data, including:
-
Text files (flat files) from text backups or external sources
-
Relational data sources
-
Essbase export files (export files do not need a rules file to load)
- Other sources. See Use Connections and Datasources to learn more about setting up connectivity to source data.
You can import or build cubes through Cube Designer using specially formatted, Microsoft Excel-based application workbook files.
You cannot use regular Excel files as source data for loading to a cube.