Получение списка элементов изменения из куба Hyperion Planning

Полезная функция для получения списка элементов измерения из куба.

Вызывается табличная функция:

select * from [scripts].[getElementListFromCube] ('Accounts','Cube1')

1	select * from [scripts].[getElementListFromCube] ('Accounts','Cube1')

Код табличной функции:

USE [custom]
GO

/****** Object:  UserDefinedFunction [custom].[scripts].[getElementListFromCube] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION  [scripts].[getElementListFromCube] ( @vDimName nvarchar(50), @vCubeCalc nvarchar(50) )

RETURNS @elementList TABLE (
	element nvarchar(50)
)
as
 
BEGIN
		declare @elementsHPLApps table(ElementCode nvarchar(50), Cube1 nvarchar(50), Cube2 nvarchar(50), Cube3 nvarchar(50));
		insert into @elementsHPLApps
			SELECT tbl2.OBJECT_NAME ElementCode, tbl1.USED_IN & 2 Cube1, tbl1.USED_IN & 1 Cube2, tbl1.USED_IN & 4 Cube3
			FROM [db_apps].[dbo].[HSP_MEMBER] tbl1
			LEFT JOIN [db_apps].[dbo].[HSP_OBJECT] tbl2 ON tbl1.MEMBER_ID = tbl2.OBJECT_ID
			WHERE tbl1.DIM_ID IN (
				SELECT OBJECT_ID
				FROM [db_apps].[dbo].[HSP_OBJECT]
				WHERE OBJECT_ID IN (
					SELECT [DIM_ID]
					FROM [db_apps].[dbo].[HSP_DIMENSION]
				)
				AND OBJECT_NAME = @vDimName
			) and tbl2.OBJECT_TYPE<>45;

		if UPPER(@vCubeCalc)='Cube1'
			INSERT INTO @elementList
			select ElementCode
			from @elementsHPLApps
			where Cube1<>0;
		else if UPPER(@vCubeCalc)='Cube2'
			INSERT INTO @elementList
			select ElementCode
			from @elementsHPLApps
			where Cube2<>0;
		else if UPPER(@vCubeCalc)='Cube3'
			INSERT INTO @elementList
			select ElementCode
			from @elementsHPLApps
			where Cube3<>0;

	RETURN;

END;

GO

USE [custom]

/****** Object: UserDefinedFunction [custom].[scripts].[getElementListFromCube] ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [scripts].[getElementListFromCube] ( @vDimName nvarchar(50), @vCubeCalc nvarchar(50) )

RETURNS @elementList TABLE (

element nvarchar(50)

)

BEGIN

declare @elementsHPLApps table(ElementCode nvarchar(50), Cube1 nvarchar(50), Cube2 nvarchar(50), Cube3 nvarchar(50));

insert into @elementsHPLApps

SELECT tbl2.OBJECT_NAME ElementCode, tbl1.USED_IN & 2 Cube1, tbl1.USED_IN & 1 Cube2, tbl1.USED_IN & 4 Cube3

FROM [db_apps].[dbo].[HSP_MEMBER] tbl1

LEFT JOIN [db_apps].[dbo].[HSP_OBJECT] tbl2 ON tbl1.MEMBER_ID = tbl2.OBJECT_ID

WHERE tbl1.DIM_ID IN (

SELECT OBJECT_ID

FROM [db_apps].[dbo].[HSP_OBJECT]

WHERE OBJECT_ID IN (

SELECT [DIM_ID]

FROM [db_apps].[dbo].[HSP_DIMENSION]

)

AND OBJECT_NAME = @vDimName

) and tbl2.OBJECT_TYPE<>45;

if UPPER(@vCubeCalc)='Cube1'

INSERT INTO @elementList

select ElementCode

from @elementsHPLApps

where Cube1<>0;

else if UPPER(@vCubeCalc)='Cube2'

INSERT INTO @elementList

select ElementCode

from @elementsHPLApps

where Cube2<>0;

else if UPPER(@vCubeCalc)='Cube3'

INSERT INTO @elementList

select ElementCode

from @elementsHPLApps

where Cube3<>0;

RETURN;

END;