[CC] (MARLO-BI) To create and add General Status dimension to OICRs, MELIAs and Total Indicators Facts

CCAFS / MARLO-BI

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

GNU General Public License v3.0

0 stars 0 forks source link

[CC] (MARLO-BI) To create and add General Status dimension to OICRs, MELIAs and Total Indicators Facts #16

Closed ccastiblancoCGIAR closed 3 years ago

ccastiblancoCGIAR commented 3 years ago

To create and add General Status dimension to OICRs, MELIAs and Total Indicators Facts

[x] Create homologation table between funding sources statuses and general statuses to create a single Status dimension
[x] Create ETL to status dimension
[x] Create queries to OICRs and MELIAs statuses and tested on prod environment
[x] Modify fact tables to add a new status dimension
[x] Modify ETLs and Jobs to add new status dimension
[x] Test ETLs and jobs on local environment
[x] Modify tables on prod environment
[x] Copy ETLs and jobs to Doppler and test on prod environment

Deliverable: New Dimension and Fact Tables with new Dimension Move to Review when: ETLs tested on local environment Move to Closed when: ETLs tested on Doppler .

ccastiblancoCGIAR commented 3 years ago

Homologation table is in MARLO: general_statuses_table

Table stg_general_statuses created in local environment ETL stg_general_statuses created and tested in local environment:

ccastiblancoCGIAR commented 3 years ago

Table dim_general_statuses created in local environment ETL for dim_general_statuses created and tested in local environment:

ccastiblancoCGIAR commented 3 years ago

Table pre_oicrs_fact modified: column pk_id_general_status added ETL for stg_pre_oicrs_fact modified and tested in local environment.

ccastiblancoCGIAR commented 3 years ago

Fact table fact_oicrs modified: column fk_id_general_status added

ETL fact_oicrs modified and tested in local environment.

ccastiblancoCGIAR commented 3 years ago

MELIAS: Table pre_melias_fact modified: column pk_id_general_status added ETL for stg_pre_melias_fact modified and tested in local environment.

Table fact_melias modified: column fk_id_general_status added.

ETL fact_melias modified and tested in local environment:

ccastiblancoCGIAR commented 3 years ago

Funding Sources: Table pre_fs_fact modified: The fk_id_agreement_status was deleted and the fk_id_general_status added.

ETL pre_fs_fact modified.

Table: fact_fs modified: fk_id_agreement_status was deleted and fk_id_general_status added.

ETL fact_funding_sources was modified and tested in local environment

ccastiblancoCGIAR commented 3 years ago

Summary Indicators: table pre_summary_indicators_fact modified:

ETL pre_summary_indicators_fact modified and tested in local environment

Table fact_summary_indicators modified:

ETL fact_summary_indicators modified and tested in local environment

ccastiblancoCGIAR commented 3 years ago

Power BI General Dashboard modified in OICRs, MELIAs and Funding Sources:

OICRs:

MELIAs:

Funding Sources:

ccastiblancoCGIAR commented 3 years ago

General dashboard modified in Locations page, to include status:

ccastiblancoCGIAR commented 3 years ago

A new Pentaho job was created to execute the whole ETL process: crp_BI_process.kjb.

Tables created in production environment: stg_general_statuses dim_general_statuses

Tables modified in production environment: pre_oicrs_fact fact_oicrs pre_melias_fact fact_melias pre_fs_fact fact_fs pre_summary_indicators_fact fact_summary_indicators

The ETLs and Jobs were transfered to Doppler and executed in production environment.