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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 7, 2007

Logical Functions: IsGeneration(): Conditional Logic within Calculations - Page 2

By William Pearson


Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered, we will use the IsGeneration() function within a couple of queries that illustrate its operation, focusing, within this article, upon scenarios where we use the function to support conditional logic within a calculation. (We examine its use in combination with the MDX Filter() function in another article of this series). We will undertake our practice exercises within scenarios that place IsGeneration() 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 function 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:

Prepare MSSQL Server Management Studio to Query Analysis Services

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 Controllers’ 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 simple values for a specific analysis task that has been discussed at a recent meeting with the Controllers.

The analysts tell us that the values under immediate consideration involve Internet Order Counts, 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 end is to simply return the Internet Order Count recorded for each of the four operating calendar years contained within the Adventure Works cube.

As is often the case, this basic need might be easily met a number of ways with an MDX query. The analysts throw a further twist into the requirement, however: In addition to being likely to parameterize the calendar year, and perhaps other date specifics at runtime, they also want to be able to support parameterization of the level within the Date dimension (Calendar hierarchy) when executing the report (that is, to be able to change it from calendar year to a lower level, such as a quarter of a month, for example – and thus to “narrow” the member selection that appears within a given iteration of the report results, producing something akin to a selective “drilldown” effect.) 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 IsGeneration() function as one 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. A method of testing whether or not a specified member lies within a given generation number (or numbers) of a specific dimensional hierarchy is something that they hope to be able to extrapolate to uses within other dimensions, as well. (As we note often within the MDX Essentials series, time / date dimensions are always good “starters” for introducing new functions. The relationships between the various levels are familiar to everyone, whereas the structures of other dimensions might not lend themselves to population accuracy and completeness “reasonability” testing undertaken by those not entirely knowledgeable of the corporate structure, geography, and so forth.)

We offer to illustrate the use of IsGeneration() to meet the immediate need, proposing to present a couple of examples, to solidify the analysts’ new understanding, as well as to assist in rounding their overall MDX “vocabularies.” We then set about the assembly of our examples to illustrate uses of IsGeneration().

MS SQL Archives

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