Monday, July 13, 2015

MDX Top function

If we need get Top n records from a relational database table, we simply use SELECT TOP (n).... But how do you do the same with Multi-dimensional database like Analysis Services databases? MDX is the language we used for querying Analysis Services databases, or Cubes. MDX function called TopCount  is the known function for similar operation but it is believed that it has to be used with a numeric expression and the result of TopCount is based on values of the numeric expression used.

In a way, it is true. But what if we need to get a set of dimension members using Top functionality without using a numeric expression. To elaborate, if I want to get 5 products from Products table, I can write something like below using TSQL;

USE AdventureWorks2014;
GO

SELECT TOP (5) Name
FROM Production.Product;

If I want to do the same with Product Dimension in a cube, how can it be done? Can I use TopCount without using a numeric expression? Yes, it is possible. The numeric expression for TopCount function is optional, hence it works like below;

SELECT 
 {} ON 0
 , {TopCount([Product].[Product Categories].[Product].Members, 3)
  } ON 1
FROM [Adventure Works];


In addition to that, Head  function can be used too.

SELECT 
 {} ON 0
 , {Head([Product].[Product Categories].[Product].Members, 3)
  } ON 1
FROM [Adventure Works];


No comments: