Practice
Preparation
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
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
CROSSJOIN ({[Food]}, {[Store].[Store City].Members}) ON ROWS
FROM
[Warehouse]
WHERE
([Time].[1998])
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:
FILTER(
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.
» 
Comment and Contribute
