Creating and Working With Duplicate Member Outlines

In This Section:

Creating Duplicate Member Names in Outlines

Restrictions for Duplicate Member Names and Aliases in Outlines

Syntax for Specifying Duplicate Member Names and Aliases

Working with Duplicate Member Names

The information in this chapter applies to block storage and aggregate storage databases.

Also see:

Creating Duplicate Member Names in Outlines

You can use duplicate names in an outline only if the outline has the allow duplicate members option enabled.

When you enable duplicate member names in an outline, Essbase displays multiple members in the outline using the same name. Create the names in the usual way. See Naming Restrictions for Dimensions, Members, and Aliases.

When you save the outline, Essbase validates and saves the outline with the duplicate member names. A qualified name format differentiates the duplicate member names.

Figure 38, Duplicate Member Name “New York” 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 38. Duplicate Member Name “New York”

This image illustrates a duplicate member outline, in which two members use the same name, as described in the text preceding the image.

The qualified member names for the example in Figure 38, Duplicate Member Name “New York” are [State].[New York] and [City].[New York]. See Syntax for Specifying Duplicate Member Names and Aliases.

  To create an outline that enables duplicate member names, or to convert a unique member name outline to a duplicate member name outline, use a tool:

Tool

Topic

Location

Administration Services

Creating Duplicate Member Name Outlines

Oracle Essbase Administration Services Online Help

MaxL

create database

Oracle Essbase Technical Reference

Note:

Save outline changes before converting the outline to a duplicate member name outline. You cannot convert an outline that has unsaved changes. After converting an outline to a duplicate member outline, save it before proceeding with other outline changes. A duplicate member outline cannot be converted back to a unique member outline.

Within a duplicate member outline, you can tag particular dimensions, generations, and levels as unique or duplicate to restrict the use of duplicate member names within a database. Doing so enables you to specify member name uniqueness at a more granular level in a duplicate member outline.

When you create a duplicate member outline, by default, all dimensions in the outline are tagged as duplicate.

When duplicate members are enabled in a dimension, you can tag particular generations or levels within the dimension as unique. If a member is assigned conflicting properties, the unique property takes precedence.

  To enable or disable duplicate member names in a dimension, see “Tagging a Dimension as Unique” in the Oracle Essbase Administration Services Online Help.

  To disable duplicate member names in a particular generation or level of a dimension, see “Naming Generations and Levels” in the Oracle Essbase Administration Services Online Help.

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 Setting 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. When using Administration Services editors, you can select the qualified member name for an outline tree. You can view the qualified member name for a duplicate member in the Outline Viewer Member Properties dialog box in Administration Services. 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.

Using 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]

Qualifying Members 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]

Using 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 the member and selecting Insert member name. You can also manually insert shortcut qualified names into scripts, spreadsheets, or MDX queries.

Essbase uses the syntax shown in Table 14 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 14. Shortcut Qualified Name Syntax

Scenario

Qualified Name Syntax

Example

Duplicate member names exist at generation 2

[DimensionMember].[DuplicateMember]

[Year].[Jan]

[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 some scenarios, the differentiating ancestor method is used as a shortcut.

[DifferentiatingAncestor].[Ancestors...].[DuplicateMember]

[2006].[Qtr1].[Jan]

Using 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;

Working with Duplicate Member Names

This topic describes the syntax for defining duplicate member names in Administration Services.

To specify duplicate member names in:

  • Smart View, see the Oracle Smart View for Office User's Guide

  • API, see the Oracle Essbase API Reference

To use duplicate member names in MaxL and MDX, see the Oracle Essbase Technical Reference.

Note:

If an alias name and member name are the same but do not represent the same member, searching by alias name is not supported in clients (for example Administration Services or the API).

In Administration Services, if you use the member selection tool to insert a duplicate member name from the outline tree, the qualified member name is inserted automatically.

If you type a duplicate member name directly into an editor, type the qualified member name and enclose the qualified name in double quotation marks (" "). For example,

"[State].[New York]"

In MDX and MaxL qualified member names are not enclosed in quotation marks. See “Inserting Dimension and Member Names in MDX Scripts” in the Oracle Essbase Administration Services Online Help. The Administration Services Data Preview feature does not support duplicate member outlines.