Loading a Data Warehouse

This example demonstrates how CloverETL can fully automate the updating of a data warehouse and then email a simple report afterwards. We load monthly batch updates for a team work log warehouse, and for each batch we generate a report showing the absences breakdown and basic utilization.

Key Highlights:

• A "File Listener" on the Server triggers execution of a master jobflow that orchestrates the process (taking care of stage files and executing the warehouse load)
• The warehouse loads jobflow runs and dimension loads in parallel. Once finished, it takes care of loading all facts
• We use an Excel template to generate the final report
• EmailSender together with a recipients DB table are used to send out the report

Orders lookup and reporting

This simple graph helps find customers who received their orders late due to delayed shipping.

Key Highlights:

• First, gather all customers into a single lookup table
• In the second phase, we select the late orders and match them with the customer in the lookup table
• We use an Excel template to generate the final report
• EmailSender together with a recipients DB table are used to send out the report

Hadoop Big Data—Apache log

This is an example of processing and reporting from a potentially huge web access log. Let's look at three different approaches—from a pure CloverETL solution to massive Hadoop processing:

• A pure CloverETL solution to the problem
• Hadoop MapReduce job—prepare data in CloverETL, store the file using HadoopWriter, and run a MapReduce job in Hadoop cluster
• HIVE query—run a HIVE query on the prepared data

Operations performed:

• Use a raw web access log to get monthly unique visitors
• Group, aggregate, and count unique IPs
• Present multiple data processing options for comparison

Credit Card Fraud Detection

This example identifies corrupt transactions (i.e. missing customer IDs and incorrect currencies) and also reports suspicious customers by detecting excessive transactions volumes.

Key Highlights:

• Input data is stored in CSV files, Fixed-Length TXT, and XML
• Output simple HTML reports using StructuredDataWriter
• Current exchange rates are fetched online to standardize the transaction amounts for easy detection of excessive transactions

Salesforce.com (Web Services)

We're using CloverETL and the WebServiceClient component to connect to Salesforce.com and perform basic operations, such as retrieve data from a query and insert or update records.

Key Highlights:

• A jobflow orchestrates the establishing of a connection to Salesforce (or notifying the user when authentication fails) and subsequent operations
• Then, we perform two steps: retrieve Salesforce records according to a query and update records based on predefined conditions
• The jobflow helps to store and pass connection credentials to subsequent operations

File Operations

CloverETL with its jobflows can be used to orchestrate a process involving manipulation with files. Here, we prepare a bunch of files (copy them to a staging folder), then run some type of processing on each of them, and finally cleanup all temporary files.

Key Highlights:

• Copy files from the source location to the staging area in preparation of data extract
• List and send each file to an ETL process to extract relevant data (in this case, a list of customers from California)
• Clean up the staging area by deleting the files after the data operation

Data Quality Firewall

This example shows a jobflow that takes care of checking the incoming data prior to starting the main process.

The jobflow runs a "data quality firewall" graph that uses ProfilerProbe, part of Data Profiler extension, to measure characteristics of the data and reject files with excessively high error rate.

Key Highlights:

• A jobflow runs a preliminary "data quality firewall" graph prior to the main processing graph
• ProfilerProbe from the Data Profiler extension computes statistics such as NullCount, Min, Max, etc.
• Files with too many errors are moved aside using file operations in the main jobflow

E-mail Validation

Another data quality example where we use CloverETL to check the validity of a list of email addresses. A special EmailFilter component can perform checks from simple syntax validation to running a domain MX record check, up to SMTP verification with anti-graylisting retry periods.

Key Highlights:

• Load a list of emails from a file
• Check email syntax, domain MX, and SMTP using EmailFilter
• Output emails either as accepted or rejected after each test

Executing external utility (MD5)

This example shows how to get an MD5 signature for a list of files by calling an external MD5 utility.

Key Highlights:

• We use a jobflow that orchestrates the whole process; ListFiles generates the list of all files in a specific folder
• The Condition component is used to determine the OS environment (Windows or UNIX)
• ExecuteScript runs the MD5 utility on the file
• We map the standard output of the ExecuteScript to a "string" field