OracleBIBlog Search

Tuesday, September 1, 2009

Essbase and Use of Duplicate Members

I just returned from a client that was using a relatively new Essbase feature called "duplicate members". The business use of duplicate members is that it provides unique cube data when people use the same name to mean different things. You say "tomato" and I say "tomato", that sort of thing. Now a tomato can be either a fruit or a vegetable. Pretty cool!

This feature became available beginning with Essbase System 9 and continues on in 11x. Do not confuse this with "shared member rollups", which share the same data value in a different part of the outline. Duplicate members allow the use of the same member name in an outline, but the values are unique, not shared.

Here is an example of duplicate members. A user defines "New York" as both a state and a city in the outline but each has a unique value because they reference two different things.

Geography
--- State
------- New York
------- Pennsylvania
------- ...
--- City
------- New York
------- Philadelphia
------- ...

You must enable the duplicate members option in the outline to use this feature. Then create a qualified member name in the outline. A qualified member name consists of the duplicate member or alias name and all ancestors up to and including the dimension name. To create uniqueness, each name must be enclosed in square brackets([]) and separated by a period (.).

For instance, the qualified outline member names would be:
[State].[New York].
[City].[New York].

This is a brief introduction to this valuable feature. Refer to the Essbase Database Administrators Guide for specific details on how to create duplicate members. Here is the link: http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_dbag/frameset.htm?dotdimb.htm

2 comments:

tisco said...

Did you encounter many issues enabling this feature?

Did FR or Smartview (if you used them) present any problems on retrieving?

Great entry, thx!

Mariano

Doug Burke said...

Mariano,

Enabling the duplicate members feature is very quick. Refer to Chapter 8 (starting on page 125) of the Essbase Database Administrators Guide (DBAG) for details. Here is a link to the Essbase System 9 DBAG:

http://download.oracle.com/docs/cd/E10530_01/doc/nav/portal_3.htm

Financial Reporting (FR) was able to reference duplicate members without problems. In fact, duplicate members with FR saved the day for the client. There was no other way to get the desired result.

I did not try Smartview but would not expect any issues. The user would just need to make sure they selected the correct outline member.

Regards,

Doug