To create an Essbase Rules Files, you have the following options
– Expand the Application >> Expand the Database >> Right Click on ‘Rules Files’ >> Select ‘Create Rules Files’ (Recommended Approach)
– Using your toolbar, you can select: File >> New >> Scripts >> Rules Files >> OK (Not Recommended Approach, see note 14 – Validate)
Once the rules file is open, you’re toolbar will appears as below:
1. New/Open/Save/Print/Send Email – Pretty generic here, but if you need help, let me know.
2. Move Field – Moves any column to a new column (kind of like re-ordering your columns). This option is useful when creating rules that require you to sort columns in order to make for the rule file to load successful. For example, you are creating a dimension build rules file using parent-child references. Say you have 3 columns of data coming in from your source, column 1 = child, column 2 = parent and column 3 = alias. You have two options here, reorder your columns from the source (recommended option, but not always possible) or you can use this option to reorder you columns to Parent, Child, Alias.
3. Split Field – Like most of the titles for these buttons, they do what their titles says. This one will split a given field into two fields. It does NOT split the field based on a delimiter like “|” but rather after so many characters. So if you always want to split the field based on the 3rd character, for instance, then this is your tool, but if you want to split the field based on a “|” then you will need to modify your source data. Keep in mind, you do have the ability to split a data feed by a normal delimiter, but they will be for all columns. See note 11 for more details on adding a delimiter to your data source.
4. Join Field – Joins 2 or more fields into one field (Add more fields by using the holding down the Ctrl key). Please note, when using this option, if you join two fields, they will become 1 field. The original independent columns will no longer exist. If you would to have the independent fields remain intact after the join, see note 5 for “Create a Field Using Join”
5. Create a Field Using Join – Creates a new field when using a join. Same idea as #4, but when you create a new field using 2 columns, the 2 columns remain intact after the field is created. Both 4 and 5 have their advantages, it just depends on your data and what you are trying to accomplish. If you need the original fields in addition to the new one, use option 5 (Create a Field Using Join), but if you do not need the original fields after the join, use option 4 (Join Field).
6. Create a Field Using Text – Adds a new column of whatever text you input. I’ve seen beginners use this option add a text field during a data load where a given dimension was missing from the data source. For instance, in the Essbase Sample.Basic database, you perform a data load but you do not have a column for Measures (highly unlikely, but this is just an example). So you can add a columns and add the text “Misc” to load to the Misc member name. The advance way to do this is detailed in Note 12 (Data Load Settings).
7. Field Properties – Please click here to see a detailed description of the Field Properties option.
8. Select Record / Reject Records – Within a given column, this will give you the option select or reject certain records. I am not a big fan of these options as it is always better to have the appropriate data coming from the source.
9. Data Load Fields / Dimension Build Fields – These two options will toggle the view of your load rule (between the Data Load view and Dimension Build View). I recommend when building a data load rule or a dimension build, make sure the right button is toggled appropriately. I’ve see beginners struggle with dimension builds since they were unaware of this option.
10. Associate Outline – When build a rules file, it is extremely helpful to associate the outline in which you are building a rules file for. This is not necessary, but you will be unable to validate your rules file without performing this operation (see note 14 – Validate, for more details)
11. Data Source Properties – The Data Source has four tabs:
– Delimiter – As mentioned in Note 3 (Split Field), this works like any other delimiter that splits a file or data source by a specified delimiter
– Field Edits – Lists all edits that have been made to this load rule (held in order of creation). For example, if you have 5 field edits, you can not delete #3 without deleting #’s 4 and 5 (since #’s 4 or 5 might be dependant on #3)
– Header – You can use this option to skip the first line of a data source (usually the header record) or if you data source is set up properly, you can use it as the data load field names or dimension build field name.
– Ignore Tokens – Here you can set up certain tokens to be ignored.
12. Data Load Settings – Please click here to see a detailed description of the Data Load Settings option.
13. Dimension Build Settings – Please click here to see a detailed description of the Dimension Build Settings option.
14. Validate – As mentioned in note 10 (Associate Outline), in order to properly validate a rules file, an outline needs to be associated with the rules file. If you created your rules from the File menu, then Associating an outline is necessary, however, if you navigated Application >> Database >> Rules Files >> Create Rule File, then the outline is already associated.