When designing Essbase Cubes, you want to look at how you're building your hierarchies. Your goal should be to create hierarchies that are clean, efficient and easy to use. Aside from streamlining your Essbase Cube, making it perform faster, this will also make life easier for the developers, admins and users who are working with it.
Other developers who build hierarchies should have the goal of eliminating mapping for easier communication. For example, if you're building hierarchies in Oracle and you plan to export these to Essbase, you want to do so according to Essbase guidelines, again, to eliminate the time-consuming process of mapping.
There are eight simple tips Essbase, HFM and other developers can leverage to build solid hierarchies for Essbase or other applications. They are easy to follow and guaranteed to make life easier for everyone.
Always use uppercase for member names. Essbase is case-sensitive. Using a mix of uppercase and lowercase characters may be easy to read, but it could create problems and frustrate users, developers and administrators. Using only uppercase characters for all member names makes it easier for users and developers to learn and use the outline. In contrast, dimension names and aliases should use the proper case. This will distinguish them from member names at a glance. It also looks better on reports and presentations.
The following screenshot shows an example of the use of uppercase and proper case when naming member and dimension names:
No spaces in member names. Essbase allows spaces in member names. However, having spaces in the member names requires the use of double quotes in calculation scripts, Load rules, MaxL scripts, Load files and other back-office maintenance objects. Not using spaces in member names simplifies administrative tasks and programming.
No special characters in member names or member aliases. Essbase does not allow the use of special characters in member names. Many General Ledger (GL) systems do not have the same restrictions. Make sure to review Essbase limitations on naming conventions before building the outline.
Never use numbers as the first characters of a member name. Essbase does not prohibit the use of numbers as the first character in member names. However, using numbers as the first character in member names may create a problem when Excel is being used for ad-hoc analysis. Excel treats member names that contain only numbers as numbers, while Essbase treats member names as text. This may create problems for users. The same thing happens with Essbase variables. Essbase requires the use of an apostrophe (') in order to recognize members that start with numbers in variables. If possible, it is better to use a letter as the first character of a member name. If it is important to show a number as the first character, the number can be displayed in the default alias.
The following show an example of using a letter as the first character in the Years dimension:
Use two aliases: Default = description, Long = member name + description. Essbase allows multiple alias tables. Using two aliases (one for description and one for name + description) makes it easier for the user to learn and navigate the outline. The table shows some examples of the use of Default and Long alias:
Keep in mind that Essbase has a limit of 30 characters for member names and 80 characters for aliases. This limit may cause concatenation of the Long alias.
The following shows an example of the use of the Default alias:
The following shows the same members with the Long alias:
Use acronyms and codes for member names. When creating member names, try to avoid using full words. Using acronyms makes it easier for developers and users to navigate and use the outline. Using acronyms also keeps the member names short. This is important, especially if the outline will have a default and long alias (see previous item). The following table shows some ideas for acronyms:
The following shows an example of naming members in the Scenario dimension:
Use a prefix and suffix in the Measure and Account dimensions. Most GL systems do not require member names to be unique. Some outlines may contain duplicate names. For example:
- Total Marketing can be an account and a department.
- 410000 can be the name of an account, a cost center and a project.
However, Essbase requires each member in the outline to have a unique name and alias. Using prefixes and suffixes ensures that the member name will be unique in the outline. The following table shows examples of prefixes that can be used in Essbase:
It is important to remember that the aliases in Essbase must also be unique throughout the outline. For this reason, the default alias must also contain a special designator that distinguishes it from the other aliases in the database. In the example above, both the Account and Department dimensions have members that refer to the Marketing description. The alias for department 400000 uses the word Department to distinguish it from the account 400000.
Just as prefixes are used to create a unique member name throughout the outline, suffixes can be used to create unique member names throughout the dimension. For example, requirements may call for the Account dimension to have five separate buckets for salaries: the core Salaries account to store activity from the GL, historical data for previous years with less detail, a bucket for eliminations, a bucket for non-Oracle data for entities in the organization that are not on the same GL system and a bucket for high-level planning data. The following table shows how to use suffixes to distinguish the four Salaries accounts:
The following shows an example of the use of suffixes in the Account dimension:
Be consistent. Consistency in naming parents and children makes life easier for users, developers and administrators. Here are a few examples:
- Use the same acronyms (TOT, ALL, NONE, STAT, ELIM, CONV) across dimensions.
- Use the same order for the acronyms across dimensions (e.g., EN_TOT, DP_TOT, PL_TOT instead of EN_TOT, TOT_DP, PL_SUM).
- Keep the same structure for all Measures dimensions (e.g., first child in the hierarchy is always the _TOT parent; all other hierarchies are under a _OH parent, the _NONE member is between the _TOT and _OH members).
The following is an example of an outline that utilizes the above ideas in the Measures dimension:
About the author:
Gidon Albert has over 15 years' experience in successfully managing increased responsibilities in large-scale, integrated and complex Fortune 500 organizations. He is a Certified Project Management Professional and a Certified Hyperion Essbase Analytics Developer.
This was first published in January 2014