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.


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


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.


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.


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.


Example of Use:


[Date].[Calendar Year].UniqueName







Table 1: Examples Showing the .UniqueName Function Employed with Different Objects

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles