ETL: definition and architecture

Definition

The ETL, an acronym for "Extraction, Transformation & Loading, describes the process of collecting financial data and metadata from an unlimited number of sources, and subsequently organizing and centralising them in a single Repository. In particular, when there is a very high volume of data to be added, ETL is the most flexible and comprehensive data collection tool compared with all other tools (e.g. Quick Data Loader, Data Transformation Package).

The phases of the process: Extraction, Transformation and Loading

In CCH Tagetik, an ETL process comprises three phases:

Step	Description
1. Extraction	Data can be extracted from any external source: Excel files, text files, XML files, CSVs, TSVs, standard REST APIs and REST APIs with Odata standard, but also data warehouses, ERP systems (SAP, Oracle or Microsoft), or from Tagetik itself. IMPORTANT: you can also extract data simultaneously from several types of external sources: data (amounts, statistical data or FX rates) or metadata (elements of dimensions, hierarchies or groupings) If a large number of elements are to be edited/inserted, ETL can be used as an alternative method to manually entering and importing/exporting from Excel.
2. Transformation	The extracted data can be transformed using SQL rules, lookup tables, transcoding and totalling. For example, you can extract the chart of accounts that exists in Oracle, and transform the code and description of each of its elements (or aggregate several accounts together) to obtain the final chart of accounts to be loaded into Tagetik;
3. Loading	Processed data can be loaded onto various destinations, both within CCH Tagetik Tagetik (Tagetik databases) and externally(custom tables on Tagetik databases, generic databases, Excel files, text files or XML files).