This chapter describes how to manipulate fields during a data load or dimension build using a rules file using 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 and Debugging a Data Load.
This chapter contains the following sections:
You can select multiple fields and then set the properties for them. Essbase grays out any menu items and controls you cannot use when more than one field is selected.
To select discontinuous fields, use one of the following methods:
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 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.
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:
Figure 332: Global Properties Page: Ignore Check Boxes
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:
Figure 333: Ignore Tokens Page
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.
Figure 334: Move Field Dialog Box
Note: To undo a move, see Undoing Field Ordering.
Caution: In some instances, moved fields may appear to merge. This may occur if you have a data file similar to this structure:
1<tab>2<tab>3
1<tab>2<tab>(null)
If you move a field with empty cells and it is the last field in the data file, the field may merge with the field to its left when moved.
To prevent this, add a tab where the empty cell is.
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.
Figure 335: Join Fields Dialog Box
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.
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.
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.
Figure 336: Create Field Using Join Dialog Box
The new field displays to the left of the first field containing joined information. For example, in Figure 337, a new 100-10 field displays to the left of the existing 100 and -10 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.
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.
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.
Figure 338: Split Field Dialog Box
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.
Note: To undo a split, see Undoing Field Ordering.
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.
Figure 339: Create Field Using Text Dialog Box
Note: To undo a field you created using text, see 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.
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:
Use a rules file to name data source fields to match 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 Essbase dimensions, you do not have to perform any field mapping.
Figure 341: Data Load Properties Page
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, Essbase automatically puts quotation marks around member names with spaces in them.
Use a rules file to replace text strings so that the fields map to 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.
Figure 342: Global Properties Page: Replace Box
Note: The Next and Prev buttons only work if a single field is selected.
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:
Use a rules file to change the case of a field so the field maps to 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.
Figure 343: Global Properties Page: Case Box
Note: The Next and Prev buttons only work if a single field is selected.
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, Essbase drops leading and trailing white space.
To drop leading or trailing white space:
Figure 344: Global Properties Page: Drop Leading/Trailing Whitespace Check Box
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:
Figure 345: Global Properties Page: Convert Spaces to Underscores Check Box
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 Essbase member names during the data load.
Figure 346: Global Properties Page: Prefix and Suffix Text Boxes
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:
Figure 347: Global Properties Page: Data Field Check Box
By default, Essbase overwrites the existing values in the database, but the following sections describe:
You can add or subtract the values in incoming records to existing values in an 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:
Figure 348: Data Values Page: Data Values Box
Caution: Using this option makes it more difficult to recover if the database crashes while loading data, although Essbase lists the number of the last row committed in the application log. For more information, see Understanding the Contents of the Application Log.
To solve this problem, as a Database Transaction setting, set the Commit Row value as 0. This causes Essbase to view the entire load as a single transaction and commit the data only when the load is complete. For more information, see Transactions.
You can clear existing data from the database before loading new values. By default, 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, 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 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, Essbase performs the following calculation:
January 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.
Figure 349: Clear Data Combinations Page
You can enter Essbase functions in the Clear Combinations text box. For example, you could clear all descendants of Massachusetts by entering @IDESCENDANTS(Massachusetts). For more information on Essbase functions, see the Technical Reference in the docs directory.
Note: You must separate member combinations with a comma.
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.
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.
Figure 350: Global Properties Page: Scale Check Box
Note: The Next and Prev buttons only work if a single field is selected.
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.
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |