ETL explained

Before explaining how the reporting solution works it is important to define an ETL and explain its function in BIA Reporting. ETL is short for Extract, Transform and Load: three functions that are combined to pull data from a source and place it in a destination database:

• Extract : the process of reading data from a source.
• Transform : the process of converting the extracted data from its previous form into the form it needs to be in so that it can be placed into another database. Transformation occurs by using business rules or lookup tables or by combining the data with other data.
• Load : the process of writing the data into the target database.

BIA Reporting uses ETL's to move and transform data from the On-Line Transaction Processing (OLTP) data source to the BIA Reporting data sources. As explained in the Overview the data is moved from Source database to the Staging data storage, on to the Central Data Warehouse (CDW), and then is pushed out to the Data Marts.

For the population of each of these tables in each database we will need a separate ETL. The following example will help explain why we need these ETL's:

A number of the Participant reports require Person information where the status is 'Active' between two dates. To obtain this information from the Person table in the source database and populate the datamarts the following ETL's are needed:

• An ETL that will take all the data from the source PERSON table and populate the Staging database S_PERSON table
• An ETL that will take the required data from the staging S_PERSON table and populate the DW_PERSONHISTORY table in the CDW. Some transformations will need to be performed during the population in the CDW as we need to keep a complete history of the Person so we know what their status is at a particular point in time.
• An ETL that will take the required data from the Central DW_PERSONHISTORY table and populate the DM_FACTPERSONHISTORY fact table and some dimension tables (e.g. gender ETL to populate the gender dimension) in the Datamarts database. This datamart contains data that is tailored to and optimized for running reports against.

From the above example we can see that we will need a minimum of four separate ETL's to 'push' the necessary person data from source tables to a dimensional solution that the reports can run against.