Etl

Z Reporting Wiki
Přejít na: navigace, hledání

ETL (Extract, Transform, Load) - modul slouží k načítání zdrojových souborů, tvorbě pravidel a podmínek pro datové transformace splňující požadavky reportingu.
Popis tabulek datového skladu je ke stažení zde: Popis databáze Reporting .
Skript pro vytvoření databáze Reporting na MS SQL je zde: SQL script
ETL je členěno do jednotlivých vláken, každé aktivní vlákno je automaticky spouštěno v pořadí ve kterém je zobrazeno v rámci modulu ETL.
Vlákna jde seskupovat do skupin. Jako jméno vlákna se uvede nejprve název skupiny a lomítko následované vlastním jménem vlákna. Např.: 01_GRP1/Vlakno_1
Pořadí vláken v modulu ETL je určeno: 1.abecedním pořadím skupin 2.pořadovým číslem vlákna v rámci skupiny. Nejsou-li skupiny použity, řídí se pořadí jen číslem vlákna.

Definice ETL vláken
Definice ETL vláken


Definice vstupních ETL souborů
Definice vstupních ETL csv souborů


Definice zakrývaných bloků v editoru ETL

  • Začátek bloku: /* #region Jméno bloku*/
  • Konec bloku: /* #endregion */

Defaultní proměnné v ETL

  • declare @err int, proměnná (=0)
  • declare @rowcount int, proměnná (=0)
  • declare @id_call int, proměnná (=0)
  • declare @call varchar(1024), proměnná (=null)
  • declare @id_audit int, vrací jedinečné číslo běhu ETL
  • declare @dr datetime, vrací aktualní datum (bez času)
  • declare @accounting int, vrací Id společnosti
  • declare @user nvarchar(50), vrací zalogovaného uživatele

Proměnné nahrazované před spuštěnim vlákna

  • {db}
    • název zákaznické databáze
    • příklad:
select * from {db}.dbo.D_ACCOUNT where ACCOUNTING = @accounting
  • {db_master}
    • název definiční databáze
    • příklad:
select * from {db_master}.dbo.V_STATEMENT where ACCOUNTING=@accounting
  • {db_super}
    • název hlavni databáze
    • příklad:
select * from {db_super}.dbo.V_ACCOUNTING where ACCOUNTING=@accounting
  • {db_mdm}
    • název MDM databáze
  • {run_id}
    • náhodné číslo vygenerované při spuštění vlákna
    • příklad:
exec p_DIM_FILTER @dim_num='1', @table='D_ITEM_DIM', @accounting=@accounting, @output_table='##filter_{run_id}'
  • {accounting}
    • Id společnosti

Funkce T-SQL

 function [dbo].[f_pulnoc] (@datum datetime) returns datetime
  • Vrací datum bez času (půlnoční čas) ze zadaného datumu s časem
 function [dbo].[f_ym] (@date as datetime) returns int
  • Vrací číslo ve formátu RRRRMM za zadaného datumu @date
 function [dbo].[f_ym_add] (@ym as int, @m as int) returns int
  • Vrací číslo ve formátu RRRRMM jako increment hodnoty @ym (ve formatu RRRRMM) o hodnotu @m - počet měsíců
 function [dbo].[f_yq] (@date as datetime) returns int
  • Vrací číslo ve formátu RRRRQ za zadaného datumu @date, (Q = kalendářní kvartál)
 function [dbo].[f_fy] (@accounting as int, @date as datetime) returns int
  • Vrací fiskální rok pro zadaný datum @date a zadanou společnost @accounting
 function {db_master}.[dbo].[f_get_FY] (@accounting as int) returns int
  • Vrací fiskální rok pro zadanou společnost @accounting, z aktuálního (posledního) spuštění přepočtu dat z aplikace.
 function {db_master}.[dbo].[f_get_YM] (@accounting as int, @fy as int) returns table
  • Vrací tabulku se sloupci YYYYMM, MONTH (pořadové číslo měsíce) a MONTH_NAME
    pro zadanou společnost @accounting a pro zadaný fiskální rok @fy.
 Příklad: select YYYYMM, MONTH, MONTH_NAME from {db_master}.dbo.f_get_YM (@accounting, @year)
 function {db_super}.[dbo].[f_get_JOB_NAME]() returns nvarchar(128)
  • Vrací jméno jobu ve kterém aktuálně běží ETL vlákno. Pokud je vlákno spuštěno ručně z ETL vrátí null.

View do definiční databáze

 {db_super}.dbo.V_AGENT_PARAM
  • Parametry pro uploader. Uploader načte hodnoty VALUE a nahradí jimi jména NAME v dotazech uložených v configuračním souboru.
  • sloupce: VALIDATE_FLAG, ACCOUNTING, NAME, VALUE, WHO_CREATED. DATE_CREATED
  • Příklad nastavení parametru ${year}:

Nastavení proměnné 'year' na hodnotu 2015 v ETL: update {db_super}.dbo.V_AGENT_PARAM set VALUE='2015' where ACCOUNTING=@accounting and NAME='year'
Použití proměnné ${year} v konfiguračním souboru config.xml, která bude nahrazena hodnotou 2015: Select YEAR, DATE,... from Table where YEAR=${year}

 {db_super}.dbo.V_PARAM
  • Paramtrizační tabulka slouží k předávání paramtrů v rámci ETL, pouze pro čtení
  • sloupce: IDZ, PARAM, ACCOUNTING, VALUE_B, VALUE_I, VALUE_F, VALUE_S, VALUE_S2, VALUE_S3, ACCTS, DATE_CREATED, WHO_CREATED, NOW
 {db_super}.dbo.V_PARAM_UPDATE
  • View pro update hodnot parametrizační tabulky
  • sloupce: IDZ, PARAM, ACCOUNTING, VALUE_B, VALUE_I, VALUE_F, VALUE_S, VALUE_S2, VALUE_S3, ACCTS, WHO_CREATED
 {db_master}.dbo.V_PERIOD_CLOSE
  • Seznam uzavřených reportovacích období
  • sloupce: IDZ, VALIDATE_FLAG, ACCOUNTING, YYYYMM, A_B ID_DIM_1, ID_DIM_2, ID_DIM_3, ID_DIM_4, ID_DIM_5, ID_DIM_6, ID_DIM_7, DATE_CLOSED, WHO_CLOSED
 {db_master}.dbo.V_STATEMENT
  • Číselník finančních reportů
  • sloupce: IDZ, ACCOUNTING, ID_STATEMENT, STATEMENT, DESCRIPTION, REMARK
 {db_master}.dbo.V_STATEMENT_ROW
  • Číselník řádků finančních reportů
  • sloupce: IDZ, VALIDATE_FLAG, ACCOUNTING, ID_STATEMENT, ID_ROW, ROW, DESCRIPTION, LOW_LEVEL_CODE
 {db_master}.dbo.V_STATEMENT_STRUCTURE
  • Číselník struktury řádků finančních reportů
  • sloupce: IDZ, VALIDATE_FLAG, ACCOUNTING, ID_ROW_PARENT, ID_ROW, COEF, RESULT_SIGN, ORD_NUM
 {db_master}.dbo.V_STATEMENT_ROW_ACCT
  • Definice účtů a dimenzí vstupujících do řádků finančních reportů ve fiskálním roce YEAR
  • sloupce: IDZ, VALIDATE_FLAG, ACCOUNTING, YEAR, ID_ROW_PARENT, ID_ACCOUNT, ID_DIM_1, ID_DIM_2, ID_DIM_3, ID_DIM_4, ID_DIM_5, ID_DIM_6, ID_DIM_7, COEF
  • Příklad použití view v případě definice účtů pomocí wildcard '*' :
select A.ACCOUNTING, A.YEAR, A.ID_ROW_PARENT, B.IDZ as IDZ_ACCOUNT, B.ID_ACCOUNT, A.ID_DIM_1, A.ID_DIM_2, A.ID_DIM_3,
       A.ID_DIM_4, A.ID_DIM_5, A.ID_DIM_6, A.ID_DIM_7, A.COEF
  from {db_master}.dbo.V_STATEMENT_ROW_ACCT A with (nolock)
     join D_ACCOUNT B with (nolock) on A.ACCOUNTING=B.ACCOUNTING and B.ID_ACCOUNT like replace(A.ID_ACCOUNT,'*','%')
  where A.ACCOUNTING=@accounting

Procedury T-SQL

{db_super}.dbo.p_journal

 Procedure {db_super}.dbo.p_journal
   @id_audit int output,          -- pořadové číslo volání podle hodnoty @accounting
   @id_volani int output,         -- pořadové číslo volání podle hodnoty @procedura
   @accounting int,               -- číslo společnosti
   @datumrezu datetime,           -- datum zpracovávaného časového řezu vstupních dat
   @modul varchar(50),            -- symbolické jméno modulu
   @procedura varchar(128),       -- symbolické jméno kroku
   @tabulka varchar(128),         -- jméno zpracovávané tabulky
   @error int,                    -- číslo navrácené chyby z příkazu @@error
   @pocet int = 0,                -- počet zpracovaných vět, nejčastěji @@rowcount
   @poznamka varchar(1024) = ' ', -- text zobrazovaný v Journalu
   @select bit = 0                -- =1 text poznámka se zobrazí i v okně ETL
  • Zapisuje informace v průběhu běhu ETL do Journalu, který je dostupný v rámci aplikace.
  • Příklad:
exec {db_super}.dbo.p_journal @id_audit,@id_call output,@accounting,@dr,'ETL','ETL-Jmeno spolecnosti','Dim',@@error,0,'Start',1
exec {db_super}.dbo.p_journal @id_audit,@id_call output,@accounting,@dr,'Dim','ETL-Jmeno spolecnosti ','D_ITEM_DIM_1',@@error,@@rowcount,'Insert Firma',1

dbo.p_CREATE_IDZ_1

 Procedure dbo.p_CREATE_IDZ_1
   @accounting int,           --Číslo společnosti
   @module char(1)            --Modul ('F','I','A')
  • Vytvoří prvky -1 v dimenzích zvoleného modulu
  • Příklad:
exec p_CREATE_IDZ_1 @accounting=@accounting, @module='F'
select * from D_DIM_1 with (nolock) where IDZ=-1

dbo.p_DIM_TREE

 Procedure dbo.p_DIM_TREE
   @dim_num varchar(2),       --Číslo dimenze 1-n
   @table varchar(20),        --Jméno tabulky dimenze bez čísla (a podtržítka)
   @accounting int,           --Číslo společnosti
   @output_table varchar(20)  --Výstup do ##tabulky
  • Vrátí strom ze zvolené dimenze
  • Příklad:
exec p_DIM_TREE @dim_num='2', @table='D_ITEM_DIM', @accounting=@accounting, @output_table='##filter_{run_id}'
select * from ##filter_{run_id}

dbo.p_DIM_FILTER

 Procedure dbo.p_DIM_FILTER
   @dim_num varchar(2),       --Číslo dimenze 1-n
   @table varchar(20)         --Jméno tabulky dimenze bez čísla (a podtržítka)
   @accounting int,           --Číslo společnosti
   @in nvarchar(max)=null     –-Vrcholový prvek(y) dimenze
   @output_table varchar(20)  --Výstup do ##tabulky
  • Vrátí všechny prvky od zadaného prvku @in ze zvolené dimenze, je-li @in=null, vezme jako vrcholový prvek takový, který nemá už žádný nadřízený. Ve výsledku jsou všechny kombinace vyšší-nižší prvek i přes více úrovní stromu.
  • Příklad:
exec p_DIM_FILTER @dim_num='1', @table='D_ITEM_DIM', @accounting=@accounting, @output_table='##filter_{run_id}'
exec p_DIM_FILTER @dim_num='1', @table='D_ITEM_DIM', @accounting=@accounting, @in='(''Id1'',''Id2'')', @output_table='##filter_{run_id}'

dbo.p_DIM_Y

 Procedure dbo.p_DIM_Y
   @accounting int,           --Číslo společnosti
   @journal=1                 –-Výsledek bude zapsán do Journalu
  • Vyplní pomocné tabulky dimenzi D_DIM_n_Y prvky, ktere jsou obsaženy v tabulce F_STATEMENT kontextově k fiskálnímu roku. Jen takové prvky jsou pak zobrazeny ve filtru dimenzí v aplikaci pro vybrané fiskální roky. Procedura je volána z procedury p_F_STATEMENT_load.
  • Příklad:
exec p_DIM_Y @accounting=@accounting

{db_super}.dbo.p_ETL

 Procedure {db_super}.dbo.p_ETL
   @accounting int,           --Číslo společnosti
   @path nvarchar(255) = '*'  --Jméno volaného vlákna, *=wildcart 
   @type varchar(7) = 'IA',   --'IA' - automatická vlakna; 'IM' - manualní vlakna
   @disabled bit = 0          --1 spustit i zakázané vlákno(a); =0 spustit jen povolené vlákno(a)
  • Zavolá automaticky nebo manuálně spouštěné vlákno(a) z ETL, pro spuštění více vláken lze použít wildcard konotaci.
  • Příklad:
exec {db_super}.dbo.p_ETL @accounting=@accounting, @path='Folder_x/*', @type='IM', @disabled=1

dbo.p_F_STATEMENT_load

 Procedure dbo.p_F_STATEMENT_load
   @accounting int,           --Číslo společnosti kde jsou uloženy definice reportů
   @year int,                 --Fiskální rok, pro který je výkaz(y) přepočten
   @id_statement int=null,    --Číslo přepočítávaného finančního výkazu (=null pro všechny výkazy)
   @journal bit=0,            --Je-li =1 vypíše (jen) hlášení Start a Stop do okna ETL
   @min_expired int=null,     --Počet minut po kterých, když nedojde k řádnému ukončení běhu procedury, dojde k odeslání notifikačního mailu na adresu "ETL mail" uvedené na kartě společnosti.
   @accts varchar(max)=@accounting --Accounting(y) společností (oddělené čárkou), kde bude proveden výpočet reportů podle sdíleného reportu ze společnosti @accounting
  • Naplní tabulku F_STATEMENT vypočtenými hodnotami výkazu(ů) pro zadaný fiskální rok. Stará data pro zadaný fiskální rok v zadaném fiskálním roce smaže.

dbo.p_F_ITEM_BALANCE_load

 Procedure dbo.p_F_ITEM_BALANCE_load 
   @accounting int,           --Číslo společnosti
   @date_min datetime         --Výpočet stavů od
  • Naplní tabulku F_ITEM_BALANCE hodnotami stavů k datu z hodnot obratů uložených v tabulce F_ITEM_TRANSACTION, výpočet začínal od data @date_min. Původní hodnoty v tabulce F_ITEM_BALANCE jsou smazány. Výpočet stavů probíhá pouze přes dimenze, které jsou vybrány v definici aplikace.

dbo.p_F_ITEM_SECURITY_load

 Procedure dbo.p_F_ITEM_SECURITY_load 
   @accounting int           --Číslo společnosti
  • Aktivuje Security nastavení v modulu Položky. Je třeba ji volat vždy po naplnění tabulek F_ITEM_...

dbo.p_F_ITEM_TRANSACTION_load

 Procedure dbo.p_F_ITEM_TRANSACTION_load 
   @accounting int,           --Číslo společnosti
   @date_from datetime=null   --Insert obratů od datumu včetně
  • Smaže a po té naplní tabulku F_ITEM_TRANSACTION z hodnot tabulky F_ITEM_TRANSACTION_DTL od datumu @date_from, je-li hodnota @date_from prázdná vezme celý obsah tabulky F_ITEM_TRANSACTION_DTL. Tabulka F_ITEM_TRANSACTION_DTL je využívána pouze pro zobrazení vět v Drill to detail a věty v ní uložené nejsou agregovány. Tabulka F_ITEM_TRANSACTION poskytuje data pro zobrazování hodnot v modulu položky. Pokud tabulka F_ITEM_TRANSACTION_DTL neobsahuje žádné věty, je zdrojem dat pro Drill to detail tabulka F_ITEM_TRANSACTION.

dbo.p_F_INVOICE_SECURITY_load

 Procedure dbo.p_F_INVOICE_SECURITY_load 
   @accounting int           --Číslo společnosti
  • Aktivuje Security nastavení v modulu Splatnost. Je třeba ji volat vždy po naplnění tabulek F_INVOICE_...

{db_super}.dbo.p_FINISH_UPDATE

 Procedure {db_super}.dbo.p_FINISH_UPDATE
   @accounting int           --Číslo společnosti
   @FIA char(1)              --Modul F=Finance, I=Položky, A=Splatnost
  • Zapíše datum a čas jako informaci o updatu dat v příslušném modulu @FIA.
    Tato informace o aktuálním zpracování dat se zobrazuje uživateli v příslušném modulu.
    Je třeba ji zavolat na konci ETL procesu.
  • Příklad:
exec {db_super}.dbo.p_FINISH_UPDATE @accounting=@accounting, @FIA='I'

dbo.p_GET_ACCT_STATEMENT

 Procedure p_GET_ACCT_STATEMENT 
   @accounting int,               --Číslo společnosti
   @year int=null,                --Fiskální rok (vybraný rok nebo všechny)
   @id_statement int=null,        --Číslo definice reportu (vybraný report nebo vše)
   @id_row int=null,              --Číslo definice řádku (vybraný řádek nebo vše)
   @output_table varchar(20)      --Výstup do ##tabulky
  • Vrátí v ##tabulce obsah definice (účty a dimenze) reportu (řádku) pro fiskální rok
  • Příklad:
informace o ID reportu (pro parametr @id_statement) pro vybranou společnost:
select ID_STATEMENT, STATEMENT from {db_master}.dbo.V_STATEMENT where ACCOUNTING=@accounting
 
exec p_GET_ACCT_STATEMENT @accounting=@accounting, @year=@year, @id_statement=@id_statement, @output_table='##STAT_{run_id}'
select * from ##STAT_{run_id}

dbo.p_GET_PERIOD_CLOSE

 Procedure p_GET_PERIOD_CLOSE 
   @accounting int,               --Číslo společnosti
   @output_table varchar(20)      --Výstup do ##tabulky
  • Vrátí v ##tabulce poslední uzamčené období pro společnost @accounting se všemi prvky dimenzí pro které uživatel provedl uzamčení.
  • Příklad:
exec p_GET_PERIOD_CLOSE @accounting=@accounting, @output_table='##CLOSE_{run_id}'
select ACCOUNTING, YYYYMM, A_B, IDZ_DIM_1, ID_DIM_1, IDZ_DIM_2, ID_DIM_2, IDZ_DIM_3, ID_DIM_3,
       IDZ_DIM_4, ID_DIM_4, IDZ_DIM_5, ID_DIM_5, IDZ_DIM_6, ID_DIM_6, IDZ_DIM_7, ID_DIM_7
  from ##CLOSE_{run_id}

dbo.p_MDM_VALUES

 Procedure p_MDM_VALUES
   @id_table_name nvarchar(255)   --Číslo tabulky nebo jméno tabulky MDM
   @accounting int,               --Číslo společnosti
   @output_table varchar(20),     --Výstup do ##tabulky
   @IOD char(1)                   --Insert=I, Output=O (defualt), Update=U, Delete=D
  • @IOD='I' Zapisuje hodnoty z ##tabulky do zadané tabulky MDM pro vybranou společnost
  • @IOD='O' Vrátí v ##tabulce obsah zadané tabulky MDM pro vybranou společnost
  • @IOD='U' Updatuje obsah zadané tabulky MDM pro vybranou společnost pro hodnoty IDZ v ##tabulce
  • @IOD='D' Maže obsah zadané tabulky MDM pro vybranou společnost pro hodnoty IDZ v ##tabulce
  • Příklad:
informace o ID a názvu MDM tabulek (pro parametr @id_table_name) pro vybranou společnost:
 select ID_MD, MD_NAME from {db_super}.dbo.V_MDM_NAMES where VALIDATE_FLAG=1 and ACCOUNTING=@accounting
 
Output:
 exec p_MDM_VALUES @id_table_name='Budget Ex.rate',@accounting=@accounting,@output_table='##MDM_{run_id}', @IOD='O'
 select * from ##MDM_{run_id}
Insert:
 create table ##MDM_{run_id} (IDZ bigint not null identity(1,1),
                              PROPERTY_1 varchar(20) null, PROPERTY_2 varchar(20) null, PROPERTY_3 float null)
 insert ##MDM_{run_id} (PROPERTY_1, PROPERTY_2, PROPERTY_3)
  select 'Jedna', 'Dva', 3.1
  union select 'Dva', 'Tri', 4.2
 exec p_MDM_VALUES @id_table_name='Budget Ex.rate',@accounting=@accounting,@output_table='##MDM_{run_id}', @IOD='I'
Update:
 create table ##MDM_{run_id} (IDZ bigint not null, PROPERTY_1 varchar(20) null)
 insert ##MDM_{run_id} (IDZ, PROPERTY_1)
  select 1, '1,1'
  union select 2, '2,2'
 exec p_MDM_VALUES @id_table_name='Budget Ex.rate',@accounting=@accounting,@output_table='##MDM_{run_id}', @IOD='U'
Delete:
 create table ##MDM_{run_id} (IDZ bigint not null)
 insert ##MDM_{run_id} (IDZ)
  select 1
  union select 2
 exec p_MDM_VALUES @id_table_name='Budget Ex.rate',@accounting=@accounting,@output_table='##MDM_{run_id}', @IOD='D'

dbo.p_id_audit

 Procedure dbo.p_id_audit
   @accounting int,           --Číslo společnosti
   @who_created varchar(50),  --Symbolické jméno procesu, který zavolal proceduru
   @id_audit int output       --Vrácená hodnota inkrementovaného čísla auditu
  • Inkrementuje a vrací hodnotu @id_audit. Je třeba volat pouze jednou na začátku celého ETL procesu.
  • Příklad:
exec p_id_audit @accounting=@accounting, @job_name='ETL', @who_created='ETL', @id_audit=0 output

dbo.p_JOURNAL_MAIL

 Procedure p_JOURNAL_MAIL
   @accounting int,                 --Číslo společnosti
   @id_audit int=null,              --Číslo auditu běhu ETL
   @search_txt nvarchar(1024)=null, --Hledaný text v Journalu (funkcí like), nalezaná poznámka se vloží do těla mailu
   @body nvarchar(4000)=null,       --Text mailu
   @use_mail_etl bit=null,          --Je-li =1 zasílat na adresu zadanou v aplikaci (ETL adresa)
   @use_mail_helpdesk bit=null,     --Je-li =1 zasílat na adresu zadanou v aplikaci (Helpdesk adresa)
   @recepient varchar(100)=null,    --Zasílat na adresu
   @cc varchar(100)=null,           --Zasílat na adresu v kopii
   @bc varchar(100)=null,           --Zasílat na adresu jako skrytá
   @subject nvarchar(255),          --Předmět zprávy
   @who varchar(50)                 –-Kdo/odkud byl zaslán mail
  • Procedura prohledá Journal zadané společnosti pro zadaný @id_audit dohledá hledané slovo @search_txt v poznámce a pošle mail na zadané adresy. Je-li @search_txt prázdné tak odešle mail s tělem @body.
  • Příklad hledání v Journalu slova Stop_ :
exec p_JOURNAL_MAIL
    @accounting=@accounting,
    @id_audit=@id_audit,
    @search_txt='Stop[_]',
    @use_mail_etl=1,
    @use_mail_helpdesk=1,
    @subject='Reporting.cz: ETL process result',
    @who='ETL'
  • Příklad odeslání mailu s tělem @body:
exec p_JOURNAL_MAIL
    @accounting=@accounting,
    @Body='Úloha skončila úspěšně.',
    @use_mail_etl=1,
    @use_mail_helpdesk=1,
    @subject='Reporting.cz: ETL process result',
    @who='ETL'

dbo.p_RUN_PROC

 Procedure p_RUN_PROC
   @accounting int,                 --Číslo společnosti
   @proc_name varchar(50),          --Jméno volané procedury
   @parameter varchar(100),         --Parametry volané procedury
   @etl_name varchar(50)            –-Kdo zavolal proceduru
  • Procedura spusti proceduru @proc_name s parametry @parameter.
  • Příklad spuštění procedury p_F_STATEMENT_load pro společnost @accounting a fiskální rok 2016 s expirací-chybovou hláškou po 60 minutách :
exec p_RUN_PROC @accounting=@accounting,
                @proc_name='p_F_STATEMENT_load',
                @parameter='@accounting=74,@year=2016,@min_expired=60',
                @etl_name='xyz'

{db_super}.dbo.p_year

 Procedure {db_super}.dbo.p_year 
   @accounting int,                 --Číslo společnosti
   @year int=null                   --Fiskální rok
  • Procedura vrátí hodnotu fiskálního roku se kterým byl spuštěno automatický přepočet jobem z aplikace.
  • Příklad spuštění procedury p_year pro společnost @accounting :
exec @year={db_super}.dbo.p_year @accounting=@accounting