MDX Syntax for Specifying Duplicate Member Names and Aliases

Use MDX when you need to identify Essbase member names in a precise way. For example, in databases in which duplicate member names are enabled, MDX helps you reference the qualified member name, enabling unique identification.

The following member specification rules apply to cubes 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.

You can manually insert shortcut qualified names into scripts, Smart View or other grid clients, 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, grid clients, and MDX queries is optimal, but not required.

Table 4-3 Construction of Shortcut Qualified Member Names

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:
1. Any internal closing bracket ( ] )used by name parts within the shortcut qualified names requires an additional ] escape character.
2. 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.

Shared Member Names Example

The following example applies to a unique member name outline that contains shared members.

In the Sample Basic database, the member [100-20] is the referenced 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;