Setting up a Rules File to Manipulate Records

This chapter describe how to create a data load or dimension build rules file that performs operations on records using Hyperion Essbase Application Manager. For information about performing operations on fields within a record, see Manipulating Fields Using a Rules File. For information about loading data using rules files, including prerequisites, see Performing a Data Load.

This chapter contains the following sections:

Selecting the Data Source

This section describes how to select your data source, including:

Connecting to the Server

Before you can create data load rules for a data source, you may want to connect to the server. You are not required to connect to the server before defining data load rules, because you can create the rules file on your client machine.

Viewing the Application and Database

Before you create data load rules for a data source, you may want to view the application and database. You are not required to view the application or database before defining data load rules. To view rules files, you must open Data Prep Editor which is available through the Application Desktop window. The Application Desktop window appears after you connect to the server.

Figure 21-1: Application Desktop Window

Select the application to view from the Applications list box; for example, the Sample application.
Select the database from the Databases list box; for example, the Basic database.

Opening the Data Prep Editor

To define a rules file, you must use the Data Prep Editor. To open the Data Prep Editor:

In the Application Desktop window, click the Data Load Rules button, . Then click New to open the Data Prep Editor with a new rules file or Open to open an existing rules file.
Select View > Data Load Fields or click the Data Load button, , to make sure that the Data Prep Editor is in data load mode.
Figure 21-2: Data Prep Editor

The Data Prep Editor contains two windows. The top window provides a view of the data source, called the raw data source. The bottom window contains a grid showing the appearance of records after rules are applied, that is, as they will be loaded into the database. Any rules you define do not modify the content of the raw data source.

Viewing Data Load Fields or Dimension Build Fields

The Data Prep Editor can display two different kinds of fields: data load fields and dimension build fields. To determine which fields are currently displayed, pull down the View menu.

Select View > Data Load or click the Data Load button, , to view data load fields. Fields set to be ignored during the data load turn gray.
Select View > Dimension Building Fields or click the Dimension Build button, , to view dimension build fields. Fields set to be ignored during the dimension build turn gray. See Introducing Dynamic Dimension Building for more information on dimension building.

Customizing the Data Prep Editor

You can customize your view of the Data Prep Editor:

To hide the gridlines, select View > Gridlines. The check mark disappears next to the Gridlines command in the View menu and the gridlines disappear from the Data Prep Editor. To show the gridlines, select View > Gridlines.
To hide the raw data, select View > Raw Data. The check mark disappears next to the Raw Data command in the View menu and the raw data window disappears from the Data Prep Editor. To show the raw data, select View > Raw Data.
To hide the toolbar, select View > Toolbar. The check mark disappears next to the Toolbar command in the View menu and the toolbar disappears from the Data Prep Editor. To show the toolbar, select View > Toolbar.

Opening a Data Source

After you open the Data Prep Editor, you can open data sources, such as text files, spreadsheet files, and SQL data sources. This section describes how to open the following kinds of data sources:

Opening a Text File

After you open the Data Prep Editor, you can open text files in it.

To open a text file:

Select File > Open Data File to view a list of text files. The Open Server Data File Object dialog box contains values for the last data source you opened for this rules file. In this example, the last file opened was the Act1 file in the Sample Basic database.
Figure 21-3: Open Server Data File Object Dialog Box
If "Text files" is not selected in the List Objects of Type list box, select it.

Note: Text files must end in.TXT on the file system. If they don't, rename them.

If you select Server, the text file to load 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 text file in the Object Name text box or select it from the Objects list box. For example, ACT1.

If you select Client, the text file 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 text file from a standard Open Client Data Files dialog box.

Note: The\ESSBASE\APP and\ESSBASE\CLIENT are the default directories specified during installation. You may have set these directories differently.
When you find the file to open, click OK. The contents of the file appear in the top window of the Data Prep Editor. The ACT1.TXT file is tab delimited, which is the default setting in the Data Prep Editor.

Opening a Spreadsheet File

To open a spreadsheet file:

Select File > Open Data File to open the Open Server Data File Object dialog box.
Figure 21-4: Open Server Data File Object Dialog Box: Spreadsheet Files
Select the kind of spreadsheet to open from the List Objects of Type list box. For example, you would select Excel Sheets to open an Excel spreadsheet file.
Select the spreadsheet from the Objects list box.
If you select Server, the spreadsheet to load 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 spreadsheet in the Object Name text box or select it from the Objects list box. For example, DATA.

If you select Client, the spreadsheet 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 spreadsheet from a standard Open Client Data Files dialog box. The ASYMM.XLS spreadsheet, for example, is in the\ESSBASE\CLIENT\SAMPLE\ directory.

Note: ESSBASE is the default directory specified during installation. You may have specified a different default directory.
When you find the file to open, click OK. The contents of the file appear in the top window of the Data Prep Editor.

Opening an SQL Data Source

When you open the Data Prep Editor, you can open an SQL data source if you've licensed Hyperion Essbase SQL Interface. The Hyperion Essbase SQL Interface Guide provides information on supported environments, installation, and connection to supported data sources. Contact your Hyperion Essbase administrator for more information.

Note:

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

To open an SQL data source:

Select File > Open SQL to open the Select Server, Application and Database dialog box. If you are connected to a server, the dialog box contains the values for that server.
Figure 21-5: Select Server, Application and Database Dialog Box
Select the server, application, and database to open. If you are already connected to the correct server, application, and database, click OK. This server, application, and database act as the client for SQL access.
Click OK. The Define SQL dialog box appears:
Figure 21-6: Define SQL Dialog Box
Select the SQL data source to use from the SQL Data Source list box; for example, dBASE files.
Enter the name of the database in the Database text box; for example, dbfexamp.
Enter the location of the SQL data source in the From list box; for example:
```
c:\essbase\app\sample\basic\dbfexamp.dbf
```
Enter any additional information that is required to connect to your SQL data source, such as the server, application, user ID, or password. To connect to a Sybase SQL Server, for example, you would enter the user ID, password, database, server, and application.
Define any select statements in the Select and Where list boxes. By default, the select statement is * (which selects all rows in the table).
Click OK/Retrieve to retrieve the SQL data source file or OK/Save to save your settings. The contents of the data source appear in the top window of the Data Prep Editor.

Setting File Delimiters

File delimiters are the character(s) that separate fields in the data source. By default, the rules file separates fields with tabs. You can set the file delimiter to be a comma, tab, whitespace, a fixed-width column, or a custom value. Usually, setting the file delimiters is the first thing you do after opening a data source.

Note:

You do not need to set file delimiters for SQL data.

To set file delimiters:

Select Options > Data File Properties or click the Data File Properties button, , to open the Data File Properties dialog box. Click the File Delimiter tab.
Figure 21-7: File Delimiter Page
Select the type of delimiter to use in your file:
- Comma
- Tab
- All Whitespace
- Custom--in the text box, enter a single character as the custom delimiter.
- Column Width--in the text box, enter the width of the column. The column width must be a five digit or smaller number. Use column width for data sources with fixed-width columns.
Click OK.

Using Header Information

Data sources can contain data records and header records. Data records contain data: member fields and data fields. Header records describe the contents of the data source and how to load data values in the data source to the database.

Rules files contain records that translate the data in the data source to map it to the database. As part of that information, rules files can also contain header records.

You can create header records using the following methods:

In the rules file using the Data Prep Editor.
In the data source using a text editor or spreadsheet; then set them as header records in the rules file.

Defining Header Information in the Rules File

You can define header information in the rules file. These headers are only used during data loading or dimension building and do not change the data source. Header information in a rules file is not used if there is also a dynamic reference in the rules file pointing to a header record in the data source.

Select Options > Data Load Settings or click the Global Data Load Properties button, , to open the Data Load Settings dialog box. Click the Header Definition tab.
Figure 21-8: Header Definition Page

Enter one or more member or member combinations in the Header Name text box or select the dimensions and members from the Dimension and Member lists. For example, to specify the Year dimension as March, enter Mar. Enter the Mar,Budget member combination to specify both the Year and Scenario dimensions. You must separate dimensions and members with a comma.

Note: Only one member per dimension is allowed in the header. For example, Feb,Mar is an invalid header because it contains two members of the Year dimension.

If the rules file is not associated with an outline, the Dimension and Member lists are empty. Click Outline to associate the rules file with an outline.
Click OK.

Defining Header Information in the Data Source for a Data Load

You can define header information directly in the data source. Placing header information in the data source makes it possible to use the same rules file for multiple data sources with different formats, because the data source format is specified in the data source header and not the rules file.

When you add one or more headers to the data source, you must also specify the location of the headers in the data source using dynamic references. Dynamic references are set in the rules file and tell Hyperion Essbase to read the header information as a header record and not a data record. When setting dynamic references, you can also specify which type of header information is in which header record.

Header information defined in the data source takes precedence over header information defined in the rules file.

To define header information in the data source:

Add the header information to the data source using a text editor or spreadsheet. Member combinations must be separated by a comma.
Select Options > Data File Properties or click the Data File Properties button, , to open the Data File Properties dialog box. Click the Header Records tab.
Figure 21-9: Header Records Page
Enter the number of lines to skip in the data source in the "Number of lines to skip" text box. Skipping lines means that Hyperion Essbase does not process the records in those lines as data records. Hyperion Essbase still processes those records as header records. You should tell Hyperion Essbase to skip all header records.
Enter the number of the record in the data source that contains the header names in the "Record containing header names" text box. Header names, for example, could be Jan, Actual.

Note: Each dynamic reference record number must be unique and cannot be larger than 4000.
Enter the number of the record that contains the data load field names in the "Record containing data load field names" text box. In this case, the data load field names record contains information to map the members in the data source to dimensions in the database. A header record in a data source to load into the Sample Basic database could contain dimension names such as Product, Market, Scenario, Measures, and Year or any valid field name.
Enter the number of the record that contains the dimension building field names in the "Record containing dimension building field names" text box. See Introducing Dynamic Dimension Building for more information.
Click OK.

Selecting Records

You can specify which records Hyperion Essbase loads into the database or uses to build dimensions by setting selection criteria. Selection criteria are string and number conditions that must be met by one or more fields before Hyperion Essbase loads the record. If a field or fields in the record does not meet the selection criteria, Hyperion Essbase doesn't load the record. For example, to load only the Budget data from a data source, you could create a selection criterion to load only records where the first field was Budget.

To define the selection criteria:

Select the field to which to apply the criteria. For example, field 1.
Select Record > Select or click the Record Selection button, , in the Data Prep Editor toolbar to open the Select Record dialog box.
Figure 21-10: Select Record Dialog Box
Set the field type as string or number by choosing the String or Number option. If the field is a string, you can define criteria that are case-sensitive by selecting Case Sensitive.
Enter the string or number upon which the criterion is based in the String/Number text box; for example, Budget.
Select how to evaluate the field by clicking one of the condition options in the Condition box; for example, Equal To.
Add the selection criterion to the list by clicking Add. To create multiple selection criteria for a single field, repeat this process for each selection criterion. To change or delete a criterion from the list, select the criterion to change or delete and click Change or Delete.

If you define multiple selection criteria on a single field, you can specify how Hyperion Essbase combines the criteria, that is whether they should be connected logically with AND or OR. If you select And from the Boolean group, the field must match all the selection criteria. If you select Or from the Boolean group, the field must only match one of the selection criteria.

Note:

If you define selection criteria on more than one field, you can specify how Hyperion Essbase combines the criteria. See Defining Multiple Select and Reject Criteria.

Click OK.

Rejecting Records

You can specify which records Hyperion Essbase does not load into the database or use to build dimensions by setting rejection criteria. Rejection criteria are string and number conditions that must be met by one or more fields to cause Hyperion Essbase to reject the record. If a field in the record does not meet the rejection criteria, Hyperion Essbase loads the record. For example, to reject the Actual data from a data source and load only the Budget data, you could set a rejection criterion to reject records where the first field was Actual.

To define rejection criteria:

Select the field to which to apply the criterion.
Select Record > Reject or click the Record Rejection button, , to open the Reject Record dialog box.
Figure 21-11: Reject Record Dialog Box
Set the field type as string or number by choosing the String or Number option. If the field is a string, you can define criteria that are case-sensitive by selecting the Case Sensitive box.
Enter the string or number upon which the criterion is based in the String/Number text box; for example, Actual.
Select how to evaluate the field by clicking one of the condition options in the Condition box; for example, Equal To.
To create multiple rejection criteria for a single field, add the first one by clicking Add and repeat this process for each rejection criterion. To change or delete a criterion from the list, select the criterion to change or delete and click Change or Delete.

If you define multiple rejection criteria on a single field, you can specify how Hyperion Essbase combines the criteria, that is whether they should be connected logically with AND or OR. If you select And from the Boolean group, the field must match all the rejection criteria. If you select Or from the Boolean group, the field must only match one of the rejection criteria.

Note:

If you define rejection criteria on more than one field, you can specify how Hyperion Essbase should combine the criteria. See Defining Multiple Select and Reject Criteria.

Click OK.

Defining Multiple Select and Reject Criteria

When you define select and reject criteria on multiple fields, you can specify how Hyperion Essbase combines the rules across fields, that is, whether the criteria are connected logically with AND or OR. If you select And from the Boolean group, the fields must match all the selection or rejection criteria. If you select Or from the Boolean group, the fields must only match one of the selection or rejection criteria. Setting the global Boolean applies it to all select or reject operations in the rules file, for both data load and dimension-building fields.

Note:

If your selection and rejection criteria apply to the same record (that is, you try to select and reject the same record), the record is rejected.

For example, to load only the New York, Actual data in a data source containing data for other markets and scenarios, create select criteria to select records that contain New York and Actual in the specified fields.

Select 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 21-12: Data Values Page: Global Select/Reject Boolean
Select the Global Select/Reject Boolean operator to use, either And or Or.
Click OK.

Setting the Records Displayed

To specify how many records Hyperion Essbase displays in the Data Prep Editor and the first record to display:

Select Record > Record View Count to open the Record View Count dialog box.
Figure 21-13: Record View Count Dialog Box
Enter the number of records to view in the View Count text box. The editor displays 50 records by default, but it can display anywhere from 1 to 200 records.

Enter the first record to view in the Start Record text box. Hyperion Essbase skips the other records in the data source and displays the number of the record that you chose first in the Data Prep Editor. For example, if you enter 5 as the starting record, Hyperion Essbase does not display records 1 through 4.

Note:

Hyperion Essbase treats header records the same as data records when counting the records to skip.

The highest record you can set as the first record is 38,527.

Figure 21-14, for example, displays records 5 through 8.

Figure 21-14: Displaying Record 5 through 8

Click OK.

Validating and Saving Data Load Rules

This section describes how to validate data load rules to make sure they are correct, and save them so you can reuse them.

Before you validate a data load rules file, you must associate the rules file with an outline. This is usually the outline of the database into which to load the data. The rules file is not permanently associated with that outline, and you can associate it with any other outline in the future. See Associating a Rules File with an Outline.

Rules files are validated to make sure the member and dimension mapping defined in the rules file maps to the outline. See Manipulating Fields Using a Rules File to learn how to map fields to members. Validation cannot ensure that the data source will load properly.

Associating a Rules File with an Outline

To associate a rules file with an outline:

Select Options > Associate Outline or click the Outline button, , to open the Associate Server Outline Object dialog box.
Figure 21-15: Associate Server Outline Object Dialog Box
Make sure the values for Object Name, Server, Application, and Database are correct. By default, Hyperion Essbase assigns the values of the last outline associated with this rules file.
Select the outline to open in the Objects list box.
Click OK.

Validating a Rules File

To validate a rules file, make sure the rules file is open. If you're building dimensions by altering the data source (using dynamic references), make sure the data source is open as well.

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 appears. It contains information about the validation process, including which fields did not map to the outline.
Figure 21-16: Validate Rules Dialog Box
If the rules file validates with no problems, you can use it to load data. See Introducing Data Loading for information on loading data.
If the rules file is not correct, you must fix it. Go to the field specified in the Validate Rules dialog that did not pass validation. In Figure 21-16, for example, Field 1 was invalid.
Make sure that the field name is valid. Check the following:
- Is the field name spelled correctly?
- Are the file delimiters correctly placed?
- Is there a member in the field name?
- Is the dimension name used in another field name or the header?
- Are you using a member as a member combination in one place and a single member in another?
- Is more than one field defined as a data field?
- Is the dimension used for sign flipping in the associated outline?
- Is the rules file associated with the correct outline?
  
  Note: Debugging and Optimizing Data Loads contains a complete list of data load rules file validation errors and possible causes.
Validate the file again. Return to step 1.

Saving Rules Files

To save a rules file:

Select File > Save or click the Save button, . If you haven't saved the rules file before, the Save Server Object dialog box appears.
Figure 21-17: Save Server Object Dialog Box
Set the Server, Application, and Database to save the object in. You can also specify whether to save the object on the server or the client by choosing Server or Client in the Location group.
Enter the rules file name in the Object Name text box. The name must be a valid name in your operating system. In addition, the rules file name you specify must be eight or fewer alphanumeric characters. Hyperion Essbase automatically adds an extension of.RUL. For example, you can name the rules file ACT1.RUL by entering ACT1 in the Object Name text box.
Click OK.

Saving Under a Different Name

To save a rules file under a different name, select File > Save As to open the Save Server Object dialog box. Follow the same steps as for saving a rules file, but enter a different name.