Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter describes how to edit a rules file to perform operations on records, fields, and data before loading the database. For a comprehensive discussion of data sources and rules files, see Understanding Data Loading and Dimension Building. For a comprehensive discussion of the process of creating a rules file, see Creating Rules Files.
This chapter contains the following sections about record operations:
This chapter contains the following sections about field operations:
This chapter contains the following sections about data operations:
You can perform operations at the record level. For example, you can reject certain records before they are loaded into the database.
This section contains the following sections:
You can specify which records Analytic Services 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 before Analytic Services loads the record. If a field or fields in the record do not meet the selection criteria, Analytic Services does not load the record. You can define one or more selection criteria. For example, to load only 2003 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 2003.
To select a record, see "Selecting Records" in the Essbase Administration Services Online Help.
Note: If you define selection criteria on more than one field, you can specify how Analytic Services combines the criteria. For a brief discussion, see Combining Multiple Select and Reject Criteria.
You can specify which records Analytic Services ignores by setting rejection criteria. Rejection criteria are string and number conditions that, when met by one or more fields within a record, cause Analytic Services to reject the record. You can define one or more rejection criteria. If no field in the record meets the rejection criteria, Analytic Services 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.
To reject a record, see "Rejecting Records" in the Essbase Administration Services Online Help.
Note: If you define rejection criteria on more than one field, you can specify how Analytic Services should combine the criteria. For a brief discussion, see Combining Multiple Select and Reject Criteria.
When you define select and reject criteria on multiple fields, you can specify how Analytic Services combines the rules across fields, that is, 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 from the Boolean group, the fields must match only one of the criteria. The global Boolean setting applies to all select or reject operations in the rules file, for both data load and dimension build fields.
Note: If selection and rejection criteria apply to the same record (that is, you try to select and reject the same record), the record is rejected.
To determine how to combine select and reject criteria on multiple fields, see "Combining Selection and Rejection Criteria" in the Essbase Administration Services Online Help.
You can specify the number of records that Analytic Services displays in Data Prep Editor. You can also specify the first record in Data Prep Editor. Analytic Services skips all preceding records and, in Data Preparation Editor, begin the display with the record number you chose as first. For example, if you enter 5
as the starting record, Analytic Services does not display records 1 through 4.
Note: Analytic Services treats header records the same as data records when counting the records to skip.
To set the records displayed, see "Setting the Records Displayed" in the Essbase Administration Services Online Help.
Data sources can contain data records and header records. Data records contain member fields and data fields. Header records describe the contents of the data source and describe how to load values from the data source to the database.
Rules files contain records that translate the data of the data source to map it to the database. As part of that information, rules files can also contain header records. For example, the Sample Basic database has a dimension for Year. If several data sources arrive with monthly numbers from different regions, the month itself might not be specified in the data sources. You must set header information to specify the month.
You can create a header record using either of the following methods:
When you add one or more headers to the data source, you must also specify the location of the headers in the data source in the rules file. The rules file then tells Analytic Services to read the header information as a header record and not a data record. 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 a header in the rules file, see "Setting Headers in the Rules File" in the Essbase Administration Services Online Help.
To define a header in the data source, see "Setting Headers in the Data Source" in the Essbase Administration Services Online Help.
You can dynamically build dimensions by adding header information to the top record of the data source and by specifying the location of the header record in the rules file.
Figure 105 contains an example of a header record.
The header record lists field definitions for each field. The field definition includes the field type, the field number, and the dimension name into which to load the fields. The format of a header record is illustrated in Figure 106:
Figure 106: Header Record with Three Field Definitions
If the file delimiter is a comma, enclose each field definition in quotation marks (" ").
After you set the header information in the data source, you must specify the location of the header information in the rules file. If a rules file refers to header information in a data source, Analytic Services uses the information in the data source-rather than the information in the rules file-to determine field types and dimensions.
Valid field types must be in capital letters and are as follows:
For each field type that you set, you must also enter a field number. When the field type is the name of an attribute dimension, the field number cannot be greater than 9. For a brief discussion and references to pertinent topics, see Setting Field Type Information.
You can perform operations at the field level, for example, moving a field to a new position in the record.
This section contains the following sections:
You can ignore all fields of a specified column of the data source. The fields still exist in the data source, but they are not loaded into the Analytic Services database.
If the data source contains fields that you do not want to load into the database, tell Analytic Services to ignore those fields. For example, the Sample Basic database has five standard dimensions: Year, Product, Market, Measures, and Scenario. If the data source has an extra field, such as Salesperson, that is not a member of any dimension, ignore the Salesperson field.
To ignore all fields in a column, see "Ignoring Fields" in the Essbase Administration Services Online Help.
You can ignore any field in the data 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 data source, not just in a particular column. Consider, for example, a data 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 all instances of a string, see "Ignoring Fields Based on String Matches" in the Essbase Administration Services Online Help.
You can set the order of the fields in the rules file to be different from the order of the fields in the data source.The data source is unchanged. The following sections describe:
Note: To undo a single operation, select Edit > Undo. To undo one or more field operations, see "Undoing Field Operations" in the Essbase Administration Services Online Help.
You can move fields to a different location using a rules file. For example, a field might be the first field in the data source, but you want to move it to be the third field during the data load or dimension build.
In some instances, moved fields may appear to merge. Merging may occur if the data file has a structure similar to the following:
1<tab>2<tab>3 1<tab>2<tab>(null)
If you move a field that contains empty cells and the moved field becomes the last field in the record, the field may merge with the field to its left.
To prevent merging, replace the empty cell with a delimiter.
To move fields, see "Moving Fields" in the Essbase Administration Services Online Help.
Note: To undo a move, see "Undoing Field Operations" in the Essbase Administration Services Online Help.
You can join multiple fields into one field. The new field is given the name of the first field in the join. For example, if you receive a data source with separate fields for product number (100) and product family (-10), you must join the fields (100-10) before you load them into the Sample Basic database.
Before you join fields, 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" in the Essbase Administration Services Online Help.
To join fields, see "Joining Fields" in the Essbase Administration Services Online Help.
Note: To undo a join, see "Undoing Field Operations" in the Essbase Administration Services Online Help.
You can join two or more fields by placing the joined fields into a new field. This procedure leaves the original fields intact. Creating a new field is useful if you need to concatenate fields of the data source to create a member.
For example, if you receive a data source with separate fields for product number (100) and product family (-10), you must join the fields (100-10) before you load them into the Sample Basic database. But suppose that you want the 100 and -10 fields to exist in the data source after the join; that is, you want the data source to contain three fields: 100, -10, and 100-10. To do this, create the new field using a join.
Before you join fields, 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.
To create a new field by joining existing fields, see "Creating a New Field Using Joins" in the Essbase Administration Services Online Help.
Note: To undo a creating using join operation, see "Undoing Field Operations" in the Essbase Administration Services Online Help.
You can create a copy of a field while leaving the original field intact. For example, assume that, during a single dimension build, you want to define a multilevel attribute dimension and associate attributes with members of a base dimension. To accomplish this task, you need to copy some of the fields. For more information about attribute dimensions, see Working with Multilevel Attribute Dimensions.
To copy a field, select one field and then create a new field using a join.
To create a new field by joining existing fields, see "Creating a New Field Using Joins" in the Essbase Administration Services Online Help.
Note: To undo a copy, see "Undoing Field Operations" in the Essbase Administration Services Online Help.
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 can split the UPC out of the field and ignore it. Then only 100-10-1, that is, the product number, is loaded. To ignore a field, see Ignoring Fields.
To split a field, see "Splitting Fields" in the Essbase Administration Services Online Help.
Note: To undo a split, see "Undoing Field Operations" in the Essbase Administration Services Online Help.
You can create a text field between two existing fields. You might create a text field to insert text between fields that are to be joined. For example, if you have two fields, one containing 100 and one containing 10-1, you can insert a text field with a dash between the two fields and then join the three fields to create the 100-10-1 member of the Product dimension.
To create a new field and populate it with text, see "Creating a New Field Using Text" in the Essbase Administration Services Online Help.
Note: To undo a field you created using text, see "Undoing Field Operations" in the Essbase Administration Services Online Help.
You can undo the last field operation that you performed, such as move, split, join, create using text, or create using join by 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 field operations from the last operation to the first operation.
To undo one or more field operations, see "Undoing Field Operations" in the Essbase Administration Services Online Help.
This section applies to data load only. If you are performing a dimension build, skip this section.
You use a rules file to map data source fields to Analytic Services member names during a data load. You can map fields in a data source directly to fields in the Analytic Services database during a data load by specifying which field in the data source maps to which member or member combination in the Analytic Services database. The data source is not changed.
Note: When you open a SQL data source, the fields default to the SQL data source column names. If the SQL column names and the Analytic Services dimension names are the same, you do not have to map the column names.
To map fields, see "Mapping Field Names" in the Essbase Administration Services Online Help.
To load a data source, you must specify how the fields of the data source map to the dimensions and members of the database. Rules files can translate fields of the data source so that the fields match member names each time the data source is loaded. This process does not change the data source. The rules file does the following:
This section contains the following sections that describe how to change field names in the data source to map members and data values to the database.
You can replace text strings so that the fields map to Analytic Services 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 during the data load or the dimension build.
For instructions on how to replace an empty field with text, see Replacing an Empty Field with Text.
To replace a text string, see "Replacing Field Names" in the Essbase Administration Services Online Help.
You may want to replace empty fields in a column with text. If, for example, empty fields in the column represent default values, you can insert the default values or insert #MI
to represent missing values.
To replace an empty field with text, see "Replacing an Empty Field with Text" in Essbase Administration Services Online Help.
You can change the case of a field so the field maps to Analytic Services member names during a data load or dimension build. 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, JAN to jan.
To change the case of values in a field, see "Changing Case of Fields" in the Essbase Administration Services Online Help.
You can drop leading and trailing spaces from around fields of the data source. 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.
By default, Analytic Services drops leading and trailing spaces.
To drop spaces around a field, see "Dropping Spaces Around Fields" in the Essbase Administration Services Online Help.
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 change spaces to underscores, see "Converting Spaces to Underscores" in the Essbase Administration Services Online Help.
You can add prefixes and suffixes to each field value of the data source. For example, you can add 2002 as the prefix to all member names in the Year dimension.
To prefix or suffix values to a field, see "Adding Prefixes and Suffixes" in the Essbase Administration Services Online Help.
This section applies to data load only. If you are performing a dimension build, skip this section.
You can perform operations on the data in a field, for example, moving a field to a new position in the record.
This section contains the following sections:
This section applies to data load only. If you are performing a dimension build, skip this section.
If each record in the data source contains a column for every dimension and one data column, you must define the data column as a data field. In Figure 107, for example, the column with the data values must be defined as a data field.
Market, Product, Year, Measures, Scenario Texas 100-10 Jan Sales Actual 42 Texas 100-20 Jan Sales Actual 82 Texas 100-10 Jan Sales Actual 37
You can define only one field in a record as a data field.
To define a data field, see "Defining a Column as a Data Field" in the Essbase Administration Services Online Help.
This section is for data load only. If you are performing a dimension build, skip this section.
By default, Analytic Services overwrites the existing values of the database with the values of the data source, but you can determine how newly loaded data values affect existing data values.
You can use incoming data values to add to or subtract from existing database values. For example, if you load weekly values, you can add them to create monthly values in the database.
Using this option makes it more difficult to recover if the database crashes while loading data, although Analytic Services lists the number of the last row committed in the application log. For a discussion of the application log, see Contents of the Application Log.
To prevent difficult recoveries if you are adding to or subtracting from existing data values and the database shuts down abnormally, as a Database Transaction setting, set the Commit Row value as 0. This setting causes Analytic Services to view the entire load as a single transaction and to commit the data only when the load is complete. For more information, see Understanding Isolation Levels.
To add to existing data values, see "Adding to Data Values" in the Essbase Administration Services Online Help.
To subtract from existing data values, see "Subtracting from Data Values" in the Essbase Administration Services Online Help.
This section is for data load only. If you are performing a dimension build, skip this section.
You can clear existing data values from the database before you load new values. By default, Analytic Services overwrites the existing values of the database with the new values of the data source. If you are adding and subtracting data values, however, Analytic Services adds or subtracts the new data values to and from the existing values.
Before adding or subtracting new values, make sure that the existing values are correct. Before loading the first set of values into the database, you must make sure that there is no existing value.
For example, assume that the Sales figures for January are calculated by adding the values for each week in January:
January Sales = Week 1 Sales + Week 2 Sales + Week 3 Sales + Week 4 Sales
When you load Week 1 Sales, clear the database value for January Monthly Sales. If there is an existing value, Analytic Services 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 contains data for January, February, and March and you want to load only the March data, you can clear the January and February data.
Note: If you are using transparent partitions, clear the values using the steps that you use to clear data from a local database.
To clear existing values, see "Clearing Existing Data Values" in the Essbase Administration Services Online Help.
This section is for data load only. If you are performing a dimension build, skip this section.
You can scale data values if the values of the data source are not in the same scale as the values of the database.
For example, assume the real value of sales was $5,460. If the Sales data source tracks the values in hundreds, the value is 54.6. If the Analytic Services database tracks the real value, you need to multiply the value coming in from the Sales data source (54.6) by 100 to have the value display correctly in the Analytic Services database (as 5460).
To scale data values, see "Scaling Data Values" in the Essbase Administration Services Online Help.
This section is for data load only. If you are performing a dimension build, skip this section.
You can reverse or flip the value of a data field by flipping its sign. Sign flips are based on the UDAs (user-defined attributes) of the outline. When loading data into the accounts dimension, for example, you can specify that any record whose accounts member has a UDA of Expense change from a plus sign to a minus sign. See Creating UDAs for more information on user-defined attributes.
To reverse a field sign, see "Flipping Signs" in the Essbase Administration Services Online Help.
![]() |