OLAP: Как обращаться к кубам через OLE DB

Некоторые репортинговые системы (в моем случае 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

(

           [127.0.0.1],
         '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 ('.. Some XMLA Query ..') at [127.0.0.1]