I was having a discussion with a co-worker recently about EPM in general, how it has really matured the last several years, connected planning, all the cool features, etc. The conversation turned to metadata and he made the statement that EPM can have duplicate member names. We bantered back and forth a bit, and while technically he is correct, I thought it might be a good idea to dig into it a bit more.
Let’s start with whether it is possible to have duplicate member names. Within the same dimension, it is not possible, you will get validation errors. However, across dimensions it is possible to have the same member name. This is possible because behind the scenes, Essbase identifies members by a combination of dimension and member. So, should you use the same member name? No, you should not. It would not be leading practice, in my opinion, to use the same member name across different dimensions.
If you have a use case that justifies using the same member name across different dimensions, then keep in mind the following:
- Business Rules and Calculations (scripts, Groovy, MDX)
- Members can become unclear when reading the code, the reader may not know which dimension is being referenced by the name.
- Always qualify members, use cross-dims and/or comments to make it clear.
- Data Integrations
- When loading data, duplicate names across dimensions can lead to mapping confusion. This is especially true if the source columns in files are poorly labeled.
- Use explicit dimension mapping, do not rely on positional assumptions.
- Reporting (Narrative, Smart View)
- Users may become confused when looking at a report, for example, does “Marketing” refer to Entity or Department? This may not seem important, but let’s say the user wants to see data for the Marketing department. If they do not query the department and instead query the Marketing entity, the result can be considerably different.
To summarize, avoid using duplicate member names in your EPM applications. While technically possible to have the same name across dimensions, it is better not to do so. This also applies to member alias’, it can be done across dimensions, but you really should not do it.
As always, happy EPM’ng
Random EPM Notes 