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.

» See All Articles by Columnist William E. Pearson, III

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

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers