Terms and Concepts

Before we begin doing all of this good stuff, we need to cover some basics. Up to this point, we have looked at cube concepts and definitions with the goal of cube creation and population. Now, we need to shift gears a bit, and look at the concepts and definitions required for extracting and analyzing the information residing in our cubes.

Let's use an analogy from the relational database world. When we are defining and populating a relational database, we need to know about normalization, constraints, and foreign keys. When we are querying data from a relational database, we need to know about INNER and OUTER JOINS, ORDER BY, and GROUP BY.

As we move to extracting and analyzing information in the cube, we need to learn how to move through our dimensional hierarchies to address a particular location or set of locations in the cube. We need to know how to select a particular measure and combine that with other measures in complex calculations. In short, we need to know how to navigate our cubes without getting lost, or worse yet, without returning utter nonsense to our users.

Where Are We?

We live in an age of many electronic wonders: computers, of course, but also cell phones, digital cameras, and global positioning satellite (GPS) receivers. With a GPS receiver, we can tell exactly where we are on the planet to within a few feet, perhaps even to within a few inches! The question is this: Will we men now be too stubborn to consult our GPS receivers for directions?

In the world of OLAP cubes, we need the equivalent of a GPS system. We need a means to uniquely specify any location within a cube. Fortunately, MDX scripting provides just such a mechanism.

Cells

The first question might be, Just what are we trying to locate? The answer is, We are trying to locate cells. Fortunately, we do not need a microscope to find these cells. (I never was very good at biology.) These cells are located right inside of our cubes.

Figure 10-1 shows a simplified version of our Max Min Manufacturing DM cube with three dimensions: Dim Machine, Dim Product, and Dim Time. The dimensions divide the cube up into smaller sections. These are cells.

Figure 10-1: A simplified Max Min Manufacturing DM cube

In our cubes, cells contain the values for our measures. In Figure 10-1, the highlighted cell contains the measures for the Woodland Creatures product type produced on the Clay Molder machine type for all of 2004. We can see that 951,959 products were produced while 10,143 products were rejected and 89,720 minutes were spent in this manufacturing process. If cells contain the measures in a cube, then it is important that we know how to properly address them.

Identifying the location of a cell in a cube is similar to identifying the location of a point on a three-dimensional graph. On a 3-D graph, we specify the value from each axis (or dimension) that corresponds to the point's location along that axis. Our cube works the same way. We specify the member from each dimension that identifies the cell's location along that dimension. So the cell in Figure 10-1 has a location of

 [Clay Molder], [Woodland Creatures], [2004] 

Note 

Identifiers in MDX—member names, dimension names, hierarchy levels, and so forth—must be enclosed in square brackets ([]) when they contain a space or other special character or start with a numeral. Because we often have one or more spaces in our identifiers and they often start with numerals, it is a good idea to always enclose identifiers in square brackets, whether they need them or not. This saves trouble down the road.

Tuples

The cell location we came up with from Figure 10-1 has a special name. It is a tuple (pronounced to pull). A tuple is a list of dimension members with one member present from each dimension.

In our example from Figure 10-1 and in most of the examples in this chapter, we are going to look at examples with three dimensions. Therefore, our tuples have three members. We are using three dimensions because this is easier for you to grasp and for me to illustrate. (I haven't yet mastered drawing in four-dimensional space!) Just don't fall into the trap of thinking all tuples have three members. A cube with 100 dimensions would have a tuple that includes 100 members.

In MDX expressions, tuples are enclosed in parentheses. So our tuple from Figure 10-1 becomes

 ([Clay Molder], [Woodland Creatures], [2004]) 

You can think of it this way: the information between the parentheses, no matter if it is three members or a hundred members, points to a single cell.

Note 

MDX expressions are not case-sensitive. The tuple ([clay molder], [woodland creatures], [2004]) is also Valid.

Levels

You are probably thinking, what's the big deal about locating cells? Creating tuples seems pretty straightforward. Well, it would be except for one little detail. Many of our dimensions contain hierarchies. We have to take those hierarchies into account as we create our tuples.

Figure 10-2 shows the structure and the members of the Dim Product hierarchy in the Max Min Manufacturing DM cube. In this hierarchy, we created three levels: the product type, the product subtype, and the product. In addition to the levels we defined, Analysis Services automatically adds another level at the top of the dimension. In Figure 10-2, this upper level is given the same name as the dimension itself: Dim Product. The upper level always contains a single element called All. This element contains all of the dimension members from the lower levels.

Figure 10-2: The Dim Product dimension

Suppose we want to specify the location of the cell containing measures for the Dragon w/Knight product. Figure 10-3 shows the path we must follow from the highest level of the dimension to reach this product at the lowest level. To specify this location in a tuple, start with the name of the dimension. Then follow the path from the top of the hierarchy to the bottom, including every member we pass along the way.

Figure 10-3: The path to the Dragon w/Knight product

What we end up with is this:

 [Dim Product].[All].[Mythic World].[Dragons].[Dragon w/Knight] 

If you look at this expression in Figure 10-3, you can see by the gray shading which level of the hierarchy provided each piece of the path. Of course, dimensions with more hierarchies are going to have longer expressions.

We do not always want to retrieve measures at the lowest level of a hierarchy. We may want to see figures for a product subtype or even a product type. Figure 10-4 shows this situation. Here, we want measures for the WWII product subtype. Again, we start with the name of the dimension, and then follow the path until we get to the desired member. We do not have to go all the way to the bottom of the tree. The correct expression from Figure 10-4 is:

 [Dim Product].[All].[Warriors Of Yore].[WWII] 

Figure 10-4: The path to the WWII product type

Shortcuts

You can see these expressions tend to get long rather quickly. Fortunately, MDX scripting provides some shortcuts for us. Figure 10-5 illustrates one of these shortcuts. Instead of following the chain of members all the way down, we specify the name of the dimension, as before, but then add the name of the hierarchy level we are interested in and, finally, the member of that hierarchy. So, the expression becomes

Figure 10-5: A shortcut path to the WWII product type

 [Dim Product].[Product Subtype].[WWII] 

Using this shortcut, it is possible to specify any dimension member with only three items. In a hierarchy with a large number of levels, this can save a lot of typing. However, this is not the only way we can save keystrokes.

It is possible to leave out additional pieces of this path. We can drop either the name of the dimension, the dimension level, or both. The key is this: we always have to end up with a nonambiguous location in the dimension. In our Max Min Manufacturing DM cube, any of the following expressions are valid references to the WWII member:

 [Product Subtype].[ WWII] [Dim Product].[ WWII] [ WWII] 

Because WWII is a unique member name across all levels of all dimensions in our cube, that's all we need to uniquely identify the dimension member.

Even if we are using our original method from Figure 10-3 and Figure 10-4, we can take some shortcuts. First, we can leave out the All member. Thus, our expression from Figure 10-3 becomes

 [Dim Product].[Mythic World].[Dragons].[Dragon w/Knight] 

The name of the dimension can be removed as well:

 [Mythic World].[Dragons].[Dragon w/Knight] 

We can also trim off members from the top of the hierarchy, so this still works:

 [Dragons].[Dragon w/Knight] 

The one thing we cannot do is drop members out of the middle of the path. Therefore, this expression will not work:

 [Mythic World].[Dragon w/Knight] 

Expression Errors

If we inadvertently enter an expression for a dimension member that is incorrect, we do not receive an error message. Instead, we end up with nothing for this dimension. The tuple that included the errant dimension is not included in the final result.

This is both a benefit and a problem. It is a benefit because an invalid path expression does not bring everything to a screeching halt. It is a problem because our expression may return information that appears good, but is missing something we expected!

Default Members

Now that we know how to specify expressions For hierarchical dimensions, let's go back to building tuples. Figure 10-6 shows a more complex, but still watered-down, representation of the Max Min Manufacturing DM cube. This cube includes two-level hierarchies on all three of its dimensions.

Figure 10-6: A more complex version of the Max Min Manufacturing DM cube

Figure 10-6 has one cell highlighted in the cube. The tuple identifying this location is also shown in the figure. Here we are using a shortcut and omitting the dimension names from our path expressions. We could even shorten this up a bit more:

 ([Machine #1], [Wolves], [2004].[Q1]) 

As the dimension members are shown in the figure, we could not use the following:

 ([Machine #1], [Wolves], [Q1]) 

There is a Q1 member under 2004 and under 2005, so this last path expression would result in an ambiguous reference.

Note 

In the actual Max Min Manufacturing DM cube we created, we use the form 2004Q1 for quarters and 200401 for months, so there is no problem with ambiguous references, even if we use only a single member name.

What happens if we leave one of the dimensions out of a tuple? Figure 10-7 shows the result. The tuple in this figure includes a member from the Dim Product dimension and the Dim Time dimension. It does not include a member from the Dim Machine dimension.

Figure 10-7: A tuple with one dimension omitted

In this situation, Analysis Services supplies the member for the missing dimension. It does this by using the default member. In this case, the default member for the Dim Machine dimension is All. (The All member is not shown in the dimensional hierarchies in Figure 10-7, but is there at the top of each hierarchy by default.) The path expression shown in Figure 10-7 is the equivalent of

 ([All], [Woodland Creatures].[Wolves], [2004].[Q1]) 

Each dimension in the cube has a default member. In almost all cases, the All member is most appropriate for the default member. For some dimensions, however, a different member makes more sense. For instance, it may be reasonable to have the Dim Time dimension to default to the current year or current quarter. We set the default member of the Dim Time dimension in the Max Min Manufacturing DM cube to 2005 in our next Learn By Doing activity.

The result of this tuple in Figure 10-7 is not six cells. Instead, it is a single cell. This cell contains aggregates of each measure for the first quarter of 2004 for Wolves that were produced on any of the machines. The cell is an aggregate, but it is still a single cell—a single number for each measure in the cube.

Indeed, tuples, as we have seen them expressed so far, can only return a single cell from the cube. Next, we see how this can be done.

Sets

Figure 10-8 shows another aggregate cell in our simplified Max Min Manufacturing DM cube. This cell contains aggregates of each measure for 2004 for Woodland Creatures produced on any of the machines. Suppose, instead of a single aggregate cell, we want to see the measures from all of the individual cells at the lower levels of the hierarchy.

Figure 10-8: Another aggregate cell in the Max Min Manufacturing DM cube

What we want is the set of cells shown in Figure 10-9. To do this, we need to use a group of tuples to specify each of the cells in our set. We express this set of tuples as follows:

 {([Am. Eagles], [2004].[Q1]), ([Am. Eagles], [2004].[Q2]),  ([Am. Eagles], [2004].[Q3]), ([Am. Eagles], [2004].[Q4]),  ([Black Bears], [2004].[Q1]), ([Black Bears], [2004].[Q2]),  ([Black Bears], [2004].[Q3]), ([Black Bears), [2004].[Q4]),  ([Wolves], [2004].[Q1]), ([Wolves], [2004].[Q2]),  ([Wolves], [2004].[Q3]), ([Wolves], [2004].[Q4]),  ([Moose], [2004].[Q1]), ([Moose], [2004].[Q2]),  ([Moose], [2004].[Q3]), ([Moose], [2004].[Q4])} 

Figure 10-9: A multicell set

Note the curly brackets ( { } ) surrounding the entire set, in addition to the parentheses around each tuple.

This notation gets the job done, but it is inefficient. Let's look at another way to specify a set that requires fewer keystrokes.

Range Operator

Excel is another Microsoft product that deals with cells. This program has a special syntax for specifying a range of cells. This is the colon (:) operator. To specify cells Al through A15, we enter A1:A15 in an Excel expression.

The same is true in Analysis Services. We can use the colon operator to specify a range of members in a tuple. To specify the set shown in Figure 10-9, we can use the range operator as follows:

 ([American Eagles]:[Wolves], [2004].[Q1]:[2004].[Q4]) 

This may not be quite as self-documenting as the set notation, but it is more efficient.

Of course, some groupings cannot be specified by a range. One such grouping is shown in Figure 10-10. Because these cells are not contiguous, we cannot use a range.

Figure 10-10: A cell group requiring a set

We can also combine ranges and sets, as shown in Figure 10-11.

Figure 10-11: A cell group using a set with ranges

Name Attributes

When we set up the MaxMinManufacturingDM data mart relational database, we used integer key fields to identify members in the dimensions. The foreign key relationships we created in the data mart are all based on these integer keys. When the OLAP cube was created on top of this data mart, the integer keys were used as the unique identifiers for our members.

This is not convenient. We don't want our users to have to remember that the Wolf Paperweight is product 21 and the plant in Mumbai is plant 6. As a matter of fact, we would not like to have to remember this stuff either. Fortunately, we and our users don't have to.

In addition to having integer key fields in our relational data mart, we also have text fields that contain names for each member value. The text fields became attributes in our OLAP cube. If we tell Analysis Services which attribute contains the name for each dimension hierarchy, we can use those names in our MDX expressions. In fact, we have anticipated this by using names rather than key values in all of the examples in this chapter. We see how to specify the name attributes using the NameColumn property in the section "Learn By Doing—Default Members, Level Names, and the NameColumn Property."

At times, we may need to reference a member by its key value rather than its name. We can do this by putting an ampersand (&) in front of the member key. For example:

 [Dim Product].[Product].&[21] 

is the same as

 [Dim Product].[Product].[Wolf Paperweight] 

Learn By Doing—Default Members, Level Names, and the NameColumn Property

Features Highlighted

• Providing user-friendly hierarchy level names

• Specifying a name column for hierarchy levels

• Specifying the sort order of a hierarchy

Business Need To make it easier for our users to query the Max Min Manufacturing DM cube, we need to provide more user-friendly names for our hierarchy levels. We should also specify which attribute holds the name for each hierarchy level. In addition, we can set the sort order of the hierarchies. Finally, we can set the default member for our Dim Time dimension to 2005.

Steps

1. Open the Business Intelligence Development Studio.

2. Open the MaxMinManufacturingDM project.

3. Double-click the Dim Time.dim dimension in the Solution Explorer. The Dimension Design tab appears.

4. Select the YearOfManufacture entry in the Attributes area.

5. In the Properties window, click the DefaultMember property. An ellipsis (…) button appears. Click the ellipsis button. The Set Default Member dialog box appears.

6. Select Choose Member to Be the Default. Expand the All entry and select 2005. The Set Default Member dialog box appears as shown in Figure 10-12.

7. Click OK to exit the Set Default Member dialog box.

8. Right-click the YearOfManufacture—QuarterOfManufacture—MonthOfManufacture—DateOfManufacture entry at the top of the Hierarchies and Levels area and select Rename from the Context menu.

9. Enter Time Hierarchy for the Name.

10. Rename all of the levels in the Hierarchies and Levels area as follows:

    Old Name	New Name
    YearOfManufacture	Year
    QuarterOfManufacture	Quarter
    MonthOfManufacture	Month
    DateOfManufacture	Day

    The Dimension Design tab should appear as shown in Figure 10-13.

11. Click the Save All button in the toolbar.

12. Close the Dimension Design tab.

13. Double-click the Dim Product.dim dimension in the Solution Explorer. The Dimension Design tab appears.

14. Select the Dim Product Subtype entry in the Attributes area.

15. In the Properties window, select (new) from the NameColumn drop-down list. The Object Binding dialog box appears.

16. Select ProductSubtypeName in the Source Column list.

17. Click OK to exit the Object Binding dialog box.

18. Again, in the Properties window, select Name from the OrderBy drop-down list.

19. Select the Dim Product Type entry in the Attributes area.

20. In the Properties window, select (new) from the NameColumn drop-down list. The Object Binding dialog box appears.

21. Select ProductTypeName in the Source Column list.

22. Click OK to exit the Object Binding dialog box.

23. Again, in the Properties window, select Name from the OrderBy drop-down list.

24. Rename the items in the Hierarchies and Levels area as follows:

    Old Name	New Name
    Dim Product Type — Dim Product Subtype	Product Hierarchy
    Dim Product Type	Product Type
    Dim Product Subtype	Product Subtype
    Dim Product	Product

    With the modified attributes expanded, the Dimension Design tab should appear as shown in Figure 10-14.

25. Click the Save All button in the toolbar.

26. Close the Dimension Design tab.

27. Double-click the Dim Machine.dim dimension in the Solution Explorer. The Dimension Design tab appears.

28. Set the NameColumn property and the OrderBy property for the following items in the Attributes area:

    Item	NameColumn	OrderBy
    Dim Country	CountryName	Name
    Dim Machine	MachineName	Name
    Dim Plant	PlantName	Name

29. Rename the items in the Hierarchies and Levels area as follows:

    Old Name	New Name
    Dim Material—Dim Machine Type	Material Hierarchy
    Dim Material	Material
    Dim Machine Type	Machine Type
    Dim Machine (under the Material Hierarchy)	Machine
    Dim Country—Dim Plant	Plant Hierarchy
    Dim Country	Country
    Dim Plant	Plant
    Dim Machine (under the Plant Hierarchy)	Machine

    With the modified attributes expanded, the Dimension Design tab should appear as shown in Figure 10-15.

30. Click the Save All button in the toolbar.

31. Close the Dimension Design tab.

32. Double-click the Dim Batch.dim dimension in the Solution Explorer. The Dimension Design tab appears.

33. Set the NameColumn property and the OrderBy property for the following item in the Attributes area:

    Item	NameColumn	OrderBy
    Dim Batch	BatchName	Name

    With the modified attribute expanded, the Dimension Design tab should appear as shown in Figure 10-16.

34. Click the Save All button in the toolbar.

35. Close the Dimension Design tab.

36. Select Build | Deploy MaxMinManufacturingDM from the Main menu.

Figure 10-12: The Set Default Member dialog box

Figure 10-13: The Dim Time dimension with revised hierarchy level names

Figure 10-14: The Dim Product dimension with revised hierarchy level names

Figure 10-15: The Dim Machine dimension with revised hierarchy level names

Figure 10-16: The Dim Batch dimension with revised hierarchy level names

Getting there from Here

We now know how to address any cell or group of cells in the cube. This type of absolute navigation is important as we move toward being able to manipulate and extract information from our cubes. We need to master one other skill, which is the skill of relative navigation. Relative navigation is the capability to start from a given location and move to another location based on that starting point.

For example, suppose we are examining measures for Wolves produced on Machine #6 in Q1, 2005. This is the Current Cell in Figure 10-17. We may want to look at those same measures from one year ago. This is designated as the Comparison Cell in the figure. We need to know how to find the position of the Comparison Cell relative to the Current Cell.

Figure 10-17: Comparing measures from two cells using relative location

Of course, we could do this using the absolute navigation we already know. We know the tuple for the Comparison Cell is

 ([Machine #6], [Wolves], [2004].[Q1]) 

This works fine until we move on to look at the measures for Q2, 2005. Now we have to figure out the tuple for the Comparison Cell all over again. Perhaps, we are looking at measures in a set of cells, say Q1, 2005 through Q4, 2005. Now it becomes even more tedious to manually determine the tuples for all of the Comparison Cells.

Even more helpful would be a way to identify one cell by its relative position to another cell. Fortunately, MDX scripting provides a way for us to do this: through the use of MDX functions. But, before we look at functions that help us determine the tuple for the Comparison Cell, we need a function that helps us determine the tuple for the Current Cell.

The Starting Point—The CurrentMember Function

Like any other cell, the Current Cell can be identified by a tuple. This tuple contains the current member from each hierarchy in the cube. But how can we determine what those current members are?

The answer is through the use of the CurrentMember function. For example, we can use the following expression to find out the current member of the Time Hierarchy dimension:

 [Dim Time].CurrentMember 

In the example in Figure 10-17, this expression would return the [2005].[Q1] member.

As with any function, the CurrentMember function takes input and returns output. In MDX expressions, the function's input is whatever precedes the function. In this case, that input is the [Dim Time] hierarchy. In fact, the Current Member function requires either a hierarchy or a dimension as its input. This makes sense because only hierarchies and dimensions have members.

The CurrentMember function returns a member. The CurrentMember function does not return a string containing the name of the member. Instead, it returns a reference to the member itself. This distinction is important as we begin to apply other MDX functions to the result returned by the CurrentMember function.

Function	Input	Output	Description
CurrentMember	Hierarchy or Dimension	Member	Returns the current member of the specified hierarchy or dimension

Relative Position Within a Hierarchy Level—The Lag, Lead, NextMember, and PrevMember Functions

A number of MDX functions enable us to select a member relative to a specified member within the same hierarchy. These are shown in Figure 10-18. Assuming the [Dim Time].CurrentMember is [2005].[Q1], the expressions corresponding to the figure are shown here:

Expression	Resulting Member
[Dim Time].CurrentMember.Lag(4)	[2004].[Q1]
[Dim Time].CurrentMember.Lead(-4)	[2004].[Q1]
[Dim Time].CurrentMember.PrevMember	[2004].[Q4]
[Dim Time].CurrentMember.NextMember	[2005].[Q2]
[Dim Time].CurrentMember.Lead(3)	[2005].[Q4]
[Dim Time].CurrentMember.Lag(-3)	[200S].[Q4]

Figure 10-18: MDX relative position functions within the same hierarchy

As you can see, the PrevMember function takes us to the member immediately preceding the current member. The NextMember function takes us to the member immediately after the current member. These functions are useful for looking at the way measures are trending from one period to the next.

The Lag and Lead functions require an additional parameter. This is an integer representing the number of members to lag or lead. This second parameter is passed in parentheses immediately after the function name.

The Lag function moves us backward by the specified number of members. The Lead function moves us forward by the specified number of members. Using a negative integer for the number of members parameter reverses the natural direction of each function.

Function	Input	Output	Description
Lag(N)	Member, N = number of members	Member	Returns the member that is N before the specified member. (A negative value for N reverses the direction of the function.)
Lead(N)	Member, N= number of members	Member	Returns the member that is N after the specified member. (A negative value for N reverses the direction of the function.)
NextMember	Member	Member	Returns the member that is immediately after the specified member.
PrevMember	Member	Member	Returns the member that is immediately before the specified member.

Immediate Relative Position Between Hierarchy Levels—The Children, FirstChild, FirstSibling, LastChild, LastSibling, Parent, Siblings Functions

Just as we may want to move across a hierarchy level, we may want to move up and down between hierarchy levels. A number of MDX functions can help us here as well. We begin by looking at the functions that deal with the immediate family in the hierarchy, and then look at functions that return more distant relations.

The functions dealing with immediate relatives are shown in Figure 10-19. Assuming the (Dim Product].CurrentMember is [Roman Legion], the expressions corresponding to the figure are shown here:

Expression	Resulting Member(s)
[Dim Product].CurrentMember.Children	[Legionnaire w/Shield], [Legionnaire w/Spear], [Legionnaire w/Sward]
[Dim Product].CurrentMember.Firstchild	[Legionnaire w/Shield]
[Dim Product].CurrentMember.FirstSibling	[Am. Revolution]
[Dim Product].CurrentMember.Lastchild	[Legionnaire w/Sword]
[Dim Product].CurrentMember.LastSibling	[WWII]
[Dim Product].CurrentMember.Parent	[Warriors Of Yore]
[Dim Product].CurrentMember.Siblings	[Am. Revolution], [Napoleonic], [Roman Legion], [WWII]

Figure 10-19: MDX immediate relative position functions within the same hierarchy

Most of the functions are self-explanatory, but a word about the Siblings functions may be helpful. If you look at the list of members returned by the Siblings function, you see that the Roman Legion member is included. This is true even though Roman Legion is the current member when we call the function. The member itself is always included in a list of its siblings.

Function			Input		Output		Description
Children			Member		Set		Returns the set of members from the hierarchy level immediately below the specified member that is related directly to the specified member
FirstChild	Member		Member		Returns the first member from the hierarchy level immediately below the specified member that is related directly to the specified member
FirstSibling	Member		Member		Returns the first member that shares the same parent with the specified member
LastChild	Member		Member		Returns the last member from the hierarchy level immediately below the specified member that is related directly to the specified member
LastSibling	Member		Member		Returns the last member that shares the same parent with the specified member
Parent	Member		Member		Returns the members from the hierarchy level immediately above the specified member that is related directly to the specified member
Siblings	Member		Set		Returns the set of members that shares the same parent with the specified member

Distant Relative Position Between Hierarchy Levels—The Ancestor, Cousin, Descendants Functions

In addition to the functions that return the close relatives of a member, a number of MDX functions let us select more distant relatives. These are shown in Figure 10-20. The expressions corresponding to the figure are shown here:

Expression	Resulting Member
Ancestor([2003].[Q3].[Jul], [Dim Time] .[YearOfManufacture]) or Ancestor ([2003].[Q3].[Jul], 2)	[2003]
Cousin([2003].[Q2].[Apr], [2003].[Q4])	[2003].[Q4].[Oct]
Descendants([2005], [MonthOfManufacfure]) or Descendants([2005], [2])	[2005].[Q1].[Jan],[2005].[Q1].[Feb], [2005].[Q1].[Mar], [2005].[Q2].[Apr], [2005].[Q2].[May], [2005].[Q2].[Jun], [2005].[Q3].[Jul], [2005].[Q3].[Aug], [2005].[Q3].[Sep], [2005].[Q4].[Oct], [2005].[Q4].[Nov], [2005].[Q4].[Dec]

Figure 10-20: MDX distant relative position functions within the same hierarchy

The Ancestor function returns the parent, grandparent, great-grandparent, and so forth of the specified member. The Ancestor function requires two parameters, both of which must be placed within the parentheses. The first parameter is the member that serves as the starting point for the function. The second parameter is either the hierarchy level where the ancestor is to be found or an integer specifying the number of levels to move upward to find the ancestor.

The Cousin function finds the equivalent member at the same level of the hierarchy, but down a different branch of the hierarchy structure. For example, January is the first month of Q1 . Its cousins would be the first months in the other three quarters of the year, namely, April, July, and October. Likewise, Q2 is the second quarter in 2003. Its cousins would be Q2, 2004 and Q2, 2005. Obviously, this is extremely helpful when we are trying to compare like periods in the time dimension.

The Cousin function requires two parameters, both of which must be placed within the parentheses. The first parameter is the member whose cousin is to be found. The second parameter is a member at a higher level in the hierarchy under which the cousin is to be found.

The Descendants function returns the children, grandchildren, great grandchildren, and so forth of the specified member. The Descendants function requires two parameters, both of which must be placed within the parentheses. The first parameter is the member that serves as the starting point for the function. The second parameter is either the hierarchy level where the descendants are to be found or an integer specifying the number of levels to move downward to find the descendants.

Function	Input	Output	Description
Ancestor	Member, Hierarchy Level, or number of levels to go up	Member	Returns the member that is a parent, grandparent, and so forth of the specified member at the specified level
Cousin	Member, Member	Member	Returns the member that is in the same sibling position as the specified member
Descendants	Member, Hierarchy Level, or number of levels to go down	Set	Returns a set of members that are the children, grandchildren, and so forth of the specified member