Featured Database Articles

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have
covered, we will use the CASE statement within a couple of queries that
illustrate its operation, focusing, within this article, upon scenarios where
we use the simple CASE statement to meet the business requirements of a
hypothetical client. (As we have noted earlier, we examine the searched type
of the statement, which returns specific values based upon its evaluation of a
set of Boolean expressions, in an independent article dedicated to the details
surrounding that type.) We will undertake our practice exercises within
scenarios that place the CASE statement within 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 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 some tag values
for a specific analysis task that has been discussed at a recent meeting with
Marketing group peers.

The analysts tell us that the values under immediate
consideration involve Internet Order Quantities, but, as is typically
the case in our collaborative sessions, they want to develop an approach that
will work equally well with other measures that have similar analysis
potential. (As we have noted in other sessions of our series, our client
colleagues often derive parameterized queries in Reporting Services from
the basic MDX syntax we assemble together, and can thus create self-serve
reports that allow information consumers to dictate what measure they wish to
analyze, and myriad other options, at run time.) The desired immediate end is
to simply return the Internet Order Quantity recorded for the initial
year of operations, Calendar Year 2001, for each Postal Code for
a sample State-Province. (They have chosen Washington as a start,
but assure us that they realize that the State-Province can be
parameterized in the reports they eventually build, based upon the sample logic
that we help them to devise.)

Our client colleagues tell us that they wish to classify the
Internet Order Quantity for each of the Postal Codes. They will
place the activity label in a column to the right of the Internet Order
Quantity column of the returned dataset, using the logic found in Table
1.

As is often the case, this basic need might be met multiple ways
with an MDX query. Because the analysts have made known the desire to
eventually evolve the query to allow parameterization of the State-Province,
as well as Calendar Year and so forth, we want to propose a sample that
lends itself to flexible modification later. Once again, the richness of MDX
affords us a number of avenues to this objective. While parameterization is
itself not a consideration in our current level of query design, we want to
make it easy to accomplish within Reporting Services. (The same concepts
would, of course, apply with other OLAP reporting tools that afford developer
access to the MDX syntax that underlies them).

After we initially explain the use of the CASE statement
as a candidate for meeting the requirement, our client colleagues state that
they are interested in understanding how they might apply conditional logic
via this function, within the context of a practical scenario such as the immediate
requirement. The simple CASE statement appears an adequate mechanism for
evaluating the Internet Order Quantity measure for each individual Postal
Code against several WHEN clauses, and for returning the label
result appropriate for the respective Postal Codes value for the year.
For Internet Order Quantities that do not match a scalar value specified
in any of the When Expressions  that is, quantities of ten (10) and
above  the scalar value of the Else Result Expression (Substantial) will
be returned.

We discuss our reasoning with the analyst group, and then offer
to illustrate the use of the CASE statement 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 use of CASE.