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

 » Database Journal Home » Database Articles » Database Tutorials MS SQL Oracle DB2 MS Access MySQL » RESOURCES SQL Scripts & Samples Tips » Database Forum » Slideshows
 Database Journal |DBA Support |SQLCourse |SQLCourse2

## MS SQL

Posted Oct 4, 2004

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

By William Pearson

### 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].
([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.

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