MDX Essentials: String Functions: The .UniqueName Function

About the Series …

This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements for getting the most out of the lessons included, please see my first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.

Overview

In this lesson, we will examine another function / property in the MDX toolset, the .UniqueName function. The general purpose of the .UniqueName function is to return the Unique Name of the object to which it is appended. .UniqueName can be used in conjunction with hierarchies, dimensions, levels, and members, in a manner similar to the .Name function that we examined in String Functions: The .Name Function, and, also like .Name, .UniqueName can be useful in a host of different applications. As I have noted in other articles, both .UniqueName and .Name allow us to exercise a great deal of presentation sleight of hand, in working with MDX in Analysis Services, as well as within Reporting Services and various other reporting applications that can access an Analysis Services cube.

The .UniqueName function can be leveraged in activities that range from generating simple lists to supporting sophisticated presentations. We will introduce the function, commenting upon its operation and touching upon examples of effects that we can employ it to deliver. As a part of our discussion, we will:

• Examine the syntax surrounding the function;
• Undertake illustrative examples of the uses of the function in practice exercises;
• Briefly discuss the results datasets we obtain in the practice examples.

The .UniqueName Function

Introduction

According to the Analysis Services Books Online, the .UniqueName function “returns the unique name of a specified level, dimension, member, or hierarchy.” .UniqueName has many applications, including its use with the Analysis Services objects that are included in the definition, as well as its pairing with other MDX functions to leverage its power even further. As we saw in our examination of the .Name property, I frequently use it with the .CurrentMember function; we will see an example of this combination within the practice exercises to follow.

We will examine the syntax for the .UniqueName function after our customary overview in the Discussion section that follows. Following that, we will conduct practice examples within a couple of scenarios, constructed to support hypothetical business needs that illustrate uses for the function. This will afford us an opportunity to explore some of the presentation options that .UniqueName can offer the knowledgeable user. Hands-on practice with .UniqueName, where we will create expressions that leverage the function, will help us to activate what we learn in the Discussion and Syntax sections.

Discussion

To restate our initial explanation of its operation, the .UniqueName function, when acting upon a level, dimension, member, or hierarchy, returns the Analysis Services Unique Name of the object to which it is appended with the period (“.”) delimiter. .UniqueName can be used for a great deal more than the support of simple lists of unique object names, as we have intimated. When we couple it with other functions, we can leverage .UniqueName to deliver a wide range of analysis and reporting utility. As in so many cases with the Microsoft integrated business intelligence solution, consisting of MSSQL Server, Analysis Services and Reporting Services, this function, residing within the Analysis Services layer, can be extended to support capabilities and attributes in the Reporting Services layer. Knowing “where to put the intelligence” among the various layers is critical to optimization, in many cases. For more of my observations on this subject, see Multi-Layered Business Intelligence Solutions … Require Multi-Layered Architects.

The UniqueName function returns the Unique Name of the object (the “fully qualified” name in MDX), not the Name, which is returned via the .Name function. As promised in String Functions: The .Name Function, we will continue an example we begin at the end of our practice session there, as a prelude to a couple of exercises where we use .Name and .UniqueName together to produce a dataset for a specific objective.

Let’s look at some syntax illustrations to further clarify the operation of .UniqueName.

Syntax

Syntactically, anytime we employ the .UniqueName function to return the associated Unique Name, the object upon which we seek to apply the .UniqueName function is specified to the left of .UniqueName. The function takes the object to which it is appended as its argument, and returns within a string the Unique Name of the object specified. The general syntax is shown in the following string:

<<Object >>.Name

As we learned to be the case with .Name in String Functions: The .Name Function, putting .UniqueName to work could not be easier. When using the function to return the Unique Name of one of the several objects with which it works, we simply append it to the right of the object under consideration. We can see a syntax example, for each of the objects for which .UniqueName can return a Unique Name, in Table 1 below.

As an example, the following snippet:

 [Warehouse].[City]. UniqueName

returns [Warehouse].[City], the Unique Name of the Warehouse City level. As another example, the following:

[Warehouse].[City]. [Seattle].UniqueName

returns [Warehouse].[All Warehouses].[USA].[WA].[Seattle], the Unique Name of the Warehouse City level member Seattle. As is probably obvious, the .UniqueName function can be best leveraged by combining it with other functions, particularly “relative” functions, to generate lists of names, and so forth, as we will see in short order. The immediate usefulness is obvious: .UniqueName generates the “MDX name,” whose utility within queries pays dividends in picklist generation, among other applications.

NOTE: For more information on several of the “relative” functions, see my article MDX Member Functions: “Relative” Member Functions, within the Database Journal MDX Essentials series.

We will practice some uses of the .UniqueName function in the section that follows.