Duplicate Member Names in Outlines
When you enable duplicate member names in an Essbase outline, multiple members can have the same name. You can use duplicate names only if the outline has duplicate members enabled.
About Duplicate Member Name Outlines
Duplicate member names are available for block storage and aggregate storage databases. You create duplicate members in the same way that you add members for a unique member name outline. See Naming Conventions for Dimensions, Members, and Aliases.
When you save the duplicate member name outline, Essbase validates the duplicate member names. A qualified naming syntax helps you differentiate the duplicates from one another.
Figure 4-2 shows an example of a duplicate member outline in which the New York state member and the New York city member appear in the outline as New York.
Figure 4-2 Duplicate Member Name “New York”
The qualified member names for the example in Figure 4-2 are [State].[New York] and [City].[New York].
To create an outline that enables duplicate member names, use the outline properties in the Essbase web interface, or, create the cube using the create database MaxL statement with the using non_unique_members clause. Or, if you build the cube from an application workbook, on the Cube.Settings tab, set the Outline Type property to Duplicate.
Note:
A duplicate member outline cannot be converted back to a unique member outline.
Refer also to About Duplicate Member Names.
Note:
Duplicate member outline attribute dimensions do not have prefixes or suffixes attached that apply to attribute dimensions in unique outlines. For example, in a duplicate member Boolean attribute dimension, members do not include dimension, parent, grandparent, or ancestors affixed to the TRUE and FALSE members. See Set Prefix and Suffix Formats for Member Names of Attribute Dimensions.
Restrictions for Duplicate Member Names and Aliases in Outlines
When creating duplicate member names and aliases in database outlines, the following must always be unique:
-
Dimension names
-
Generation names and level names
-
Siblings under a parent member
If you are using aliases, this additional restriction applies: an alias table that contains duplicate alias names is valid only with a duplicate member outline.
Note:
Do not use quotation marks (" "), brackets ([ ]), or tabs in member, dimension, or alias names. For example, you cannot create a member name “[New York].[Area 1]”. Outline verification does not display an error for member names that contain the invalid sequence of characters, and you can save the outline; however, Essbase cannot accurately query the data.
Syntax for Specifying Duplicate Member Names and Aliases
Although duplicate member names appear in the outline, each nonshared member name uniquely identifies a member in the database. A qualified name format differentiates the duplicate member names. A qualified name must be used to specify a duplicate member name.
A qualified member or alias name can be specified in any of the following formats:
-
Fully qualified member name
-
Member name qualified by differentiating ancestor
-
Shortcut qualified member name
Note:
A qualified name must comprise all alias names or all member names. You cannot mix member names and alias names in a qualified name.
Fully Qualified Member Names
A fully qualified member name comprises the duplicate member or alias name and all ancestors up to and including the dimension name. Each name must be enclosed in brackets ([ ]) and separated by a period (.). The syntax is as follows:
[DimensionMember].[Ancestors...].[DuplicateMember]For example:
[Market].[East].[State].[New York]
[Market].[East].[City].[New York]Members Qualified by Differentiating Ancestor
A member name qualified by differentiating ancestor uses the member or alias name and all ancestors up to and including the ancestor that uniquely identifies the duplicate member or alias. The top ancestor in the path will always be a unique member name. Each name must be enclosed in brackets ([ ]) and separated by a period (.). The syntax is as follows:
[DifferentiatingAncestor].[Ancestors...].[DuplicateMember]For example:
[State].[New York]
[City].[New York]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, spreadsheets, or MDX queries.
Essbase uses the syntax shown below 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.
Table 4-1 Shortcut Qualified Name Syntax
| Scenario | Qualified Name Syntax | Example |
|---|---|---|
|
Duplicate member names exist at generation 2 |
|
|
|
Duplicate member names exist in an outline but are unique within a dimension |
|
|
|
Duplicate member names have a unique parent |
|
|
|
Duplicate member names exist at generation 3 |
|
|
|
Duplicate member names exist at a named generation or level, and the member is unique at its generation or level |
|
|
|
In some scenarios, the differentiating ancestor method is used as a shortcut. |
|
|
Qualified Member Names in Unique Member Name Outlines
Qualified member names are also applicable for referencing member names in a unique member name outline (an outline with duplicate member names not enabled). Qualified member names can be used to differentiate shared members from their original members.
For example, 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;The double closing brackets in the example are needed as escape characters. For more details about the MDX syntax, see MDX Syntax for Specifying Duplicate Member Names and Aliases.