Intrinsic Member Properties: The MEMBER_KEY Property - Page 2

Practice

Preparation

To reinforce our understanding of the basics we have covered so far, we will use the MEMBER_KEY property in a hands-on example that illustrates its operation. We will do so in a simple scenario that places MEMBER_KEY within the context of meeting business requirements similar to those we might encounter in our respective daily environments. The intent, of course, is to demonstrate the operation of the MEMBER_KEY property in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain. If you do not know how to access the SQL Server Management Studio in preparation for using it to query an Analysis Services cube (we will be using the sample Adventure Works cube in the Adventure Works DW Analysis Services database), please perform the steps of the following procedure, located in the References section of my articles index:

This procedure will take us through opening a new Query pane, upon which we can create our first query within the section that follows.

Procedure: Satisfy Business Requirements with MDX

As a basis for our practice example, we will assume that we have received another call from the Reporting department of our client, the Adventure Works organization, requesting our assistance in meeting a specific report presentation need. The client has implemented the integrated Microsoft BI solution, and, in addition to using Analysis Services as an OLAP data source, they use Reporting Services as an enterprise reporting solution. The MDX we explore together, we are told, will thus be adapted and extended for ultimate use within Reporting Services, in multiple parameterized reports. Finally, we learn that the requirements will very closely parallel those of a previous visit, documented in Intrinsic Member Properties: The MEMBER_CAPTION Property , an earlier article in this series.

Per the original requirement, we are told, our client wishes 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;” the “filters” are thus enacted when the consumer types or selects a value, or a series of values, at run time.

As we have noted numerous times in articles within my MSSQL Server Reporting Services series and elsewhere, 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 Customer Geography Cities within the Adventure Works sample cube.

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

It is simple to generate a list that provides all we need to support parameterization within Reporting Services and other enterprise reporting applications. Let’s get some hands-on exposure to assembling a list whose primary objective is picklist support. We will construct a dataset upon which the picklist selections can be based, and then examine briefly an illustration of the use of this dataset in MSSQL Server Reporting Services.

Let’s assume, as a background scenario, that, our client colleagues extend a request that we documented in Intrinsic Member Properties: The MEMBER_CAPTION Property. As we recall, the request was a common one: they wanted to provide picklist support within an OLAP report, which they had constructed using MSSQL Server Reporting Services. The data source was to be the Adventure Works sample cube, which accompanies an installation of MSSQL Server Analysis Services 2005 (and with which most of us are familiar. Our colleagues originally asked that the selector for a parameter picklist for the Product Category display, within the selector, only the “regular” Name for each Product Category each time an information consumer executed the report. The only change, they tell us this time around, is within the Caption for the parameter picklist.

The client representatives tell us that they want the new Caption for the Product Category picklist to consist of a combination of the Product Category Key, a numeral that is familiar to many (particularly “old school”) Adventure Works information consumers, and the “regular” Name for their Product Categories. The new Caption, seen at report runtime, for the Product Category picklist will therefore be a concatenation of the Key and the Caption / Name (with a “ – “ separator) for the Product Category. An illustration would be a Caption for the Components Product Category, which would appear as “2 – Components”.

While the focus of our article is the MDX required to meet this request, and specifically upon the use of the MEMBER_KEY property within an MDX query, the dataset that this query generates would be added in Reporting Services’ Report Designer, among other steps, to meet the requirement for parameterization within the designated OLAP report. Let’s create a query to generate the needed picklist support elements, and then take a look at how we might use the data returned within the reporting layer.