MDX Member Functions: The Cousin () Function - Page 3

May 19, 2003

Practice

Let's construct an expression that calls the Cousin() function into action to reinforce our understanding of how it operates. The MDX Sample Application (see the second article in our series, Structure of the MDX Data Model, for more information about the Sample Application) will be our tool for constructing and executing our expressions, and for viewing the result 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 Budget cube in the Cube drop-down list box.

We will begin with a simple illustration that involves the Cousin() function: a basic query designed to extract the total of the measure Amount (the sole measure in the Budget cube) for Q1 of 1998. While this is a relatively meaningless total, we will refine the query to a more practical format as we develop it in subsequent steps.

5.  Type the following query into the Query pane:

``` -- MDX07-1:  Tutorial Query No. 1
SELECT {Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS
FROM [BUDGET]
WHERE ([Measures].[Amount])

```

6.  Click the Run button (the green "arrowhead" icon) on the toolbar atop the Sample Application, to execute the query.

We see the result dataset below, which appears as soon as Analysis Services fills the specified cells in the Results pane.

Illustration 4: The Query Result Dataset

The query delivers the results that we requested; we see the total for the Amount measure for Q1 of 1998 (the cousin of Q1 1997, as we can clearly see).

7.  If it is desirable to save this or subsequent queries, we can do so by selecting File ` Save As, and give the file a meaningful name and location.

Next, we'll develop our approach further to make it a bit more useful. This time, we will write a basic query to return Total Expense by Account, for Q1 1998 (via the Cousin() function again). We will again reference our introduction of the .Children function in Lesson 5. From its use we will enumerate the Accounts contained within Total Expense level of the Account dimension. Our objective will be to create a column for Q1 1998 and a row for each Expense Account.

8.  Type the following into the Query pane:

``` -- MDX07-2:  Tutorial Query No. 2
SELECT {Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS,
{[Account].[All Account].[Net Income].[Total Expense].Children} ON ROWS
FROM [BUDGET]
WHERE ([Measures].[Amount])

```

9.  Click Query on the top menu, and then select Run.

The Results pane appears as shown in Illustration 5.

Illustration 5: The Query Results

The query delivers the totals for the enumerated children of the Total Expense level of the Account dimension.

Now let's go yet a step further and build a result set that provides the basis of a comparison between Q1 1997 (our source specification) and Q1 1998 (the cousin of the source). Our objective will be to create a column each for Q1 1997 and Q1 1998, providing again a row for each Expense Account.

10.  Type the following into the Query pane:

``` -- MDX07-3:  Tutorial Query No. 3
SELECT {[Time].[1997].Q1, Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS,
{[Account].[All Account].[Net Income].[Total Expense].Children} ON ROWS
FROM [BUDGET]
WHERE ([Measures].[Amount])
```

11.  Click Query on the top menu, and then select Run.

Analysis Services populates the cells that the query specifies, and we see the result dataset as depicted in Illustration 6.

Illustration 6: The Result Dataset

The query delivers the total for each enumerated child of the Total Expense level of the Account dimension.

Next in Our Series ...

In this lesson, we explored the slightly more challenging Cousin() function. We discussed the Cousin() function in detail, illustrated the syntax that is appropriate for its effective use, and then tested our understanding of how to leverage this remaining "family" function by undertaking relevant practice exercises. Finally, for each practice exercise, we commented on the results we obtained using the Cousin() function, and discussed some of the limitations that are inherent in its use.

In our next lesson, we will begin an overview of additional "relative" member functions, refreshing and expanding upon our earlier exposure to the powerful .Members function, then extending our exploration to the .CurrentMember, .PrevMember and .NextMember functions.

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

MDX Essentials Series