best practices for your member naming convention

In “design guidelines” by Andrea Leandri

Whether on premise or on cloud HPCM, PBCS or Hyperion Planning need to obey the naming standards set by the Essbase calculation engine that sits underneath them.

In this blog post I will explain what the golden rules are and top them up with extra best practices, to give you an overview of the do’s and don’ts when it comes down to naming your dimensional members. Generally, this will ensure a smooth application deployment and maximum compatibility with whatever other product you use from the Oracle stack.

Remember even when these practices do not impact your current situation as they relate to products or combinations of products not in use an advantage of following them will be that you will not run into unnecessary troubles should that situation change.

The following are based on Non-Unicode applications. Unicode applications have either the same or more forgiving limits.

ID Description
1.         Member name cannot be longer than 80 characters.
2.         Alias (= description of member) also cannot be longer than 80 characters. Luckily alias tables can be several, e.g. one per reporting language.
3.         It’s best practice to not use duplicate member names. When referring to those later you will have to specify also the node they belong to (imagine a string of characters of 80+80 = 160 in your code for a member, nice and easy to read ?!?)
4.         Therefore, following form point 3, member names and alias names must be unique across ALL dimensions
5.         Case sensitivity is not enabled by default therefore names are NOT case sensitive. And this is great when working on Smartview in Ad-hoc mode!
6.         It’s a must NOT to use double quotations, “, or tabs anywhere. The consequences for calculations, data files, and reports are disastrous
7.         Strongly avoid special characters, I know you have heard this one before, but here is my twist: should you really want them, up to you, but DO not place them at the beginning of a member name or alias. You will not enjoy making smartview reports later.
8.         Do not use spaces at the beginning or end of member name or alias. They are going to turn into hidden traps and you will stare at your code for hours not understanding why it’s not working.
9.         In fact avoid using only numbers as well in member names or alias name. Why? Well, Excel will interpret them as numbers rather than text and quickly apply formatting you don’t need or will stop Smartview from recognizing them as dimensionality! So… if your accounts are picking from the general ledger where they are called “0123456789”, just add a prefix like “A” or “AC” and make them “AC123456789”. Similarly for your product dimension (add a “P”) or entity dimension (add “ENT”) where I have seen long digits are mostly used.
10.     Essbase has a set of formulas and functions that can be used in calculation manager. See the Technical reference (the link refers to 11.1.2.4). I strongly suggest you avoid using formula names as members names or alias names. By the way the technical reference contains a ton of good information and examples for script’s formulas and functions, bookmarking it will help you a lot in your calculation’s development.
11.     Essbase has also a list of software specific (reserved) words, like ALL, DIM, CALC, MEMBERNAME, #MI,…See the Database Administrator Guide (the link refers to 11.1.2.4) for further information. Avoid reserved words as well in the member name or alias name.

 

-----
about the author
mm

Andrea Leandri

LinkedIn

I enjoy solving customer puzzles in Oracle's Planning & Budgeting projects. When everything comes together, it feels like taming a wild beast really. I have learned the do’s and don’ts thanks to a lot of practice and blogs like this one, well not exactly like this one ;-). In the process I cannot deny to have had my share of mistakes. By sharing my knowledge with you my goal is to save your time and resources. Enjoy!

share this post