SQL Lion

Although we have .Members and .Children for navigating through members of a particular dimension, but as a BI user, we need more flexibility in traversing through each level of the hierarchy.  And For this, MDX has one more amazing function called DESCENDANTS, which not only provides listing of current level members but also has functionality to display Parent and Grand-Child members till the leaf level.

We will first discover the pattern of hierarchical data and the functionality of DESCENDANTS function over it.

Above is a simple example of hierarchical data with only four levels i.e. Country, States, Cities and Retailers. Now the question » what is the role of DESCENDANTS and how it is helpful. Considering the above hierarchical information, DESCENDANTS can answer the below questions very effectively and efficiently;

• Annual sale information for all the cities under Orissa (or any state)
• Annual sale information for all the sub categories under Orissa (Including Cities and Retailers)
• Annual sale information for the leaf levels only i.e. at retailer’s level for any State or City.
• Filter on Parent along with child i.e. Annual sale for all retailers under Bhubaneswar and at the state level of Bhubaneswar i.e. for Orissa.
• Filter on Parent for a particular Retailer or City i.e. viewing annual sales for the state of Bangalore.

Now let’s get into action, to show up the above example I need at least two tables on my warehouse (I named it as MyArticlesDB). And the tables as

dbo.Location (Dimension for storing Location information)

dbo.InternetSales  (Fact to store transactional information about sales)

In real scenario, one can face more complex and nested set of tables, but for simplicity I have taken only two tables (one dimension, one fact).

The below script will create the above two tables as well as populate some sample data.

USE [MyArticlesDB]
CREATE TABLE [dbo].[Location](
	[Location_ID] [int] IDENTITY(1,1) NOT NULL,
	[Retailers] [varchar](100) NULL,
	[Cities] [varchar](100) NULL,
	[States] [varchar](100) NULL,
	[Country] [varchar](1000) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[InternetSales](
	[Location_ID] [int] NULL,
	[QtySold] [int] NULL,
	[Profit] [money] NULL
) ON [PRIMARY]
GO
INSERT INTO [Location] VALUES (1, 'Retailer#1', 'Bangalore', 'Karnataka', 'India');
INSERT INTO [Location] VALUES (2, 'Retailer#2', 'Bangalore', 'Karnataka', 'India');
INSERT INTO [Location] VALUES (3, 'Retailer#3', 'Belur', 'Karnataka', 'India');
INSERT INTO [Location] VALUES (4, 'Retailer#4', 'Bhubaneswar', 'Orissa', 'India');
INSERT INTO [Location] VALUES (5, 'Retailer#5', 'Cuttack', 'Orissa', 'India');
INSERT INTO [Location] VALUES (6, 'Retailer#6', 'Puri', 'Orissa', 'India');
INSERT INTO [Location] VALUES (7, 'Retailer#7', 'Hyderabad', 'Andhra Pradesh', 'India');
INSERT INTO [Location] VALUES (8, 'Retailer#8', 'Secunderabad', 'Andhra Pradesh', 'India');
INSERT INTO [Location] VALUES (9, 'Retailer#9', 'Guwahati', 'Assam', 'India');
GO
INSERT INTO [InternetSales] VALUES (2, 200, 250.0000);
INSERT INTO [InternetSales] VALUES (1, 12, 120.0000);
INSERT INTO [InternetSales] VALUES (3, 50, 130.0000);
INSERT INTO [InternetSales] VALUES (4, 10, 20.0000);
INSERT INTO [InternetSales] VALUES (5, 60, 350.0000);
INSERT INTO [InternetSales] VALUES (6, 55, 340.0000);
INSERT INTO [InternetSales] VALUES (7, 65, 640.0000);
INSERT INTO [InternetSales] VALUES (8, 30, 380.0000);
INSERT INTO [InternetSales] VALUES (9, 70, 890.0000);
GO

Now create a simple cube in BIDS (Business Intelligence Development Studio) for the above two tables. Below figure shows the relation between the above two tables at DSV (Data Source View) level.

Create the cube and configure the Location dimension as below:

Above I created a simple hierarchy and named it as LocationHierarchy, so that we can work with our DESCENDANTS function in MDX. Process the cube, if you haven’t done till yet.

Now the time has come to burn our fingers on MDX queries. Remember, the Location dimension has LocationHierarchy which categories all retailers (leaf level information) into four levels i.e. Country, States, Cities, Retailers which will looks like below as a hierarchical tree.

Below is the overall information of the cube that we just created. A very simple and small cube .

And below is a simple view of the data that we are going to produce by using MDX queries.

USING DESCENDANTS: DESCENDANTS is the amazing piece of machinery in MDX, that returns a set of descendants of a member by referencing any member of the provided dimension. In simple words, it can show ALL / SOME (i.e. with respect to levels) contents of any member provided.

For example, to view [Qty Sold] and [Profit] under Bangalore city, we need to specify our MDX query as below:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants([Location].[LocationHierarchy].[Cities].&[Bangalore]) ON 1
FROM [InternetSalesCube];

OUTPUT:

The above result set shows data only for Bangalore city and the sub-hierarchy levels.

DESCENDANTS function comes with some HINTS / FLAGS that facilitates user to easily navigate forth and back. All these HINTS / FLAGS are elaborated below:

Syntax:

Let’s understand the parameters of Descendants function clearly, (they said, winning a battle does not need a powerful sword but a skilled swordsman). Although, we have a powerful sword, let’s grab some skills on it. So, in order to use the weapon efficiently, one should know every in and out of the weapon .

Parameter #1: FROM_Expression: This will accept two types of Values:

• Member Expression: Suppose you want to know the sales information for all the cities under Karnataka states, then Karnataka State becomes the Member Expression for the first parameter. This will looks like something as:

[Location].[LocationHierarchy].[States].&[Karnataka]

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
 		  [Location].[LocationHierarchy].[States].&[Karnataka]
		 ) ON 1
FROM [InternetSalesCube];

Here in the above example, I provided only one parameter (Note: The rest parameters are optional). Hence, it will show results for all the levels under Karnataka state.

• Set Expression: Suppose you want to know the sales information for all the cities under Karnataka and Orissa states, then the combination of Karnataka state and Orissa state becomes the Set Expression for the first parameter. This will looks like:

[Location].[LocationHierarchy].[States].&[Karnataka] : [Location].[LocationHierarchy].[States].&[Orissa]

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		      [Location].[LocationHierarchy].[States].&[Karnataka] 
		    : [Location].[LocationHierarchy].[States].&[Orissa]
		 ) ON 1
FROM [InternetSalesCube];

Here, I am providing a set of members i.e. Karnataka and Orissa as the value for the first parameter. Hence, it will show results for all the levels under Karnataka and Orissa states.

Parameter #2: On_Or_What [Optional]: This will again accept two types of Values:

• On: Suppose you want to see sales information for Karnataka state, 1 or 2 level down i.e. at city level or retailer level. The numbers will be decided dynamically with respect to the first parameter provided.

In the previous example, the first parameter provide is [Location].[LocationHierarchy].[States].&[Karnataka] i.e. State Level. Hence, the second parameter we can provide with respect to first parameter as shown below:

0 » [States]

1 » [Cities]

2 » [Retailers]

>=2  » [Retailers] (any value greater than or equal to last level will show the last level members in the output)

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		  [Location].[LocationHierarchy].[States].&[Karnataka] 
		 ,1 /* For City Level Information */
		 ) ON 1
FROM [InternetSalesCube];

OUTPUT:

Note: Negative values i.e. <0 will not produce any result set.

• What: This is somewhat similar to “On” option, here instead of numerical level number; we can provide the level name to get the result set for that particular level. The second parameter will looks like:

[Location].[LocationHierarchy].[Cities]

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		  [Location].[LocationHierarchy].[States].&[Karnataka] 
		 ,[Location].[LocationHierarchy].[Cities]
		 ) ON 1
FROM [InternetSalesCube];

Output will be same as shown in previous example.

Note: Level names which are parent to the level provided in first parameter will not produce any result set.

If you don’t specify any thing on 2^nd parameter, it will take the level of the expression provided by 1^st parameter by default.

Parameter #3: Desc_Flag [Optional]: This one I like the most, as it gives you the full power to look back and forth . This parameter accepts seven types of values / Hints. This asks you a question: “What else you want?”  :)

• SELF (Default): This is the default option. Whatever is specified in the 2^nd parameter, it will display the members of that level only.

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		  [Location].[LocationHierarchy].[States].&[Karnataka] 
		 ,[Location].[LocationHierarchy].[Cities]
		 ,SELF
		 ) ON 1
FROM [InternetSalesCube];

Output will be same as shown in previous example, try all previous examples with ‘SELF’ as 3^rd parameter, you will get same results as before.

• BEFORE: This will list down all the members in between the level of the 1^st parameter provided and the 2^nd parameter, but will exclude the members of the level in 2^nd parameter.

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		   [Location].[LocationHierarchy].[States].&[Karnataka] 
		  ,[Location].[LocationHierarchy].[Retailers]
		  ,BEFORE
		 ) ON 1
FROM [InternetSalesCube];

Notice that the output shows result in between the levels of 1^st parameter and 2^nd parameter, but excluded the 2^nd parameter level i.e. Retailers. The members marked cross (X) are not included in the result set. It is traversing from 2^nd parameter’s level to 1^st parameter’s level.

• SELF_AND_BEFORE: This is same as BEFORE option, but with the exception that it will include the 2^nd parameter level. This will list down all the members in between the level of the 1^st parameter provided and the 2^nd parameter along with the members of 2^nd parameter’s level.

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		  [Location].[LocationHierarchy].[States].&[Karnataka] 
		  ,[Location].[LocationHierarchy].[Retailers]
		  ,SELF_AND_BEFORE
		 ) ON 1
FROM [InternetSalesCube];

• AFTER: This is another fascinating keyword that will list down all the members after the level provided in 2^nd parameter (excluding the members of 2^nd parameter’s level) but will list down all the members falling under 1^st parameter.

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		   [Location].[LocationHierarchy].[States].&[Karnataka] 
		  ,[Location].[LocationHierarchy].[Cities]
		  ,AFTER
		 ) ON 1
FROM [InternetSalesCube];

Note: It’s listing down all the members under Karnataka state i.e. 1^st parameter which are coming after 2^nd parameter’s level. The members marked cross (X) are not included in the result set.

It is traversing from 2^nd parameter’s level till the Leaf level.

• SELF_AND_AFTER: This is same as AFTER option, but it will also include the 2^nd parameter level’s members. This will list down all the members after the level provided in 2^nd parameter (including the members of 2^nd parameter’s level), for all those members that are falling under 1^st parameter.

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		   [Location].[LocationHierarchy].[States].&[Karnataka] 
		  ,[Location].[LocationHierarchy].[Cities]
		  ,SELF_AND_AFTER
		 ) ON 1
FROM [InternetSalesCube];

• SELF_BEFORE_AFTER: If you want to check for both the down level and up level members from the 2^nd parameter’s level, then this is what you need to provide in 3^rd parameter. In general it will list all the members under 1^st parameter’s level till the leaf level.

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		   [Location].[LocationHierarchy].[States].&[Karnataka] 
		  ,[Location].[LocationHierarchy].[Cities]
		  ,SELF_BEFORE_AFTER
		 ) ON 1
FROM [InternetSalesCube];

Note: It is combined result of SELF, BEFORE and AFTER. (Please do not get confused by looking at the result set from the above examples, in each example, the 2^nd parameter is different. Keep the 2^nd parameter same for all, then you will get the combined result as same.)

• LEAVES: Now here comes the most confusing keyword, which guides many developers in the bugs’ pool . By name it seems that it will list down all the leaf level members for a particular member provided in the first parameter. And indeed it does but it got affected by 2^nd parameter i.e. the leaf level members will be considered as the last level in between the 1^st parameter’s level and 2^nd parameter’s level.

Example:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		   [Location].[LocationHierarchy].[States].&[Karnataka] 
		  ,[Location].[LocationHierarchy].[Cities]
		  ,LEAVES
		 ) ON 1
FROM [InternetSalesCube];

Note: In the above query, only two levels (i.e. States » 1^st parameter and Cities » 2^nd parameter) are eligible and the lower level from these two is the Cities level; hence, the result set shows Bangalore and Belur.

But if you specify the Retailers in 2^nd parameter, you will get all the members of retailer level as the lowest level in between 1^st and 2^nd parameters is the Retailers level:

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		   [Location].[LocationHierarchy].[States].&[Karnataka] 
		  ,[Location].[LocationHierarchy].[Retailers]
		  ,LEAVES
		 ) ON 1
FROM [InternetSalesCube];

NULL / Empty Parameter values: Now let’s see how default values behaves (NULL / EMPTY) for the optional parameters.

Scenario #1: Without any optional parameters

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		[Location].[LocationHierarchy].[States].&[Karnataka] 
		 ) ON 1
FROM [InternetSalesCube];

It shows all the members corresponding to all the levels under Karnataka state.

Scenario #2: With one or more EMPTY optional parameters

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		[Location].[LocationHierarchy].[States].&[Karnataka] 
		,
		,
		 ) ON 1
FROM [InternetSalesCube];

The 2^nd parameter is taken as 1^st parameter’s level by default.

Scenario #3:  Providing 2^nd parameter empty or NULL

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		[Location].[LocationHierarchy].[States].&[Karnataka] 
		,NULL
		,SELF_AND_AFTER 
		 ) ON 1
FROM [InternetSalesCube];

This concludes my Scenario #2.

Scenario #4:  Providing 3rd parameter as NULL

SELECT 
  {
    [Measures].[Qty Sold]
   ,[Measures].[Profit]
  } ON 0
 ,Descendants(
		[Location].[LocationHierarchy].[States].&[Karnataka] 
		,NULL
		,NULL
		 ) ON 1
FROM [InternetSalesCube];

OUTPUT: It will throw parsing error itself:

Executing the query …
Query (2, 2) The third argument of the Descendants function must be one of the following: SELF, BEFORE, AFTER, SELF_AND_BEFORE, SELF_AND_AFTER, BEFORE_AND_AFTER, SELF_BEFORE_AFTER, or LEAVES.
Execution complete

Conclusion: We understood the power of DESCENDANTS and learned how to use it. Now let’s get ready for the battle  .

Waiting for your valuable feedback and suggestions on this topic.