INF: How to Control a Member's Unique Name

In Analysis Services a Member's Unique Name may be different depending on the setting of the MDX Unique Name Style connection string property of the Microsoft OLE DB Provider for OLAP 8.0. This article describes the different formats of the Member's Unique Name and when you would want to control the format of the Unique Name that is used.

Microsoft OLAP Services 7.0 generated Unique Member Names by using the Name Path algorithm, and by listing the Dimension.Member.Member....Member, such as:

[Time].[1997].[Q1].[1]

This syntax can get quite lengthy depending on the number of levels a hierarchy has.

An alternative to the Name Path algorithm is the Key Path algorithm, where the path is defined by the keys rather than the member names. If the path is defined by keys, the syntax is

[Time].&[1997].&[Q1].&[1]

because in the FoodMart Time dimension, the keys are the same as the names. However, keys are often meaningless surrogate integers and are usually more terse than names. For example, you may see a Key Name as

[Customers].&[USA].&[North Carolina].&[1234]

where &[1234] represents a specific name.

Finally, Microsoft Analysis Services 2000 can create a Member's Unique Name by using the information in the member properties. Not only are these unique names shorter, or at worst, no longer than the Key Path or Name Path, these names are guaranteed to be stable and unique over time. The Key Path algorithm is needed because Analysis Services supports ragged hierarchies, which can move the position of a member in the hierarchy.

The Unique Name should be transparent to the database administrator or user; however, the problem is that some client tools have written logic on top of the unique name in which the tool parses the unique name. If Analysis Services changes the algorithm, the client tool will not function correctly. To prevent your applications from breaking, you can use the MDX Unique Name Style property of the connection string. The MDX Unique Name Style property takes four values, as shown in the table that follows:


A sample connection string is similar to:


NOTE: If the Allow Duplicate Names dimension advanced property is FALSE, which it is by default, and the MDX Compatibility property of the connection is 1, again the default, then the MDX Unique Name Style is set to 2 "Key Path" for that dimension, no matter how you have set the MDX Unique Name Style property in the connection string. The reason here is that if the MDX Compatibility property is set to 1, then you may be using an older or less functional client, so you should use the old-style unique name algorithm. Otherwise, the default is the compatible algorithm (3).

REFERENCES

For more information about the MDX Unique Name Style and MDX Compatibility properties, see SQL Server 2000 Books Online.