Пример реализации MDX из T-SQL (MS SQL + Essbase)

Данный пример отражает структуру вызова MDX к Essbase из T-SQL:

DECLARE @Periods TABLE (MonthName nvarchar(80) );
INSERT INTO @Periods SELECT 'P07' as MonthName;
INSERT INTO @Periods SELECT 'P08' as MonthName;
INSERT INTO @Periods SELECT 'P09' as MonthName;
INSERT INTO @Periods SELECT 'P10' as MonthName;
INSERT INTO @Periods SELECT 'P11' as MonthName;
INSERT INTO @Periods SELECT 'P12' as MonthName;

DECLARE @Drivers TABLE (DriverName nvarchar(80) );
INSERT INTO @Drivers SELECT 'acc_drivers0007' as DriverName;
INSERT INTO @Drivers SELECT 'acc_drivers0008' as DriverName;
INSERT INTO @Drivers SELECT 'acc_drivers0009' as DriverName;

select MonthName,
		DriverName,
		Dim29 as BU,
		Dim30 as Driver,
		DblValue as DriverValue
from @Periods
cross apply
@Drivers
cross apply
	[dbo].[BuildXmlaDataTable] ( 
	'mdxLev0'
	,	
	'	  SELECT NON EMPTY ({[' + MonthName + ']}) ON COLUMNS,
	NON EMPTY NONEMPTYBLOCK 
			CrossJoin({ Descendants([iBU_TBU90200009],1)}
			,{ [' + DriverName + '] } )	
			        		    
			ON ROWS 
		FROM D08FUNC.D08FUNC
	where ( 
		[FY16],
		[BU_NA],
		[C00_LvlBefore],
		[C03_NA],
		[C04_NA],
		[C05_NA],
		[C06_NA],
		[C07_NA],
		[C09_NA],
		[C11_NA],
		[C12_NA],
		[C01_NA],
		[C02_NA],
		[SCN_BUD],
		[VER_WRKYR],
		[C10_ALT90400072]
	)
	'	
	)
Where DblValue>0;

DECLARE @Periods TABLE (MonthName nvarchar(80) );

INSERT INTO @Periods SELECT 'P07' as MonthName;

INSERT INTO @Periods SELECT 'P08' as MonthName;

INSERT INTO @Periods SELECT 'P09' as MonthName;

INSERT INTO @Periods SELECT 'P10' as MonthName;

INSERT INTO @Periods SELECT 'P11' as MonthName;

INSERT INTO @Periods SELECT 'P12' as MonthName;

DECLARE @Drivers TABLE (DriverName nvarchar(80) );

INSERT INTO @Drivers SELECT 'acc_drivers0007' as DriverName;

INSERT INTO @Drivers SELECT 'acc_drivers0008' as DriverName;

INSERT INTO @Drivers SELECT 'acc_drivers0009' as DriverName;

select MonthName,

DriverName,

Dim29 as BU,

Dim30 as Driver,

DblValue as DriverValue

from @Periods

cross apply

@Drivers

cross apply

[dbo].[BuildXmlaDataTable] (

'mdxLev0'

' SELECT NON EMPTY ({[' + MonthName + ']}) ON COLUMNS,

NON EMPTY NONEMPTYBLOCK

CrossJoin({ Descendants([iBU_TBU90200009],1)}

,{ [' + DriverName + '] } )

ON ROWS

FROM D08FUNC.D08FUNC

where (

[FY16],

[BU_NA],

[C00_LvlBefore],

[C03_NA],

[C04_NA],

[C05_NA],

[C06_NA],

[C07_NA],

[C09_NA],

[C11_NA],

[C12_NA],

[C01_NA],

[C02_NA],

[SCN_BUD],

[VER_WRKYR],

[C10_ALT90400072]

)

Where DblValue>0;

Результат: