Banner Home Previous Next Index Help


Building Dimensions Using a Rules File

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

Go to top About Dimensions and Rules Files

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.

You can also use import database dimensions in MaxL, or the BUILDDIM and INCBUILDDIM commands in ESSCMD, to build dimensions dynamically. See the online Technical Reference in the DOCS directory for information.

Go to top 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.

Go to top 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 Hyperion 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.

    Figure 14-1: Dimension Definition Page

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

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.

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.

    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.

    Figure 14-2: Defining Attribute Dimensions

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

  3. For each new attribute dimension that you define to associate with the base dimension:
  4. To remove an attribute dimension from the list box:
  5. 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.
  6. Click OK to close the Dimension Properties dialog box.

Go to top 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.

    Figure 14-3: Dimension Properties Page

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

Go to top 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.

    Figure 14-6: Dimension Build Settings Page

  2. 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.
  3. Select the build method from the Build Method box. If you are not sure which method to use, see Introduction to Build Methods.

    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.

Go to top Step 3: Specifying Changes to Dimensions

To specify the changes that you want Hyperion 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.

    Select the option... When you want to...
    Ignore Conflicts (valid only with the Add as... build methods) Ignore member names that already exist in the outline under different dimensions.
    Allow Moves Allow a member and its descendants to be moved to a new parent in the same dimension. Hyperion 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 Property Changes Allow changes to the properties, UDAs, and aliases of existing members.
    Allow Formula 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.
    Do Not Share (valid only with the parent/child references build method) 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.

  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, Hyperion Essbase merges new members found in the data source into the dimension. To remove existing members if Hyperion 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:

    Select the option... When you want to...
    Allow Association Chgs 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, Hyperion Essbase associates product 100-10 with the attribute 8.
    Do Not Create Mbrs 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, Hyperion Essbase does not add the member 8 to the Ounces dimension.

  6. Click OK.

Go to top Step 4: 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 Hyperion 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.

Go to top Setting 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.

    Figure 14-8: Dimension Building Properties Page

  4. If the Dimension list is empty, click the Outline button to associate the rules file with an outline.
  5. Select the field type from the Field Type list box. Table 14-1 lists valid field types for each build method.

    Table 14-1: 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 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

  6. Enter the generation or level number in the Number text box.
  7. 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.
  8. To define ranges for members of the numeric attribute dimension specified in the Field Type list box, click Ranges. Otherwise, proceed to step 14.

    Data Prep editor displays the Numeric Range Rules dialog box.

    Figure 14-9: Defining Range Size for Numeric Attribute Dimensions

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

  9. To enable automatic range building for the specified numeric attribute dimension members, click "Place attribute members within a member range."
  10. In the Range Size text box, enter the numeric value of the range size for each member; for example, 3000000.
  11. In the Start Value text box, enter a numeric value for the name of one member of the numeric attribute dimension.

    This member becomes the pivot point upon which Hyperion Essbase uses the range size to create other range members above and below the specified start value. Hyperion 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, Hyperion 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, Hyperion 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, Hyperion Essbase builds the following members of the numeric dimension: 0, 3000000, 6000000, and so on.
  12. 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."

    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.

  13. Click OK to close the Numeric Range Rules dialog box.
  14. To move to the next field, click Next.
  15. When you are finished setting the field types, click OK.
    Note:   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 Setting Field Information and Working with Multilevel Attribute Dimensions.

Go to top 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.

    Figure 14-10: Global Settings Page

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

Go to top 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 Hyperion Essbase finishes the validation, the Validate Rules dialog box displays.

    Figure 14-11: 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.

  3. 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 14-11, for example, Field 1 is invalid.

    Make sure that the field name is valid.

  4. Validate the file again. Return to step 1.

Go to top 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:

Go to top 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 14-12 contains an example of a header record.

Figure 14-12: 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 14-13: Header Record with Three Field Definitions

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

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, Hyperion 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 and are:

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 4: Setting Rules File Field Types.

Go to top 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)

Go to top Performing Dimension Builds

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

Go to top Updating Dimensions in Outline Editor

The outline must have at least one dimension defined before you can do a dynamic dimension build. Define this dimension using Outline Editor. Then you can start building dimensions dynamically.

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.

    Figure 14-14: Outline Update Dialog Box

  3. Select the kind of data source by choosing SQL or Data File.
  4. If you chose SQL, all of the information you need is stored in the rules file. Skip to step 7.
  5. If you select Data File, click Find to select the data source.

    The Open Server Data File Object dialog box displays.

    Figure 14-15: Open Server Data File Object Dialog Box

  6. Make sure the appropriate Hyperion Essbase server, application, and database are selected from their respective lists.

    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 14-16: Open Client Data File Dialog Box

  7. 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.
  8. Click OK. Hyperion Essbase adds the dimensions in the data source to the outline.

Go to top 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 couldn't 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.

Go to top Debugging Dimension Builds

Hyperion Essbase displays the results of dimension builds in the Dimension Build Completed dialog box as shown in Figure 14-17.

Figure 14-17: Dimension Build Completed Dialog Box

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

If errors occurred during dimension building, Hyperion Essbase logs them in the \ESSBASE\CLIENT\DIMBUILD.ERR file.

To find data errors and correct the data source:
  1. Open the error log located in the file\ESSBASE\CLIENT\DIMBUILD.ERR.
    Note:   \ESSBASE is the default directory specified during installation. You may have set this directory differently.
  2. Browse through the error log file. It contains a list of each of the data sources and records that didn't load. Figure 14-18 is an example of an error log that was generated during a dimension build.

    Figure 14-18: Error Log 

    \\Record #1 - Child Member 600 with no parent (3331)
600     600-10
\\Record #1 - Child Member 600-10 with no parent (3331)
600     600-10
\\Record #2 - Child Member 600 with no parent (3331)
600     600-20
\\Record #2 - Child Member 600-20 with no parent (3331)
600     600-20
\\Record #3 - Child Member 600 with no parent (3331)
600     600-30
\\Record #3 - Child Member 600-30 with no parent (3331)
600     600-30
  1. Open the data sources or records that didn't load completely and fix them.

    The dimension build fails completely if Hyperion Essbase cannot initialize the rules file. Check the following:

  2. Reprocess the records that failed.

Go to top How Hyperion Essbase Builds Dimensions

Sometimes, you can track down problems with dimension builds by understanding how Hyperion Essbase initializes the rules file and processes the data source. Hyperion 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 Hyperion 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 and/or member information to the outline.

Home Previous Next Index Help Banner


Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.