MDX Essentials: Basic Set Functions: The EXTRACT() Function - Page 2

October 4, 2004

The Extract()Function


The EXTRACT() function, according to the Analysis Services Books Online, "Returns a set of tuples from extracted dimension elements." We will examine the function's manner of accomplishing these extractions, which, as we shall see, eliminates duplicates automatically, in the sections that follow.

We will examine the syntax for the EXTRACT() function in general, building to the extraction of a set of tuples from a dimension we specify in the function, from a set we construct as a part of preparation for a practice exercise, with which we will meet a hypothetical business need. In this way, we will be able to clearly see that the EXTRACT () function does, in fact, generate the results we might expect. Our objective is to gain a richer understanding of the capabilities found within the EXTRACT () function, together with a feel for the "oppositeness" that it maintains with the CROSSJOIN() function that we discussed in Basic Set Functions: The CrossJoin() Function.


EXTRACT() allows us to return a set from an initial set we specify, with the returned set composed of tuples from specified dimensional components. As we have stated, EXTRACT() acts in a manner opposite to CROSSJOIN(). In addition, the function always removes duplicates, so, with EXTRACT(), we have no concern for flags or the conscious management of duplicates.

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


Syntactically, the set from which the specified dimensional members are to be extracted within the EXTRACT() function is placed within the parentheses to the right of EXTRACT. The dimension(s) are separated by comma(s). The syntax is shown in the following string:

EXTRACT( <<Set>>, <<Dimension>>[, <<Dimension>>...] )

The members of the dimension(s) specified in the function are extracted into fresh tuples, as we shall see in a step in the practice example. No duplicates are allowed to remain in the set that is produced.

The following simple example illustrates conceptually the operation of the EXTRACT() function. (We will be doing a practice exercise in subsequent sections, but if you want to "test drive" a sample, you can certainly cut and paste, or type, the below into the MDX Sample Application).

We will extract the tuples comprising the Position dimension from a set as shown in the following working query, which we can apply to the sample HR cube.

   {[Measures].[Count]} ON COLUMNS, 
    {([Position].[All Position].[Store Management], [Store].
        [All Stores].[Canada]),
           ([Position].[All Position].[Store Temp Staff], 
               [Store].[All Stores].[Mexico]),
                   ([Position].[All Position].[Store Management], 
                       [Store].[All Stores].[USA])}, Position)  
                           ON ROWS

This query, for the measure Count (of employees), would result in the extraction of a set similar to that depicted in Table 1.


Store Management


Store Temp Staff


Table 1: Results of an EXTRACT() Operation, Selecting Employee Count as the Measure

Note that, although the Store Management level appears twice in the original set (for each of countries of Canada and USA), there are no duplicates in the returned dataset.

We will activate the concepts involved in the foregoing discussions by practicing the use of the EXTRACT() function in the section that follows.