MDX Essentials: String Functions: The .UniqueName Function - Page 2

Practice

Preparation

To reinforce our understanding of the basics we have covered so far, we will use the .UniqueName function in a couple of ways that illustrate its operation. We will do so in simple scenarios that place .UniqueName within the context of meeting business requirements similar to those we might encounter in our respective daily environments. The intent is, of course, to demonstrate the operation of the .Unique Name function in a straightforward, memorable manner.

Let's return to the MDX Sample Application as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

Let's assume, for our practice example, that we have received a call from the Reporting department of the FoodMart organization, requesting our assistance in meeting a specific report presentation need. A group of report authors want to display the Names of the US Warehouse Cities, alongside the respective "MDX names" (their term for the qualified names / Unique Names within Analysis Services), to provide an index for a developer who needs the Unique ("MDX") Names for a reporting project he has undertaken.

This represents a simple, yet practical, need that we can readily answer using the .UniqueName function in conjunction with a relative function, .CurrentMember. The solution also includes the .Name function, so our example will also serve as a review of what we covered in String Functions: The .Name Function. We will create a basic query that returns the Warehouse City names for each US City in which we conducted Warehouse operations over the past couple of years (1997 and 1998), along with the Unique Name for each respective US Warehouse City. Some of the Unique Names we generate with the query will ultimately find their way into the Dataset definition of reports that the developer intends to construct within Reporting Services – the "MDX" name for the Warehouse City can be used axes, slicers, and so forth, within queries against the Analysis Services cube under consideration.

Let's construct a simple query, therefore, to return the requested Warehouse City information, presenting the Names and Unique Names in two, side-by-side columns, with the corresponding Warehouse City members as rows.

-- MDX035-01  Using .Name  and .UniqueName to generate a name / unique name 
-- index within the data grid
WITH
MEMBER
     [Measures].[Warehouse USA City]
AS
     '[Warehouse].CurrentMember.NAME'
MEMBER
     [Measures].[Warehouse USA City - MDX]
AS
     '[Warehouse].CurrentMember.UNIQUENAME'
SELECT
     {[Measures].[Warehouse USA City], [Measures].[Warehouse USA City - MDX]} 
         ON COLUMNS,
     {DESCENDANTS([Warehouse].[All Warehouses].[USA], [Warehouse].[City])} 
         ON ROWS
FROM 
     [Warehouse]  

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 1 appears.

We see Warehouse City Names, the output of the Warehouse USA City calculated member, populating the first data column, with the respective Warehouse City Unique Names (a "qualified" MDX name that can, itself, be used within a query against the Warehouse cube) for each occupying the second data column. The Warehouse City members themselves occupy the row axis, as we requested.

The calculated members Warehouse USA City and Warehouse USA City – MDX employ the .Name and .UniqueName functions, respectively, in conjunction with the "relative" .CurrentMember function, which, as we can easily see from our practical example, results in a combination list of the Names / qualified names of the members that we specify in our row axis. (Similarly, if we had specified the Warehouse State Province or Warehouse Country levels in the row axis instead, we would have obtained a list of the members of those levels as a result). Intersecting the calculation with the members under consideration can be leveraged, in similar fashion, to produce sophisticated results within more elaborate structures and processes.

Let's look at an example that expands upon our first, this time to meet a mechanical need within the reporting layer of an integrated BI application. As many of us are aware, enterprise reporting applications typically allow for parameterization (via what are sometimes known as "prompts" or "parameter prompts") to enable information consumers to quickly find the information they need from a report. These parameters, whose values are physically passed to an axis specification or a slicer in the dataset query, often act to put filters into place "on the fly"; these "filters" are thus enacted when the consumer types or selects a value, or a series of values, at run time.

In general, there are two primary types of parameters, type-in and picklist, which can be mechanized through various means. Type-in parameters accept directly typed user input for the value upon which the report is based. An example of input might, for a report based upon an Analysis Services cube, consist of the Unique Name for a given filter, say, for one of the Warehouse Cities in the list we created earlier.

The trouble with type-in parameters is that they are subject to input error, and thus can fail to produce the desired results if they are not precisely correct. This can be particularly cumbersome for information consumers when the report is based upon an Analysis Services cube, because, even with a list like the one we generated above with the Unique Names mapped to the "English" names for various filter selections, the precise MDX qualified name might present a typing challenge for some.

For this reason, the alternative parameter type, the picklist, provides a more user-friendly experience. A picklist presents a selection of choices to a consumer, based upon a static file, a dataset from a larger data source, or through other means. The picklist is often the tool of choice, because of its inherent elimination of typing errors. A well-constructed picklist makes selection easy for the consumer (who is not often pleased with a long scrolling process, or other cumbersome method, as the initial step in generating a commonly requested report). An investment in developing a good picklist often pays great dividends in consumer satisfaction.

The list we have generated above provides virtually all we need to support parameterization within Reporting Services and other enterprise reporting applications. Let's do another example, this time with picklist support the primary objective. We will construct a dataset upon which the picklist selections can be based, and then I will illustrate briefly using this dataset in MSSQL Server Reporting Services.