The ETL (Extract, Transform, Load) process flow diagram's aim is to demonstrate the dependencies between all extraction, sorting and merging utilities, transformations, temporary work files or tables, error-handling processes, reconciliation activities, and the load sequence. The ETL applications, or ETL tool modules, must be run in this order.
Several source files and source databases from which data is derived may have operational dependencies. These interdependencies must be recognised since they may have an impact on the timing and sequence of running the ETL extract operations.
Sorting and Merging Utilities
Almost every step requires the collected data to be sorted in a specific way so that it may be merged before any further processing can take place. Sorting can also boost load performance significantly.
Transformations
For a number of reasons, the majority of the data must be changed. It is critical to investigate the best periods to carry out the transformations.
Remember that the BI decision-support environment has only one coordinated ETL procedure. As a result:
- Transformations that apply to all source data, such as data type and code conversions or data domain enforcement, should be carried out early in the ETL process flow.
- Transformations that are specific to a target database, such as aggregation and summarization for a specific data mart, should be performed near the end of the ETL process flow.
Temporary Work Files or Tables
Sorting, combining, and transforming all necessitate a significant amount of temporary storage space to keep the interim results. These temporary work files and tables can be just as huge as the original extraction.
Furthermore, these "temporary" work files and tables are not in fact "temporary." Make a plan to keep that space available for your staging area on a permanent basis.
Error-Handling Processes
Many errors are found during the ETL process as the data-cleansing standards are applied to the source data. If error reports are generated or erroneous records are rejected into a suspense file, the ETL process flow diagram should reflect this.
Activities for Reconciliation
Every programme module that manipulates data should generate reconciliation totals. This can take the shape of:
- Input and output record counts
- Domain-specific counts
- Amount counts
For extract, sort, and merge modules, record counts are adequate. Domain counts are useful for more complex transformation specifications, such as separating data values from a single source data element into many target columns.
Amount counts are often conducted on all amount data items, regardless of whether they are moved as-is, changed into a new format, or utilised in a computation.
Load Sequence
Because of potential interdependencies and a recursive relationship on one table, the sequence in which the tables must be loaded must be determined.
- If RI is enabled and the sales data mentions products, the Product dimension table may need to be loaded before the Sales table.
- Other tables can be loaded concurrently, which can significantly speed up the loading process.
The Staging Area
The staging area is where the ETL procedure takes place. Dedicated disc space, ETL application libraries, temporary and permanent work files and tables, and even a dedicated server are all examples.
The staging area can be centralised or distributed:
- If the majority of the source data is in flat files on a mainframe, it could be a central mainframe staging area.
- It could alternatively be on a separate server where the source data is loaded.
- The staging area is frequently decentralised. For example, a complicated mainframe file with numerous redefines and occurs clauses may need to be flattened out with a COBOL programme in a mainframe staging area before being downloaded to a UNIX system for further processing by an ETL tool.
Why ETL Design is Challenging
The ETL process is by far the most difficult to design and develop in any BI project. Because the BI decision-support environment has just one (logical) coordinated ETL process, growing the ETL scripts with each new BI application becomes quite hard, and regression testing takes an increasing amount of time.
Because of these factors, most firms prefer to employ an ETL tool for all or part of the ETL process, particularly the extract and transformation phases.
