Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 6, 2005

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

By William Pearson

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.

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

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.

5.  Type (or cut and paste) the following query into the Query pane:

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

6.  Execute the query by clicking the Run Query button in the toolbar.

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


Illustration 1: Results Dataset – Combined Use of .Name and .UniqueName with .CurrentMember

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.

7.  Select File -> Save As, name the file MDX035-1, and place it in a meaningful location.

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.

NOTE: For one hands-on approach (as you will see, they are Legion) to constructing the picklist in Reporting Services, see my article Mastering OLAP Reporting: Cascading Prompts here at Database Journal.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM