EssBase ODBC interface / MsSQL 2008R2 EssBase XMLA integration

Под катом описание процесса публикации данных EssBase в табличной функции MsSQL 2008R2 64Bit

Дано: запрос MDX, который возвращает одиночную колонку с данными. Нужно получить результат выполнения этого запроса на стороне реляционной базы данных. Уже два года как существует решение для Oracle RDB. Теперь пришло время для MsSQL.

Итак, исходный MDX запрос :

SELECT NON EMPTY {( [Actual])} ON COLUMNS,

NON EMPTY NONEMPTYBLOCK CrossJoin (CrossJoin (Descendants ([Product]) , Descendants ([Market]) ), CrossJoin ({[Jan]} ,{[COGS]} ))

ON ROWS FROM Sample.Basic

Для работы нам потребуется Essbase Provider Services. Его наличие можно проверить, обратившись с http запросом по следующему адресу : http://127.0.0.1:13080/aps/XMLA

Для начала, нужно скачать и скопировать на диск CLR C# расширение для MsSQL 2008R2, которое реализует XMLA транспорт и разбор ответа в табличный вид. (Данная библиотека создана из предположения, что APS , MsSQL 2008R2 и EssBase находятся на одном сервере)

Затем, нужно настроить MsSQL 2008R2 сервер, выполнив последовательно команды :

sp_configure
‘show advanced options’, 1;

GO RECONFIGURE;

sp_configure
‘clr enabled’, 1;

GO RECONFIGURE

sp_configure
‘Ole Automation Procedures’, 1;

RECONFIGURE; GO

ALTER DATABASE ESS_XMLA SET TRUSTWORTHY ON ;

Потом, нужно зарегистрировать внешнюю функцию.

USE [ESS_XMLA]
GO
/*
drop function [dbo].[BuildXmlaRowValuesArray]
GO
drop ASSEMBLY [EssXMLALibrary]
GO
*/

CREATE ASSEMBLY [EssXMLALibrary] AUTHORIZATION [dbo] FROM  ‘C:\C#Projects\TSQLEssBaseInterface.dll’
WITH PERMISSION_SET = UNSAFE
GO
CREATE FUNCTION [dbo].[BuildXmlaRowValuesArray](@vTypeq [nvarchar](4000), @vQueryBody [nvarchar](4000), @vLogin [nvarchar](4000), @vPassword [nvarchar](4000))
RETURNS  TABLE (
[Dim01] [nvarchar](80) NULL, [Dim02] [nvarchar](80) NULL, [Dim03] [nvarchar](80) NULL, [Dim04] [nvarchar](80) NULL, [Dim05] [nvarchar](80) NULL,
[Dim06] [nvarchar](80) NULL, [Dim07] [nvarchar](80) NULL, [Dim08] [nvarchar](80) NULL, [Dim09] [nvarchar](80) NULL, [Dim10] [nvarchar](80) NULL,
[Dim11] [nvarchar](80) NULL, [Dim12] [nvarchar](80) NULL, [Dim13] [nvarchar](80) NULL, [Dim14] [nvarchar](80) NULL, [Dim15] [nvarchar](80) NULL,
[Dim16] [nvarchar](80) NULL, [Dim17] [nvarchar](80) NULL, [Dim18] [nvarchar](80) NULL, [Dim19] [nvarchar](80) NULL, [Dim20] [nvarchar](80) NULL,
[Dim21] [nvarchar](80) NULL, [Dim22] [nvarchar](80) NULL, [Dim23] [nvarchar](80) NULL, [Dim24] [nvarchar](80) NULL, [Dim25] [nvarchar](80) NULL,
[Dim26] [nvarchar](80) NULL, [Dim27] [nvarchar](80) NULL, [Dim28] [nvarchar](80) NULL, [Dim29] [nvarchar](80) NULL, [Dim30] [nvarchar](80) NULL,
[StrValue] [nvarchar](80) NULL, [DblValue] [decimal](32, 9) NULL, [XmlaRowValue] [nvarchar](4000) NULL
) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [EssXMLALibrary].[UserDefinedFunctions].[BuildXmlaRowValuesArray]
GO


После того как все команды успешно выполнились, настала пора выполнения запросов:

select * from dbo.BuildXmlaRowValuesArray ( ‘mdx’

,‘SELECT NON EMPTY {( [Actual])} ON COLUMNS,

NON EMPTY CrossJoin (CrossJoin (Descendants ([Product]) , Descendants ([Market]) ), CrossJoin ({[Jan]} ,{[COGS]} ))

ON ROWS

FROM Sample.Basic’

,‘hypadmin’ ,‘hyperion’ )

На данный момент этот интерфейс реализует два метода mdx и mdxLev0. Второй метод при трансформации результата в таблицу выводят в значении измерений только элементы нулевого уровня.