MDX Essentials: Basic Set Functions: The Union() Function

 “So we grew together, Like to a double cherry, seeming parted, But yet an union in partition” William Shakespeare, A Midsummer Night’s Dream, Act III, Sc. 2

This is the thirteenth article of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements needed for getting the most out of the lessons included, please see the first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

What We Accomplished in our Last Article

In the last article of the series, Basic Set Functions: The Order() Function, we explored the versatile, Order() function, which we find to be useful in many queries – queries that rank from the simplest to the most advanced. The Order() function provides the sorting capabilities we need within MDX; its support of the two general types of sort order, hierarchized and nonhierarchized, make it an important part of our analysis toolsets.

In addition to discussing the purpose of the Order() function, we examined the syntax surrounding its uses, and illustrated its application in practice exercises, providing hands-on exposure to both hierarchized and nonhierarchized order types. Finally, we discussed the results we obtained in each exercise, remarking on the distinguishing characteristics of each.

Introduction

In this lesson, we will focus our attention on another commonly used MDX tool, the Union() function. The rather straightforward purpose of the Union() function is to combine two sets into one, but the direct and indirect uses to which the function can be put are legion. Suffice it to say that Union() provides important capabilities within MDX, and is thus another valuable part of our analysis toolsets.

Along with an introduction to the Union() function, this lesson will include:

• an examination of the primary and alternate syntaxes surrounding the function;
• illustrative examples of the uses of the function in practice exercises;
• a brief discussion of the MDX results we obtain in the practice examples.

The Union() Function

The Union() function, according to the Analysis Services Books Online, “returns the union of two sets, eliminating duplicates by default.” The function provides, moreover, a means of retaining duplicates for those scenarios where this might be desirable. As we shall see, alternative ways of accomplishing unions of sets, both with and without duplicates, are possible in Analysis Services using its extensions to OLE DB MDX.

We will examine the syntax for the Union() function in general, building to the union of two sets after creating an individual query for each, to show that the Union() function does, indeed, generate the results we might expect within the context of an illustrative business need. Next, our practice example will be modified to introduce the possibility of duplication within a Union() function, so that we can examine the means that MDX affords us for managing duplication. Finally, we will present alternative syntax where appropriate, illustrating its use in practice examples that parallel uses for the primary syntax we present, so as to make the comparisons more meaningful.

This will give us a richer understanding of the options we have within the Union() function, together with a feel for the similarities, as well as for the differences, of the results those options afford.

Discussion

Union() allows us to combine sets. The primary syntax we use to take advantage of Union() affords us an optional flag to force the presentation of duplicates that might occur within the sets we are subjecting to the Union() function. The default is the elimination of duplicates, through the removal of duplicate members from the end (“tail”) of the unioned set, as we shall see.

Analysis Services also presents a couple of alternate syntax structures that achieve the effects of Union(), and that carry implicit handling of duplicates within their definitions. As we mentioned earlier, the alternate syntax structures comprise extensions to MDX in OLE DB, and are thus relevant specifically to Analysis Services implementations.

The Union() function acts as a vehicle to allow us to meet myriad common and uncommon business needs, including the obvious need to bring together two sets into a single set, for presentation, computation, and perhaps other purposes. Additional uses I have seen include forced elimination of duplicates between two sets, in scenarios where this default behavior can accomplish an elimination where other attempts fail, or are more costly with regard to operating overhead and so forth.

Let’s look at some syntax illustrations to further clarify the operation of Union().

Syntax

Syntactically, the sets to be combined in the Union() function are placed within the parentheses to the right of Union, and separated by a comma. The optional ALL flag overrides the default behavior of the function with regard to duplicates, and allows duplicates to remain in the set that is produced. The syntax is shown in the following string:

`Union(<<Set1>>, <<Set2>>[, ALL])`

Alternate forms of the function also exist. These are shown in the following strings:

Alternate Syntax 1:

`{<<Set1>>, <<Set2>>}`

Alternate Syntax 2:

`<<Set1>> + <<Set 2>>`
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.
Previous articleBuffer Hit Ratio
Next articleMonth Calendar