For the Field Properties portion of an Essbase Load Rule, there are three tabs (Global Properties, Data Load Properties, and Dimension Build Properties). Global Properties affect both Data Load and Dimension Build rules, where as Data Load Properties and Dimension Build Properties are specific for each. Get used to this menu option, you will use it quite often.
1. This section deals with whether the data is to be applied in its Original Case, Lowercase or Uppercase. Original is the default. You can add a Prefix (leading text) or a Suffix (ending text). You can Drop lead/trailing spaces. Keep in mind, adding a prefix/suffix with impact all members in a given dimension, not just one specific member. That’s where #2 comes into play.
2. Instead of changing all members within a given dimension with a prefix/suffix, you can specify one member to be modified with the ‘Replace/With’ section. Be careful with this, if some members names are partial to other member names (East is a part of Northeast), so you don’t want to change all East to “(E) East” because it will turn Northeast into “North(E) East”, which is obviously what you don’t want. Therefore, make sure you check the Match Whole Word option.
Data Load properties:
3. For your data load rules, you will use this section to specify a columns association to a dimension, you can manually enter the dimension name or, the easier way, double click the dimension name and it will populate the Field Name box. Keep in mind, any dimension that contains a space, quotes will be applied to the dimension name in the Field Name box (this is normal). Also, it is not always mandatory to use the dimension name, you may have 12 data columns which you would specify as “Jan”, “Feb”, “Mar”, “Apr”, etc instead of putting in “Year”.
4. You can specify if a column is a data field or if you would like to ignore this column. You would want to ignore a column when your data is coming in with excess columns that is not necessary for this load rule. The scale option allows you take a number, say 1,000,000 and put a scale of 0.001. This will automatically change you data from 1,000,000 to 1,000. This comes in hand when you have an Millions/Thousands/Dollars member names…simply perform this pseudo calculation on the way in as opposed to changing it in you data source.
5. You will cycle through each column (usually starting from left to right) specifying the dimension it is associated with or whether to “Ignore field during dimension build” (#7). Simply double click the dimension name to apply it to the Dimension field at the top.
6. For the given column that you are in, you will first select a dimension (see Note 5), then you will select its association to the outline. For instance, you can build your dimension via Generation References, Level References or Parent-Child references. Generations are a top-down approach, where you database name is Generation 0, the dimension names are Generation 1 and so on. Level References are just the opposite, starting from the leaf level (or lowest possible level) and working up from 0 to the database name. Each has its pros and cons based on your data because you may not always have the same amount of generations or levels, so it might be tricky to build your rules file. The recommended approach would be to utilize the Parent-child reference, because no matter how many generations or levels you have, you will always have a Parent-Child relationship. No matter which method you choose, you need to associate a number to it, I’ve already discussed the generation and level numbers you would use, so for parent-child references, please use 0. Keep in mind, in this section you have the ability to not only assigned a member name, but its Alias (description), UDA (user defined attribute – which can be used in security filters and partitions), and its Property (i.e., + for aggregation, ~ to ignore or – for subtraction).
7. This option simply ignores the column during the dimension build