Некоторые репортинговые системы (в моем случае QlikView) не умеют обращаться к кубам напрямую, поэтому приходиться искать другие пути обращения к ним.
Я использовал подключение к кубам через OLE DB. Для этого понадобиться драйвер MSOLAP (Microsoft OLE DB Provider for Analysis Services 10.0), на клиенте у меня этого драйвера не было, поэтому все запросы шли через SQL Server. Посмотреть список установленых OLE DB провайдеров в SQL Server можно хранимой процедурой master..xp_enum_oledb_providers
Варинат 1 (через OPENROWSET):
SELECT
"[DimServices].[Name].[Name].[MEMBER_CAPTION]" service,
"[Measures].[CountServiceAccounts]" regs
FROM OPENROWSET
(
'MSOLAP',
'Provide=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Data Source=127.0.0.1;Initial Catalog=cubeRegs;',
'select {[Measures].[CountServiceAccounts]} on 0, { [DimServices].[Name].Children } on 1 from [Регистрации]'
)
Если в MDX-запросе к кубу используется иерархия, то она будет преобразована в набор колонок, например если в иерархии времени есть год, месяц и день, то в результате получиться три разных колонки таблицы.
Проблема с OPENROWSET в том, что эту команду не может исполнять пользователь с ограниченными правами. Если не только админ должен выполнять запросы к кубам, то нужно использовать OPENQUERY.
Вариант 2 (OPENQUERY):
Сначала надо создать линкованный сервер:
EXEC master.dbo.sp_addlinkedserver @server = '127.0.0.1', @srvproduct='MSOLAP', @provider='MSOLAP', @datasrc='127.0.0.1', @catalog='cubeRegs'
Далее, нужно прописать пользователя, который будет обращаться к линкованному серверу:
EXEC sp_addlinkedsrvlogin '127.0.0.1', 'false', 'db_user', 'olap_user', 'olap_user_psw'
db_user - пользователь, который будет обращаться к линкованному серверу;
olap_user - пользователь, который имеет прова доступа к кубу
olap_user_psw - пароль olap_user
Теперь можно обращаться к кубам:
SELECT
"[DimServices].[Name].[Name].[MEMBER_CAPTION]" service,
"[Measures].[CountServiceAccounts]" regs
FROM
OPENQUERY
(
[127.0.0.1],
'select {[Measures].[CountServiceAccounts]} on 0, { [DimServices].[Name].Children } on 1 from [Регистрации]'
)
Отмечу так же, что как в первом, так и во втором случае запрос вернет колонки типа ntext, с которым работать не очень удобно, поэтому надо прописать преобразование в нужные типы:
SELECT
CAST("[DimServices].[Name].[Name].[MEMBER_CAPTION]" as varchar) service,
CAST(CAST("[Measures].[CountServiceAccounts]" as varchar) as int) regs
FROM
OPENQUERY
CAST("[DimServices].[Name].[Name].[MEMBER_CAPTION]" as varchar) service,
CAST(CAST("[Measures].[CountServiceAccounts]" as varchar) as int) regs
FROM
OPENQUERY
(
[127.0.0.1],
'select {[Measures].[CountServiceAccounts]} on 0, { [DimServices].[Name].Children } on 1 from [Регистрации]'
)
'select {[Measures].[CountServiceAccounts]} on 0, { [DimServices].[Name].Children } on 1 from [Регистрации]'
)
Если нужно выполнить XMLA запрос
XMLA запросы нужны для управления кубами, например процессинг куба, удаление партиций, создание партиций. Ни OPENQUERY, ни OPENROWSET не будут выполнять запрос XMLA, а будут выдавать ошибку, т.к. они не могут принять запрос в формате XML.
Для того, что бы выполнить запрос XMLA нужно создать линкованный сервер (как для OPENQUERY), разрешить на нем RPC:
EXEC sp_serveroption @server='127.0.0.1', @optname='rpc', @optvalue='TRUE'
EXEC sp_serveroption @server='127.0.0.1', @optname='rpc out', @optvalue='TRUE'
EXEC sp_serveroption @server='127.0.0.1', @optname='rpc out', @optvalue='TRUE'
И выполнить следующую команду:
EXEC ('.. Some XMLA Query ..') at [127.0.0.1]
