Featured Database Articles

String Functions: The .Properties Function - Page 2

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have
covered, we will use the .Properties function within a couple of queries
that illustrate its operation, focusing, within this article, upon scenarios
where we use the basic .Properties functionto meet the business
requirements of a hypothetical client. (As we have noted earlier, we examine
the use of the .Properties functionwith the optional TYPED
flag, within Part II of this article.) We will undertake our practice
exercises within scenarios that place the .Properties functionwithin
the context of meeting basic requirements similar to those we might encounter
in our respective daily environments. The intent is to demonstrate the use of
the statement 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 DWAnalysis 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 will create our first query within the section that
follows.

Procedure: Satisfy Business Requirements with MDX

Lets assume, for purposes of our practice example, that we
have received a request for assistance from representatives of our client, the Adventure
Works organization. Analysts and report writers within the VP - Sales
group, with whom we have worked in the past to deliver solutions to meet
various reporting and analysis needs, inform us that they have received a
request to generate datasets for several reporting tasks that have been
discussed at a recent meeting with Marketing group peers.

The analysts tell us that they wish to generate information
about the Adventure Works customer base. Specifically, they wish to
create a dataset upon which they can build tabular and graphical reports, to
include the following information that they know to be stored in the cube as Customer
member properties:

Customer Address

Customer Birth Date

Customer Daily Commute Distance

Number of Children Living at Customer Home

Customer Phone Number

Customer Yearly Income

For the time being, our colleagues want simply to generate a
dataset containing the above information for each customer in the Adventure
Works cube. The end use of the information will go beyond reporting, and
perhaps be used to support picklists and the like (as we have demonstrated in
many of the articles within my MDX Essentials, and other Database
Journal, series). Moreover, the group assures us that, once they grasp
the concepts to meet the immediate need, they will want to expand prospective
queries to bring in additional measures for the customers.

After we initially explain the use of the basic .Properties
function as a candidate for meeting the requirement, our client colleagues
state that they are interested in understanding how they might apply this
function within the context of a practical scenario such as the immediate
requirement. The basic .Properties function appears an adequate
mechanism for delivering the information requested. We discuss our reasoning
with the analyst group, and then offer to illustrate the use of the .Properties
function to meet the immediate need, both to solidify the analysts new
understanding and to assist in rounding their overall MDX vocabularies. We
then set about the assembly of our example to illustrate the basic use of .Properties.

Procedure: Use the Basic .Properties Function within Calculations

Per the request of our client colleagues, we will construct
a simple query to provide an illustration of the use of the basic .Properties
function within a common context, the definition of a calculated members
with which we will then deliver selected information stored as Customer
member properties.

Our initial example will serve as an introduction to a means
of generating a dataset containing member property information, as requested
by the analysts. This will serve as a basis for meeting the business
requirement, with the intended purpose of presenting the member property
information as columns appearing alongside the individual customer member
names, which will populate the row axis.

The above query returns the specified member properties
for each member of the Customer dimension (Customer Geography attribute
hierarchy, Customer level) in the cube.

We alert our client colleagues to the fact that we might
insert logic to support parameterization, primarily by using the Descendants()
function, wherein we might, as an example, parameterize the level within
the function to allow control of the detail presented, among other elements of
our query, to accomplish potentially extended ends of our client colleagues. In doing so, we could set up a hierarchical
picklist within Reporting Services,whereby information consumers
might select a given Country, State-Province, City, Postal Code, and
even Customer Name, and so forth, to drive the level whose
members values are returned, among other possibilities. The obvious advantage
is that consumers can dictate the dimensional level as well as the
member(s) of that level (Reporting Services 2005 supports
multiple selection within properly constructed picklists),upon which
the focus is enacted. In some circumstances, double leverage could be
provided by modifying the query to do more within a single parameter  which
might be seen as a desirable efficiency within the realm of simulated dynamic
drilldown effects and so forth.

In the calculated member definitions, we put the
basic .Properties function to work to allow the retrieval of the
respective string values. We juxtapose the individual customer member
names in our dataset through the use of the .MEMBERS function within the
row axis specification.

NOTE: For more information about,
and hands-on practice within, working examples of MDX query parameterization, see various member articles
of my MSSQL
Server Reporting Services series.

2.
Execute the
query by clicking the Execute button in the toolbar, as depicted in Illustration
3.

Illustration 3: Click Execute to Run the Query...

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

In the partial view of the returned dataset, we see that the
calculated members accomplish the intended purposes - generating the member
properties strings for the individual Customer Geography hierarchy
members that belong to Customer level.

3.
Select File
-> Save MDXQuery1.mdx As ..., name the file MDX058-001,
and place it in a meaningful location.

Our client colleagues express satisfaction with the
contextual backdrop we have established for introducing the basic .Properties
function. We agree to next use a similar query within another such example,
to confirm understanding of the concepts. This query will provide an
illustration of the use of the .Properties function within the context
we have already seen, the definition of calculated members, which we will,
in turn, use to retrieve member property data within a dataset for
analysis and reporting. As before, we will base our example upon a local
scenario posed by the client representatives: a need to provide member
property data to support reports they plan to create in Reporting
Services 2005. Moreover, as always, they want to master concepts that can
be extended beyond the immediate example as the need arises.

The analysts tell us that they wish, once again, to generate
information from the Adventure Works database, but that this time their
focus is employee information. Specifically, they again wish to create
a dataset upon which they can build tabular and graphical reports, to include
the following information that they know to be stored in the cube as Employee
member properties:

Employee Hire Year

Employee Hire Date

Employee Base Rate of Pay

Employee Pay Frequency

For the time being, our colleagues want only to generate a
list containing the above information for each employee in the Adventure
Works cube, in much the same way we generated the specified information
stored within the customer member properties in our earlier example. End
uses for the information will, again, go beyond reporting, with picklist
support and other possible utility to be explored, and with prospective queries
likely to be expanded to bring in additional measures and other data surrounding
employees.

The analysts tell us that the All
level for employees should not appear within the returned dataset, but
that every employee name is to be presented within the row axis,
juxtaposed against the selected member property information that will
again appear in columns. Per the request of our client colleagues, we will
construct a simple query to provide an illustration of the use of the basic .Properties
function, once again within the definition of calculated members,
which we will then select for retrieval into the end dataset.

4.
Press key
combination ALT + N, to open a tab for a new query within the current Analysis
Server connection.

The Query pane appears,
with our input, as depicted in Illustration 5.

Illustration 5: Our Second Practice Query in the Query
Pane ...

The above retrieves the calculated members that specify the
requested member properties for each member of the Employee
dimension, Employee attribute hierarchy. Within each calculated member,
we put the basic .Properties function to work to retrieve the associated
information. The same sort of logic might, of course, be applied within far larger,
more elaborate scenarios, a fact that we emphasize to our client colleagues.

Finally, and rather obviously, because we have specified
that the Employee members are to populate the rows axis, through our use
of the .CHILDREN function, we have populated the row axis with all Employee
members, while excluding the All level of the dimension (All Employees
will appear if we simply use [Employee].[Employee].MEMBERS in the row
specification.)

In the view of the returned dataset, we see that the
calculated members accomplish their intended purposes  generating the
specified member properties strings for the individual employee
members.

7.
Select File
-> Save MDXQuery2.mdx As ..., name the file MDX058-002,
and place it in a meaningful location.

In the returned dataset, we see that the query appears to
meet the business requirements outlined by the client representatives. We have
delivered member properties by putting the basic .Properties
function to work within simple calculated members,which we can then
select within Reporting Services, or within any other application that
can successfully pass similar MDX queries to an Analysis Services 2005
database. We can, of course, use the member properties data that we
retrieve for reporting and analysis purposes, as well as to drive report
attributes, among a host of other possible uses.

The
client representatives confirm that the immediate goal of the practice example
has been met: the creation of calculated members whose employment of the .Properties
function provides a vehicle for retrieving string values from selected member
properties  all in a manner that lends itself to the parameterization
opportunities that are expected to arise at the Reporting layer.
Moreover, they state that the illustration we have provided can be easily
extrapolated to other scenarios where they need to perform an action, or to
present a value, based upon the data contained within the member properties
residing at various levels within the dimensional structures of their Analysis
Services 2005 data sources.

Summary ...

In
this, the first half of a twopart article, we explored the basic MDX .Properties
function, which can be called upon in activities that range from generating
simple lists and supporting parameter picklists, to supplying data contained
within the member properties of our dimensional structures to meet
myriad analysis and reporting needs within our local business environments. We
introduced the basic .Properties function, commenting upon its operation,
and touching upon the data strings we can deliver through its use.

We
examined the syntax involved with .Properties, and then undertook
illustrative practice examples of business uses for the basic function,
generating queries that capitalized upon its capabilities. Throughout our
practice session, we briefly discussed the results datasets we obtained from
each of the queries we constructed, as well as extending our discussion to other
possible options and uses for the concepts we exposed.