Manipulating Fields Using a Rules File

This chapter describes how to manipulate fields during a data load or dimension build using a rules file using Hyperion Essbase Application Manager. Before you can manipulate fields, you must open the data source and set the file delimiters. After you set up the rules file, you must save and validate it. For more information on these topics, see Setting up a Rules File to Manipulate Records.

For more information about loading data using rules files, including prerequisites, see Performing a Data Load

. This chapter contains the following sections:

Selecting Multiple Fields

You can select multiple fields and then set the properties for them. Hyperion Essbase grays out any menu items and controls you cannot use when more than one field is selected.

To select continuous fields:

Click the first field.
Shift-click the last field.

To select discontinuous fields, use one of the following methods:

Select the fields in the first region, hold down Ctrl and drag the mouse along the fields in the second region.
Click the first field, then hold down Ctrl and click the other fields.

Ignoring Fields

You can ignore all the fields in a column in your data source that do not map to the database. The fields still exist in the data source, but they are not loaded into the Hyperion Essbase database. For example, you could have a column containing comment fields and you could choose to ignore the fields in that column for each record in the data source.

You can also ignore individual fields in your data source that match a string called a token. When you ignore fields based on string values, these fields are ignored everywhere they appear in the data source, not just in a single column.

Ignoring All Fields in a Column

You can ignore an entire column, that is, ignore the field in a specified column for each record.

To ignore all fields in a column:

Select the columns to ignore.
Choose Field > Properties or click the Field Properties button, , to open the Field Properties dialog box. Click the Global Properties tab.
Figure 22-1: Global Properties Page: Ignore Check Boxes
Check "Ignore field during data load" to ignore the field during a data load. Check "Ignore field during dimension build" to ignore the field during a dimension build. To ignore the field for both, check both boxes.
Click OK. The values in that field now appear grayed out in the Data Prep Editor to indicate that the field is ignored.
To hide ignored fields, choose View > Ignored Fields.

Ignoring Fields Based on String Matching

You can also ignore fields in your data source that match a string called a token. When you ignore fields based on string values, these fields are ignored everywhere they appear in the data source, not just in a single column.

To ignore all fields in a data source that match a certain string:

Select Options > Data File Properties to bring up the Data File Properties dialog box.
Click the Ignore Tokens tab. Token is another word for string.
Figure 22-2: Ignore Tokens Page
Enter the token to ignore in the Token entry field.
Click Add to add the token to the list.
Repeat steps 2 and 3 for each token to ignore.
To change or delete the tokens to ignore, select the token in the list and click Change or Delete.
Click OK.

Ordering Fields

You can change the order of fields in a data source by specifying their new position in the rules file. The data source is unchanged. The following sections describe:

Note:

Whenever you want to undo a single operation, choose Edit > Undo. To undo multiple field operations, see Undoing Field Ordering.

Moving Fields

To move one or more fields:

Select the field to move. If you do not select a field, the currently active field is selected by default.
Choose Field > Move or click the Move button, , to open the Move Field dialog box.
Figure 22-3: Move Field Dialog Box
Click Up or Down to move the field to the proper position.
If you need to move another field, select the field and repeat step 3.
Click OK to close the dialog box.

Note:

To undo a move, see Undoing Field Ordering.

Joining Fields

You can join multiple fields into one field. For example, if you receive a data source with separate fields for the product number (100) and product family (-10), you must join those fields (100-10) to load them into the Sample Basic database.

Move the fields to join into the order in which you want to join them. If you do not know how to move fields, see Moving Fields.
Select the fields to join, for example 100 and -10. If you do not know how to select multiple fields, see Selecting Multiple Fields.
Choose Field > Join or click the Join button, , to open the Join Fields dialog box.
Figure 22-4: Join Fields Dialog Box
If you did not select the fields to join before opening the Join Fields dialog box, select the fields in the Fields to Join list box. The order in which fields are joined is determined by the order in which they appear in the list box.
Click OK.
The selected fields merge into one. The new field is named after the first field in the join. The original fields are part of the joined field.

Note:

To undo a join, see Undoing Field Ordering.

Creating a New Field While Leaving Existing Fields Intact

You can create a copy of a field or you can join two or more fields by putting the joined fields into a brand new field. This leaves the existing fields intact.

Creating a New Field By Joining Fields

You may need to concatenate fields from your source data to create the member you want to define in your rules file.

For example, if you receive a data source with separate fields for the product number (100) and product family (-10), you must join those fields (100-10) to load them into the Sample Basic database. But suppose that you want to leave the 100 and -10 fields in the data source after the join, that is, the data source would contain three fields: 100, -10, and 100-10. To do this, create the new field using a join.

Select the fields to join, for example, 100 and -10. If you do not know how to select multiple fields, see Selecting Multiple Fields.
Choose Field > Create Using Join or click the Create Using Join button, , to open the Create Field Using Join dialog box.
Figure 22-5: Create Field Using Join Dialog Box
If you did not select the fields to join, select the fields in the Create Field Using Join dialog box.
Click OK.
The new field displays to the left of the first field containing joined information. For example, in Figure 22-6, a new 100-10 field displays to the left of the existing 100 and -10 fields.

Figure 22-6: New 100-10 Field

Copying Fields

You may need to create a new field in the rules file that is a copy of an existing one; for example, when you define a multilevel attribute dimension and associate attributes to members of a base dimension during the same dimension build.

Select the field to copy, for example, 12.
Choose Field > Create Using Join or click the Create Using Join button, , to open the Create Field Using Join dialog box.
Make sure the only field selected in the Create Field Using Join dialog box is the field you want to duplicate.
Click OK.
The new field displays to the left of the field you selected to copy. You may need to move the field to another location. See Moving Fields.

Splitting Fields

You can split a field into two fields. For example, if a data source for the Sample Basic database has a field containing UPC100-10-1, you could split the UPC out of the field and ignore it. To ignore a field, see Ignoring All Fields in a Column. Then 100-10-1, that is, the product number, is loaded.

To split a field:

Select the field to split in the Data Prep Editor.
Choose Field > Split or click the Split button, , to open the Split Field dialog box.
Figure 22-7: Split Field Dialog Box
Enter the number of characters to split out.
For example, to split the UPC out of the UPC100-10-1 field, split away the first three digits. Set the character position to 3.
The field you split out displays to the left of the original field.

Note:

To undo a split, see Undoing Field Ordering.

Creating Additional Text Fields

You can create a text field between two existing fields. You might do this to insert text between fields that are to be joined. For example, if you had two fields containing 100 and 10-1, you could insert a text field between them with a dash and then join them to create the 100-10-1 member of the Product dimension.

To create a new text field:

Select the field to put the new field in front of, for example, 10-1.
Choose Field > Create Using Text or click the Create Using Text button, , to open the Create Field Using Text dialog box.
Figure 22-8: Create Field Using Text Dialog Box
Enter the text to put into the new field, for example, a hyphen (-).
Click OK. The new field displays to the left of the selected field.

Note:

To undo a field you created using text, see Undoing Field Ordering.

Undoing Field Ordering

You can undo the last field operation you performed such as move, join, split, or create using text, using the Edit > Undo command. You can also undo field operations even if you have performed other actions. Undoing field operations is sequential; you must undo them from the last operation to the first.

Select the field or fields. If you do not know how to select multiple fields, see Selecting Multiple Fields.
Choose Options > Data File Properties or click the Data File Properties button, , to open the Data File Properties dialog box.
Click the Field Edits tab.
Figure 22-9: Field Edits Page
Select the operation to delete in the Operation list and click Delete. Operations must be deleted in reverse order, that is, by deleting the last operation first.
When you are finished, click OK.

Mapping Fields to Member Names

To load a data source, you must specify how the fields in the data source map to the dimensions in your database. Rules files can translate fields in the data source to match member names each time the data source is loaded without changing the data source. The rules file does the following:

Maps member fields in the data source to members in the database
Maps data fields in the data source to member names or member combinations (such as Jan, Actual) in the database

You can define rules to:

Name, or translate, the fields in the data source to match members in the database. See Naming Fields.
Replace strings, or translate, the fields in the data source to match members in the database. See Replacing Text Strings.

Naming Fields

Use a rules file to name data source fields to match Hyperion Essbase dimension names during a data load. The data source is not changed.

Note:

When you open an SQL data source, the fields default to the SQL data source column names. If these names are the same as your Hyperion Essbase dimensions, you do not have to perform any field mapping.

To name a field:

Select the field to name in the Data Prep Editor.
Choose Field > Properties or click the Field Properties button, , to open the Field Properties dialog box.
Click the Data Load Properties tab.
Figure 22-10: Data Load Properties Page

Type the member name into the Field Name text box or paste it by clicking on the appropriate member in the Dimension and Member lists. If the Dimension and Member lists are empty, click the Outline button to select a database outline.

Note:

If you enter a member name with a space in it, such as New York, be sure to put quotation marks around the member name. If you click the member name in the Member list box, Hyperion Essbase automatically puts quotation marks around member names with spaces in them.

To name the next field in the Data Prep Editor, click the Next button. To change the previous field, click the Prev button. This saves all changes before going to the next field.
When you are finished, click OK.

Replacing Text Strings

Use a rules file to replace text strings so that the fields map to Hyperion Essbase member names during a data load. The data source is not changed. For example, if the data source abbreviates New York to NY, you could have the rules file replace each NY with New York while loading the data.

Select the field or fields containing the text string you want to change.
Choose Field > Properties or click the Field Properties button, , to open the Field Properties dialog box. Click the Global Properties tab.
Figure 22-11: Global Properties Page: Replace Box
Enter the text string you want to replace in the Replace text box. For example, NY. You must enter a text string in the Replace text box. You cannot replace blank fields with text directly. To replace blank fields with text, see Replacing an Empty Field with Text.
Enter the text to replace it with in the With text box; for example, New York. You can leave the With text box empty, because you can replace a text string with an empty string.
Specify if the replacement operation should:
- Be case-sensitive; that is, only replace text strings that match the capitalization of the string in the Replace text box.
- Replace the text string only when it occurs as a whole word. For example, to replace the 10 in the string 100 10 1 with an A, setting the Match Whole Word option changes the string to 100 A 1. Not setting the Match Whole Word option changes the string to A0 10 1.
- Replace all occurrences of the string. For example, if you replaced all occurrences of 10 in the string 100 10 1 with an A, the string changes to A0 A 1. By default, Hyperion Essbase only changes the first occurrence.
Click Add to add the replacement operation to the list. To change an existing operation in the list, select the operation and click Change. To delete an operation from the list, select the operation and click Delete.
To move to the next column, click the Next button. To move to the previous column, click the Prev button.

Note: The Next and Prev buttons only work if a single field is selected.
Click OK.

Replacing an Empty Field with Text

You may want to replace empty fields in a column with text. If, for example, empty fields in the column represent default values, you could insert the default values to replace the empty ones or insert #MI to represent missing values.

Replacing an empty field with text requires several steps. To replace an empty field with text:

Select the column containing the empty fields you want to replace.
Choose Field > Create Using Text or click the Create Using Text button, .
Enter the text to put in the new field. Enter a dummy string that is not in the selected column, such as "temp." Click OK.
Join the new field with the column containing the fields to replace. Select both columns, choose Field > Join, and click OK. The previously blank fields now contain the dummy string you entered in step 3; for example, temp.
Select the column containing the dummy string and choose Field > Properties or click the Field Properties button, . Click the Global Properties tab.
Enter the text string you want to replace in the Replace text box. This should be the dummy string you entered in step 3; for example, temp. Now enter the text to replace it with in the With text box. This should be the final value you want to put in the fields, for example, default. Select the Match Whole Word.
Click Add and then click OK.
Now replace the extra dummy strings in the column with nothing. Choose Field > Properties or click the Field Properties button, . Click the Global Properties tab. Enter the dummy string in the Replace text box; for example, temp. Enter nothing in the With text box. Make sure the Match Whole Word option is not checked. Click Add and then click OK.

Changing the Case of Fields

Use a rules file to change the case of a field so the field maps to Hyperion Essbase member names during a data load. The data source is not changed. For example, if the data source capitalizes a field that is in lower case in the database, you could change the field to lower case; for example, from JAN to jan.

Select the field or fields containing the text string you want to change.
Choose Field > Properties or click the Field Properties button, , to open the Field Properties dialog box. Click the Global Properties tab.
Figure 22-12: Global Properties Page: Case Box
Choose the case to change the field to from the Case box, either Original, Upper Case, or Lower Case.
To move to the next column, click the Next button. To move to the previous column, click the Prev button.

Note: The Next and Prev buttons only work if a single field is selected.
Click OK.

Dropping Leading/Trailing White Space

You can drop leading or trailing white space from around fields in your data source. A field value containing leading or trailing white spaces does not map to a member name, even if the name within the white spaces is an exact match.

By default, Hyperion Essbase drops leading and trailing white space.

To drop leading or trailing white space:

Select the field or fields. If you do not know how to select multiple fields, see Selecting Multiple Fields.
Choose Field > Properties or click the Field Properties button, , to open the Field Properties dialog box.
Click the Global Properties tab.
Figure 22-13: Global Properties Page: Drop Leading/Trailing
Whitespace Check Box
By default, the "Drop leading/trailing whitespace" box is checked. If it is not already checked, check it.
Click OK.

Converting Spaces to Underscores

You can convert spaces in fields in the data source to underscores to make them match member names in the database.

To convert spaces to underscores:

Select the field or fields. If you do not know how to select multiple fields, see Selecting Multiple Fields.
Choose Field > Properties or click the Field Properties button, , to open the Field Properties dialog box. Click the Global Properties tab.
Figure 22-14: Global Properties Page: Convert Spaces to
Underscores Check Box
Check Convert spaces to underscores.
Click OK.

Adding Prefixes or Suffixes to Field Values

You can add prefixes or suffixes to each field value in the data source. For example, you could add ESS as the prefix to all Hyperion Essbase member names during the data load.

Select the field or fields. If you do not know how to select multiple fields, see Selecting Multiple Fields.
Choose Field > Properties or click the Field Properties button, , to open the Field Properties dialog box.
Click the Global Properties tab.
Figure 22-15: Global Properties Page: Prefix and Suffix Text Boxes
Enter the prefix or suffix in the Prefix or Suffix text box.
Click OK.

Defining a Column as a Data Field

Some data sources contain a single data column that does not map to a specific member. When this occurs, you must define the data column as a data field. You can only define one field in a record as a data field.

To define a column as a data field:

Select the field at the top of the data column.
Choose Field > Properties or click the Field Properties button, , to open the Field Properties dialog box.
Click the Global Properties tab.
Figure 22-16: Global Properties Page: Data Field Check Box
Check Data Field.
Click OK.

Changing Data Values

By default, Hyperion Essbase overwrites the existing values in the database, but the following sections describe:

Adding to and Subtracting from Existing Values

You can add or subtract the values in incoming records to existing values in an Hyperion Essbase database. For example, if you load weekly values, you can add them to create monthly values in the database.

To add or subtract existing values:

Select the field to add to or subtract from.
Choose Options > Data Load Settings or click the Global Data Load Properties button, , to open the Data Load Settings dialog box.
Click the Data Values tab.
Figure 22-17: Data Values Page: Data Values Box

Choose "Add to existing values" to add the values or the "Subtract from existing values" to subtract the values.

CAUTION: Using this option makes it more difficult to recover if the database crashes while loading data, although Hyperion Essbase lists the number of the last row committed in the application event log file. For more information, see Application Event Log File.

To solve this problem, as a Database Transaction setting, set the Commit Row value as 0. This causes Hyperion Essbase to view the entire load as a single transaction and commit the data only when the load is complete. For more information, see Specifying Isolation Level.

Click OK.

Clearing Existing Data Values

You can clear existing data from the database before loading new values. By default, Hyperion Essbase overwrites the existing values in the database with the new values in the data source. If you are adding and subtracting the data values, however, Hyperion Essbase adds or subtracts the new values with the existing ones.

Before adding or subtracting new values, you need to make sure the existing values are correct. If you are loading the first set of values into the database, you must make sure there is no existing value.

For example, let us assume that the Sales figures for January are calculated by adding together the values for each week in January. That means:

January Monthly Sales = Week 1 Sales + Week 2 Sales + Week 3 Sales + Week 
4 Sales

When you load Week 1 Sales, you must make sure that the value for January Monthly Sales is cleared in the database. If there is an existing value, Hyperion Essbase performs the following calculation:

January Monthly Sales = Existing Value + Week 1 Sales + Week 2 Sales + 
Week 3 Sales + Week 4 Sales

You can also clear data from fields that are not part of the data load. For example, if a data source contained data for January, February and March and you only wanted to load the March data, you could clear the January and February data.

Note:

If you are using transparent partitions, you can clear the values using just the same steps as for clearing data in a local database.

To clear existing values:

Choose Options > Data Load Settings or click the Global Data Load Properties button, , to bring up the Data Load Settings dialog box.
Click the Clear Data Combinations tab.
Figure 22-18: Clear Data Combinations Page
Enter the member combinations to clear in the Clear Combinations text box or click the dimensions and members in their lists. If the lists are empty, click Outline to associate the rules file with an outline. See Associating a Rules File with an Outline for more information on associating a rules file with an outline.
You can enter Hyperion Essbase functions in the Clear Combinations text box. For example, you could clear all descendants of Massachusetts by entering @ISDESCENDANTS(Massachusetts). For more information on Hyperion Essbase functions, see the online Technical Reference in the DOCS directory.

Note: You must separate member combinations with a comma.
Click Add to add the member combination to the list.
To change a member combination that is in the list, select the item in the list and click Change. It appears in the Clear Combinations text box.
To delete member combinations from the list, select them and click Delete.

Scaling Data Values

You can scale data values if the values in the data source are not in the same scale as the values in the database. For example, the data source could track Sales in hundreds while the database tracks them in thousands. In this case, you would want to multiply the incoming values by 10.

Select the field to scale.
Choose Field > Properties or click the Field Properties button, , to open the Field Properties dialog box.
Click the Global Properties tab.
Figure 22-19: Global Properties Page: Scale Check Box
Check Scale. After Scale is checked, you must enter a number in the Scale text box.
Enter the value to multiply by in the Scale text box. For example, enter 10 to increase the value by 10 times; enter 0.1 to decrease the value by 10 times.
To move to the next column, click the Next button. To move to the previous column, click the Prev button.

Note:

The Next and Prev buttons only work if a single field is selected.

Flipping Field Signs

You can reverse or flip the value of a data field by flipping its sign. Sign flips are based on UDAs (user-defined attributes) in the outline. When loading data into the Accounts dimension, for example, you could specify that any record whose Accounts member had a UDA of Expense should change from a plus sign to a minus sign. You set UDAs in the Outline Editor. See Creating and Changing Database Outlines for more information on user-defined attributes.

To set sign flipping:

Choose Options > Data Load Settings or click the Global Data Load Properties button, , to open the Data Load Settings dialog box.
Click the Data Values tab.
Figure 22-20: Data Values Page
Check On UDA under Sign Flip.
Enter the UDA, for example, Expense. Use the Outline Editor to get a list of UDAs. See Creating and Changing Database Outlines for more information.
Enter the dimension or click the dimension in the Dimension list. If there are no dimensions in the list, click the Outline button, , to associate an outline with the rules file. See Associating a Rules File with an Outline for more information.
Click OK.