Working with Rules Files

In This Section:

Process for Creating Data Load Rules Files

Process for Creating Dimension Build Rules Files

Combining Data Load and Dimension Build Rules Files

Creating Rules Files

Setting File Delimiters

Naming New Dimensions

Selecting a Build Method

Setting and Changing Member and Dimension Properties

Setting Field Type Information

Setting Dimension Build Operational Instructions

Defining Data Load Field Properties

Performing Operations on Records, Fields, and Data

Validating, Saving, and Printing

Also see:

Process for Creating Data Load Rules Files

To create a data load rules file:

  1. Determine whether to use the same rules file for data loads and dimension builds.

    See Combining Data Load and Dimension Build Rules Files.

  2. Create a rules file.

    See Creating Rules Files.

  3. Set the file delimiters for the data source.

    See Setting File Delimiters.

  4. Map each rules file field to the data source and define field properties.

    See Defining Data Load Field Properties.

  5. If necessary, set record, field, and data operations to change the data in the data source during loading.

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

  6. Validate and save the rules file.

    See Setting Dimension Build Operational Instructions.

Process for Creating Dimension Build Rules Files

To create a dimension build rules file:

  1. Determine whether to use the same rules file for data loads and dimension builds.

    See Combining Data Load and Dimension Build Rules Files.

  2. Create a rules file.

    See Creating Rules Files.

  3. Set the file delimiters for the data source.

    See Setting File Delimiters.

  4. If you are creating a dimension, name the dimension.

    See Naming New Dimensions.

  5. Select the build method.

    See Selecting a Build Method.

  6. If necessary, change or set the properties of members and dimensions you are building.

    See Setting and Changing Member and Dimension Properties.

  7. If necessary, set record and field operations to change the members in the data source during loading.

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

  8. Set field type information, including field type, field number, and dimension.

    See Setting Field Type Information.

  9. Validate and save the rules file.

    See Setting Dimension Build Operational Instructions.

Combining Data Load and Dimension Build Rules Files

Before building a rules file, you must decide whether the rules file will be used for data loads and dimension builds, for only data loads, or for only dimension builds. After you create a rules file, you cannot separate it into two rules files. Similarly, you cannot merge two rules files into one file.

Use the same rules file for both data load and dimension build if you plan to load the data source and build new dimensions simultaneously.

Use separate rules files for data load and dimension build under any of the following circumstances:

  • To build an outline from scratch

  • To perform different field operations during the data load and dimension build

  • To reuse the data load or dimension build rules file separately

  • To use data sources that contain no data values, only dimensions

Creating Rules Files

A rules files tells Essbase what changes to make to the data source and outline during a data load or dimension build.

Note:

In rules files, record size is limited to 64 KB.

  To create a rules file:

  1. If you are creating the rules file on the Essbase Server, connect to the server.

    Connecting to the server is not necessary if you are creating the rules file on the client.

  2. Open Data Prep Editor.

    See “Creating Rules Files” or “Opening Rules Files” in the Oracle Essbase Administration Services Online Help.

    You can open Data Prep Editor with a new or existing rules file. After you open Data Prep Editor, put the editor in the correct mode. See “About Data Prep Editor” in the Oracle Essbase Administration Services Online Help.

  3. Open the data source.

    In Data Prep Editor, you can open data sources such as text files, spreadsheet files, and SQL data sources. Data Prep Editor displays the data source, enabling you to see what needs to be changed.

    • To open text files and spreadsheet files, see “Opening Data Files” in the Oracle Essbase Administration Services Online Help.

    • To open SQL data sources, see “Opening SQL Databases” in the Oracle Essbase Administration Services Online Help.

      To open an SQL data source, use SQL Interface. The Oracle Essbase SQL Interface Guide provides information on supported environments, installation, and connection to supported data sources. Contact your Essbase administrator for more information.

      You can define a substitution variable for the data source name (DSN). When you open a SQL data source, you can select the substitution variable for the value you want to use as the DSN. For example, you can create a substitution variable named Payroll_detail and create a rules file that specifies Payroll_detail as the substitution variable for the data source name. Before performing the data load or dimension build, you must set the value for Payroll_detail to the data source name you want to use; for example, an Oracle or IBM DB2 database. When a data load or dimension build is performed, the substitution variable value that Essbase Server finds at that time is used. See Using Substitution Variables.

      Note:

      When you open an SQL data source, the rules fields default to the SQL data source column names. If the names are not the same as the Essbase dimension names, map the fields to the dimensions. See Changing Field Names.

Setting File Delimiters

A file delimiter is the character (or characters) used to separate fields in the data source. By default, a rules file expects fields to be separated by tabs. You can set the file delimiter as a comma, tab, space, fixed-width column, or custom value. Acceptable custom values are characters in the standard ASCII character set, numbered from 0 through 127. Usually, setting file delimiters is what you do first after opening a data source.

Note:

You do not need to set file delimiters for SQL data.

  To set file delimiters, see “Setting File Delimiters” in the Oracle Essbase Administration Services Online Help.

Naming New Dimensions

If you are not creating a dimension in the rules file, skip this section.

If you are creating a dimension, you must name it in the rules file. See Naming Restrictions for Dimensions, Members, and Aliases.

If you are creating an attribute dimension, the base dimension must be a sparse dimension already defined in the outline or the rules file. See Working with Attributes.

  To name a new dimension, see “Using Rules Files To Name Dimensions” in the Oracle Essbase Administration Services Online Help.

Selecting a Build Method

If you are not performing a dimension build, skip this section.

If you are building a new dimension or adding members to an existing dimension, you must specify a build method for each dimension that you are creating or modifying. For information about each build method, see Understanding Build Methods.

  To select a build method, see “Specifying Build Methods” in the Oracle Essbase Administration Services Online Help.

Setting and Changing Member and Dimension Properties

If you are not performing a dimension build, skip this section.

If you are performing a dimension build, you can set or change the properties of the members and dimensions in the outline. Some changes affect all members of the selected dimension, some affect only the selected dimension, and some affect all dimensions in the rules file.

You can set or change member and dimension properties using the Data Prep Editor or change the member properties in the data source.

Using Data Prep Editor to Set Dimension and Member Properties

If you are not performing a dimension build, skip this section.

  To set dimension properties, see “Setting Dimension Properties” in the Oracle Essbase Administration Services Online Help.

  To set member properties, see “Setting Member Properties” in the Oracle Essbase Administration Services Online Help.

Using the Data Source to Work with Member Properties

You can modify the properties of new and existing members during a dimension build by:

  • Including member properties in a field in the data source

  • Leaving the data source field empty to reset the property to the default value, or to remove the formula or UDA

In Administration Services Console, the following dimension build options control whether the value in the data source property field is applied to the associated member:

  • Allow property changes

  • Allow formula changes

  • Allow UDA changes

In the data source, put the properties in the field directly following the field containing the members that the properties modify. For example, to specify that the Margin% member not roll up into its parent and not be shared:

  1. Position the ~ property (which indicates that the member should not roll up into its parent) and the N property (which indicates that the member should not be shared) after the Margin% field. For example:

    Margin%  Margin%  ~ N Sales
  2. Set the field type for the properties fields to Property.

    See Setting Field Type Information.

Removing a formula, UDA, or attribute, or resetting a property to its default value, includes the following additional steps:

  • In Administration Services Console, select the Delete when the field is empty option for the Property field on the Dimension Build Properties tab of the Field Properties dialog box. (This option is ignored if the appropriate dimension property is not selected in the Dimension Build dialog box.)

  • Leave the field NULL or empty in the data source.

Table 40 lists all member codes used in the data source to assign properties to block storage outline members. (For a list of properties that can be assigned to aggregate storage outline members, see Rules File Differences for Aggregate Storage Dimension Builds.)

Table 40. Member Property Codes

Code

Description

%

Express as a percentage of the current total in a consolidation

*

Multiply by the current total in a consolidation

+

Add to the current total in a consolidation

-

Subtract from the current total in a consolidation

/

Divide by the current total in a consolidation

~

Exclude from the consolidation

^

Exclude from all consolidations in all dimensions

A

Treat as an average time balance item (applies to accounts dimensions only)

B

Exclude data values of zero or #MISSING in the time balance (applies to accounts dimensions only)

E

Treat as an expense item (applies to accounts dimensions only)

F

Treat as a first time balance item (applies to accounts dimensions only)

L

Treat as a last time balance item (applies to accounts dimensions only)

M

Exclude data values of #MISSING from the time balance (applies to accounts dimensions only)

N

Never allow data sharing

O

Tag as label only (store no data)

S

Set member as stored member (non-Dynamic Calc and not label only)

T

Require a two-pass calculation (applies to accounts dimensions only)

V

Create as Dynamic Calc and Store

X

Create as Dynamic Calc

Z

Exclude data values of zero from the time balance (applies to accounts dimensions only)

Setting Field Type Information

If you are not performing a dimension build, skip this section.

In a dimension build, each field in the data source is part of a column that describes an outline member. Fields can contain information about:

  • Member names

  • Member properties

  • Attribute associations

For Essbase to process this information, you must specify the following information when setting field types:

  • Field type

    The type of field to expect in that column, such as a generation field or an alias field. The field type depends on the data source and the build method (see Understanding Build Methods).

  • Dimension

    The dimension to which the members of that column belong.

  • Generation or level number

    The generation or level number of the members of that column.

  To set field type information, see “Setting Field Types” in the Oracle Essbase Administration Services Online Help.

Field Types and Valid Build Methods

Table 41 lists field types and valid build methods.

Table 41. Field Types and Valid Build Methods

Field Type[1]

What the Field Contains

Valid Build Methods

Alias

An alias

Note:

The alias value will not be assigned to the new member if Member update dimension build is set to Remove unspecified and the data source for a new member contains the alias value of a removed member.

Generation, level, and parent-child references

See Rules for Assigning Field Types.

Property

A member property.

See Table 40, Member Property Codes.

 

Formula

A formula

 

Currency name

(Block storage outlines only) A currency name

 

Currency category

(Block storage outlines only) A currency category

 

UDA

A UDA

 

Attribute parent

In an attribute dimension, the name of the parent member of the attribute member in the following field

 

The name of a specific attribute dimension

A member of the specified attribute dimension. This member is associated with a specified generation or level of the selected base dimension.

 

Generation

The name of a member in the specified generation

Generation references

Duplicate generation

The name of a member with a shared member as a child

 

Duplicate generation alias

The alias for the shared member

 

Level

The name of a member in a level

Level references

Duplicate level

The name of a member with a shared member as a child

 

Duplicate level alias

The alias for the shared member

 

Parent

The name of a parent

Parent-child reference

Child

The name of a child

 

1 Field types whose names begin with duplicate (such as duplicate generation and duplicate level alias), are not related to duplicate member names described in Creating and Working With Duplicate Member Outlines.

Rules for Assigning Field Types

Table 42 lists the rules for selecting valid field types, depending on the build method.

Table 42. Rules for Assigning Field Types Based on Build Method

Build Method

Rules for Assigning Field Types

Generation

  • If GEN numbers do not start at 2, the first member of the specified generation must exist in the outline.

  • GEN numbers must form a contiguous range. For example, if GEN 3 and GEN 5 exist, you must also define GEN 4.

  • Put DUPGEN fields immediately after GEN fields.

  • Put DUPGENALIAS fields immediately after DUPGEN fields.

  • Group GEN fields sequentially within a dimension. For example:

    GEN2,PRODUCT   GEN3,PRODUCT   GEN4,PRODUCT

  • Put attribute association fields after the base field with which they are associated, and specify the generation number of the associated base dimension member. For example:

    GEN2,PRODUCT   GEN3,PRODUCT   OUNCES3,PRODUCT

    The generation number must correspond to the generation of the member in the outline for which the field provides values. For example, the 3 in GEN3,PRODUCT shows that the values in the field are third-generation members of the Product dimension. The 2 in ALIAS2,POPULATION shows that the values in the field are associated with the second-generation member of the Population dimension.

Note:

When using the generation build method to create a duplicate member dimension, the maximum number of generations is 20.

Level

  • Put DUPLEVEL fields immediately after LEVEL fields.

  • Put DUPLEVELALIAS fields immediately after the DUPLEVEL fields.

  • Each record must contain a level 0 member. If a level 0 member is repeated on a new record with a different parent, Essbase rejects the record unless you select the Allow Moves member property. See “Setting and Modifying Member Properties” in the Oracle Essbase Administration Services Online Help.

  • Group level fields sequentially within a dimension.

  • Put the fields for each roll-up in sequential order.

  • Use a single record to describe the primary and secondary roll-ups.

  • Put attribute association fields after the base field with which they are associated, and specify the level number of the associated base dimension member. For example:

    LEVEL3,PRODUCT   OUNCES3,PRODUCT   LEVEL2,PRODUCT

  • The level number must correspond to the level of the member in the outline for which the field provides values. For example, the 3 in LEVEL3,PRODUCT shows that the values in the field are level 3 members of the Product dimension. The 2 in ALIAS2,POPULATION shows that the values in the field are associated with the second level of the Population dimension.

Parent-child

If field type is parent or child, enter 0 (zero) in the Number text box.

Attribute dimension name

The generation or level number must correspond to the generation or level of the associated base member in the outline. For example, the 3 in OUNCES3,PRODUCT shows that the values in the field are the members of the Ounces attribute dimension that are associated with the third-generation member of the Product dimension in the same source data record.

If necessary, move the fields to the required locations. See Moving Fields.

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

Setting Dimension Build Operational Instructions

If you are not performing a dimension build, skip this section.

Within the rules file, you define operations to be performed after the data source has been read:

  • Whether to sort members after Essbase has processed and added all members from the data source

  • Whether to add the members to the existing outline or to remove unspecified members from the outline

    Removing unspecified members is available only with the generation reference, level reference, and parent-child reference build methods.

Note:

Outlines are invalid if removing members results in level 0 Dynamic Calc members without formulas.

Defining Data Load Field Properties

You must map each rules file field to the corresponding outline member, or as a data field or ignored field. Other field characteristics may also apply.

For duplicate member outlines, you must specify the method (level reference, generation reference, or dimension reference) that Essbase uses to map the field.

  • Level and generation references: The data source contains multiple fields within the duplicate member dimension to uniquely identify duplicate members.

    • Use the level reference method when fields within a dimension are organized bottom-up in the data source.

    • Use the generation reference method when fields within a dimension are organized top-down in the data source. For example:

      gen2,Market, gen3,Market, Product, Year, Measures, Scenario, *data*
      State,"New York","100-10",Jan,Sales,Actual,42
      City,"New York","100-20",Jan,Sales Actual,82
      State,Texas,"100-10",Jan,Sales,Actual,37
  • Dimension reference: If an outline contains a duplicate member name in different dimensions—for example, a member name such as Other can be meaningful in different dimensions—you can use the dimension reference method. When you set a field to use the dimension reference method, you also identify the dimension to which members in that field belong. When the dimension reference method is specified for a field, Essbase checks to ensure that members in the field belong to the dimension specified for the field.

  To specify generation, level, or dimension references for data loads, see “Mapping Field Names” in the Oracle Essbase Administration Services Online Help.

Performing Operations on Records, Fields, and Data

A rules file enables you to perform operations on records, fields, and data values before loading them into the database without changing the data source. See Using a Rules File to Perform Operations on Records, Fields, and Data.

Validating, Saving, and Printing

Rules files are validated to ensure that the members and dimensions in the rules file map to the outline. Validation cannot ensure that the data source loads properly.

  To validate a rules file, see “Validating Rules Files” in the Oracle Essbase Administration Services Online Help.

If the rules file is correct, you can perform a data load or dimension build. See Performing and Debugging Data Loads or Dimension Builds.

If the rules file is not valid, see the appropriate topic for each rules file type:

  To save a rules file, see “Saving Rules Files” in the Oracle Essbase Administration Services Online Help.

Requirements for Valid Data Load Rules Files

For a data load rules file to validate, all of the following questions must be answered “yes.”

  • Is the rules file associated with the correct outline?

    See “Validating Rules Files” in the Oracle Essbase Administration Services Online Help.

  • Does each record in the data source contain only one member from each dimension?

    See Items in a Data Source.

  • Are all member and dimension names spelled correctly?

  • Are all members surrounded by quotation marks if they contain numbers or file delimiters?

    See Valid Member Fields.

  • Are there no extra delimiters in the data source?

    See Extra Delimiters with a Rules File.

  • Is the member that each data field maps to spelled correctly in the rules file?

    See Changing Field Names.

  • Are the file delimiters correctly placed?

    See Valid Delimiters.

  • Is the member in the field name a valid member?

    See Mapping Fields.

  • Is the dimension name used in only one field (for example, not in a field name and the header)?

    You can map a single data value to only one set of members.

  • Is only one field defined as a data field?

    See Defining Columns as Data Fields.

  • Is the UDA used for sign flipping in the associated outline?

    See Flipping Field Signs.

Requirements for Valid Dimension Build Rules Files`

For a dimension build rules file to validate, all of the following questions must be answered “yes.”

  • Is the rules file associated with the correct outline?

    See “Validating Rules Files” in the Oracle Essbase Administration Services Online Help.

  • Does each record contain only one member from each dimension?

    See Items in a Data Source.

  • Are all member and dimension names spelled correctly?

  • Are all members enclosed in quotation marks if they contain numbers or file delimiters?

    See Valid Member Fields.

  • Are there no extra delimiters in the data source?

    See Extra Delimiters with a Rules File.

  • Are the reference numbers sequential?

    See Rules for Assigning Field Types.

  • Are there no repeated generations?

    See Rules for Assigning Field Types.

  • Is each field type valid for the build method?

    See Field Types and Valid Build Methods.

  • Are all the fields in correct order?

    See Rules for Assigning Field Types.

  • Does each child field have a parent field?

  • Do all dimension names exist in the outline or the rules file?

  • Are any dimensions specified in both the header record in the rules file and the header record in the data source?

    Dimensions can be specified in either the header in the rules file or the header in the data source, but not in both. See Defining Header Records.

Copying Rules Files

You can copy rules files to applications and databases on any Essbase Server, according to your permissions. You can also copy rules files across servers as part of application migration.

  To copy a rules file, use a tool:

Tool

Topic

Location

Administration Services

Copying Rules Files

Oracle Essbase Administration Services Online Help

MaxL

alter object

Oracle Essbase Technical Reference

ESSCMD

COPYOBJECT

Oracle Essbase Technical Reference

Printing Rules Files

You can print the entire contents and properties of a data load or dimension build rules file. You can also specify properties and settings to print.

  To print a rules file, see “Printing Rules Files” in the Oracle Essbase Administration Services Online Help.