Product

philippines.jpg

small irc button

 
Home > Developers > Wiki > Working With The Sample Data Mart
Views
Actions
last edited 4 months ago by keith
Document Actions

THIS PAGE IS UNDER CONSTRUCTION

Documentation is being updated with the latest information on the sample data mart to be available with, or shortly after, the release of Mifos version 1.1.

The ETL jobs that are shipped with the Example data mart are described at that page. They were built with Talend Open Studio (TOS), an open source, innovative, and powerful data integration solution. This page describes how the sample data mart and ETL jobs are organized in the Mifos project repository, and how to work with the sample.

To download TOS go to Talend

Repository Organization

ETL Job Designs

Talend refers to a single ETL as a "job design". In the repository Mifos job designs are under trunk/mifos/TalendETL/<jobname>. Talend captures design information in files and subdirectories:

mifos/TalendETL/design/<jobname>/
Contains subdirectory process and the file talend.project.
mifos/TalendETL/design/<jobname>/process/
Contains the two design files <job_name>.item and <job_name>.properties.

ETL Source code

The ETL java source is in mifos/TalendETL/src/org_mifos_etl/<jobname>/<JobName?>.java, as a single, stand-alone Java file auto-generated from the above design documents.

Configuration

The environment in which ETL jobs run is set up in a global property file, and one for each job design:

mifos/TalendETL/conf/hibernate.properties
All the ETLs? are designed to use this single property file
mifos/TalendETL/src/org_mifos_etl/<jobname>/contexts/Default.properties
The default context is needed to populate the default values for database connections. This can have some fictitious values. [TODO: explain this]?

Schema

The directory /TalendETL/sql contains schema definitions that can be used to create the data mart's tables. Execute create_reporting_schema.sql to create the data mart and drop_reporting_schema.sql to drop the schema.

Libraries

The directory /TalendETL/lib contains all the java libraries needed for execution of the !ETL jobs.

Running the ETL jobs

If you want to populate the data mart from an existing transactional database, you can execute the script mifos/TalendETL/bin/execute_master_ETL.bat. This batch job executes the MasterETL job, which in turn runs all of the jobs in proper sequence.

How to work on a Talend ETL design

Preparation

  1. Download and install Talend Open Studio.
  2. Check out the Talend jobs and schema definitions from the Mifos SVN repository.
  3. If you want to run the jobs against a Mifos transactional database, use the sql schema definitions to create a database for the data mart.
  4. Modify /TalendETL/conf/hibernate.properties to point to the correct databases.
  5. Be sure that hibernate.properties is in your classpath.

Viewing and editing an ETL design

  1. Launch Talend Open Studio.
  2. If this is your first use of TOS, then you must first set a connection and an email address. Choosing "local" seems to work.
  3. If this is the first time you've worked on the job, then you need to create a new Talend project. If you want to preserve the package naming conventions therepository's package naming convention, be sure to name the project "org_mifos_etl".
  4. Once the project org_mifos_etl has been launched, import the design job (say regionalofficeloader):
  1. File > import...
  2. Select General/File System and press next.
  3. Browse the Mifos directory to /TalendETL/design/regionalOfficeLoader.
  4. Select ``regionalofficeloader``Talend will read the talend.project file and read the two files in process directory.
  1. Select the checkbox on the project you are importing and hit OK
  2. Talend will generate the flow diagram for the design job.
  3. Edit the design.
  4. Run the job (see below) and observe its effects.

Checking in new or updated designs

You need to copy your work from the TOS workspace to the Mifos directory structure:

  1. Copy the newly generated Java source from``<your_talend_workspace>/.Java/src/org_mifos_etl/<job_name>`` into ``/TalendETL/src/org_mifos_etl/<job_name>.
  2. Export the Job Item on local machine and copy the talend.project + the process folder in the local repository and check in the updated job design and source code.

If you would like to submit your work to the Mifos project, you can simply create a patch as usual and submit it using the usual procedures. If you have commit privileges you can update your project as usual.
Grameen logo