This page describes a typical ETL job created by Talend Open Studio. The job ClientLoanFactLoader is used as an example. This job loads selected client loan account facts from several transactional tables into datamart table client_loan_fact. The facts currently loaded are

• loan_account.loan_balance
• loan_arrears_aging.overdue_balance
• loan_arrears_aging.days_in_arrears.

The data represent a snapshot of all loans in the system, taken at the moment in time that the job was run.

This diagram -- a snapshot from Talend Open Studio -- shows the various components involved in the complete ETL job. The MifOS?_OLAP_DB and MifOS?_OLTP_DB represent the mifos datamart and transaction schema respectively. The job is broken into the following operations.

Extract (E)

The Mifos_OLTP_Loan_fact component extracts

• the client loan account details from the mifos transactional tables
• dimensional information from the datamart tables client_loan_account_dim and time_dim, preparing for the next stage.

Transform (T)

The tMap component assigns foreign keys to the facts that link them to the two dimensions. The client mapping is done by correlating the``account.global_account_num`` from each fact record with the corresponding attribute in client_loan_account_dim table. Note that the actual keys are anonymous integers, which follows best practice in defining keys in a data warehouse.

Load (L)

The snapshot fact is then inserted into the client_loan_fact table.

The schema below displays the relations between the client_loan_fact with other dmensions.