The Extract()Function
Introduction
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.
Discussion
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().
Syntax
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.
SELECT
{[Measures].[Count]} ON COLUMNS,
EXTRACT(
{([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
FROM
[HR]
This query, for the measure Count (of
employees), would result in the extraction of a set similar to that depicted in
Table 1.
|
|
Count
|
|
Store Management
|
648
|
|
Store Temp Staff
|
1,680
|
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.
» 
Comment and Contribute
