34 Subsystems of ETL

In this, and in the next series of posts, I will be exploring the 34 subsystems of ETL Data Integration as defined by the Kimball Group. I introduce the subsystems in this post, and then I will discuss how each fits (or does not fit) into Talend & PDI .

The subsystem concept is a best-practice initiative formulated by The Kimball Group to help organizations design effective and efficient Data Integration environments for Data Warehousing using the Dimensional Model.

The Kimball Group categorizes the subsystems into 4 distinct groups: Data Extraction, Cleansing and Conforming Tasks, Data Delivery, and Management.

Data Extraction

1. Data Profiling

Talend: Talend has a separate tool for data profiling & data quality called 'Talend Open Studio for Data Quality'
Pentaho: 'DataCleaner' plugin is available for download for this purpose

2. Change Data Capture (CDC)

Talend: Talend has a inbuilt trigger based CDC feaature which can be applied easily. (Enterprise version only feature). It also has option of log based CDC for Oracle database.
Pentaho: Has no specific components

3. Extraction

Both Talend & PDI has plenty of connectors available to connect to a variety of sources

Cleansing and Conforming Tasks

4. Data Cleansing Subsystem
5. Error Event Management
6. Auditing
7. Removing Duplicates
8. Data Conformance

Data Delivery

9. Slowly Changing Dimensions (SCD)
10. Surrogate Key Generator
11. Hierarchy Manager
12. Special Dimensions Manager
13. Fact Table Builders
14. Surrogate Key Management
15. Bridge Table Builder
16. Late Arriving Data Handler
17. Dimension Manager
18. Fact Table Provider
19. Aggregate Generation
20. OLAP Cube Builder
21. Data Propagation Manager

Management

22. Scheduler
23. Backup System
24. Recovery and Restart
25. Version Control
26. Version Migration
27. Work flow Monitor
28. Sorting
29. Data Lineage and Dependency
30. Problem Escalation
31. Paralleling and Pipelining
32. Security
33. Compliance Manager
34. Metadata Repository

Comments

Post a Comment

Popular posts from this blog

Increase Java Memory For Pentaho Data Integration

Pentaho runs inside a Java virtual machine, and hence is bound by the properties of that VM. These optimisations can apply to just about any Java application, including the Pentaho BI Server and GUI tools. Method to increase memory allocation: Open the file spoon.sh or spoon.bat in a text editor Look for a section that looks like this: # ****************************************************************** # ** Set java runtime options                                     ** # ** Change 256m to higher values in case you run out of memory.  ** # ****************************************************************** OPT="-Xmx256m -cp $CLASSPATH -Djava.library.path=$LIBPATH -DKETTLE_HOME=$KETTLE_HOME -DKETTLE_REPOSITORY=$KETTLE_REPOSITORY -DKETTLE_USER=$KETTLE_USER -DKETTLE_PASSWORD=$KETTLE_PASSWORD -DKETTLE_PLUGIN_PACKAGES=$KETTLE_PLUGIN_PACKAGES" Change the -Xmx parameter to alter the maximum heap size, e.g. -Xmx1024m
Read more

Different Match Models in tMap with example

Unique Match:     (Also called Last match) If several matches are found in the Inner Join, i.e. the last row matching the explicit join as well as the filter will be added to the output flow. First Match: If several matches are found in the Inner Join, i.e. the first row matching the explicit join as well as the filter will be added to the output flow. All Match: If several matches are found in the Inner Join, i.e. rows matching the explicit join as well as the filter, all of them will be added to the output flow Example: deptid   sales 1           1000 2           2000 3           3000 2           5000 3           6000 Outputs: 1. Unique match deptid   sales 1           1000 2           5000 3           6000 2. All matches deptid   sales 1           1000 2           2000 3           3000 2           5000 3           6000 3. First match deptid   sales 1           1000 2           2000 3           3000
Read more

Simple Transformation from a csv to Excel Output

Image
Lets start with a basic transformation to convert your comma seperated values file input to excel output Step 1: Launch kettle 4.1 Step 2: select file>new>transformation Step 3: drag input>csv input & output>csv output on the workspace Step 4: Arrange them one after another & create a hop/connection between them by holding the shift key & dragging the arrow from center of csv input step to excel output step Step 5 : double click the csv input step & configure as shown in the figure above. Choose the filenae location & select get fields button to auto retrieve fields. Step 6: double click the excel output step & give the desired path location of the new excel file which is to be created, click the fields tab & select the get fields button to retrieve all the fields currently present in the stream. Step 7: Click the run button above the workspace else Select --> Action>Run from the menu. Your file is automatically crea
Read more