Оптимизированная схема работает в связке «Hyperion Planning» -> «CSV» -> «таблица MS SQL». Ниже описана реализация и приведены основные компоненты решения.

Contents

1 Шаг 1 — выгрузка в CSV
2 Подготовка файла формата данных для MS SQL (BULK LOAD) — data_format.fmt
3 Шаг 3 — подготавливаем хранимую процедуру
4 Берем батник для запуска хранимой процедуры из Hyperion Planning (@jRunCMD)
5 Шаг 4 — Стартуем из Hyperion Planning процедуру после выгрузки в файл CSV

Шаг 1 — выгрузка в CSV

Ниже приведен код бизнес-правила Hyperion Planning:

SET MSG NONE;           /*Вывод в лог обобщающей информации при расчете*/
SET UPDATECALC OFF;     /*Вычисляются все блоки данных, всегда выключаем интеллектуальный расчет*/
SET AGGMISSG ON;        /*Агрегируем пустые значения*/
SET EMPTYMEMBERSETS ON; /*Останавливается расчет в FIX, если зафиксирован пустой набор членов*/
SET CACHE ALL;          /*Включается calculator cache*/
SET CACHE HIGH;         /*Размер calculator cache*/
SET LOCKBLOCK HIGH;     /*Максимальное количество блоков для одновременного расчета sparse member formula*/
SET CREATEBLOCKONEQ OFF;
SET FRMLBOTTOMUP ON;

SET DATAEXPORTOPTIONS {
	DataExportLevel ALL;
	DataExportDynamicCalc OFF;
	DataExportOverwriteFile ON;
	DATAEXPORTCOLFORMAT ON;
	DATAEXPORTDIMHEADER OFF;
	DataExportRelationalFile ON;
	DataExportColHeader "D_Period";
};

FIX(Определяем наш срез данных)
      DATAEXPORT "File" ";" "E:\Data\Integration\OurDataFromHyperionPlanning.csv" "NULL";
ENDFIX

SET MSG NONE; /*Вывод в лог обобщающей информации при расчете*/

SET UPDATECALC OFF; /*Вычисляются все блоки данных, всегда выключаем интеллектуальный расчет*/

SET AGGMISSG ON; /*Агрегируем пустые значения*/

SET EMPTYMEMBERSETS ON; /*Останавливается расчет в FIX, если зафиксирован пустой набор членов*/

SET CACHE ALL; /*Включается calculator cache*/

SET CACHE HIGH; /*Размер calculator cache*/

SET LOCKBLOCK HIGH; /*Максимальное количество блоков для одновременного расчета sparse member formula*/

SET CREATEBLOCKONEQ OFF;

SET FRMLBOTTOMUP ON;

SET DATAEXPORTOPTIONS {

DataExportLevel ALL;

DataExportDynamicCalc OFF;

DataExportOverwriteFile ON;

DATAEXPORTCOLFORMAT ON;

DATAEXPORTDIMHEADER OFF;

DataExportRelationalFile ON;

DataExportColHeader "D_Period";

};

FIX(Определяем наш срез данных)

DATAEXPORT "File" ";" "E:\Data\Integration\OurDataFromHyperionPlanning.csv" "NULL";

ENDFIX

Подготовка файла формата данных для MS SQL (BULK LOAD) — data_format.fmt

data_format.fmt (в формате txt)
Это файл формата XML:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1"  xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2"  xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3"  xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4"  xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5"  xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6"  xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7"  xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8"  xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9"  xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="15" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="16" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="17" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="18" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="19" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="20" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="21" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="22" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="23" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="24" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="25" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="26" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="27" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="28" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="29" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="30" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="100"  COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1"  NAME="Dim01"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2"  NAME="Dim02"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3"  NAME="Dim03"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4"  NAME="Dim04"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5"  NAME="Dim05"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6"  NAME="Dim06"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="7"  NAME="Dim07"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8"  NAME="Dim08"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9"  NAME="Dim09"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="10" NAME="Dim10"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="11" NAME="Dim11"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="12" NAME="Dim12"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="13" NAME="Dim13"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="14" NAME="Dim14"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="15" NAME="Dim15"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="16" NAME="Dim16"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="17" NAME="Dim17"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="18" NAME="Dim18"  xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="19" NAME="Data01" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="20" NAME="Data02" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="21" NAME="Data03" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="22" NAME="Data04" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="23" NAME="Data05" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="24" NAME="Data06" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="25" NAME="Data07" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="26" NAME="Data08" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="27" NAME="Data09" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="28" NAME="Data10" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="29" NAME="Data11" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="30" NAME="Data12" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

<?xml version="1.0"?>

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

Шаг 3 — подготавливаем хранимую процедуру

USE [custom]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dwh].[Data_Export]
AS
	truncate table [dbo].[target_table];
	INSERT INTO [dbo].[target_table]
	SELECT
		Replace(csvtbl.Dim01,'"',''),
		Replace(csvtbl.Dim06,'"',''),
		Replace(csvtbl.Dim11,'"',''),
		Replace(csvtbl.Dim13,'"',''),
		Replace(csvtbl.Dim14,'"',''),
		Replace(csvtbl.Dim15,'"',''),
		Replace(csvtbl.Dim16,'"',''),
		Replace(csvtbl.Dim17,'"',''),
		csvtbl.Data01,
		csvtbl.Data02,
		csvtbl.Data03,
		csvtbl.Data04,
		csvtbl.Data05,
		csvtbl.Data06,
		csvtbl.Data07,
		csvtbl.Data08,
		csvtbl.Data09,
		csvtbl.Data10,
		csvtbl.Data11,
		csvtbl.Data12
		FROM OPENROWSET(
				BULK 'E:\Data\Integration\OurDataFromHyperionPlanning.csv',
				FORMATFILE = 'E:\Data\Integration\data_format.fmt'
		) AS csvtbl;

USE [custom]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dwh].[Data_Export]

truncate table [dbo].[target_table];

INSERT INTO [dbo].[target_table]

SELECT

Replace(csvtbl.Dim01,'"',''),

Replace(csvtbl.Dim06,'"',''),

Replace(csvtbl.Dim11,'"',''),

Replace(csvtbl.Dim13,'"',''),

Replace(csvtbl.Dim14,'"',''),

Replace(csvtbl.Dim15,'"',''),

Replace(csvtbl.Dim16,'"',''),

Replace(csvtbl.Dim17,'"',''),

csvtbl.Data01,

csvtbl.Data02,

csvtbl.Data03,

csvtbl.Data04,

csvtbl.Data05,

csvtbl.Data06,

csvtbl.Data07,

csvtbl.Data08,

csvtbl.Data09,

csvtbl.Data10,

csvtbl.Data11,

csvtbl.Data12

FROM OPENROWSET(

BULK 'E:\Data\Integration\OurDataFromHyperionPlanning.csv',

FORMATFILE = 'E:\Data\Integration\data_format.fmt'

) AS csvtbl;

Берем батник для запуска хранимой процедуры из Hyperion Planning (@jRunCMD)

@ECHO ON

SET vSQLDir=C:\Program Files\Microsoft SQL Server\110\Tools\Binn
SET vCMDLINE=%*
SET vWorkDir=%EPM_ORACLE_INSTANCE%\Folder
SET vEssDir=%EPM_ORACLE_INSTANCE%\EssbaseServer\bin
SET vLogDir=%vWorkDir%\log
SET vBinDir=%vWorkDir%\bin

set vODBCLogin=login
set vODBCPassword=password

set  vLogName ="vLogName"
set  vStoredProcedure ="vStoredProcedure"
set  vFlag=0;

GOTO main
                                                                  
:SplitArgs
  if "%*" NEQ "" (
    for /F "tokens=1,2,* delims== " %%i in ("%*") do call :AssignKeyValue %%i %%j & call :SplitArgs %%k
  )
  goto :eof
 
:AssignKeyValue

   if /i %1 EQU vParam (
     if %vFlag%==0 (SET vParam=%2)
     if %vFlag%==1 (SET vParam=%vParam%, %2)
     SET  vFlag=1
    )

   if /i %1 EQU vLogName (
     SET vLogName=%2
   ) 

   if /i %1 EQU vStoredProcedure (
     SET vStoredProcedure=%2
   )   
                
  goto :eof

:getLogFileName 
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c%%a%%b)
For /f "tokens=1-3 delims=/:" %%a in ("%TIME%") do  (set mytime=%%a%%b%%c)
set mytime=%mytime: =%
set mytime=%mytime:.=%
  
set FilePostfix=%mydate%%mytime%
SET FilePostfix=%FilePostfix:,=%
SET FilePostfix=%FilePostfix:.=%
SET vLogFileName=%vLogDir%\%vLogName%_%FilePostfix%.log
SET vErroFileName=%vLogDir%\%vLogName%_%FilePostfix%.err
goto :eof

:main

call :SplitArgs %vCMDLINE%
call :getLogFileName
call %vWorkDir%\bin\srvDelOldFiles >nul 2>&1

call "%vSQLDir%\sqlcmd.exe" -S server-name\MSSQL -Q "EXEC %vStoredProcedure% %vParam%;" -o %vLogFileName% 1>nul 2>%vErroFileName%
echo sqlcmd run stored procedure: "%vStoredProcedure% %vParam%;" >> %vLogFileName%

FOR /F "usebackq" %%A IN ('%vErroFileName%') DO set vSizeExcpFileName=%%~zA
if %vSizeExcpFileName% LSS 4 (
  del %vErroFileName% 
 EXIT /B 0
)  
 
EXIT /B 2

@ECHO ON

SET vSQLDir=C:\Program Files\Microsoft SQL Server\110\Tools\Binn

SET vCMDLINE=%*

SET vWorkDir=%EPM_ORACLE_INSTANCE%\Folder

SET vEssDir=%EPM_ORACLE_INSTANCE%\EssbaseServer\bin

SET vLogDir=%vWorkDir%\log

SET vBinDir=%vWorkDir%\bin

set vODBCLogin=login

set vODBCPassword=password

set vLogName ="vLogName"

set vStoredProcedure ="vStoredProcedure"

set vFlag=0;

GOTO main

:SplitArgs

if "%*" NEQ "" (

for /F "tokens=1,2,* delims== " %%i in ("%*") do call :AssignKeyValue %%i %%j & call :SplitArgs %%k

)

goto :eof

:AssignKeyValue

if /i %1 EQU vParam (

if %vFlag%==0 (SET vParam=%2)

if %vFlag%==1 (SET vParam=%vParam%, %2)

SET vFlag=1

)

if /i %1 EQU vLogName (

SET vLogName=%2

)

if /i %1 EQU vStoredProcedure (

SET vStoredProcedure=%2

)

goto :eof

:getLogFileName

For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c%%a%%b)

For /f "tokens=1-3 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b%%c)

set mytime=%mytime: =%

set mytime=%mytime:.=%

set FilePostfix=%mydate%%mytime%

SET FilePostfix=%FilePostfix:,=%

SET FilePostfix=%FilePostfix:.=%

SET vLogFileName=%vLogDir%\%vLogName%_%FilePostfix%.log

SET vErroFileName=%vLogDir%\%vLogName%_%FilePostfix%.err

goto :eof

:main

call :SplitArgs %vCMDLINE%

call :getLogFileName

call %vWorkDir%\bin\srvDelOldFiles >nul 2>&1

call "%vSQLDir%\sqlcmd.exe" -S server-name\MSSQL -Q "EXEC %vStoredProcedure% %vParam%;" -o %vLogFileName% 1>nul 2>%vErroFileName%

echo sqlcmd run stored procedure: "%vStoredProcedure% %vParam%;" >> %vLogFileName%

FOR /F "usebackq" %%A IN ('%vErroFileName%') DO set vSizeExcpFileName=%%~zA

if %vSizeExcpFileName% LSS 4 (

del %vErroFileName%

EXIT /B 0

)

EXIT /B 2

Шаг 4 — Стартуем из Hyperion Planning процедуру после выгрузки в файл CSV

Если процедура с параметрами, то назначаем одной и той же переменной vParam параметры запуска процедуры в том порядке, в котором заданы переменные в коде процедуры (см. закомменченный код).

    "D_Measures"(
			  D_Measures = 0;
			  D_Measures = @jRunCMD("C:\Oracle\Middleware\epmsystem\folder\bin\runSQLCMD.bat",
						   @LIST(
							 "vStoredProcedure=[custom].[dwh].[Data_Export]"
							 ,"vLogName=Data_Export"
							 /*,@CONCATENATE("vParam=", @NAME( {vPeriodActualCalc} ) )							
							 ,@CONCATENATE("vParam=", @NAME( {vYearCalc} ) )*/
						) );

			  IF (D_Measures + 0 <> 0 )
				@RETURN (
				  @CONCATENATE( "Error in the running external procedure check runSQLCMD ([custom].[dwh].[Data_Export])", @NAME(&SYS_CurrDB) ) ,ERROR);
			  ENDIF
     )

"D_Measures"(

D_Measures = 0;

D_Measures = @jRunCMD("C:\Oracle\Middleware\epmsystem\folder\bin\runSQLCMD.bat",

@LIST(

"vStoredProcedure=[custom].[dwh].[Data_Export]"

,"vLogName=Data_Export"

/*,@CONCATENATE("vParam=", @NAME( {vPeriodActualCalc} ) )

,@CONCATENATE("vParam=", @NAME( {vYearCalc} ) )*/

) );

IF (D_Measures + 0 <> 0 )

@RETURN (

@CONCATENATE( "Error in the running external procedure check runSQLCMD ([custom].[dwh].[Data_Export])", @NAME(&SYS_CurrDB) ) ,ERROR);

ENDIF

)