Essbase - Dimension build rules files

Essbase Overview

About

When you create dimension with thousands of member or with many alternate hierarchy, you want to automate the process to add the member.

Essbase Dimension Build Rules Files

You use Dimension build rules files to map dimension and member from a data source to an Essbase outline.

During dimension building, the Rules files tells Essbase how to build the hierarchy and how to transform member before loading them.

When you execute a dimension build, Essbase :

Example

Use rules to address issues of hierarchy management.

  • Loading complex hierarchy
  • Loading thousands of member at once
  • Sorting, adding, and deleting member
  • Automating hierarchy maintenance with batch process
  • Duplicating, parsing and concatenating data_source fields to construct hierarchy
  • Adding prefixes or suffixes to names to provide clarity and to comply with unique member name requirements
  • Creating alias

Process for Creating Dimension Build Rules Files

To create a dimension build rules file:

  • Determine whether to use the same rules file for data loads and dimension builds. See : Essbase - Rules file used for data loads and/or dimension builds ?
  • Create a rules files.
  • Set the file delimiter for the data source.
  • If you are creating a dimension, you must name it in the rules file.
  • If you are creating an attribute dimension, the base dimension must be a sparse dimension already defined in the outline or the rules file.
  • Select the build method.
  • If necessary, change or set the properties of members and dimensions you are building.
  • If necessary, set record and field operations to change the members in the data source during loading.
  • Set field type information, including field type, field number, and dimension.
  • Validate and save the rules file.

Using the Data Source to Work with Member Properties

You can modify the properties of new and existing Essbase - Members during a Essbase - (Dimension|Outline) 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 Essbase - 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 Essbase - 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


Removing a Essbase - Formulas, Essbase - UDA, or Essbase Attributes, or resetting a property to its default value, includes the following additional steps:

  • In Essbase - 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.


The table below 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.)

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 Essbase - (Consolidations|Aggregations) 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 Essbase - #MI or #MISSING from the time balance (applies to accounts dimensions only)
N Never allow data sharing
O Tag as Essbase - Label only (store no data)
S Set member as stored member (non-Dynamic Calc and not label only)
T Require a Essbase - Two-Pass Calculations (applies to accounts dimensions only)
V Create as Essbase - Dynamic Calculations and Store
X Create as Essbase - Dynamic Calculations
Z Exclude data values of zero from the time balance (applies to accounts dimensions only)

Setting Field Type Information

In a Essbase - (Dimension|Outline) Build, each field in the Essbase - Data Source is part of a column that describes an outline Essbase - Members. Essbase - 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 Essbase - Aliases field. The field type depends on the Essbase - Data Source and the Essbase - Build Method.
  • 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.

Field Types and Valid Build Methods

Generation, level, and parent-child references

See rules_for_assigning_field_types.

Field Type What the Field Contains
Essbase - Aliases 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.
Property A member property.
See member_property_codes.
Essbase - Formulas A formula
Currency name (Essbase - Block storage outlines only) A currency name
Currency category (Essbase - Block storage outlines only) A currency category
Essbase - UDA A UDA
Attribute parent In an Essbase - Attribute dimensions, 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.
Essbase - Generation The name of a member in the specified generation

Generation references

Field Type What the Field Contains
Duplicate generation The name of a member with a Essbase - Shared Members as a child
Duplicate generation alias The alias for the Essbase - Shared Members
Essbase - Level The name of a member in a level

Duplicate properties are not related to duplicate member names.

Level references

Field Type What the Field Contains
Duplicate level The name of a member with a Essbase - Shared Members as a child
Duplicate level alias The alias for the shared member

Duplicate properties are not related to duplicate member names.

Parent-child reference

Field Type What the Field Contains
parent The name of a parent
child The name of a child

Rules for Assigning Field Types

The table below lists the rules for selecting valid field types, depending on the build method.

Rules for Assigning Field Types Based on Build Method

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, Essbase rejects the record unless you select the Allow Moves member property.
  • 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.

Setting Dimension Build Operational Instructions

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

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

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?
  • Does each record contain only one member from each dimension?
  • Are all member and dimension names spelled correctly?
  • Are all members enclosed in quotation marks if they contain numbers or file delimiters?
  • Are there no extra delimiters in the data source?
  • Are the reference numbers sequential?
  • Are there no repeated generations?
  • Is each field type valid for the build method?
  • Are all the fields in correct order?
  • 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.





Discover More
Essbase Overview
Essbase - (Dimension|Outline) Build

Building dimensions (known also as building outline) is the process of loading dimension and members data into an Essbase database outline by using a data source and a dimension build rules file. When...
Essbase Rules File
Essbase - Data Prep Editor

Data prep editor permit to edit . When it open, the menu in the change. The upper screen frame shows the file in its original form. The lower screen frame shows how Essbase will interpret the data...
Rules File
Essbase - Rules File

A rules files tells Essbase what changes to make to : the data source to load and the outline to build during : a data load or a dimension build. You have then two type of rules files : ...



Share this page:
Follow us:
Task Runner