George's BI Blog

OpenQuery + Linked Server is commonly used if you want to querying against SSAS cube through T-SQL. However, you cannot use “top N” to specify how many records you want to retrieve. try SELECT top 1 1 AS id, * FROM OPENQUERY(<Linked Server>,<Your MDX Query>) yourself and you’ll find all records are returned, which means the top 1 is ignored.

However, if you try the following:

Select *
from (	Select top 1000 ROW_NUMBER() over(order by id) as RowNum, t.*
		from (	SELECT top 1 1 AS id, * 
				FROM OPENQUERY(<Linked Server>,<Your MDX Query>)) as t
      ) as ds

You’ll get top 1000 rows if the whole result set contains more.

Now to achieve fake (what I mean by fake is that it is not true pagination at SSAS side) pagination, you do sth like:

DECLARE @start AS INT
		,@end AS INT
set @start = 1
set @end = 20

Select *
from (	Select top 1000
			ROW_NUMBER() over(order by id) as RowNum, t.*
		from (	SELECT top 1 1 AS id, * 
				FROM OPENQUERY(<Linked Server>,<Your MDX Query>)) as t
     ) as ds
where ds.RowNum between @start and @end
Go