链接服务器动态目录，用于通过OpenQuery执行MDX [英] Linked server dynamic catalog for executing MDX through OpenQuery

问题描述

我的项目中有多个OLAP数据库，因此可以动态确定执行此MDX查询的目录吗?

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

我不想为每个OLAP数据库创建一个单独的链接服务器. 关系数据库和多维数据集数据库都位于同一台物理计算机上.

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

或者，可以用MDX脚本中的[OLAPDBName].[MyCube]之类的OLAP数据库名称完全限定多维数据集名称吗?

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

请帮助，谢谢.

推荐答案

如果使用OpenRowset而不是OpenQuery，则可以动态地将连接参数指定为字符串:

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')

这篇关于链接服务器动态目录，用于通过OpenQuery执行MDX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！