The following member specification rules apply to databases with duplicate member names enabled.
Note: | These rules are also applicable if you need to use MDX to explicitly reference shared member names in a unique member name outline (an outline with duplicate member names NOT enabled). See the “Shared Member Names Example” in this topic. |
Qualified names must be used to specify duplicate member names. Qualified member or alias names can be specified using:
Fully qualified member names—Consist of duplicate member or alias name and all ancestors up to and including the dimension name. Each name must be enclosed in square brackets([]) and separated by a period.
[DimensionMember].[Ancestors...].[DuplicateMember]
For example:
[Product].[100].[100-10]
Shortcut qualified member names—Essbase internally constructs shortcut qualified names for members in duplicate member outlines. These can be inserted into scripts using Administration Services by right clicking on the member and selecting Insert member name. You can also manually insert shortcut qualified names into scripts, spreadsheets, or MDX queries.
Essbase uses the following syntax to construct shortcut qualified names. Using the same syntax that Essbase uses when you reference members in scripts, spreadsheets, and MDX queries is optimal, but not required.
Scenario | Qualified Name Syntax | Example |
---|---|---|
Duplicate member names exist at generation 2 | [DimensionMember].[DuplicateMember] | [Year].[Jan] or [Product].[Jan] |
Duplicate member names exist in an outline, but are unique within a dimension | [DimensionMember]@[DuplicateMember] | [Year]@[Jan] |
Duplicate member names have a unique parent | [ParentMember].[DuplicateMember] | [East].[New York] |
Duplicate member names exist at generation 3 | [DimensionMember].[ParentMember].[DuplicateMember] | [Products].[Personal Electronics].[Televisions] |
Duplicate member names exist at a named generation or level, and the member is unique at its generation or level | [DimensionMember]@[GenLevelName]|[DuplicateMember] | [2006]@[Gen1]|[Jan] |
In MDX, either one the following syntax methods must be used to reference shortcut qualified member names:
Escape Character method—Because MDX syntax also uses square brackets:
Any internal closing bracket ( ] )used by name parts within the shortcut qualified names requires an additional ] escape character.
The entire shortcut qualified member name must be enclosed in a set of square brackets ([]).
Examples:
[Year].[Jan] is referenced as [[Year]].[Jan]]] in MDX.
[Year]@[Jan] is referenced as [[Year]]@[Jan]]] in MDX.
[2006]@[Gen1]|[Jan] is referenced as [[2006]]@[Gen1]]|[Jan]]] in MDX.
Note: | The above syntax also works for fully qualified member names, but is not required. |
StrToMbr Function method—You can use the StrToMbr function to convert qualified name strings to member value expressions.
Examples:
[Year].[Jan] is referenced as StrToMbr("[Year].[Jan]") in MDX.
[Year]@[Jan] is referenced as StrToMbr("[Year]@[Jan]") in MDX.
[2006]@[Gen1]|[Jan] is referenced as StrToMbr("[2006]@[Gen1]|[Jan]") in MDX.
Note: | The above syntax also works for fully qualified member names, but is not required. |
Duplicate Member Names Query Example
The following query uses both methods of referencing shortcut member names in MDX:
SELECT { Sales, Profit } ON COLUMNS, {[[Store]]@[6]]], StrToMbr("Product.SKU.1")} ON ROWS FROM MySample.Basic WHERE ([[1998]].[Q1]].[1]]])
Note: | StrToMbr accepts any type of member-identifier strings: names, aliases or qualified names. |
The following example applies to a unique member name outline that contains shared members.
In the Sample Basic database, the member [100-20] is an original member under parent [100], and has a shared member associated with it under parent [Diet]. The shared member [100-20] can be referred to explicitly, using the unique name [Diet].[100-20], as shown in the following query:
SELECT {Sales} ON COLUMNS, {[[Diet]].[100-20]]]} PROPERTIES MEMBER_UNIQUE_NAME ON ROWS FROM Sample.Basic;