SSAS MDX Queries part 3

 

TopCount Function

TopCount MDX function is one of the most useful function in analyzing data. Most of the user are interested in knowing top and bottom performers(entities). For example, Finding top 5 customers for particular product revenue.

Syntax: TOPCOUNT( «Set», «Count»[, «Numeric Expression»] )

Topcount Function is useful when there is need to show specific number of set members with Highest value. Here is the example to explain this function:

SELECT
 {[Measures].[Internet Order Count],[Measures].[Sales Amount]} ON COLUMNS
 ,TopCount
  (
    [Sales Territory].[Sales Territory Region].[Sales Territory Region]
   ,2
   ,[Measures].[sales Amount]
  ) ON ROWS
FROM [Adventure Works];

BottomCount Function 

BottomCount MDX function is one of the most useful function in analyzing data. Most of the user are interested in knowing top and bottom performers(entities). For example, Finding top and bottom 5 customers for particular product revenue.
 

Syntax: BOTTOMNCOUNT( «Set», «Count»[, «Numeric Expression»] )

Bottomcount Function is useful when there is need to show specific number of set members with lowest value

Example
SELECT
 NONEMPTY({[Measures].[Internet Order Count],[Measures].[Sales Amount]}) ON COLUMNS
 ,BottomCount
  (
    NONEMPTY([Sales Territory].[Sales Territory Region].[Sales Territory Region])
   ,2
   ,[Measures].[sales Amount]
  ) ON ROWS
FROM [Adventure Works]; 

Filter Function

Syntax: FILTER( «Set», «Search Condition» )

Filter function filters each tuple of set based on Search condition specified as second argument. 

SELECT [Measures].[Internet Sales Amount] on columns
      ,FILTER([Date].[Calendar Year].[Calendar Year].members
            ,([Measures].[Internet Sales Amount] > 4000000)) on rows
  FROM [Adventure Works]  

Order Function

Syntax: ORDER( «Set», {«String Expression» | «Numeric Expression»}[, ASC | DESC | BASC | BDESC] )

Order MDX function arranges set in specific order. It might preserve hierarchy or break it based on last argument(ASC|DESC|BASC|BDESC).

Example
WITH SET [2001_2002_Months] AS {[Date].[Calendar Year].Allmembers}
SELECT [2001_2002_Months] on Rows
      ,ORDER([Measures].[Internet Sales Amount],ASC) on Columns
  FROM [Adventure Works]  

Extract function

The Extract function returns a set that consists of tuples from the extracted hierarchy elements. For each tuple in the specified set, the members of the specified hierarchies are extracted into new tuples in the result set. This function always removes duplicate tuples.

The Extract function performs the opposite action of the CROSSJOIN function.

SELECT
  [Measures].[Internet Sales Amount] ON COLUMNS
 ,Extract
  (
    NonEmpty
    (
        [Date].[Calendar].[Calendar Year].MEMBERS*
        [Sales Territory].[Sales Territory].[Country].MEMBERS*
        [Measures].[Internet Sales Amount]
    )
   ,[Sales Territory].[Sales Territory]
  ) ON ROWS
FROM [Adventure Works];

TAIL Function

SYNTAX: TAIL( «Set»[, «Numeric Expression»] )

TAIL Function returns last n tuples from set. If number has not been defined as second argument than it will return first member(default value = 1). 

SELECT
  [Measures].[Internet Sales Amount] ON COLUMNS
 ,[Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works];

COUNT & DISTINCTCOUNT Function

Syntax: DISTINCTCOUNT( «Set» )

Distinctcount MDX function returns distinct members count in specified set. It calculates only non empty members.
 

Syntax: COUNT( «Set» )
Count MDX function returns members count in specified set.

Example :
WITH
  SET geography_country AS
    {
      [Geography].[Country].&[Australia]
     ,[Geography].[Country].&[Canada]
     ,[Geography].[Country].&[Canada]
     ,[Geography].[Country].&[United States]
     ,[Geography].[Country].&[United States]
    }
  MEMBER distinct_count_country AS
    DistinctCount(geography_country)
  MEMBER count_country AS
    Count(geography_country)
SELECT
  {
    distinct_count_country
   ,count_country
  } ON COLUMNS
FROM [Adventure Works];  

RanK Function

Syntax: Rank( Tuple, Set [,Numeric Expression])

Rank function provides rank of tuple within specified set.

Suppose we want to calculate ranks of each product subcategory based on internet sales amount.

Example :
WITH
  SET OrderedProducts AS
    Order
    (
      [Product].[Product Categories].[Subcategory].MEMBERS
     ,[Measures].[Internet Sales Amount]
     ,BDESC
    )
  MEMBER [Measures].[Product Rank] AS
    Rank
    (
      [Product].[Product Categories].CurrentMember
     ,OrderedProducts
    )
SELECT
  {
    [Measures].[Product Rank]
   ,[Measures].[Internet Sales Amount]
  } ON columns
 ,OrderedProducts ON rows
FROM [Adventure Works];  

COALESCEEMPTY Function

Syntax: COALESCEEMPTY( «Numeric Expression1»[, «Numeric Expression2 »...] ,....[, «Numeric Expression n»...])

COALESCEEMPTY MDX function evaluates first numeric expression and if it is empty then evaluate next expression to find non empty expression. COALESCEEMPTY function can take any number of numerical expression. Function will keep evaluating empty expression list till it finds non empty one. If COALESCEEMPTY function does not find any non empty expression then it will return null value. 

Example  :

WITH
  MEMBER COALESCEEMPTY_DEMO AS
    CoalesceEmpty
    (
      [Measures].[Internet Sales Amount]
     ,[Measures].[Reseller Sales Amount]
     ,0
    )
SELECT
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[Reseller Sales Amount]
   ,COALESCEEMPTY_DEMO
  } ON COLUMNS
 ,[Product].[Subcategory].MEMBERS ON ROWS
FROM [Adventure Works];