Perform Operations on Records

Using fields in the Essbase data load or dimension build rule, you can define selection or rejection criteria for records in the source. You can also customize the rule to indicate how Essbase should handle header records for a data load.

• Select and Reject Records
• Set the Records Displayed
• Handle Header Records

Select and Reject Records

You can build filters into Essbase data load and dimension build rule fields to define precise selection and rejection criteria for records. For example, you can reject certain records before they are loaded into the cube.

Select Records

You can specify which records 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 within a record for Essbase to load the record. If a field or fields in the record do not meet the selection criteria, Essbase does not load the record. You can define one or more selection criteria. For example, to load only 2019 Budget data from a data source, create a selection criterion to load only records in which the first field is Budget and the second field is 2019. If you define selection criteria on multiple fields, you can specify how Essbase combines the criteria.

Reject Records

You can specify which records Essbase ignores by setting rejection criteria. Rejection criteria are string and number conditions that, when met by one or more fields within a record, cause Essbase to reject the record. You can define one or more rejection criteria. If no field in the record meets the rejection criteria, Essbase loads the record. For example, to reject Actual data from a data source and load only Budget data, create a rejection criterion to reject records in which the first field is Actual.

Combine Multiple Select and Reject Criteria

When you define select and reject criteria on multiple fields, you can specify how Essbase combines the rules across fields: whether the criteria are connected logically with AND or with OR. If you select AND from the Boolean group, the fields must match all of the criteria. If you select OR, the fields must match only one of the criteria. The global Boolean setting applies to all select or reject operations in the rule, for data load and dimension build fields.

Note:

If selection and rejection criteria apply to the same record (you define select and reject criteria on the same record), the record is rejected.

To filter the records,

1. Select the field to which to apply the criteria.

   
   
   
   
   

2. Click Field Properties, and click Filters.

3. Click the + icon to add a filter.

   
   
   
   
   

4. Add as many filter critera as needed. These instructions apply for Selection as well as Rejection criteria. Selection/Rejection criteria applied to a field let you define filters to be met for approval or rejection of a data load or dimension build record when loading to the Essbase cube. Filtration options include string or numeric matching of specific values, logical join options, and case sensitivity.

   For example, for Condition, if you select String Filter, the condition applies to string matching. If you do not select String Filter, the condition applies to numeric values.

   For string matching, click String Filter and choose conditions:

   
   
   
   
   
   1. = means the value you specify must be an exact string match
   2. <> means the value you specify must not be not a string match
   3. Contains means a specified string match must be found
   4. Does not contain means a specified string match must not be found
   5. Case sensitivity can optionally be applied

   For numeric data value matching, use the mathematical Condition operands as needed (=, <>, >, <, >=, <=).

5. If you define more than one filter, choose a logical join operator, AND or OR. AND means that all the defined selection or rejection criteria must apply (if even one criterion is not met for any given record, then the filter does not apply to that record). OR means the opposite (if even one criterion is met for a given record, the filter applies).

Set the Records Displayed

You can specify the number of records to display in the rule preview, and you can also tell Essbase to skip a certain number of records from the data load or dimension build. For example, in the following new data load rule definition, the first 2 records will be skipped from the data load (but will not be omitted from the preview). No more than 10 records will display in the preview (this limit does not affect the data load operation).

Note:

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

Handle Header Records

Header records can be used or skipped in Essbase data load rules, and should be skipped in dimension build rules. Using fields in the rule, you can specify how Essbase should handle header records, if they exist in the source data.

Sources of data can contain:

• Data records, which contain member fields and data fields

• Header records, which describe the contents of the source data

  Note:

  When you are loading SQL data, the data load rule cannot include header records.

The following is an example of a data file with a header record:

"Product","Market","Year","Scenario","Sales","COGS"
"100-10","New York","Jan","Actual","678","271"
"100-10","New York","Jan","Budget","640","260"
"100-10","New York","Feb","Actual","645","258"
"100-10","New York","Feb","Budget","610","240"
"100-10","New York","Mar","Actual","675","270"
"100-10","New York","Mar","Budget","640","250"
"100-10","New York","Apr","Budget","670","270"
"100-10","New York","May","Actual","756","302"
"100-10","New York","May","Budget","710","280"
"100-10","New York","Jun","Actual","890","356"
"100-10","New York","Jun","Budget","840","340"

Header Records and Dimension Build Rules

Header records are not useful for dimension build rules. To design a dimension build rule that skips the header record,

1. Upload a text file like the one above to the Sample Basic cube directory.

2. On the Applications page, expand the application (Sample).

3. From the Actions menu, to the right of the cube name (Basic), launch the inspector.

4. Select the Scripts tab, and then click Rules.

5. Click Create and choose Dimension Build (Regular).

6. Choose any name for the rule.

7. For Source Type, select File, and for File, navigate to the source data file.

8. Increment Header Record Number to 1.

   
   
   
   
   

9. Preview the data and click Proceed.

   The header record is omitted from the preview.

   
   
   
   
   

Header Records and Data Load Rules

Header records can be used or skipped in data load rules. To design a data load rule that incorporates the header record,

1. Complete steps 1-4 in the previous procedure.

2. Click Create and choose Data Load.

3. Choose any name for the rule.

4. For Source Type, select File, and for File, navigate to the source data file.

5. Increment Data Load Record Number to 1.

   
   
   
   
   

6. Preview the data and click Proceed.

   The header information is incorporated into the field properties, in your preview.