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
Categories: BI, SQL Analysis Services
OpenQuery, SSAS
Comments (0)
Trackbacks (0)
Leave a comment
Trackback