Linked server dynamic catalog for executing MDX th

Question

I have multiple OLAP databases in my project, so is it possible to dynamically decide the catalog for executing this MDX query?

SELECT * FROM OpenQuery(OLAP_SERVER, 'WITH MEMBER measures.X AS dimensions.count SELECT Measures.X ON 0 FROM MyCube') as X

I don't want to create a separate linked server for each of the OLAP database. Both the relational and cube databases reside on the same physical machine.

My linked server configuration are:

EXEC master.dbo.sp_addlinkedserver 
@server = N'OLAP_SERVER'
, @srvproduct=N'OLAP_SERVER', @provider=N'MSOLAP'
, @datasrc=N'localhost'
--, @catalog=N'xxxx' default catalog commented out
GO

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'OLAP_SERVER'
, @locallogin = NULL 
, @useself = N'FALSE'
, @rmtuser=N'xxxx'
, @rmtpassword='xxxx'
GO

Alternatively, is it possible to fully qualify the cube name with the OLAP database name like [OLAPDBName].[MyCube] in the MDX script?

Please help, thank you.

Answer 1

If you use OpenRowset instead of OpenQuery, you can specify the connection parameters dynamically as a string:

select *
from OpenRowset('MSOLAP', 
                'Data Source=localhost;Initial Catalog=xxxx;Provider=MSOLAP.4;Integrated Security=SSPI;Format=Tabular;',
'WITH MEMBER measures.X AS dimensions.count
 SELECT Measures.X ON 0 FROM MyCube')