Using a Rules File to Perform Operations on Records, Fields, and Data

In This Section:

Performing Operations on Records

Performing Operations on Fields

Performing Operations on Data

Also see:

Performing Operations on Records

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.

Selecting Records

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 multiple fields, you can specify how Essbase combines the criteria. See Combining Multiple Select and Reject Criteria.

  To select a record, see “Selecting Records” in the Oracle Essbase Administration Services Online Help.

Rejecting Records

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.

  To reject a record, see “Rejecting Records” in the Oracle Essbase Administration Services Online Help.

Combining Multiple Select and Reject Criteria

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.

Note:

If selection and rejection criteria apply to the same record (you define select and reject criteria on the same record), the record is rejected.

  To determine how to combine select and reject criteria on multiple fields, see “Combining Selection and Rejection Criteria” in the Oracle Essbase Administration Services Online Help.

Setting the Records Displayed

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.

Note:

Essbase treats header records the same as data records when counting the records to skip.

  To set the records displayed, see “Setting How Records Are Displayed” in the Oracle Essbase Administration Services Online Help.

Defining Header Records

Data sources can contain:

  • 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 “Specifying Data-Source Members in Rules Files” 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.

Data Source Headers

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. Figure 65, Header Record with Three Field Definitions illustrates the format of a header record:

Figure 65. Header Record with Three Field Definitions

This image illustrates a header record with these field definitions: "Gen2,Product", "Gen3,Product", "Gen4,Product".

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 Data Source Header Field Types

Valid field types, which must be in capital letters:

  • GEN, DUPGEN, and DUPGENALIAS

  • LEVEL, DUPLEVEL, and DUPLEVELALIAS

  • PARENT, CHILD

  • PROPERTY

  • ALIAS

  • FORMULA

  • CURNAME

  • CURCAT

  • UDA

  • ATTRPARENT

  • The name of an attribute dimension, such as CAFFEINATED

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.

Performing Operations on Fields

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.

Ignoring Fields

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.

  To ignore all fields in a column, see “Ignoring Fields” in the Oracle Essbase Administration Services Online Help.

Ignoring Strings

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.

  To ignore all instances of a string, see “Ignoring Fields Based on String Matches” in the Oracle Essbase Administration Services Online Help.

Arranging Fields

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.

Moving Fields

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.

  To move fields, see “Moving Fields” in the Oracle Essbase Administration Services Online Help.

Joining Fields

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.

  To join fields, see “Joining Fields” in the Oracle Essbase Administration Services Online Help.

Creating a Field by Joining Fields

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.

  To create a field by joining existing fields, see “Creating Fields Using Joins” in the Oracle Essbase Administration Services Online Help.

Copying Fields

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.

  To copy a field, select one field and then create a field using a join; see “Creating Fields Using Joins” in the Oracle Essbase Administration Services Online Help.

Splitting Fields

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.

  To split a field, see “Splitting Fields” in the Oracle Essbase Administration Services Online Help.

Creating Additional Text Fields

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.

  To create a field and populate it with text, see “Using Text to Create Fields” in the Oracle Essbase Administration Services Online Help.

Undoing Field Operations

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.

  To undo one or more field operations, see “Undoing Field Operations” in the Oracle Essbase Administration Services Online Help.

Mapping Fields

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.

Note:

When you open a SQL data source, the fields default to the SQL data source column names. If the SQL column names and the Essbase dimension names are the same, you need not map the column names.

  To map fields, see “Mapping Field Names” in the Oracle Essbase Administration Services Online Help.

Changing Field Names

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:

  • Maps member fields of the data source to dimensions and members of the database

  • Maps data fields of the data source to member names or member combinations (such as Jan, Actual) of the database

See the following sections.

Replacing Text Strings

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.

  To replace a text string, see “Replacing Field Names” in the Oracle Essbase Administration Services Online Help.

Placing Text in Empty Fields

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.

  To replace an empty field with text, see “Placing Text in Empty Fields” in the Oracle Essbase Administration Services Online Help.

Changing the Case of Fields

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.

  To change the case of values in a field, see “Changing Case of Fields” in the Oracle Essbase Administration Services Online Help.

Dropping Leading and Trailing Spaces

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.

By default, Essbase drops leading and trailing spaces.

  To drop spaces around a field, see “Dropping Spaces Around Fields” in the Oracle Essbase Administration Services Online Help.

Converting Spaces to Underscores

You can convert spaces in fields of the data source to underscores to make the field values match the member names of the database.

  To change spaces to underscores, see “Converting Spaces to Underscores” in the Oracle Essbase Administration Services Online Help.

Adding Prefixes or Suffixes to Field Values

You can add prefixes and suffixes to each field value of the data source. For example, you can add 2002 as the prefix to all member names in the Year dimension.

  To add prefix or suffix values to a field, see “Adding Prefixes and Suffixes” in the Oracle Essbase Administration Services Online Help.

Performing Operations on Data

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.

Defining Columns as Data Fields

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:

Market, Product, Year, Measures, Scenario
Texas   100-10   Jan   Sales     Actual    42
Texas   100-20   Jan   Sales     Actual    82
Texas   100-10   Jan   Sales     Actual    37

You can define only one field in a record as a data field.

  To define a data field, see “Defining Columns as Data Fields” in the Oracle Essbase Administration Services Online Help.

Adding to and Subtracting from Existing Values

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.

For block storage databases, set the Commit Row database transaction option to 0 to prevent difficult recoveries. 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.

Using Administration Services Console, you can add to and substract from existing values in block storage and aggregate storage databases. Using MaxL, you can only add to and substract from existing values in aggregate storage databases only.

  To add to or substract from existing data values, use a tool:

ToolTopicLocation
Administration Services

Adding to Data Values

Subtracting from Data Values

Oracle Essbase Administration Services Online Help
MaxL

import data (aggregate storage)

Oracle Essbase Technical Reference

Clearing Existing Data Values

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.

  To clear existing values, see “Clearing Existing Data Values” in the Oracle Essbase Administration Services Online Help.

Note:

If you are using transparent partitions, clear the values using the steps that you use to clear data from a local database.

Replacing All 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.

Scaling Data Values

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

  To scale data values, see “Scaling Data Values” in the Oracle Essbase Administration Services Online Help.

Flipping Field Signs

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.

  To reverse a field sign, see “Flipping Signs” in the Oracle Essbase Administration Services Online Help.