8000 character limit in OPENQUERY against Analysis Server

Question

I have a query like

SELECT column1, column2 FROM OPENQUERY(AnalysisServerName, 'MDX QUERY ...').

It is inside a stored procedure. A MDX query is dynamically built in it and the length of the query much longer than 8000 characters (can reach up to 400 000 signs).

The MDX query returns something about 200 columns and I need only some of them. I am not able to reduce the amount because of dimensions complexity.

So, I have to write results to a temporary table or directly SELECT them as the result of the procedure. I decided to SELECT them. But, when I try to omit the limitation of 8000 characters with EXEC OPENQUERY(AnalysisServerName, 'MDX QUERY ...') AT AnalysisServerName, I can't SELECT or even save them to a temporary table as it happens inside the stored procedure and nesting is not allowed (following this thread).

I wish I can do

SELECT column1, column2 FROM (EXEC OPENQUERY(AnalysisServerName, 'MDX Query') AT AnalysisServerName)

Even if I can execute a very long MDX query directly on Analysis Server

DECLARE @myStatement VARCHAR(MAX)
SET @myStatement = 'OPENQUERY(AnalysisServerName, 'MDX Query')'

EXECUTE (@myStatement) AT AnalysisServerName

I can't do anything with the results in my stored procedure, because

DECLARE @myStatement VARCHAR(MAX)
SET @myStatement = 'SELECT column1, column2 FROM OPENQUERY(AnalysisServerName, 'MDX Query')'

EXECUTE (@myStatement) AT AnalysisServerName

is not allowed.

---

So what can I do in such situation?

I need to

• query Analysis Service with OPENQUERY,
• handle the query of length greater than 8000,
• save the results to a temporary table or select them directly.

Answer 1

I need to

• query Analysis Service with OPENQUERY,
• handle the query of length greater than 8000,
• save the results to a temporary table or select them directly. sql-serverssasmdxEdit tags

Instead of OPENQUERY use INSERT ... EXEC ... AT LinkedServer, like this:

create table #t(id int, a int)

insert into #t
exec ('select 1 id, 2 a') at LinkedServer

select * from #t

Here's a sample using SSAS and MDX:

USE [master]
go

/****** Object:  LinkedServer [SSAS]    Script Date: 7/29/2021 11:27:57 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SSAS', @srvproduct=N'', @provider=N'MSOLAP', @datasrc=N'localhost', @catalog=N'Adventure Works Internet Sales'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation compatible', @optvalue=N'false'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'data access', @optvalue=N'true'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'dist', @optvalue=N'false'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'pub', @optvalue=N'false'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc', @optvalue=N'true'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc out', @optvalue=N'true'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'sub', @optvalue=N'false'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'connect timeout', @optvalue=N'0'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation name', @optvalue=null
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'lazy schema validation', @optvalue=N'false'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'query timeout', @optvalue=N'0'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'use remote collation', @optvalue=N'true'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'remote proc transaction promotion', @optvalue=N'false'
go



drop table if exists #t
go
create table #t
(
  [[Date]].[Fiscal Year]].[Fiscal Year]].[MEMBER_CAPTION]]] varchar(20), 
  [[Measures]].[Internet Total Sales]]] decimal(20,2),
  [[Measures]].[Internet Total Tax Amt]]] decimal(20,2)
)

insert into #t
exec ('
SELECT  
    { [Measures].[Internet Total Sales],   
        [Measures].[Internet Total Tax Amt]} ON COLUMNS,  
    { [Date].[Fiscal Year].AllMembers  } ON ROWS  
FROM [Adventure Works Internet Sales Model]  
') at SSAS

select * from #t

Answer 2

You could try to avoid openquery and use something like SSIS or Azure Data Factory or some other ETL tool which doesn't have the 8K character query limit.

Alternatively if you want to keep using openquery but if you are getting over 8000 characters I would suspect you either have a long list of measures or a long list of dimension members (or both)

You could maybe reduce the number of members you have to type out by creating a NAMED SET.

Then instead of having something along the lines of

WHERE [resellers].[reseller1], [resellers.reseller2]...

you could write

WHERE [resellers].[myopenqueryresellers]

you could achieve something similar by adding a hierarchy or a fake dimension.

Which solution would be best depends on your cube design and actual query, but a way around this is making some changes to your cube that allow you to stay within the 8000 character limit