Cloverleaf BI FAQs - Extract, Transform & Load

What Is an ETL?

ETL (Extract, Transform and Load) is a process responsible for pulling data out of source systems and moving it into a target system. In the case of Cloverleaf Analytics the target is the ODS (operational data store) and data warehouse.

What Is An ETL?

How Do We Load Data into Cloverleaf Analytics?

Cloverleaf Analytics has partnered with several policy administration companies to provide connecters to load data from those systems into Cloverleaf Analytics. If you have a custom developed solution or a solution from a vendor that has not partnered with Cloverleaf Analytics yet, we provide core policy and claim templates as well as line of business specific templates for loading data. You can extract your data into the template formats or load the data directly to the template staging tables. Cloverleaf Analytics solution with automatically, via our ETL, perform the following on your data:

– Validate the data ensuring data types, lengths and referential integrity
– Run insurance business rules against the data for logical business level validation of the data
– Load the data into the operational data store (ODS)
– Load the data into the data warehouse, calculating insurance KPIs during the process

Instead of Extracting Data to Files, Can We Load Data Directly to Cloverleaf Analytics Template Database?

Absolutely! We encourage loading data direct to the Cloverleaf Analytics template staging area. This is a faster movement of the data from the source system to Cloverleaf Analytics then through flat files. All the powerful validations and business rules that come with Cloverleaf Analytics are run against data in the tables just as they are with the files, so you can be confident in the integrity of your data.

Can We Load Data from More than One Policy or Claim Admin System?

Yes, we built Cloverleaf Analytics to be loaded from as many different systems as you have. We have built in code translations and calculations to handle conforming data from multiple systems into a uniform dataset to give you the best analysis across all your systems.

We are not owned by a policy administration system vendor, like other solutions, so we are not tied to one system, but are open to all.

Each of Our Systems Have Different Codes for the Same Field. How Does Cloverleaf Analytics Handle This?

Cloverleaf Analytics has code translation tables in our configuration toolset that were built specifically for this purpose. You can specify the code coming from each system and the uniform code you would like in the ODS and Cloverleaf Analytics will do the work for you.

Each Of Our Systems Have Different Codes For The Same Field. How Does Cloverleaf Analytics Handle This?

Can I Add My Own Fields to Cloverleaf Analytics? Do I Have to Edit the ETL Process Like the Other BI Solutions?

Absolutely, you can add your own fields! Cloverleaf Analytics has configuration tables that have all the fields that are loaded into the system. To add new fields you simply add your new field, it’s data type and description. Cloverleaf Analytics ETL will automatically add the new field to the database for you. You don’t have to edit ETL processes like other systems, nor do you need to modify the database. Cloverleaf Analytics takes care of updating the database and loading the data for you.

Does Cloverleaf Analytics Perform Validations on the Data We Are Loading?

Yes, Cloverleaf Analytics performs the following validations on the data as it is loaded:

– Validation of proper datatypes – verify that the data is in the proper data type
– Validation of field lengths – verify that the data is not too long or too short based on what is required
– Validation of mandatory or required fields – verify that required information is present
– Validation of referential integrity – check that references from one set of data to another are valid
– Business rules validation – Cloverleaf Analytics runs business rules against the data to verify it is valid insurance data
– We work closely with our customers and their data and continually add new business rules and validations to ensure the quality of the data being loaded.

If My Data Does Not Pass Validations How Will I Know?

When validation errors are found in the data, Cloverleaf Analytics ETL sends an e-mail with an Excel attachment that has all the issues listed along with a detailed description of the error and an example record with the problem. You can configure the e-mail to go to as many recipients as you want.

Can I Add My Own Validations Without Modifying the ETL?

Absolutely! Our web configuration toolset allows you to add your own rules and even turn on and off rules. No need to modify ETL processes or do any programming.

Does Cloverleaf Analytics Have a Staging Area?

Yes, we have a staging database that contains the template data tables. The data is loaded into the staging area where it is validated before being loaded into the ODS an data warehouse.

Other BI Solutions Require Us to Manually Balance the Data. Does Cloverleaf Analytics Have an Automated Balancing Process?

Yes, Cloverleaf Analytics has an automated balancing process for both the detailed and summary data being loaded. This allows the system to ensure that the transaction level data balances as well as the point-in-time numbers like ending reserves and earned premium.