Home > BI, SQL Analysis Services > Top N records with OpenQuery against SSAS data sources

Top N records with OpenQuery against SSAS data sources

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
Advertisements
Categories: BI, SQL Analysis Services Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: