Building Dimensions Using a Rules File

This chapter describes how to build dimensions using a rules file in Essbase. For background information on dynamic dimension building, see Introducing Dynamic Dimension Building.

About Dimensions and Rules Files

Before defining dimensions, you must associate the rules file with the outline.

You can build dimensions dynamically in the following ways:

Validate the rules file and perform the dimension build. If you have problems validating the rules file or using it to build dimensions, this chapter also discusses debugging tips.

Tip: You can dynamically build dimensions outside Application Manager, using ESSCMD commands BUILDDIM or INCBUILDDIMI. For more information, see the Technical Reference in the docs directory.

Step 1: Defining Dimensions

To define a dimension:

  1. Name new dimensions and specify whether a standard dimension comes from the outline or a rules file.
  2. Set the build type and the properties for new dimensions or change the properties of existing dimensions.

Naming New Dimensions

The processes for naming new standard dimensions and new attribute dimensions are different.

To name a new dimension:

  1. Select the application and database in the Application Desktop window in Essbase Application Manager.
  2. Click the Data Load Rules button, .
  3. Click New to open Data Prep Editor with a new rules file or Open to open an existing rules file.
  4. Select View > Dimension Building fields or click the Dimension Build button, , to make sure that Data Prep Editor displays dimension building fields and not data load fields.
  5. Select Options > Dimension Build Settings to open the Dimension Build Settings dialog box. Select the Dimension Definition tab.
  6. Figure 269: Dimension Definition Page

    When Rules File is selected, the large list box displays the names of all new standard dimensions defined in the rules file. To see new attributes dimensions, you must follow the instructions in Naming New Attribute Dimensions.

    When Outline is selected, the large list box displays the names of all dimensions in the existing outline.

  7. If the list of dimensions is empty, make sure that the rules file is associated with an outline. Click the Outline button, then open the outline file.
  8. Note: If the outline is empty, the list of dimensions remains empty, even when properly associated with the outline.

Naming a New Standard Dimension

If you are naming a new attribute dimension to be associated with an existing standard dimension, proceed to Naming New Attribute Dimensions.

To name a new standard dimension:

  1. Select Rules File to indicate that the dimension is defined in the rules file.
  2. Enter the name of the new dimension, such as NewProducts. See Rules for Naming Applications and Databases.
  3. Click Add to add it to the end of the outline.
  4. If you are not also defining associated attribute dimensions, continue with setting the dimension properties. See Setting Dimension Properties.

Naming New Attribute Dimensions

To create a new attribute dimension, the base dimension must already be defined in either the outline or the rules file. The base dimension must be a sparse dimension.

  1. Select the base dimension in the list box and click Properties to open the Dimension Properties dialog box.
  2. On the Dimension Properties page, click Attribute Dimensions to display the Define Attribute Dimensions dialog box.
  3. Figure 270: Defining Attribute Dimensions

    The large list box lists the names of new attribute dimensions associated with the base dimension in the current rules file.

  4. For each new attribute dimension that you define to associate with the base dimension:
  5. To remove an attribute dimension from the list box:
  6. After adding all new attribute dimension names and specifying their types, click OK to close the dialog box and return to the Dimension Properties dialog box.
  7. Click OK to close the Dimension Properties dialog box.

Setting Dimension Properties

To set the properties of a standard dimension:

  1. If the Dimension Build Settings dialog box is not open, select Options > Dimension Build Settings to open it.
  2. Click the dimension name in the list box.
  3. Click Properties to open the Dimension Properties dialog box.
  4. Figure 271: Dimension Properties Page

  5. For an accounts dimension, click the Account Dimension Properties tab.
  6. To name the generations and levels in the current dimension, select the Generation/Level Names tab.

Step 2: Choosing the Build Method

To specify the build method for the rules file:

  1. If it is not showing, click the Dimension Build Settings tab of the Dimension Build Settings dialog box.
  2. Figure 274: Dimension Build Settings Page

  3. Select the dimension from the Dimension list. If the list is empty, click Outline to associate the dimension build rules file with an outline. The list includes new standard dimensions defined in the rules file.
  4. Select the build method from the Build Method box. If you are not sure which method to use, see Introduction to Build Methods.
  5. The following build methods require that you specify additional information:

Build Method
What to Specify

Use generation references

Whether to use null processing. See Null Processing with Generation References.

Use level references

Whether to use null processing. See Null Processing with Level References.

Add as child of

The parent to which the new members are added.



Step 3: Specifying Changes to Dimensions

To specify the changes that you want Essbase to make to dimensions in the outline:

  1. On the Dimension Build Settings page of the Dimension Build Settings dialog box, select the dimension from the Dimension list.
  2. Select the types of changes you will allow to existing members of the selected dimension in the outline from Figure 28.
  3. To sort the members of a dimension after building it, select either Ascending (A to Z) or Descending order (Z to A). To leave the members unsorted, select None.
  4. By default, Essbase merges new members found in the data source into the dimension. To remove existing members if Essbase does not encounter them in the data source, select Remove Unspecified.
  5. If the rules file works with attribute source data, select the attribute dimension and select the types of changes to allow from Figure 29.
  6. Click OK.

Table 28: Existing Member Changes Allowed

Type of Change
Option to Select

Ignore member names that already exist in the outline under different dimensions.

Ignore Conflicts (valid only with the Add as... build methods)

Allow a member and its descendants to be moved to a new parent in the same dimension. Essbase cannot move the member to itself or to another member below it in the tree.

Use Allow Moves to reorganize primary members in the outline to match the data source. To reorganize shared members, use Outline Editor.

Allow Moves

Allow changes to the properties, UDAs, and aliases of existing members.

Allow Property Changes

Allow changes to the formulas of existing members. To include quotation marks in a formula, precede the marks with a backslash. For example, \"Other Variable\" + Tax.

Allow Formula Changes

Reject records that specify a new parent for an existing member. If you do not select this box, each time a member is repeated with another parent, it is created as a shared member.

Do Not Share (valid only with the parent/child references build method)



Table 29: Attribute Dimensions Changes Allowed

Type of Change
Option to Select

Allow an existing association to be changed. For example, if the source data shows the Ounces attribute for product 100-10 as 8 and the existing outline shows the attribute as 12, Essbase associates product 100-10 with the attribute 8.

Allow Association Chgs

Prevent creation of new members of the attribute dimension. For example, if the source data shows the Ounces attribute for product 100-10 as 8 and the Ounces attribute dimension does not include the member 8, Essbase does not add the member 8 to the Ounces dimension.

Do Not Create Mbrs



Step 4a: Setting Rules File Field Types

Each field defines a source data column that becomes a member in the outline, a property of a member, or information that helps to define an association; for example, associating an alias with a member or an attribute with a base dimension member.

Setting the field type tells Essbase what kind of field to expect, such as a generation field or an alias field. At the same time, you specify the dimension for the member and its generation or level number.

To set the field types in Data Prep Editor, you must set Data Prep Editor to dimension building mode and you must open the data source.

Step 4b: Setting Rules File Field Information

This section describes how to define the field type for each field in the rules file. Many of the details for each field depend on the nature of the data source and the build method to be used. See Introducing Dynamic Dimension Building, for explanations and examples of how the fields should be defined to build or modify outlines for various situations.

To map the rules file fields to the source data, you may need to manipulate how the data is used. For example, you may need to create a member name from two source data fields, or you may need to ignore a source data field. For information on mapping and manipulating fields, see Manipulating Fields Using a Rules File.

To set field types:

  1. In Data Prep Editor window, select the field for which you want the field type set.
  2. Select Field > Properties or click the Field Properties button, , to open the Field Properties dialog box.
  3. Select the Dimension Building Properties tab.
  4. Figure 276: Dimension Building Properties Page

  5. If the Dimension list is empty, click the Outline button to associate the rules file with an outline or check the rules file to be sure that at least one dimension is defined.
  6. Select the field type from the Field Type list box. Table 30 lists valid field types for each build method from.
  7. Enter the generation or level number in the Number text box.
  8. In the Dimension box, enter the dimension for which the field provides values, or select the dimension name from the Dimension list. For attribute associations, select the base dimension from the Dimension list.
  9. To define ranges for members of the numeric attribute dimension specified in the Field Type list box, click Ranges. Otherwise, proceed to Step 14.
  10. Data Prep editor displays the Numeric Range Rules dialog box.

    Figure 277: Defining Range Size for Numeric Attribute Dimensions

    For information about using attribute dimension members to represent ranges of base member values, see Assigning Member Names to Ranges of Values and Working With Numeric Ranges.

  11. To enable automatic range building for the specified numeric attribute dimension members, click Place attribute members within a member range.
  12. In the Range Size text box, enter the numeric value of the range size for each member; for example, 3000000.
  13. In the Start Value text box, enter a numeric value for the name of one member of the numeric attribute dimension.
  14. This member becomes the pivot point upon which Essbase uses the range size to create other range members above and below the specified start value. Essbase uses the start value only when it builds an attribute dimension and associates its members to members of a base dimension in the same build operation.

    Assume, for example, that you set the range size as 10 and the start value as 5. As it processes the data source, Essbase may build the following members of the numeric attribute dimension: 5-, 5, 15, 25, and so on.

    Note: Although you enter the names of negative numeric attributes with the minus sign before the number, for example -5, Essbase creates the member name with the minus sign after the number, for example 5-.

    The start value can be a positive or negative whole number, zero, or a decimal value. To enter a negative number, type the minus sign in front of the number; for example, -15.

    Tip: If you want a numeric range member named 0, specify 0 as the start value. For example, if the range size is 3000000, Essbase builds the following members of the numeric dimension: 0, 3000000, 6000000, and so on.

  15. To remove and re-create all members of the specified attribute dimension and reassociate them with the members of the base dimension, select "Delete all members of this attribute dimension."
  16. Caution: All base member associations with the attribute dimension are lost. To enable the dimension build to re-create the associations, the source data must include all members of the base dimension that you want to be associated with members of this attribute dimension.

  17. Click OK to close the Numeric Range Rules dialog box.
  18. To move to the next field, click Next.
  19. When you are finished setting the field types, click OK.
  20. If needed, move the fields to the required locations. The required location of fields depends on the build method and what you want to achieve through the dimension build operation. For specific requirements, see the sixth step under Step 4b: Setting Rules File Field Information and Working with Multilevel Attribute Dimensions.

Table 30: Defining Field Types in Rules Files  

Field Type
What the Field Contains
Valid Build Methods

Alias

An alias

Generation, level, and parent/child references

Property

A member property

Formula

A formula

Currency name

A currency name

Currency category

A currency category

UDA

A UDA (user-defined attribute)

Attribute Parent

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

The name of a specific attribute dimension

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

Generation

Name of a member in a generation

Generation references

Duplicate generation

A member that is shared by more than one parent

Duplicate generation alias

Alias for the new parent of the shared member as the member is created

Level

Name of member in a level

Level references

Duplicate level

Name of member that has duplicate parents; that is, a member that is shared by more than one parent

Duplicate level alias

Alias for the new parent of the shared member as the member is created

Parent

Name of a parent

Parent/child reference

Child

Name of a child



Table 31: Rules Files Fields and Levels or Generations

Type of Number
Rules for Assigning

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 Allow Moves. See Step 3: Specifying Changes to Dimensions.
  • 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

Generation

  • If GEN numbers don't start at 2, the first member in 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


Step 5: Setting Global Options

Global options affect all dimensions in the rules file. Generally, you build one dimension per rules file. The global build options include:

To set global build options:

  1. Click the Global Settings tab of the Dimension Build Settings dialog box.
  2. Figure 278: Global Settings Page

  3. Select which alias table to update with new aliases from the data source. If you do not specify an alias table, Essbase updates the Default table.
  4. Select either:
  5. Select either And or Or to determine how Essbase combines select and reject criteria. For more information, see Defining Multiple Select and Reject Criteria.
  6. Click OK to save the changes.

Step 6: Validating Dimension Build Rules

To validate a rules file, make sure that the rules file is open and associated with an outline. If you're building dimensions by altering the data source (using dynamic references), open the data source.

  1. Select View > Dimension Building Fields or click the Dimension Build button, , to make sure that Data Prep Editor is in dimension building mode.
  2. Select Options > Validate or click the Validate Rules button, , to validate the rules file against the outline. When Essbase finishes the validation, the Validate Rules dialog box displays.
  3. Figure 279: Validate Rules Dialog Box

    If the rules file is correct, you can use it perform a dimension build. For more information, see Performing Dimension Builds.

  4. If the rules file is not valid, fix it before using it to build dimensions. Go to the invalid fields listed in the Validate Rules dialog. In Figure 279, for example, Field 1 is invalid.
  5. Make sure that the field name is valid.

  6. Validate the file again. Return to Step 1.

Manipulating the Data Source

You can also build dimensions or change the properties of existing members in a dimension by adding information to the data source. You can add:

Using Dynamic References

You can dynamically build dimensions by adding header information to the top of the data source and specifying the location of the header information in the rules file as a dynamic reference. Figure 280 contains an example of a header record.

Figure 280: Header Record

The header record lists field definitions for each field. The field definition includes the field type and number and the dimension name into which to load the fields. The header record must be in the following format:

Figure 281: Header Record with Three Field Definitions

If the file delimiter is a comma, enclose each field definition in quotation marks (" ").

Make sure that the data source contains the same number of columns, and in the same order, as specified in the header information. Otherwise, the dimension build may not work as expected. If some columns are missing from the data source, replace each missing column with a comma. The comma tells Essbase that the column is there, but has no values.

After you set the header record in the data source, you must use a dynamic reference to specify the location of the header record in the rules file. If a rules file contains a dynamic reference, Essbase uses the information in the header record-rather than that in the rules file itself-to determine field types and dimensions.

Valid field types must be in capital letters:

For each field type that you set, you must also enter a field number. When the field type is the name of an attribute dimension, the field number cannot be greater than 9. For more information on field numbers, see Step 4a: Setting Rules File Field Types.

Setting Member Properties

You can modify the properties of both new and existing members during a dimension build by setting the properties directly in the data source. Put properties in the field directly following the field they modify. For example, to specify that the Margin% member not roll up into its parent and not be shared, use the following data source:

Margin%  Margin%  Sales  ~ N 

Set the field type for the properties field to Property. To set the field type to property, see Manipulating the Data Source.

The following table lists all member codes used to assign properties to members in the data source.

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

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

Expense item (applies to accounts dimensions only)

F

First time balance item (applies to accounts dimensions only)

L

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

Label only (store no data)

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 Dimension Builds

When you have a valid dimension build rules file, you can create and update dimensions in the database in the following ways:

To create new dimensions, you must define them in the rules file.

Tip: You can perform a dimension build outside Application Manager, using the ESSCMD command BUILDDIM. For more information, see the Technical Reference in the docs directory.

Updating Dimensions in Outline Editor

You can start the dimension build from within Outline Editor.

To update dimensions using Outline Editor:

  1. Open the outline in Outline Editor. See Opening Outlines.
  2. Select File > Update Outline to open the Outline Update dialog box.
  3. Figure 282: Outline Update Dialog Box

  4. Select the kind of data source by choosing SQL or Data File.
  5. If you chose SQL, all of the information you need is stored in the rules file. Skip to Step 7.
  6. If you select Data File, click Find to select the data source. The Open Server Data File Object dialog box displays:
  7. Figure 283: Open Server Data File Object Dialog Box

  8. Make sure the appropriate server, application, and database are selected from their respective lists.
  9. If you select Server, the data source must reside in the database directory under \essbase\app\application_name\database_name, where application_name and database_name represent the name of your application and database. Type the name of the data source in the Object Name text box or select it from the Objects list box.

    If you select Client, the data source may reside in either the application or database directory under \ESSBASE\CLIENT or on the drives accessible from the client file system. Click File System to select a data source from a standard Open Client Data Files dialog box. Select the data source to open.

    Note: The \essbase\app and \essbase\client are the default directories specified during installation. You may have set these directories differently.

    Figure 284: Open Client Data File Dialog Box

  10. Select the dimension build rules file to load by clicking Find and then selecting the rules file in the Open Server Data File Object or Open Client Data File dialog box as described in Step 5.
  11. Click OK. Essbase adds the dimensions in the data source to the outline.

Building Dimensions Without Connecting to the Server

You can build dimensions dynamically without connecting to the server. This might be the case if, for example, you want to do a dynamic dimension build at home and could not connect to a server from there.

To build dimensions without a connection to the server:

  1. Move the outline and the data source to the client machine using standard Windows tools.
  2. Perform the dimension build using Outline Editor.
  3. Move the updated outline back to the server using standard Windows tools.

Debugging Dimension Builds

Essbase displays the results of dimension builds in the Dimension Build Completed dialog box as shown in Figure 285.

Figure 285: Dimension Build Completed Dialog Box

The Dimension Build dialog box displays the results in three different windows.

If errors occurred during dimension building, Essbase logs them in the \essbase\client\dimbuild.err file. To find data errors and correct the data source, use the strategies outline in Debugging a Data Load. When you fix the problem, see Loading Dimension Build or Data Load Error Logs to reload the failed records.

Understanding How Essbase Builds Dimensions

Sometimes, you can track down problems with dimension builds by understanding how Essbase initializes the rules file and processes the data source. Essbase performs the following steps to initialize a rules file:

  1. Validates the rules file against the associated outline.
  2. Validates the dimensions. This includes ensuring that the build method and field types are compatible and that each dimension name is unique. Member names must also be unique or shared.
  3. Adds new dimensions defined in the rules file to the outline.
  4. Reads header records specified as dynamic references.

Then Essbase performs the following operations on each record in the data source:

  1. Sets the file delimiters.
  2. Applies field operations to the data, including joins, moves, splits, and creating fields using text and joins.
  3. Performs all replace operations.
  4. Applies select and reject criteria.
  5. Adds members or member information, or both, to the outline.




© 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com