Stored procedure

Tagetik allows data processing via stored procedures.

Stored procedures are necessary in the following cases:

the functionalities required by the user are not feasible using standard or multidimensional account calculation logic, built-in logic, or ETL processes;
when there is a performance issue such that using an ETL process would be less efficient;
when the use of an ETL would not produce the desired results, or when parameterisation would be too complex.

Whenever using a stored procedure, it is essential to remember that

as with ETLs, special attention should be paid to the sequence in which they are executed (a sequence not structurally guaranteed by Tagetik);
The execution of a stored procedure does not use the Tagetik technological framework; that is, it is executed without any controls related to user restrictions, Entity / custom dimension and account / custom dimension restrictions, or scenario / period locking (unless, of course, the stored procedure itself is explicitly developed to take these aspects into account).

A stored procedure should always be built with particular care to avoid destructive effects on the rest of the application. It is advisable to always seek the advice of Tagetik technicians.

A stored procedure can be:

scheduled by inserting it within a job via a 'Data processing - Stored procedure' type task;
inserted as a task within a workflow via a 'Stored Procedures' type task;
executed within a data entry form;
executed by ETL via a Stored Procedure type 'Command'.

Stored procedure definition¶

The definition of a stored procedure on the application database can be accessed from the administrator user web interface, by following the path:

Setup & Admin > Processes tile > Database objects. In this case, users can access the full database objects list

For further details on the stored procedure definition window see Database objects.

In both cases, Tagetik always records within the audit trail the operations performed through these functionalities so that you always have full control over 'who, when and how' operated on the data

The system places a condition on the name of the stored procedures that can be run directly by Tagetik: they must have a name (independently of any existing schema) with CPM_SP_ prefix

Parameters¶

Stored procedures can define input, output, or input/output parameters, depending on the type of database on which they are created and used.

Parameters can be of any type except binary.

SQL Server¶

@INPUT_PARAMETER VARCHAR(30)

@OUTPUT_PARAMETER VARCHAR(100) OUTPUT

Oracle¶

INPUT_PARAMETER IN VARCHAR2

OUTPUT_PARAMETER OUT VARCHAR2

INOUT_PARAMETER IN OUT NUMBER

Postgresql¶

IN INPUT_PARAMETER VARCHAR(30)

OUT OUTPUT_PARAMETER VARCHAR(30)

INOUT INOUT_PARAMETER INTEGER

SAP Hana¶

IN INPUT_PARAMETER VARCHAR(30)

OUT OUTPUT_PARAMETER VARCHAR(100)

INOUT INOUT_PARAMETER NUMERIC(2)

When selecting the stored procedure pre-execution parameters, only Input and Input/Output type parameters will be displayed.

If the stored procedure has been added as a task within a workflow, parameters can be assigned values inherited from the context—by using specific parameter naming—such as the data collection process, entity, source scenario, period, and step, thus avoiding the need to manually set them again.

For each parameter, values can be selected from a custom editor; the type of editor depends on the name assigned to the individual parameter (see the rules listed in the tables below).

Dimension	Prefix Name	Prefix Name with value inherited from workflow
Data collection process	RACCOLTA	RACCOLTA
Period	DIM_PER	WFDIM_PER
Scenario (both original and consolidation)	DIM_SCE
Original scenario	DIM_SCE_O	WFDIM_SCE_O
Consolidation scenario	DIM_SCE_C
Entity	DIM_AZI	WFDIM_AZI
Step	DIM_FASE	WFDIM_FASE
Custom dimension 1	DIM_DEST1
Custom dimension 2	DIM_DEST2
Custom dimension 3	DIM_DEST3
Custom dimension 4	DIM_DEST4
Custom dimension 5	DIM_DEST5
Category	DIM_CAT
Accounts	DIM_CONTO
Cause	DIM_CAUSALE
Unit	DIM_VAL
Form	DIM_PROSP

For all these parameters related to Tagetik dimensions, the assigned editor is an elements list lookup.

Other types	Prefix Name	Editor
Session user	SESSION_USER	This parameter is never manually defined by the user, but is always defined automatically by the system with the code of the session who requested the launch of the stored procedure. For Postgresql the parameter to use is SESSION_USR
Flag	FLAG_	Check box
Tagetik Enumerated Values List	COMBO_	Drop-down list

Below are the names to be used for the most common enumerations applicable to “List of values from Tagetik enumerations” type parameters:

NaturaConto;
TipoConto;
TipoCategoria;
TipoScenario.

if a parameter does not have a valid name or type, or if the stored procedure has not been deployed, then it will not appear in the list of user-executable procedures;
It is possible to have multiple parameters that reference the same Tagetik dimension. for example RACCOLTA1 and RACCOLTA2;
if a parameter has the name DIM_SCE_C1 or DIM_SCE_O1, it will be considered by the system as a consolidation scenario and an original scenario, respectively;
if the Scenario and Period parameter is used, the lists proposed for the selection of values will not be related to each other; i.e. it will be possible to select a value for the period parameter regardless of whether such a period exists for the given scenario;
in the lists proposed for the selection of parameter values, user restrictions are never applied.

When the stored procedures are executed, all Input, Output and Input/Output parameters will be reported in the audit logs.

EXAMPLES

Sql Server

CREATE PROCEDURE CPM_SP_TEST (

@RACCOLTA VARCHAR(30),

@OUTPUT1 VARCHAR(100) OUTPUT,

@DIM_SCE_O VARCHAR(15),

@OUTPUT2 VARCHAR(1000) OUTPUT,

@DIM_PER VARCHAR(2),

@NUM1 NUMERIC(2) OUTPUT,

@WFDIM_AZI VARCHAR(30),

@NUM2 NUMERIC(2) OUTPUT,

@COMBO_NATURACONTO VARCHAR(1),

@FLAG_TEST NUMERIC(1),

@SESSION_USER VARCHAR(90)

)

AS

BEGIN

SET @NUM1 = @NUM2 + 1

SET @OUTPUT1 = 'Test output1'

SET @OUTPUT2 = @RACCOLTA + '-' + @DIM_SCE_O + '-' + @DIM_PER + '-' + @WFDIM_AZI + '-' + @SESSION_USER

RETURN (1)

END

Oracle

CREATE OR REPLACE PROCEDURE CPM_SP_TEST (

RACCOLTA IN VARCHAR2,

OUTPUT1 OUT VARCHAR2,

DIM_SCE_O IN VARCHAR2,

OUTPUT2 OUT VARCHAR2,

DIM_PER IN VARCHAR2,

NUM1 IN OUT NUMBER,

WFDIM_AZI IN VARCHAR2,

NUM2 IN OUT NUMBER,

COMBO_NATURACONTO IN VARCHAR2,

FLAG_TEST IN NUMBER,

SESSION_USER IN VARCHAR2

)

IS

BEGIN

NUM1 := NUM2 + 1;

OUTPUT1 := 'Test output1';

OUTPUT2 := RACCOLTA || '-' || DIM_SCE_O || '-' || DIM_PER || '-' || WFDIM_AZI || '-' || SESSION_USER || '-' || NUM1 || '-' || NUM2;

END CPM_SP_TEST;

Postgresql

CREATE OR REPLACE FUNCTION CPM_SP_TEST (

IN RACCOLTA VARCHAR(30),

OUT OUTPUT1 VARCHAR(30),

IN DIM_SCE_O VARCHAR(15),

OUT OUTPUT2 VARCHAR(30),

IN DIM_PER VARCHAR(2),

INOUT NUM1 INTEGER,

IN WFDIM_AZI VARCHAR(30),

INOUT NUM2 INTEGER,

IN COMBO_NATURACONTO VARCHAR(1),

IN FLAG_TEST INTEGER

)

RETURNS void AS

$$

BEGIN

NUM1 := NUM2 + 1;

OUTPUT1 := 'Test output1';

OUTPUT2 := RACCOLTA || '-' || DIM_SCE_O || '-' || DIM_PER || '-' || WFDIM_AZI || '-' || SESSION_USER || '-' || NUM1 || '-' || NUM2;

END;

$$ LANGUAGE plpgsql;

SAP Hana

CREATE PROCEDURE "CPM_SP_TEST" (

IN RACCOLTA VARCHAR(30),

IN DIM_SCE_O VARCHAR(15),

OUT OUTPUT2 VARCHAR(1000),

IN DIM_PER VARCHAR(2),

INOUT NUM1 NUMERIC(2),

IN WFDIM_AZI VARCHAR(30),

INOUT NUM2 NUMERIC(2),

IN COMBO_NATURACONTO VARCHAR(1),

IN FLAG_TEST NUMERIC(1),

IN "SESSION_USER" VARCHAR(255)

)

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

OUTPUT2 = :RACCOLTA || '-' || :DIM_SCE_O || '-' || :DIM_PER || '-' || :WFDIM_AZI || '-' || :SESSION_USER || '-' || :NUM1 || '-' || :NUM2;

END;

Example: The following stored procedure reads the data from the cogs table, assigns them progressive values, and then loads them into the gross balances table based on the account in which they must be stored.

ALTER PROCEDURE [dbo].[CPM_SP_COGS_TO_DSL] as

declare @provenienza varchar(50)

declare @SCE_O VARCHAR(10)

begin

set @SCE_O = '2011MACT'

set @provenienza = 'CPM_SP_COGS_TO_DSL'

select

cod_azienda,

cod_scenario,

cod_periodo,

'G01' Dest1,

ProdCategoryCode Dest2,

ClusterCustomerCode Dest3,

CompanyCurrencyCode CompanyCurrencyCode,

DocCurrencyCode DocCurrencyCode,

-sum( isnull(Qty_L , 0 ) ) QTY_L,

-sum( isnull(Qty_A , 0 ) ) QTY_A,

-sum( isnull(Qty, 0 ) ) AV_P1_M2,

-sum( - isnull(SalesmanCommision, 0)

isnull(DistributorCommision, 0)) P25100_M2,

-sum( -isnull(Cost , 0 ) ) P15110_M2,

-sum( -isnull(CostCorporate_S,0 )) CPR_S,

-sum( -isnull(YearEndBonus,0)) B00001,

-sum( isnull(Value_F_A ,0 )) VAL_F_A,

sum( isnull(Value_F_S ,0 )) VAL_F_S, -sum( isnull(Value_F_A ,0 )+ isnull(Value_F_S ,0 )) VAL_F,

-sum( isnull(Value_L_S ,0 )) VAL_L_S,

-sum( -isnull(Royalties , 0 )) P25200_M2

/* Start: temporary table */

into #COGS_TO_DSL

from cogs

where cod_scenario = @SCE_O

and cod_azienda in (select cod_azienda from azienda )

group by

cod_azienda,

cod_scenario,

cod_periodo,

ProdCategoryCode,

ClusterCustomerCode,

DocCurrencyCode,

CompanyCurrencyCode

/* END temporary table */

select

cod_azienda,

cod_scenario,

p.cod_periodo,

Dest1,

Dest2,

Dest3,

CompanyCurrencyCode CompanyCurrencyCode,

DocCurrencyCode DocCurrencyCode,

sum( QTY_L ) QTY_L,

sum( QTY_A ) QTY_A,

sum( AV_P1_M2) AV_P1_M2,

sum( P25100_M2 ) P25100_M2,

sum( P15110_M2 ) P15110_M2,

sum( CPR_S ) CPR_S,

sum( B00001 ) B00001,

sum( VAL_F_A ) VAL_F_A,

sum( VAL_F_S ) VAL_F_S,

sum( VAL_F ) VAL_F,

SUM( VAL_L_S) VAL_L_S,

sum( P25200_M2 ) P25200_M2

into #COGS_TO_DSL_YTD

from #COGS_TO_DSL t, periodo p

where t.cod_periodo <= p.cod_periodo

and cod_scenario = @SCE_O

group by

cod_azienda,

cod_scenario,

p.cod_periodo,

dest1,

dest2,

dest3,

DocCurrencyCode,

CompanyCurrencyCode

--/* FINE PROGRESSIVIZZAZIONE */

delete from dati_saldi_lordi

where cod_scenario = @SCE_O

and provenienza = @provenienza

/* START: insert into dati_Saldi_lordi */

--50225 SalesPeople Costs

insert into dati_saldi_lordi(OID_DATI_SALDI_LORDI, COD_SCENARIO, COD_PERIODO, COD_AZIENDA, COD_CONTO, COD_DEST1, COD_DEST2, COD_DEST3, COD_CATEGORIA, IMPORTO, COD_VALUTA, IMPORTO_VALUTA_ORIGINARIA, COD_VALUTA_ORIGINARIA, PROVENIENZA, USERUPD, DATEUPD, NOTE)

select

newid() OID_DATI_SALDI_LORDI,

t.cod_scenario COD_SCENARIO,

t.cod_periodo COD_PERIODO,

cod_azienda COD_AZIENDA,

'50225' COD_CONTO,

Dest1,

Dest2,

Dest3,

'S' COD_CATEGORIA,

P25100_M2 IMPORTO,

COMPANYCURRENCYCODE COD_VALUTA,

P25100_M2 IMPORTO_VALUTA_ORIGINARIA,

COMPANYCURRENCYCODE COD_VALUTA_ORIGINARIA,

@PROVENIENZA PROVENIENZA,

'Young' USERUPD,

getdate() DATEUPD,

null NOTE

from #COGS_TO_DSL_YTD t

where P25100_M2 <> 0

and cod_scenario = @SCE_o

and Dest2 <> 'POP'

-- 50220 - Advertising and Promotion

insert into dati_saldi_lordi(OID_DATI_SALDI_LORDI, COD_SCENARIO, COD_PERIODO, COD_AZIENDA, COD_CONTO, COD_DEST1, COD_DEST2, COD_DEST3, COD_CATEGORIA, IMPORTO, COD_VALUTA, IMPORTO_VALUTA_ORIGINARIA, COD_VALUTA_ORIGINARIA, PROVENIENZA, USERUPD, DATEUPD, NOTE)

select

newid() OID_DATI_SALDI_LORDI,

t.cod_scenario COD_SCENARIO,

t.cod_periodo COD_PERIODO,

cod_azienda COD_AZIENDA,

'50220' COD_CONTO,

Dest1,

Dest2,

Dest3,

'S' COD_CATEGORIA,

P15110_M2 IMPORTO,

COMPANYCURRENCYCODE COD_VALUTA,

P15110_M2 IMPORTO_VALUTA_ORIGINARIA,

COMPANYCURRENCYCODE COD_VALUTA_ORIGINARIA,

@PROVENIENZA PROVENIENZA,

'Young' USERUPD,

getdate() DATEUPD,

null NOTE

from #COGS_TO_DSL_YTD t

where P15110_M2 <> 0

AND Dest2 <> 'POP'

AND T.COD_SCENARIO = @SCE_O

end