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:
- Create a rules file using the Dimension Build Settings dialog box in Data Prep Editor. This process includes defining new dimensions, specifying changes to existing dimensions, setting global options, and validating the dimension build rules.
- Create a dynamic reference in a rules file to a record in the data source that defines each field. This method enables you to use a single rules file with several different source files. To use this method, you must manipulate the date source to include this header record.
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:
- Name new dimensions and specify whether a standard dimension comes from the outline or a rules file.
- 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:
- Select the application and database in the Application Desktop window in Essbase Application Manager.
- Click the Data Load Rules button,
.
- Click New to open Data Prep Editor with a new rules file or Open to open an existing rules file.
- 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.
- Select Options > Dimension Build Settings to open the Dimension Build Settings dialog box. Select the Dimension Definition tab.
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.
- 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.
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:
- Select Rules File to indicate that the dimension is defined in the rules file.
- Enter the name of the new dimension, such as NewProducts. See Rules for Naming Applications and Databases.
- 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.
- Select the base dimension in the list box and click Properties to open the Dimension Properties dialog box.
- If the base dimension is defined in the outline, on the Dimension Definition page of the Dimension Build Settings dialog box, select Outline to display the base dimension name in the list box.
- If the base dimension is defined in the rules file, on the Dimension Definition page of the Dimension Build Settings dialog box, select Rules to display the base dimension name in the list box
- On the Dimension Properties page, click Attribute Dimensions to display the Define Attribute Dimensions dialog box.
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.
- For each new attribute dimension that you define to associate with the base dimension:
- Type the attribute dimension name; for example, Population.
- Select the attribute dimension type; for example, Numeric.
- Click Add.
- To remove an attribute dimension from the list box:
- Select the attribute dimension name.
- Click Remove.
- 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.
- Click OK to close the Dimension Properties dialog box.
Setting Dimension Properties
To set the properties of a standard dimension:
- If the Dimension Build Settings dialog box is not open, select Options > Dimension Build Settings to open it.
- Select the Dimension Definition page.
- If the dimension exists in the outline, click the Outline option to display the names of the dimensions in the outline. If the list box is empty, click the Outline button to associate the dimension build rules file with an outline and display the dimensions in the list box.
- If the dimension is new, click Rules File. The list box displays the new dimensions that you named.
- Click the dimension name in the list box.
- Click Properties to open the Dimension Properties dialog box.
Figure 271: Dimension Properties Page
- Set the Dimension Type.
- Select the Data Storage property.
- Select a Dense or Sparse configuration. Base dimensions must be set as sparse.
- If you are not sure what settings to use, click Help.
- For an accounts dimension, click the Account Dimension Properties tab.
- To name the generations and levels in the current dimension, select the Generation/Level Names tab.
- Set the generation/level names and click OK.
- If you are not sure which settings to use, click Help.
Figure 273: Generation/Level Names Page
Note: If you define a name for a generation or level that already exists in the outline, the new name overwrites the existing name.
Step 2: Choosing the Build Method
To specify the build method for the rules file:
- If it is not showing, click the Dimension Build Settings tab of the Dimension Build Settings dialog box.
Figure 274: Dimension Build Settings Page
- 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.
- 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:
Step 3: Specifying Changes to Dimensions
To specify the changes that you want Essbase to make to dimensions in the outline:
- On the Dimension Build Settings page of the Dimension Build Settings dialog box, select the dimension from the Dimension list.
- Select the types of changes you will allow to existing members of the selected dimension in the outline from Figure 28.
- 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.
- 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.
- If the rules file works with attribute source data, select the attribute dimension and select the types of changes to allow from Figure 29.
- 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.
- Select View > Dimension Building Fields or click the Dimension Build button,
, to ensure that Data Prep Editor is in dimension building mode.
- Select File > Open Data File or File > Open SQL, whichever is appropriate, to specify the source location and open the data source. For more details, see Opening a Data Source.
As shown in Figure 275, Data Prep Editor displays the data source in the upper half of the window and rules fields in the lower half of the window.
Figure 275: Data Prep Editor
If desired, you can customize Data Prep Editor. For example, you can hide the raw data or maximize the window, to set a better view of the rules fields. See Customizing the Data Prep Editor.
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:
- In Data Prep Editor window, select the field for which you want the field type set.
- Select Field > Properties or click the Field Properties button,
, to open the Field Properties dialog box.
- Select the Dimension Building Properties tab.
Figure 276: Dimension Building Properties Page
- 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.
- Select the field type from the Field Type list box. Table 30 lists valid field types for each build method from.
- Enter the generation or level number in the Number text box.
- If Field Type is the name of an attribute dimension, 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 data values in the field are members of the Ounces attribute dimension associated with the third generation member of the Product dimension in the same source data record.
- If Field Type is parent or child, enter 0 (zero) in the Number text box.
- If Field Type is not the name of an attribute dimension nor parent nor child, the generation or level number must correspond to the generation or level of the member in the outline for which the field provides values. For example, the 3 in GEN3,PRODUCT shows that the data values in the field are third generation members of the Product dimension. The 2 in ALIAS2,POPULATION shows that the data values in the field are associated with the second generation or level member of the Population dimension.
As described in Table 31, how you assign and sequence fields in the rules file can vary depending if the number is for a level or generation build.
- 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.
- 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 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.
- To enable automatic range building for the specified numeric attribute dimension members, click Place attribute members within a member range.
- In the Range Size text box, enter the numeric value of the range size for each member; for example, 3000000.
- 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 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.
- 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.
- Click OK to close the Numeric Range Rules dialog box.
- To move to the next field, click Next.
- When you are finished setting the field types, click OK.
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:
- Whether to configure dimensions as dense or sparse automatically or to use the dense/sparse configuration defined in the outline or rules file
- Which alias table to update
- How to combine field select or reject criteria between fields
To set global build options:
- Click the Global Settings tab of the Dimension Build Settings dialog box.
Figure 278: Global Settings Page
- 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.
- Select either:
- The Use Dimension Property Settings to keep using the current data configuration or use the one specified in the rules file.
- The Autoconfigure Dense/Sparse to let Essbase determine the data configuration automatically. For more information on dense and sparse dimensions, see Sparse and Dense Dimensions.
When you change the configuration settings, Essbase restructures the database.
- Select either And or Or to determine how Essbase combines select and reject criteria. For more information, see Defining Multiple Select and Reject Criteria.
- 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.
- Select View > Dimension Building Fields or click the Dimension Build button,
, to make sure that Data Prep Editor is in dimension building mode.
- 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.
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.
- 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.
Make sure that the field name is valid.
- Are the reference numbers sequential?
- Are there repeated generations?
- Is the field type valid for the build method?
- Are the fields in correct order?
- Does the child field have a parent field?
- Do all dimension names exist in the outline or the rules file?
- 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:
- Header information to specify the dimension and field types
- Member codes that set member properties
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:
- GEN, DUPGEN, and DUPGENALIAS
- LEVEL, DUPLEVEL, and DUPLEVELALIAS
- PARENT, CHILD
- PROPERTY
- ALIAS
- FORMULA
- CURNAME
- CURCAT
- UDA
- ATTRPARENT
- The name of an attribute dimension.
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:
- Open the outline in Outline Editor. See Opening Outlines.
- Select File > Update Outline to open the Outline Update dialog box.
Figure 282: Outline Update Dialog Box
- Select the kind of data source by choosing SQL or Data File.
- If you chose SQL, all of the information you need is stored in the rules file. Skip to Step 7.
- If you select Data File, click Find to select the data source. The Open Server Data File Object dialog box displays:
Figure 283: Open Server Data File Object Dialog Box
- Make sure the appropriate 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 284: Open Client Data File Dialog Box
- 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.
- 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:
- Move the outline and the data source to the client machine using standard Windows tools.
- Perform the dimension build using Outline Editor.
- 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.
- The top window lists files that loaded completely.
- The middle window lists files that may have partially loaded.
- The bottom window list files that did not load at all.
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:
- Validates the rules file against the associated outline.
- 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.
- Adds new dimensions defined in the rules file to the outline.
- Reads header records specified as dynamic references.
Then Essbase performs the following operations on each record in the data source:
- Sets the file delimiters.
- Applies field operations to the data, including joins, moves, splits, and creating fields using text and joins.
- Performs all replace operations.
- Applies select and reject criteria.
- Adds members or member information, or both, to the outline.