Examples & Solutions

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
Requires
CloverETL Server

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
Runs on
CloverETL Designer

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
Requires
CloverETL Server

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
Runs on
CloverETL Designer

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
Requires
CloverETL Server

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
Requires
CloverETL Server

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
Requires
CloverETL Server

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
Runs on
CloverETL Designer

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
Requires
CloverETL Server

Case Studies

Try It Yourself

Get Started

Test drive these examples in our 45-day trial of the CloverETL Designer and Server.

Customer Stories

For many companies CloverETL's ease of use is what draws them in.

One Canadian customer in particular chose CloverETL to manage a major element of its website data management. They were relieved to find that with CloverETL, it only took thirty seconds to manage the data transformation from their legacy system to their website—when it used to take three hours.

CloverETL addresses many of the challenges faced by growing IT operations. CloverETL can connect to and parse data from many sources, such as reading and writing to Lotus Domino. It's often the right package in terms of price and runtime production sizing.

CloverETL combines flexibility and power, making it ideal for many MDM customers.

"CloverETL has been a powerful data integration software core that enables quicker project implementation and faster performance. With a low cost buy-in and a scalable architecture, it represents the best value in the market today in the growing Business Intelligence arena," said a customer with ever-evolving MDM requirements.

Typical ETL software could not get the job done: it did not provide the flexibility or extensibility needed to embed into a wider offer. The company needed Clover, an application that would extract, transform, and load data between their platform and external systems, simplifying implementation and enhancing the end-user experience.

In emerging Latin American markets, CloverETL became a strong OEM partner.

"We chose CloverETL because it is perfect fit for us as we craft solutions in data. Customers now want a combination of best-of-breed ETL to drive and work with other data tools. Clover brings out the best in what we offer, fitting our strategies in address validation, data cleansing, and data profiling," said our latest partner.

To many of our OEM customers, Clover represents the future architecture of data with agile solutions at a great price.

Customers using CloverETL

Press Releases

OEM Partner Program

Embedded data integration platform

If you are a solution provider for Business Intelligence, Master Data Management or any other data dependent, you can greatly benefit from implementing the CloverETL OEM Foundation which gives you an industry-standard data integration layer as part of your offering.

More about partnering with CloverETL ›



Data integration software and ETL tools provided by the CloverETL platform offer solutions for such data management tasks such as data integration, data migration, or data quality. CloverETL is a vital part of such enterprise solutions as data warehousing, business intelligence (BI) or master data management (MDM). CloverETL’s flagship applications are CloverETL Designer and Server. CloverETL Designer is a visual data transformation designer that helps define data flows and transformations in a quick, visual, and intuitive way. CloverETL Server is an enterprise ETL runtime environment. It offers a set of enterprise features such as automation, monitoring, user management, real-time ETL, clustering, or cloud data integration.