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 Oct 4, 2004

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

By William Pearson



To reinforce our understanding of the basics we have covered so far, we will use the EXTRACT() function in a manner that illustrates its operation within a multi-step example. We will first create a set that resembles information that might be useful in a business scenario. We will then explore the use of the EXTRACT() function to return fresh tuples from the original set, based upon a dimension that we specify.

We will call upon the MDX Sample Application again, as our tool to construct and execute the MDX we examine, and to view the results datasets we obtain.

1. Start the MDX Sample Application.

2. Clear the top area (the Query pane) of any queries or remnants that might appear.

3. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4. Select the WAREHOUSE cube in the Cube drop-down list box.

Let's assume for our practice example that we have received a request from a group of information consumers in the Planning unit of the FoodMart2000 organization for support in the presentation of some data, housed within the Warehouse sample cube, regarding Warehouse Sales for 1998. The consumers wish to know to which five Store Cities, in descending order of sales, we can attribute the sales of the Products with the highest Warehouse Sales.

We will begin by composing a simple query to prepare the original set, upon which we will use the EXTRACT() function in our next section. My objective at the same time is to illustrate use of the function to meet the hypothetical business requirement we have outlined as a means of reinforcing the concepts in our minds where they can be triggered upon meeting a similar situation in our respective business environments.

First, we will use a CROSSJOIN() function to build a "starter" set, which we will then chisel down to a result dataset that matches the needs of the intended audience.

5. Type the following query into the Query pane:

-- MDX24-1:  Tutorial Query Step 1
    {[Measures].[Warehouse Sales]} ON COLUMNS,
    CROSSJOIN ({[Food]}, {[Store].[Store City].Members}) ON ROWS

The purpose of this stage of the query is to simply crossjoin Food products with the membership of the Store Cities, all within operating year 1998.

6. Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 1 appears.

Illustration 1: Initial Results Dataset

We see there are a total of twenty-four Store Cities (one of which actually has no Warehouse Sales activity) that appear crossjoined with Food products.

7. Select File --> Save As..., name the file MDX24-1, and place it in a meaningful location. Leave the file open for the next step.

8. Modify the comment line to read:

-- MDX24-2:  Tutorial Query Step 2

9. Save the file as MDX24-2, to protect MDX24-1.

Now we will take another preparatory step and filter out the Store City with no Warehouse Sales. To do this, we will employ the NOT ISEMPTY keywords, as follows.

10.  Insert the Filter() function, as shown:


between the third line of the query (counting the comment line, and shown below):

  {[Measures].[Warehouse Sales]} ON COLUMNS,

and the fourth line of the query (beginning with CROSSJOIN, as follows):

        CROSSJOIN ({[Food]}, {[Store].[Store City].Members}) ON ROWS

11.  Remove the ON ROWS keyword from the above line, replacing ON ROWs with a comma (,).

12.  Insert the following line immediately below the line that once contained ON ROWS,

             NOT ISEMPTY([Measures]. [Warehouse Sales]))  ON ROWS

The Query pane appears as shown in Illustration 2, with changes circled in red.

Illustration 2: Modified Query in Query Pane (Compressed View)

13.  Execute the query by clicking the Run Query button in the toolbar, once again.

The Results pane is populated once more, and the dataset depicted in Illustration 3 appears.

Illustration 3: Results Dataset, Filtering Out Empties

We see there are a total of twenty-three Store Cities, now that the "empty" Store City of Alameda is filtered out.

14.  Select File --> Save to save the query as MDX24-2. Leave the file open for the next step.

15.  Modify the comment line to read:

-- MDX24-3:  Tutorial Query Step 3

16.  Save the file as MDX24-3, to protect MDX24-2.

We have completed our preparatory steps, and now have a realistic dataset with which to work. We will put the EXTRACT() function to use within our query in the following section.

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