The beginning of winter brought with it some exciting rides into the world of Oracle Data Integrator and Essbase. Feels lovely when things start to unravel their beauty and prowess. After several sessions (more to come!) of brainstorming on Shared and Duplicate members, we came to a few interesting findings that can help us plan better.
The “Duplicate” members are providing the below benefits:
- Allows same member to be used multiple times across altogether different dimensions. This is different from a “Shared” member where members need to be part of the same dimension.
- Besides the dimensions where we need Duplicate members, we can explicitly mark other dimensions as Unique which need not contain Duplicate members, thus ensuring integrity.
One drawback being once the outline is marked as enabled for Duplicate member we will be unable to rollback this setting!
However, unlike a “Shared” member, some of the inconveniences that are caused by “Duplicate” members are:
- To get clarity on the dimension used, Smart View users will now have to select either the fully qualified or ancestor-driven qualified name to ensure the correct member is selected. This defeats the aesthetics to an extent. Like [Parent].[Member].
- Existing calculations if used referring these members will need to be revisited to ensure they refer the correct required member from the corresponding dimension.
- The KM “SQL to Hyperion Essbase (METADATA)” doesn’t seem to like allow loading the duplicate alias for the duplicate member. The second dimension loaded latter suffers, the first dimension gets loaded fine. The first gets all the alias fine, the next run doesn’t get any alias for the members in the second dimension.
Considering all the above, the most effective solution now becomes a “new dimension”:
- We are going to compromise on space and performance as a drawback for this
- But now, we can have our distinct prefixing and naming convention for each member to make them identifiable at-a-glance
- Reference of any members in any calculations can stay intact without any ambiguity
- The KM “SQL to Hyperion Essbase (METADATA)” is at its best again