TSQL Essbase Registartion DDL commands


sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

alter database ess_xmla set TRUSTWORTHY ON ;
GO

drop function [dbo].[getDataFromMDXRaw]
GO
drop ASSEMBLY [EssXMLALibrary]
GO

CREATE ASSEMBLY [EssXMLALibrary]
AUTHORIZATION [dbo]
FROM ‘C:\path\to\dll\TSQLEssBaseInterface.dll’
WITH PERMISSION_SET = UNSAFE
GO

CREATE FUNCTION [dbo].[getDataFromMDXRaw](@vIP [nvarchar](80),@vLogin [nvarchar](80),@vPassword [nvarchar](80),@vTypeq [nvarchar](80), @vQueryBody [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

CREATE FUNCTION [dbo].[getDataFromMDX](@vTypeq [nvarchar](4000), @vQueryBody [nvarchar](4000))
RETURNS @DataTable 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,
[DblValue] [decimal](32, 9) NULL
)

AS
BEGIN
insert @DataTable
select
[Dim01], [Dim02], [Dim03], [Dim04], [Dim05],
[Dim06], [Dim07], [Dim08], [Dim09], [Dim10],
[Dim11], [Dim12], [Dim13], [Dim14], [Dim15],
[Dim16], [Dim17], [Dim18], [Dim19], [Dim20],
[Dim21], [Dim22], [Dim23], [Dim24], [Dim25],
[Dim26], [Dim27], [Dim28], [Dim29], [Dim30],
[DblValue]
from dbo.getDataFromMDXRaw (‘ApsServerName’,’EssBaseUser’,’EssBasePassword’,@vTypeq, @vQueryBody ) ;

return;
END;