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];
SELECT {} ON 0 , {Head([Product].[Product Categories].[Product].Members, 3) } ON 1 FROM [Adventure Works];
No comments:
Post a Comment