MARLO-BI

MARLO BI module is a set of Dashboards created to help users to analyze their information registered in MARLO.

The Cubes created to analyze information are:

• Funding Sources

• OICRs

• MELIAs

• Innovations

• Policies

• Summary Indicators

Also there are some tables created to provided information about: Project Status Deliverables Milestones

MARLO-BI Github Structure

The structure is divided in three folders:

ETLs: ETLs folder has the pentaho Transformations and Jobs created to Extract, Transform and Load data from MARLO OLTP Application to MARLO BI Data warehouse (user marloBI)

models: models folder has the models for each cube in the data warehouse

Tables: Tables folder has the scripts used in the whole data warehouse creation process. They include staging tables, dimension tables and fact tables.

ETLs Folder Structure:

ETLs folder is divided in the following sub-folders:

• 01dim: In this folder, you could find the pentaho transformations for dimensions: basic dimension, info dimensions and intersect dimensions. Info dimensions are all the dimension related to the fact, for example: dim_oicrs_info, dim_melias_info, dim_fs_info, and so on.

• 02stg: This folder contains the pentaho transformations related to staging tables. These transformations are intermediate ETLs and they participate in dimension and fact table process. There are in there, the folowing sub-folders:

  • Deliverables, funding_sources, innovations, melias, milestone_status, milestones, oicrs, policies and projects: Transformations for specific dimension or table

  • generic: the transformatios for all staging tables related with generic dimensions

  • alliance: in this folder there are the transformations related to the Alliance CRIs.

  

• 03fact: The folder has the pentaho transformations for the fact tables processes.

• 04job: In this folder could be found the pentaho jobs that execute the ETL processes. This folder has the following sub-folders:

  

  The folder has a few pentaho jobs that are the principal jobs to execute the process:

  • crp_BI_process.kjb: Executes the whole process. This pentaho job calls the following pentaho jobs:

    • 01_basic_dimensions_process.kjb: This job executes all the transformations related to basic dimensions

    • 02_info_dimensions_process.kjb: This job executes all the transformations related to info dimensions (i.e. dim_oicrs_info, dim_melias_info, etc)

    • 03_intersect_dimensions_process.kjb: This job executes all the transformations related to the intersect dimensions. Intersect dimensions have been created to deal with the many to many relationships between the fact tables and the dimensions.

    • facts_process.kjb: This job executes the transformations to the fact tables process.

• 05misc: This folder has transformations for result dashboard process that could be useful in the MARLO-BI process

• config: This folder contains csv files for year, iso_alpha3 and general statuses. Also has the transformation to set the phase, which is used in some jobs.

Tables Folder Structure:

Tables folder has the folowing sub-folders:

• 01dim: This folder contains the sql scripts used to create dimension tables: basic dimension, info dimension an intersect dimension tables

• 02stg: This folder is used to store the sql creation scripts for staging tables. There are the following sub-folders in there:

  • deliverables, funding_sources, innovations, melias, oicrs, policies and projects have the staging tables sql scripts for the staging info dimension tables

  • gendim sub-folder has the sql scripts about the staging tables for: basic dimensions and intersect dimensions.

  

  Note: deliverables folder has the staging tables for the existent deliverable table used for Deliverables Dashboard. Those tables will be replaced once the deliverables cube have been released.

• 03fact:This folder has the scripts for the fact tables

• 04misc: Inside this folder you could find the scripts to create process_log table and bi_phase_by_cubes table. bi_phase_by_cubes is created in the source schema to join the source tables.

MARLO-BI How to execute the process

The process is executed using pentaho jobs and pentaho software: Pehtaho data integrator desktop or kitchen in command line mode.

The whole process use the crp_BI_process.kjb

this job is in the folder: MARLO-BI\ETLs\04job

In doppler server there is a shell file to execute the process using command line kitchen program: create_crp_BI.sh

located in /home/doppler_admin/batchs/BI folder /home/doppler_admin/batchs/BI