Ignored fields from the source will not be loaded into the Essbase cube. For example, the Sample.Basic cube has five standard dimensions: Year, Product, Market, Measures, and Scenario. If the source has an extra field that is not a member of any dimension, such as Salesperson, you can tell Essbase to ignore the Salesperson field.

To ignore a column during an Essbase data load or dimension build,

1. Open your load rule in the Essbase web interface.

2. Select the unwanted field and click Ignore.

   
   
   
   
   

3. Verify and save the rule.

For your data load and dimension build rules, you can ignore any field in the source that matches a string (called a token). When you ignore fields based on string values, the fields are ignored everywhere they appear in the source data, not just in a particular column. For example, in a source that is a computer-generated report in text format, special ASCII characters might be used to create horizontal lines between pages or boxes around headings. These special characters can be defined as tokens to be ignored.

To ignore strings/tokens in the source data during an Essbase dimension build,

1. Open your dimension build rule in the Essbase web interface.

2. Click Source Properties.

   
   
   
   
   

3. On the Details tab, in Tokens to Ignore, enter one or more string(s) to ignore, separated by whitespace.

   
   
   
   
   

4. If you entered more than one string, select AND or OR in Tokens Joined By to control whether the string matching is done logically with AND or with OR. If you select AND, both strings must be present in the source to be ignored. If you select OR, either one or the other must be present.

5. Click OK. Verify and save the rule.

You can move fields to a different location using a rules file. For example, you can specify the first field in the source data to be the third field during the data load or dimension build.

Move operation is not available in Index-based dimension build rules, where fields can be in any order.

To move a field in an Essbase data load or dimension build rule,

1. Open your load rule in the Essbase web interface.

2. Select the field.

   
   
   
   
   

3. Click Move.

4. Indicate the position to move to, and click OK.

   
   
   
   
   

5. Verify and save the rule.

When adding a new field to a load rule,

• If you select an existing field and then add a new field, the new column is added to the left of the existing column.

• If you select no fields, the new field is added to the end (to the right of all existing fields).

In the following dimension build rule example, assume you want to add a Generation 2 field prior to the existing Generation 3 field.

To add a field in an Essbase data load or dimension build rule,

1. Open your load rule in the Essbase web interface.

2. Select the field so that it is highlighted.

   
   
   
   
   

3. Click Create > Regular.

   
   
   
   
   

   Field 1 moves to become Field 2, and a new Field 1 is added.

   
   
   
   
   

4. Enter the required information for the field you just added.

   
   
   
   
   

5. Verify and save the rule.

For example, if the source data has separate fields for product number (100) and product family (-10), you must use the rule to join the fields (100-10) before loading them into the Sample.Basic cube.

Consider the following dimension build rule and preview:

For the following exercise, assume your goal is to join the fields to create qualified member names of the following format:

[500].[500-10].[500-10-10]

You can replace Field 3 / Generation 4 with the new qualified member name format.

To construct the required fields using joins and splits in your Essbase dimension build rule,

1. Select Field 1.

   
   
   
   
   

2. Click Create > With Static Value.

3. Enter [ and click OK.

   Field 1 is replaced with a new field containing the character you entered, and the other fields are shifted right.

   
   
   
   
   

4. Select Field 3 and click Create > With Static Value.

5. Enter ] and click OK.

   Field 3 is replaced with a new field containing the character you entered, and fields 4 and 5 are shifted right.

   
   
   
   
   

6. Select Field 1.

   
   
   
   
   

7. Hold Shift while clicking fields 2 and 3, to add fields 2 and 3 to the current selection.

   
   
   
   
   

8. Click Join.

   Fields 1, 2, and 3 are joined into a new Field 1 containing [500].

   
   
   
   
   

   If your joined Field contains the brackets going the wrong directions, such as ]500[, then you probably selected in order of Fields 3, 2, 1 instead of Fields 1, 2, and 3. The order of your selections impacts how the Join behaves. To fix this, select the incorrectly joined field and click Split (using position 1, and then position 3), and try again, reversing your selection order before clicking Join.

9. Copy the remaining generation fields (Generations 3 and 4) using Create > With Join, and, add more static values for periods and brackets, until you have built a field containing a qualified member name of the desired format:

   [500].[500-10].[500-10-10]

10. If you need to review your history of column operations including joins, splits, moves, and static value creation, click Column Operations to view a list of recent operations. You can undo the last operation.

    
    
    
    
    

11. Finish editing the rule, then verify and save it. The final goal for this exercise is to replace the Generation 4 field to use qualified member names. In this example, the original Generation 4 field (in Field 4) is now ignored, as it's been replaced with Field 3.

    
    
    
    
    

Field Expressions (Available for Index-Based Dimension Build Rules)

Although Index-based rules do not have split, join, and move column operations, you can join fields and perform other operations using a field expression.

1. Select a field and click Expression to add an operational expression to a field.

2. To join two columns, click a function and provide the column indices as arguments. For example,

   
   
   
   
   

For example, if a source of data has separate fields for product number (Example: 100) and product family (Example: -10), you may need to join the fields (Example: 100-10) before you load them into the Sample.Basic cube. If, however, you want to preserve the two existing fields in the source, you can create a new field (100-10) using a join, while preserving the original fields (100 and -10).

Consider the following dimension build rule and preview:

Assume your goal is to add a generation 4 field of the following format:

500-500-10

To get started,

1. Select Field 2 and click Create > With Static Value.

2. Enter a hyphen - and click OK.

   
   
   
   
   

3. Select Fields 1, 2, and 3 (in that order), and click Create > With Join, using join position 4.

   
   
   
   
   

4. If you need to review your history of column operations including joins, splits, moves, and static value creation, click Column Operations to see an ordered list of recent operations.

5. You can move Field 2 out of the way to reuse later. Select it and click Ignore if you do not want it to affect the rule functionality.

Field Expressions (Available for Dimension Build Rules)

Although Index-based rules do not have split, join, and move column operations, you can join fields and perform other operations using a field expression.

1. Click Create > With Expression to add a field that you will define using an expression.

   
   
   
   
   

2. To join two columns, click a function and provide the column indices as arguments. For example,

   
   
   
   
   

This operation is the opposite of a join, and is available only in Regular rules. For example, if a data source for the Sample.Basic database has a field containing UPC100-10-1, you can split “UPC” out of the field and ignore it. Then, only 100-10-1, the product number, is loaded.

To split a field during an Essbase data load or dimension build,

1. Open your load rule in the Essbase web interface.

2. Select the field.

   
   
   
   
   

3. Click Split.

4. Indicate the split position and direction, in terms of characters. For example, to separate 10 away from 500-, indicate split position 4, Left to Right.

   
   
   
   
   

5. Click OK. Verify and save the rule.

For example, if, during a single dimension build, you want to define a multilevel attribute dimension and associate attributes with members of a base dimension, you must copy some of the fields.

To copy a field during an Essbase data load,

1. Open your load rule in the Essbase web interface.

2. Select a field to copy.

3. Click Create > With Join.

   
   
   
   
   

4. Enter the number of the field you want to duplicate.

5. Click OK. Verify and save the rule.

If Create >With Join is unavailable, refer to Create Field with Join to learn how to use an expression.

This section applies to data load only. If you are performing a dimension build, skip this section.

Map fields in the source directly to fields in the Essbase cube during a data load by specifying which field in the source maps to which member or member combination in the Essbase cube. The source data is not changed.

To map a field during an Essbase data load,

1. Open your load rule in the Essbase web interface.

2. Select the unmapped field.

   
   
   
   
   

3. Either type a member name, or click the edit field/member selection tool next to the field.

4. If you are using the member selection tool, search for (or navigate to) the member you want to associate with this field.

   
   
   
   
   

5. Double click the member so that it is added to the Field Name.

   
   
   
   
   

6. Click OK. Verify and save the rule.

To load a source of data, you must specify how the fields of the source map to the dimensions and members of the cube. Rules can translate fields of the source so that the fields match member names each time the source is loaded. This process does not change the source.

The rules file:

• Maps member fields of the source file to dimensions and members of the cube

• Maps data fields of the source file to member names or member combinations (such as Jan, Actual) of the cube

Naming Fields

To name a field during an Essbase data load or dimension build,

1. Open your load rule in the Essbase web interface.

2. Select the field.

   
   
   
   
   

3. Click Field Properties.

   
   
   
   
   

4. Enter a name in the Name field. If the name has a space in it, enclose it in quotation marks.

   
   
   
   
   

5. Click OK. Verify and save the rule.

Replace Text Strings

You can replace text strings so that the fields map to Essbase member names during a data load or dimension build. The data source is not changed. For example, if the data source abbreviates New York to NY, you can have the rules file replace each NY with New York.

To replace strings during an Essbase data load or dimension build,

1. Open your load rule in the Essbase web interface.

2. Select the field.

   
   
   
   
   

3. Click Field Properties.

4. Click the Filters tab.

5. Click Replacements.

6. Click the + icon to add string replacement criteria.

7. Complete the Find and Replace values. For example, replace CA with Calif.

   
   
   
   
   

8. Specify if the replacement operation should:

   • Be case-sensitive
   • Make the replacement only if the whole word matches.
   • Replace all occurrences of the string.

9. Click OK. Verify and save the rule.

Change the Case of Fields

You can change the case of a field so that the field maps to Essbase member names during a data load or dimension build. The data source is not changed. For example, if the source data capitalizes a field (for example, JAN) that is in init capped in the cube outline (Jan), you can have the rules file change the field to First Letter Capital.

To change field case during an Essbase data load or dimension build,

1. Open your load rule in the Essbase web interface.

2. Select the field.

   
   
   
   
   

3. Click Field Properties.

4. For Case, select an operation to perform on incoming data.

   
   
   
   
   

5. Click OK. Verify and save the rule.

Drop Leading and Trailing Spaces

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

To remove spaces during an Essbase data load or dimension build,

1. Open your load rule in the Essbase web interface.

2. Select the field.

   
   
   
   
   

3. Click Field Properties.

4. On the Details tab, select Trim.

5. Click OK. Verify and save the rule.

By default, Essbase drops leading and trailing spaces.

Convert Spaces to Underscores

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

To convert spaces to underscores during an Essbase data load or dimension build,

1. Open your load rule in the Essbase web interface.

2. Select the field.

   
   
   
   
   

3. Click Field Properties.

4. On the Details tab, click Convert Spaces to Underscore.

5. Click OK. Verify and save the rule.

Add Prefixes or Suffixes to Field Values

You can add prefixes and suffixes to each field value of the data source. For example, you can add 2024 as the prefix to all member names in the Year dimension.

To add a prefix or suffix during an Essbase data load or dimension build,

1. Open your load rule in the Essbase web interface.

2. Select the field.

   
   
   
   
   

3. Click Field Properties.

4. On the Details tab, enter the prefix or suffix to add.

   
   
   
   
   

5. Click OK. Verify and save the rule.