Creating Rules Files Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Creating Rules Files


A rules files tells Analytic Services what changes to make to the data source and outline during a data load or dimension build. For a definition and discussion of rules files, see Rules Files. This chapter describes how to create a rules file for data loading or dimension building:

For a comprehensive discussion of performing a data load or dimension build after you create a rules file, see Performing and Debugging Data Loads or Dimension Builds.

Understanding the Process for Creating Data Load Rules Files

To create a data load rules file, follow these steps:

  1. Determine whether to use the same rules file for data loading and dimension building.

    For a discussion of factors that influence your decision, see Combining Data Load and Dimension Build Rules Files.

  2. Create a new rules file.

    For a process map, see Creating Rules Files.

  3. Set the file delimiters for the data source.

    For a description of file delimiters, see Setting File Delimiters.

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

    For a comprehensive discussion, see Using a Rules File to Perform Operations on Records, Fields, and Data.

  5. Validate and save the rules file.

    For references for pertinent topics, see Validating, Saving, and Printing.

For a comprehensive discussion of data sources and rules files, see Understanding Data Loading and Dimension Building.

Understanding the Process for Creating Dimension Build Rules Files

To create a dimension build rules file, follow these steps:

  1. Determine whether to use the same rules file for data loading and dimension building.

    For a discussion of factors that influence your decision, see Combining Data Load and Dimension Build Rules Files.

  2. Create a new rules file.

    For a process map, see Creating Rules Files.

  3. Set the file delimiters for the data source.

    For a description of file delimiters, see Setting File Delimiters.

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

    For references to pertinent topics, see Naming New Dimensions.

  5. Select the build method.

    For references to pertinent topics, see Selecting a Build Method.

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

    For references to pertinent topics, 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.

    For a comprehensive discussion, 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.

    For references to pertinent topics, see Setting Field Type Information.

  9. Validate and save the rules file.

    For references to pertinent topics, see Validating, Saving, and Printing.

For a comprehensive discussion of data sources and rules files, see Understanding Data Loading and Dimension Building.

Combining Data Load and Dimension Build Rules Files

Before you start building a rules file, you should determine whether to use that rules file for both data load and dimension build. Once you create a rules file, you cannot separate it into two rules files. Likewise, once you create two rules files, you cannot merge them into one rules file.

Use the same rules file for both data load and dimension build if you wish to load the data source and build new dimensions at the same time.

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

Creating Rules Files

To create a new rules file:

  1. If you are creating the rules file on the Analytic Server, connect to the server. If you are creating the rules file on the client, you do not need to connect to the Analytic Server.

  2. Open Data Prep Editor.

    For references to pertinent topics, see Opening Data Prep Editor.

  3. Open the data source.

    For a brief discussion and for references to pertinent topics, see Opening a Data Source.

Opening Data Prep Editor

You can open Data Prep Editor with a new or existing rules file. After you open Data Prep Editor, be sure to put the editor in the correct mode.

To open Data Prep Editor, see "Creating a Rules File" or "Opening an Existing Rules File" in the Essbase Administration Services Online Help.

To learn how to use Data Prep Editor, see "About Data Prep Editor" in the Essbase Administration Services Online Help.

Opening a Data Source

After you open Data Prep Editor, you can open data sources, such as text files, spreadsheet files, and SQL data sources. The data source appears in Data Prep Editor so that you can see what needs to be changed.

You can open a SQL data source only if you have licensed Essbase SQL Interface. The Essbase Analytic Services SQL Interface Guide provides information on supported environments, installation, and connection to supported data sources. Contact your Analytic Services administrator for more information. When you open a SQL data source, the rules fields default to the column names of the SQL data source. If the names are not the same as the Analytic Services dimension names, you need to map the fields to the dimensions. For a comprehensive discussion of mapping, see Changing Field Names.

To open text files and spreadsheet files, see "Opening a Data File" in the Essbase Administration Services Online Help.

To open SQL data sources, see "Opening a SQL Data Source" in the Essbase Administration Services Online Help.

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 expected to be 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 the file delimiters is the first thing you do 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 Essbase Administration Services Online Help.

Naming New Dimensions

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

If you are creating a new dimension, you must name it in the rules file. Before choosing a dimension name, see Understanding the Rules for Naming Dimensions and Members.

If you are creating an attribute dimension, the base dimension must be a sparse dimension already defined in either the outline or the rules file. For a comprehensive discussion of attribute dimensions, see Working with Attributes.

To name a new dimension, see "Creating a New Dimension Using a Rules File" in the 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 tell Analytic Services what algorithm, or build method, to use. You must specify a build method for each dimension that you are creating or modifying. For information about each build method, see Table 22.

To select a build method, see "Choosing a Build Method" in the 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 a change the member properties in the data source.

Using the Data Prep Editor to Set Dimension and Member Properties

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

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

Using the Data Source to Set Member Properties

You can modify the properties of both new and existing members during a dimension build by including member properties in a field in the data source. 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:
    Margin%  Margin%  ~ N Sales
    

  2. Set the field type for the properties fields to Property. For a brief discussion and pertinent references, see Setting Field Type Information.

The following table 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.


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

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)



Performing Operations on Records, Fields, and Data

In a rules file you can perform operations on records, fields, and data values before loading them into the database. The data source is not changed.

For a comprehensive discussion, see Using a Rules File to Perform Operations on Records, Fields, and Data.

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 a member in the outline. Fields can contain information about member names, member properties, or attribute associations. In order for Analytic Services to process this information, you must specify the field type in the rules file. You must specify the following information when setting field types:

The following sections contain detailed information about field types:

To set field information, see "Setting Field Types" in the Essbase Administration Services Online Help.

List of Field Types

Table 20 lists valid field types for each build method.


Table 20: Field Types  

Field Type
What the Field Contains
Valid Build Methods

Alias

An alias

Note: If the Member update dimension build setting is set to Remove unspecified and the data source for a new member contains the alias value of a removed member, the alias value will not be assigned to the new member.

Generation, level, and parent-child references

Property

A member property. For a list of properties to set in the data source, see Using the Data Source to Set Member Properties.

Formula

A formula

Currency name

A currency name (block storage outlines only)

Currency category

A currency category ((block storage outlines only)

UDA

A UDA (user-defined attribute)

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 will be 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 that has duplicate parents; that is, a member that is shared by more than one parent

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 that has duplicate parents; that is, a member that is shared by more than one parent

Duplicate level alias

The alias for the shared member

Parent

The name of a parent

Parent-child reference

Child

The name of a child



Rules for Field Types

The field type that you choose for a field depends on the build method that you selected. Table 21 lists the rules for selecting valid field types, depending on the build method. If necessary, move the fields to the required locations. For a brief discussion, see Moving Fields.

To move fields, see "Moving Fields" in the Essbase Administration Services Online Help.


Table 21: Field Numbers

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.

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, Analytic Services rejects the record unless you select the Allow Moves member property. To set member properties, see "Setting Member Properties" in the 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   UNCES3,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.



Validating, Saving, and Printing

Rules files are validated to make sure 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 a Rules File" in the Essbase Administration Services Online Help.

To save a rules file, see "Saving a Rules File" in the Essbase Administration Services Online Help.

If the rules file is not valid, complete one of the following actions:

If the rules file is correct, you can perform a data load or dimension build. For a comprehensive discussion of how to load data and members, see Performing and Debugging Data Loads or Dimension Builds.

Requirements for Valid Data Load Rules Files

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

Requirements for Valid Dimension Build Rules Files

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

Copying Rules Files

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

To copy a rules file, use any of the following methods:


Tool
Topic
Location

Administration Services

Copying a Rules File

Essbase Administration Services Online Help

MaxL

alter object

Technical Reference

ESSCMD

COPYOBJECT

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 Essbase Administration Services Online Help.



Hyperion Solutions Corporation link