Create a new Component called the ETL (Extract Transform Load)

[brightoibe]

ETL means extract transform and load. It is a program that transforms the obs table into a user friendly format called RADET that the end users can easily understand. Specifications

• ETL transforms operational tables (obs) into analytic tables (RADET, clinical, drugs, lab,viral load)
• The focus of our first ETL operation will be the RADET analytic table.
• The ETL component calls another component called the Cohort Builder.
• ETL will be the component that will merge data from other sources (LIMS, Community Pharmacy etc) into the analytic tables.
• ETL service transforms an obs into RADET line listing
• The Consumer service cannot be running on when the ETL process is running to avoid data consistency issues. The convention is to program the ETL to run when upload is not happening e.g Evening time. All stop the Consumer service when the ETL is about to run.
• ETL service can be programmed to run every 6hrs, 12hrs or 24hrs or even after a zip file representing one facility is consumed.
• ETL should have a properties file just like the Consumer App that tell it the following
  • source database parameters
  • desitination database parameters
• ETL operates on all obs for one facility after the Consumer App has finished processing data for that facility, it produces a RADET.
• The Analytic database (star schema) will be on a seperate database from the staging db (Normalized)

[lordmaul]

I think I go with the option to run the ETL after every zipped file is consumed. This will provide analysis that is as close to real time as possible (of course, "real time" here is relative to the time of upload). So, once a zipped file is consumed (during consumption, validation can take place), the Consumer service will trigger the ETL module to run.