Also see:
You can perform operations at the record level. For example, you can reject certain records before they are loaded into the database. See the following sections.
You can specify which records Essbase loads into the database or uses to build dimensions by setting selection criteria. Selection criteria are string and number conditions that must be met by one or more fields within a record for Essbase to load the record. If a field or fields in the record do not meet the selection criteria, Essbase does not load the record. You can define one or more selection criteria. For example, to load only 2003 Budget data from a data source, create a selection criterion to load only records in which the first field is Budget and the second field is 2003. If you define selection criteria on more than one field, you can specify how Essbase combines the criteria. See Combining Multiple Select and Reject Criteria.
You can specify which records Essbase ignores by setting rejection criteria. Rejection criteria are string and number conditions that, when met by one or more fields within a record, cause Essbase to reject the record. You can define one or more rejection criteria. If no field in the record meets the rejection criteria, Essbase loads the record. For example, to reject Actual data from a data source and load only Budget data, create a rejection criterion to reject records in which the first field is Actual.
When you define select and reject criteria on multiple fields, you can specify how Essbase combines the rules across fields: whether the criteria are connected logically with AND or with OR. If you select AND from the Boolean group, the fields must match all of the criteria. If you select OR, the fields must match only one of the criteria. The global Boolean setting applies to all select or reject operations in the rules file, for data load and dimension build fields.
If selection and rejection criteria apply to the same record (you define select and reject criteria on the same record), the record is rejected.
You can specify the number of records, and the first record, that Essbase displays in Data Prep Editor. When you specify the first record, Essbase skips all preceding records. For example, if you enter 5 as the starting record, Essbase does not display records 1 through 4.
Data records, which contain member fields and data fields
Header records, which describe the contents of the data source and how to load values from the data source to the database
Rules files contain records that translate the data of the data source to map it to the database. As part of that information, rules files can also contain header records. For example, the Sample.Basic database has a dimension for Year. If several data sources arrive with monthly numbers from different regions, the month itself might not be specified in the data sources. You must set header information to specify the month.
You can create a header record using one of the following methods:
Define header information in the rules file.
Rules file headers are used only during data loading or dimension building and do not change the data source. Header information set in a rules file is not used if the rules file also points to header records in the data source.
Define header information in the data source and, in the rules file, point to the header records.
Placing header information in the data source makes it possible to use the same rules file for multiple data sources with different formats, because the data source format is specified in the data source header (not in the rules file).
When you add one or more headers to the data source, you must also specify in the rules files the location of the headers in the data source. The rules file tells Essbase to read the header information as a header record (not as a data record). You can also specify the type of header information in each header record.
Header information defined in the data source takes precedence over header information defined in the rules file.
To define a header in the rules file, see “Setting Headers in the Rules File” in the Oracle Essbase Administration Services Online Help.
To define a header in the data source, see “Setting Headers in the Data Source” in the Oracle Essbase Administration Services Online Help.
You can build dimensions dynamically by adding header information to the top record of the data source and by specifying the location of the header record in the rules file.
The header record lists field definitions for each field. The field definition includes the field type, the field number, and the dimension name into which to load the fields. The format of a header record is illustrated below:
If the file delimiter is a comma, enclose each field definition in quotation marks (" ").
After you set the header information in the data source, you must specify the location of the header information in the rules file. If a rules file refers to header information in a data source, Essbase uses the information in the data source—rather than the information in the rules file—to determine field types and dimensions.
Valid field types, which must be in capital letters:
Each field type that you set requires a field number. When the field type is the name of an attribute dimension, the field number cannot be greater than 9. See Setting Field Type Information.
You can perform operations at the field level. For example, you can move a field to a new position in the record. See the following sections.
You can ignore all fields of a specified column of the data source. The fields still exist in the data source, but they are not loaded into the Essbase database. For example, the Sample.Basic database has five standard dimensions: Year, Product, Market, Measures, and Scenario. If the data source has an extra field that is not a member of any dimension, such as Salesperson, you can tell Essbase to ignore the Salesperson field.
You can ignore any field in the data source that matches a string, called a token. When you ignore fields based on string values, the fields are ignored everywhere they appear in the data source, not just in a particular column. For example, in a data source that is a computer-generated report in text format, special ASCII characters might be used to create horizontal lines between pages or boxes around headings. These special characters can be defined as tokens to be ignored.
You can set the order of the fields in the rules file to be different from the order of the fields in the data source. The data source is unchanged. See the following sections.
You can move fields to a different location using a rules file. For example, you can specify the first field in the data source to be the third field during the data load or dimension build.
In some instances, moved fields may appear to merge. If you move a field that contains empty cells, and the moved field becomes the last field in the record, as shown below, the field may merge with the field to its left.
1<tab>2<tab>3 1<tab>2<tab>(null)
To prevent merging, replace the empty cell with a delimiter.
You can join multiple fields into one field. The new field is given the name of the first field in the join. For example, if a data source has separate fields for product number (100) and product family (-10), you must join the fields (100-10) before loading them into the Sample.Basic database.
Before you join fields, move the fields to join into the order in which you want them joined. See “Moving Fields” in the Oracle Essbase Administration Services Online Help.
You can join fields by placing the joined fields into a new field. This procedure leaves the original fields intact. Creating a field is useful if you need to concatenate fields of the data source to create a member.
For example, if a data source has separate fields for product number (100) and product family (-10), you must join the fields (100-10) before you load them into the Sample.Basic database. If, however, you want to preserve the two existing fields in the data source, you can create a field (100-10) using a join. The data source now includes all three fields (100, -10, and 100-10).
Before you join fields, move the fields to join into the order in which you want them joined. See “Moving Fields” in the Oracle Essbase Administration Services Online Help.
You can create a copy of a field while leaving the original field intact. For example, if, during a single dimension build, you want to define a multilevel attribute dimension and associate attributes with members of a base dimension, you must copy some of the fields. See Working with Multilevel Attribute Dimensions.
You can split a field into two fields. For example, if a data source for the Sample.Basic database has a field containing UPC100-10-1, you can split “UPC” out of the field and ignore it. Then, only 100-10-1, the product number, is loaded.
You can create a text field between two fields. You might create a text field to insert text between fields that are to be joined. For example, if one field contains 100 and one contains 10-1, you can insert a text field with a dash between the two fields and then join the three fields to create the 100-10-1 member of the Product dimension.
You can undo the last field operation that you performed, such as move, split, join, create using text, or create using join, by using Undo command (select Edit, then Undo). You can undo field operations even if you have performed other actions. Undoing field operations is sequential, starting with the most recently performed operation.
This section applies to data load only. If you are performing a dimension build, skip this section.
You use a rules file to map data source fields to Essbase member names during a data load. You can map fields in a data source directly to fields in the Essbase database during a data load by specifying which field in the data source maps to which member or member combination in the Essbase database. The data source is not changed.
To load a data source, you must specify how the fields of the data source map to the dimensions and members of the database. Rules files can translate fields of the data source so that the fields match member names each time the data source is loaded. This process does not change the data source.
The rules file:
You can replace text strings so that the fields map to Essbase member names during a data load or dimension build. The data source is not changed. For example, if the data source abbreviates New York to NY, you can have the rules file replace each NY with New York.
You may want to replace empty fields in a column with text. For example, if empty fields in the column represent default values, you can insert the default values or insert #MI to represent missing values.
You can change the case of a field so that the field maps to Essbase member names during a data load or dimension build. The data source is not changed. For example, if the data source capitalizes a field (for example, JAN) that is in lowercase in the database (jan), you can have the rules file change the field to lowercase.
You can drop leading and trailing spaces from around fields of the data source. A field value containing leading or trailing spaces does not map to a member name, even if the name within the spaces is an exact match.
You can convert spaces in fields of the data source to underscores to make the field values match the member names of the database.
This section applies to data load only. If you are performing a dimension build, skip this section.
You can perform operations on the data in a field; for example, moving a field to a new position in the record. See the following sections.
This section applies to data load only. If you are performing a dimension build, skip this section.
If each record in the data source contains a column for every dimension and one data column, you must define the data column as a data field, as shown in the following example:
This section is for data load only. If you are performing a dimension build, skip this section.
By default, Essbase overwrites the existing values of the database with the values of the data source, but you can determine how newly loaded data values affect existing data values.
You can use incoming data values to add to or subtract from existing database values. For example, if you load weekly values, you can add them to create monthly values in the database.
Using this option makes recovery more difficult if the database crashes while loading data, although Essbase lists the number of the last row committed in the application log. See Contents of the Application Log.
To prevent difficult recoveries, set the Commit Row database transaction option to 0. This setting causes Essbase to view the entire load as a single transaction and to commit the data only when the load is complete. See Understanding Isolation Levels.
This section is for data load only. If you are performing a dimension build, skip this section.
You can clear existing data values from the database before you load new values. By default, Essbase overwrites the existing values of the database with the new values of the data source. If you are adding and subtracting data values, however, Essbase adds or subtracts the new data values to and from the existing values.
Before adding or subtracting new values, make sure that the existing values are correct. Before loading the first set of values into the database, make sure that there is no existing value.
For example, assume that the Sales figures for January are calculated by adding the values for each week in January:
January Sales = Week 1 Sales + Week 2 Sales + Week 3 Sales + Week 4 Sales
When you load Week 1 Sales, clear the database value for January Monthly Sales. If there is an existing value, Essbase performs the following calculation:
January Sales = Existing Value + Week 1 Sales + Week 2 Sales + Week 3 Sales + Week 4 Sales
You can also clear data from fields that are not part of the data load. For example, if a data source contains data for January, February, and March, and you want to load only the March data, you can clear January and February data.
This section applies to loading data into an aggregate storage database only. If you are loading data into a block storage database or performing a dimension build, skip this section.
In an aggregate storage database, Essbase can remove all of the data in the database or all of the data in each incremental data slice in a database, and replace the data with the contents of a specified data load buffer. This functionality is useful when working with data sets that are small enough to reload completely, or when working with data that can be separated into large, static data sets that are never updated and small, volatile data sets in which you need to track changes.
To replace all data, see Replacing Database or Incremental Data Slice Contents and “Replacing the Contents of an Aggregate Storage Database” in the Oracle Essbase Administration Services Online Help.
This section is for data load only. If you are performing a dimension build, skip this section.
You can scale data values if the values of the data source are not in the same scale as the values of the database.
For example, assume the real value of sales is $5,460. If the Sales data source tracks the values in hundreds, the value is 54.6. If the Essbase database tracks the real value, you must multiply the value coming in from the Sales data source (54.6) by 100 to have the value display correctly in the Essbase database (as 5460).
This section is for data load only. If you are performing a dimension build, skip this section.
You can reverse, or flip, the value of a data field by flipping its sign. Sign flips are based on the UDAs of the outline. When loading data into the accounts dimension, for example, you can specify that any record whose accounts member has a UDA of Expense change from a plus sign to a minus sign. See Creating UDAs.