aeps platform documentation

AEPS Platform DocumentationRelease 2.0.0

Steven Sotelo

Feb 06, 2020

GET STARTED

1 Get started with AEPS 2.0 3

2 Installation with Docker 5

3 Introduction 9

4 ODK (version 1) 11

5 Introduction 17

6 AEPS Database 19

7 AEPS Users 21

8 Introduction 23

9 Install 25

10 Users 27

11 Forms 29

12 Setup 37

13 Reports 41

14 Introduction 43

15 Configuration 45

16 Pandas (ODK) 51

17 Introduction 59

18 Superset 61

i

AEPS Platform Documentation, Release 2.0.0

Agricultura Especifica por Sitio (AEPS) Platform is a suite of open source tools that help organizations collect, store,analyze and visualize information about productive events of crops as support in the decision-making, under focus ofsite-specific agriculture.

GET STARTED 1


2 GET STARTED

CHAPTER

ONE

GET STARTED WITH AEPS 2.0

This project has the following repositories in Github:

• https://github.com/CIAT-DAPA/aeps_platform: This repository has the website to configure the platform.

• https://github.com/CIAT-DAPA/aeps_platform_db: This repository has the databases scripts of the platform.

• https://github.com/CIAT-DAPA/aeps_platform_forms: This repository has the surveys that have been created tocollect data.

• https://github.com/CIAT-DAPA/aeps_platform_etl: This repository has the scripts to transform data from sourceand save records into the database.

• https://github.com/CIAT-DAPA/aeps_platform_docker: This repository has the docker files to create the imagesof all components.

• https://github.com/CIAT-DAPA/aeps_platform_visualizations: This repository has the dashboards in whichusers can see information.

• https://github.com/CIAT-DAPA/aeps_platform_docs: This repository has the documentation about all platform.

1.1 How does it work?

The process start when farmers, technical assistants, researchers and others collect data in the field. Once they havecollected data (could be online or offline) they should send this information to central repository, this process we calledsync data. Then an administrator user should takes those data and pass them through a set of pipes, which ones willclear data. In the process of transforming the system applies a set of filters and rules in order to assurance the qualitydata, then it will save all records into a central database.

Once the data are stored into the database, they are ready to be anaylized with many methodologys: Bussines intelli-gence, Machine Learning, Artifical intelligence, etc. When the results are ready, we can show them through interactiveplots that users can understand.

The following picture shows how the general process is:

3

https://github.com/CIAT-DAPA/aeps_platform

https://github.com/CIAT-DAPA/aeps_platform_db

https://github.com/CIAT-DAPA/aeps_platform_forms

https://github.com/CIAT-DAPA/aeps_platform_etl

https://github.com/CIAT-DAPA/aeps_platform_docker

https://github.com/CIAT-DAPA/aeps_platform_visualizations

https://github.com/CIAT-DAPA/aeps_platform_docs


4 Chapter 1. Get started with AEPS 2.0

CHAPTER

TWO

INSTALLATION WITH DOCKER

This chapter guides you through the installation and initial setup of a self-hosted instance of all platform from Dockercontainers. You can find the Dockerfiles into the Github repository: https://github.com/CIAT-DAPA/aeps_platform_docker. Initially you will learn how to install individually each component, however in the last part of this tutorial, itwill show how to install all components together.

You should start cloning the repository from Github:

1 git clone https://github.com/CIAT-DAPA/aeps_platform_docker.git

Tip: The following recommendations should be followed before to execute the commands:

• Rename the files called .env.example to .env

• Set real values for the variables into each file .env

• Check your current position, each container tells to you where you should be

• Commands have to be executed in command line

2.1 AEPS Database (MySQL)

AEPS Database is a container based on MySQL engine. The folder in which you should be in cmd isaeps_database_mysql.

Warning: Please open the file aeps_database_mysql/conf/01_create_db_and_user.sql and edit the lines 2,3,you have to set the password that you want for the user of the database.

1 create database if not exists `aeps_2_0`;2 create user 'aeps_user'@'%' identified by 'your_password';3 grant all on `aeps_2_0`.* to 'aeps_user'@'%' identified by 'your_password';4 flush privileges;

1 # Build the image2 docker build -t stevensotelo/aeps_database_mysql:latest .3 # Create a container like a deamon4 docker run -p 3306:3306 --env-file ./.env --name aeps_db -d stevensotelo/aeps_

→˓database_mysql:latest

If you want to check if the container is running correct:

5




1 docker logs aeps_db2 > [Entrypoint] MySQL Docker Image 5.7.21-1.1.43 > [Entrypoint] Initializing database4 > [Entrypoint] Database initialized5 > Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.6 > Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.7 > Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.8 > Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.9 > Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping

→˓it.10

11 > [Entrypoint] running /docker-entrypoint-initdb.d/01_create_db_and_user.sql12

13

14 > [Entrypoint] running /docker-entrypoint-initdb.d/02_aeps_db.sql15

16

17 > [Entrypoint] Server shut down18

19 > [Entrypoint] MySQL init process done. Ready for start up.20

21 > [Entrypoint] Starting MySQL 5.7.21-1.1.4

Note: Once you have checked that the container is running, you can try connecting from a MySQL Client

2.2 AEPS Web management (Linux)

AEPS Web management is a container based on ASP.Net Core 2.2. The folder in which you should be in cmd isaeps_web_management.

Warning: Please open the file aeps_web_management/site/appsettings.json and edit the lines 3,4, with the datato connect with AEPS Database. The following file is just a test with default parameters, Please don’t use it inproductions enviroments

1 {2 "ConnectionStrings": {3 "AEPSDatabase": "server=172.17.0.2;port=3306;user=aeps_user;password=your_

→˓password;database=aeps_2_0",4 "AEPSUsersDatabase": "server=172.17.0.2;port=3306;user=aeps_user;password=your_

→˓password;database=aeps_2_0"5 },6 "Logging": {7 "LogLevel": {8 "Default": "Warning"9 }

10 },11 "Languages": "es-CO,en-US",12 "AllowedHosts": "*",13 "Installed": "false"14 }

6 Chapter 2. Installation with Docker


1 # Build the image2 docker build -t stevensotelo/aeps_web_management:latest .3 # Create a container like a deamon4 docker run --name aeps_webadmin -p 8000:80 --env-file ./.env -d stevensotelo/aeps_web_

→˓management:latest5

6 # Once you have installed the website from a browser, you have to change the→˓appsettings.json

7 # Open command line8 docker exec -it aeps_webadmin bash9 # Opening the file appsettings.json with VIM

10 vim appsettings.json11

12 # Change the file appsettings.json, it should be like this (remember change→˓Connection Strings):



→˓password;database=aeps_2_0"17 },18 "Logging": {19 "LogLevel": {20 "Default": "Warning"21 }22 },23 "Languages": "es-CO,en-US",24 "AllowedHosts": "*",25 "Installed": "true"26 }27 # To saved the file you should type the following command:28 # :wq!29

30 # Now we have to restart the container31 exit32 docker restart aeps_webadmin

Tip: You should check the section Install of the AEPS WEB ADMINISTRATOR while you are trying to install thewebsite

2.3 AEPS ETL

2.4 AEPS Superset

AEPS Superset is a container based on Python 3 and Node. The folder in which you should be in cmd isaeps_visualization_superset.

Warning: Please open the file aeps_visualization_superset/conf/superset_config.py and edit all lines withyour custom values. The following file is just a test with default parameters, Please don’t use it in productionsenviroments

2.3. AEPS ETL 7


1 MAPBOX_API_KEY = 'your_key'2 #CACHE_CONFIG = {3 # 'CACHE_TYPE': 'redis',4 # 'CACHE_DEFAULT_TIMEOUT': 300,5 # 'CACHE_KEY_PREFIX': 'superset_',6 # 'CACHE_REDIS_HOST': 'redis',7 # 'CACHE_REDIS_PORT': 6379,8 # 'CACHE_REDIS_DB': 1,9 # 'CACHE_REDIS_URL': 'redis://redis:6379/1'}

10 SQLALCHEMY_DATABASE_URI = 'mysql://aeps_user:your_password@mysql:3306/aeps_2_0'11 SQLALCHEMY_TRACK_MODIFICATIONS = True12 HTTP_HEADERS = {'X-Frame-Options': 'ALLOWALL'}

1 # Build the image2 docker build -t stevensotelo/aeps_superset:latest .3 # Create a container like a deamon4 docker run -p 8088:8088 --name aeps_vz -d stevensotelo/aeps_superset:latest

8 Chapter 2. Installation with Docker

CHAPTER

THREE

INTRODUCTION

AEPS Collect data is the component that allows to users capture data. Currently, the module is working with ODK foreverything related to data capture.

The forms are available in Github respository https://github.com/CIAT-DAPA/aeps_platform_forms.

9

https://opendatakit.org

https://github.com/CIAT-DAPA/aeps_platform_forms


10 Chapter 3. Introduction

CHAPTER

FOUR

ODK (VERSION 1)

The forms are created using the XLSForm standard, then are transformed with the ODK XLSForm tool for the gener-ation of XML files.

Tip: The form that is distributed to users who collect the information is [my_survey].xml. This file is interpretedby ODK Collect to deploy the controls and allow users to collect data. The form structure is found in the document[my_survey].xlsx.

We have developed a list of forms which are available at: https://github.com/CIAT-DAPA/aeps_platform_forms/tree/develop/odk

Table 1: FormsCategory Name File

nameDeveloped by Description

Crops Rice productionevents

aeps_production_eventCIAT

4.1 Administrator guide

This document describes how to set the configuration with ODK Tools in order to be able for collecting informationof production events with a site-specific agriculture approach. This guide was designed for people who have theadministrator role. An administrator is the person who is in charge of distributing the surveys and centralizing thedata collected by the collectors users.

The administrator should prepare the surveys before to share with users. Preparing files is a process that allowsto administrators configure and customize the parameters of the surveys. The file in which is easy to make thosechanges are in Excel files ([my_survey].xlsx or [my_survey].xls), however administrator can make the changes in[my_survey].xml. We will focus in Excel files.

See also:

You can see a list of surveys in ODK (version 1)

4.1.1 Set the submission url for answers

To perform this process, administrator must modify the form ([my_survey].xlsx) in the sheet called settings. Admin-istraror must locate the column called submit_url, then in the next row must replace the url of the destination, it couldbe the Google Spreadsheet url or the other services that administrator will implement.

11

https://xlsform.org/en/

https://opendatakit.org/xlsform/

https://github.com/CIAT-DAPA/aeps_platform_forms/tree/develop/odk

https://github.com/CIAT-DAPA/aeps_platform_forms/tree/develop/odk


See also:

If you want to know how get a submission url from Google Drive Administrator guide - Google drive

4.1.2 Transforming forms in surveys

It is the process to transform the Excel files into XML files. the easiest way to do it is through the online tool https://opendatakit.org/xlsform/, in which you just have to upload the Excel file, then download the XForm (XML file).

The forms can be edited according to the needs of each implementation following the rules of XLS Form. Files aretransformed into XML using the online tool https://opendatakit.org/xlsform/. For more information about Open DataKit (ODK) you can consult the official documentation at: https://docs.opendatakit.org/.

12 Chapter 4. ODK (version 1)



http://xlsform.org/en/


https://docs.opendatakit.org/


4.1.3 Sharing surveys

Currently, we have different ways to share the form to these users such as: Implement an Aggregate ODK server, useGoogle Drive, distribution through a file, etc. We have documented the following methods to spread forms:

• Administrator guide - Google drive

4.2 Administrator guide - Google drive

4.2.1 Getting submission url

With this method you will be able to get a submission url from Google drive.

1. Create a folder inside of Google Drive

2. You must get inside of the created folder and add a new Blank spreedsheet

3. Set the name for the new document

4.2. Administrator guide - Google drive 13


4. In order to get a submission url, you will have to share the document. Click on the Share button, once there, awindow will be opened in which you have to do click in Get shareable link. Ensure that the user has editingpermissions.

4.2.2 Distribution surveys

This way of distribution allows you to quickly and easily send the form through the Google office platform. Thismethod requires that all users (administrator included) have an Google Account. Follow the instructions to carry outthis process:

1. Create a folder inside of Google Drive



2. You must get inside of the created folder and upload the form e.g.: aeps_production_event.xml

3. The next step is to share the form with users. To do this we press right click on the form and click on the Shareoption, this will open a new window in which we must type each of the emails of the users, which will collectdata in field.

4.2. Administrator guide - Google drive 15


4.3 Collector guide


CHAPTER

FIVE

INTRODUCTION

It is the database component, which was designed for Transactional SQL systems and built in My SQL. This schemawas designed for offering a dynamic structure to manage complex surveys for agriculture. We have 3 potentialdatabases:

• AEPS Database: That database stores all information about agronomy practices of the platform.

• AEPS Users: It has information about the Users. They are generate from ASP.NET Core Identity.

• Superset management: It has all tables that are used in Superset application.

Tip: All databases can be stored in the same engine, even within the same database.

17

CHAPTER

SIX

AEPS DATABASE

6.1 Relational model

The following picture shows how the database was built and the relation between tables:

19


6.2 Table dictionary

In the following table you can see the list of all tables:

Table 1: AEPS TablesTable name Engine Commentcon_countries InnoDB This table has the list of countriescon_municipalities InnoDB This table has the list of municipalities - administrative level 3con_states InnoDB This table has the list of states - administrative level 2far_answers InnoDB This table has the forms answers of each production eventfar_farms InnoDB This table has the information about farmsfar_plots InnoDB This table has the information about farms plotsfar_production_events InnoDB This table has the history about production events infar_responses_bool InnoDB This table has the forms answers of each production event for ques-

tion with the boolean valuesfar_responses_date InnoDB This table has the forms answers of each production event for ques-

tion with the date valuesfar_responses_numeric InnoDB This table has the forms answers of each production event for ques-

tion with the numeric valuesfar_responses_options InnoDB This table has the forms answers of each production event for ques-

tion with the unique or multiple typefar_responses_text InnoDB This table has the forms answers of each production event for ques-

tion with the text valuesfrm_blocks InnoDB This table has the list of blocks or sections to add to the formsfrm_blocks_forms InnoDB This table has the relationship between forms and blocksfrm_forms InnoDB This table has the list of formsfrm_forms_settings InnoDB This table has the list of settings for formsfrm_options InnoDB This table has the list of options for questions, the questions have to

be unique or multiplefrm_questions InnoDB This table has the list of questions for the formsfrm_questions_rules InnoDB This table has the list of rules for each question. A rule depends of

the application in where is gonna be validatedsoc_associations InnoDB This table has the information about associationssoc_people InnoDB This table has the information about people. It has personal infor-

mation about farmers and technical assistanssoc_technical_assistants InnoDB This table has the information about who people are technical assis-

tants

6.3 Views dictionary

In the following table you can see the list of all views to extract data from the database:

Table 2: AEPS ViewsViews name Commentvw_answers This view has information about all answers of the surveys. The answers are

separated according of data type.vw_production_events This view has information about all production events stored in the database. It

has information about farmers, technical assistants, farms and plots.vw_forms This view has information about how the surveys are built.vw_answers_ta This view is a mix of vw_answers and vw_production_events.

20 Chapter 6. AEPS Database

CHAPTER

SEVEN

AEPS USERS

7.1 Relational model

The following picture shows how the database was built and the relation between tables:

21


22 Chapter 7. AEPS Users

CHAPTER

EIGHT

INTRODUCTION

It is a website which allows the parameterization of the AEPS Platform. It was developed in .Net Core 2.2. Thewebsite is multilanguage, currently it is in spanish and english.

The web administrator allows to user:

• Management forms

• Setup global parameters

• Management users

• See dashboards

The following picture shows a screenshot of the website:

8.1 Guide

In order to users can take advantage of this guide, we explain what should be the step by step to configure the platform.

1. Install: You have to install the website in order to create the first user.

2. Manage user: You should create all users which are available to work in the platform. Users can sigin in thewebsite in order to configure parameters and see dashboards about information.

23


3. Configure forms: Once users have been created, you should create the forms. A form is the structure how thedata will be organized. You can import XLS Form or Create a new form, both have their implications that youwill see in the next chapters.

4. Setup additional parameters: Additional parameters are some global configurations which affects reports.

5. Visualize dashboards: Depending of the platform selected to visualize information, Some dashboards havebeen design to display information about crop performance and collect performance.


CHAPTER

NINE

INSTALL

In this chapter you will learn how to install the website. The installation is the process in which you have to create thefirst user, he will be the administrator of all platform.

Tip: You should check the variable’s value Installed inside of appsettings.json, if it is true, it means that the websitewas configured, however if it is equals to false, you have to create the admin user.

The instructions to install the website are the following:

1. Check the appsettings.json file and the value of the variable Installed, it should be with value false

2. Check the Connection Strings of the database and setup them

3. Start the website

4. Create the administrator user

5. Change the value of the variable Installed to true

6. Restart the website

7. Log in into the website with that administrator user

The final status of the appsettings.json should be like that:

25




→˓password;database=aeps_2_0"5 },6 "Logging": {7 "LogLevel": {8 "Default": "Warning"9 }

10 },11 "Languages": "es-CO,en-US",12 "AllowedHosts": "*",13 "Installed": "true"14 }

26 Chapter 9. Install

CHAPTER

TEN

USERS

The users is the module in which you can control who would be able to sign in into the platform.

With users’ module, you can:

• Add new users

• Check users’ status

• Disable users

10.1 Create users

Users just can be added by other user. The first user is the platform’s administrator (Check install chapter).

27


Let’s see what information is stored inside of an user.

Table 1: AssociationsField DescriptionEmail email addressPassword Initial password (Users once have signed in, they can change it)Confirm password Retype the passwordLockout end Date until user was enabled to log in

28 Chapter 10. Users

CHAPTER

ELEVEN

FORMS

The forms is the module, which has all surveys and its configurations available. In order to analyze data, we shouldhave to configure how information is organized and the forms are the best way to do that.

With forms, users can:

• Import ODK Forms

• Manage forms

• Manage blocks

• Manage questions

• Set options and rules for validating questions

Caution: It our context a form is the set of questions that can change for each crop’s production event. Allquestions, which are asked to farmers, are not part of a form,for example: the information about technical assistantsor farmers, even the data about farm or plot are not part, however all data that we collect about production event, itis part of a form.

29


11.1 Import forms

Tip: The easiest way to configure a form is importing from a XLS Form file. It is because the website will import allcharacteristics from XLS and fix them into the database, so you shouldn’t add nothing new.

In order to import a new form into the platform, you should be known what is the variable, which will be used like astart point to import. The start point (variable’s name) is a reference where the dynamics questions are contained. Bydefault, the start point is called plot. You can see more collect section

Once you have typed the variable’s name, you have to choose the XLS file from your pc, then you just have press in thebutton Import XLS Form, so the application will start to create blocks, questions, rules and configure the new form.The global settings like name, title and description will be taken from the settings sheet of the XLS file.

Tip: Import forms option is just able to import data in one language, so you shouldn’t have language labels in thedefinitions of the questions inside of XLS file.

11.2 Manage forms

You are able to see details, add, edit and delete forms, also you can set the questions’ blocks for each form. In casethat you have many forms in your instance, you can search inside of the list just typing in the search box. To create anew form from scratch, you just need press in the button Create new. In front of each form in the list, you can see theoptions: Details, Edit, Delete and Set Blocks.

11.2.1 Details form

Let’s see what information is stored into of a form.

30 Chapter 11. Forms

http://xlsform.org/en/

https://aeps-platform-docs.readthedocs.io/en/latest/collect.html


Table 1: FormField DescriptionName machine name of the formTitle form titleDescription short description of the formEnable it sets if the form is able (1) or disable (0)Extern Id external identificatorDate created date when it was createdDate updated date when it was updatedBlocks It is the list of blocks associated to this formSettings Extra settings, they are used to get from settings sheet in XLS file

11.2.2 Set blocks

Once you had selected the form, you can add new blocks to this form. You should select the block that you want toadd and set the order, then press in the button Add. The order field is just to set in which order it should be listed, it isa number. The list that you can see below are the blocks which are part of this form. At the same time you can removeblocks from a form, you just need to press in the icon Delete in front of each block’s name.

11.2. Manage forms 31


11.3 Manage blocks

A block is a set of question which can be added to a form. You are able to see details, add, edit and delete blocks.

11.3.1 Details block

Let’s see what information is stored inside of a block.



Table 2: FormField DescriptionName machine name of the sectionTitle block titleDescription short description of the blockRepeat it sets if the block can repeat againTimes it sets the amount times that a block can repeatEnable it sets if the form is able (1) or disable (0)Extern Id external identificatorDate created date when it was createdDate updated date when it was updatedForms It is the list of forms associated to this block

11.4 Manage questions

A question can or cannot answered by users. All questions have to be related with just one block. The questions havea type of question, it determines how is the way how it will be displayed to users. The questions, which are selectedfrom a list, have a extra configuration called options. You are able to see details, add, edit and delete blocks.

11.4. Manage questions 33


11.4.1 Details question

Let’s see what information is stored inside of a question.



Table 3: QuestionsField DescriptionBlock blockName machine name of the questionLabel label for questionDescription short description of the questionType it sets the type of answer that it will hope gets in the question [string, int, double, bool, date,

time, datetime, unique, multiple, geopoint, file]Order It sets the order of the question in each block. between the value is higher will be lowerEnable it sets if the form is able (1) or disable (0)Extern Id external identificatorDate created date when it was createdDate updated date when it was updatedRules It is the list of all rules associated to this question

11.4.2 Options

Options are just useable for questions which have unique or multiple type. All options just can be associated to onequestion. Let’s see what information is stored inside of an option.

Table 4: QuestionsField DescriptionQuestion questionName machine name of the optionLabel label for optionEnable it sets if the form is able (1) or disable (0)Extern Id external identificatorDate created date when it was createdDate updated date when it was updated

11.4. Manage questions 35


11.4.3 Rules

Rules are a set of statement that will be validate by other apps. A rule can be useable for a specific app or many ofthem. Each question is just associated to one question. Let’s see what information is stored inside of a rule.

Table 5: RulesField DescriptionQuestion questionApplication the application which is gonna be validated: all, odk, pdiType of rule it sets which is kind of validation to check [required, constraint, relevant, appearance, cal-

culation, choice_filter]Message this message will show when the rule will be brokenRule rule in terms of application


CHAPTER

TWELVE

SETUP

Setup is the module in which you can manage parameters of the platform.

Within setup module, you can:

• Manage associations

• Manage geographic information

• Manage people

12.1 Associations

Associations are the entities who manage the collect events. Each technical assistant have to be related in each collectactivity (production event) to one association.

Within of this module you can see the list of all associations, also you are able to add, edit or delete them.

Let’s see what information is stored inside of an association.

37


Table 1: AssociationsField DescriptionName name of associationEnable it sets if the form is able (1) or disable (0)Extern Id external identificatorDate created date when it was createdDate updated date when it was updated

12.2 Countries

Countries are geographic information about where people are located. Each person should be associated to one country.

Let’s see what information is stored inside of a country.

Table 2: CountriesField DescriptionName name of countryIso 2 ISO 2 CodeExtern Id external identificatorDate created date when it was createdDate updated date when it was updated

12.3 States

States are geographic information about where people are located. Each person should be associated to one state.

Let’s see what information is stored inside of a state.

Table 3: StatesField DescriptionName name of stateCountry Country in which is locatedExtern Id external identificatorDate created date when it was createdDate updated date when it was updated

12.4 Municipalities

Municipalities are geographic information about where people are located. Each person should be associated to onemunicipality.

Let’s see what information is stored inside of a municipality.

38 Chapter 12. Setup


Table 4: MunicipalitiesField DescriptionName name of stateState State in which is locatedExtern Id external identificatorDate created date when it was createdDate updated date when it was updated

12.5 People

People is the section in which users can see contact information about technicals assistants and farmers. This moduleallows to manage information of people who collect data and answer the surveys.

Let’s see what information is stored inside of a person.

Table 5: PeopleField DescriptionName NameLastName Last NameMunicipality Municipality where person was bornKind Document Kind of document of the person. It can be N = National, P = Passport, O = OtherGender Gender of the person. It can be F = Female, M = Male, O = OtherDocument Number of document of identificationCellphone Number of CellphoneAddress Phisical address where person is livingEmail EmailExtern Id external identificatorDate created date when it was createdDate updated date when it was updated

12.5. People 39


40 Chapter 12. Setup

CHAPTER

THIRTEEN

REPORTS

Actually this module is a redirection to the visualization tool. If you want to include a new report you can edit the fileViewsShared_Sidebar.cshtml

41


42 Chapter 13. Reports

CHAPTER

FOURTEEN

INTRODUCTION

AEPS ETL (Extract, Transform and Load) is the module which allows to extract the collected data in field and saveinto the central database. This module works in three steps:

1. Extracting information from raw data (those files could be Excel file, text file, i.e.).

2. Transforming information in the database format.

3. Loading of the data in the database. This step is splitted by two steps more: insert and update of records.

Source code is available at: https://github.com/CIAT-DAPA/aeps_platform_etl

43

https://github.com/CIAT-DAPA/aeps_platform_etl

CHAPTER

FIFTEEN

CONFIGURATION

The ETL module has many standard configuration files, which are the main parameters to execute all process oftransforming data. In this chapter, we will describe which are those parameters. It was designed for working withdatasources in Excel format.

Tip: Form: refers to the overview. The questions about farmers, technical assistants and farms are in the head of theform.

Survey: refers to the questions that are related with the production events.

15.1 Parameters

The parameters are in the file configuration.xlsx. This file has the global parameters to execute the transformingprocess. It has information about how to connect with database, how are the relation between tables into database andsome additional specific conditions for each table. Let’s see what each sheet has:

• global: This sheet has the global parameters for the ETL. It has the parameters to connect directly with databaseand the type of process to execute.

Table 1: VariablesParameter type Descriptiondatabase_user string Name of user to connect with databasedatabase_pwd string Password of the user to connect with databasedatabase_host string IP or hostname of the server in which is the databasedatabase_port int Port in which is available the database in the serverdatabase_schema string Name of the database. By default is: aeps_2_0type_process string It sets the kind of process to execute. The values can be: full: It execute the

three steps

• dependencies: This sheet has the relationship between tables of the database.

The first row has the tables names, the first column has also the tables names. The values, which instersecttables, are the references about how the ETL should connect data from the source. The values are composed inthe following way: the first value is the column name of the top table (it is the name of the foreign key for thetable that we are relating both tables), the second value is the column name of the table in the row, which theETL has to search the id. The following is an example of this sheet:

45


Table 2: DependenciesColumn Descriptionparent_table It is the name of the table which is the parent tableparent_field It is the field which will be used to find the id of the parent recordchild_table It is the name of current table which is being processedchild_field It is the field which is the foreign key in the current table

Note: We have to do this because in most of the cases we don’t have the id of each table directly, so it is a method torelate tables without lose information.

Warning: You have to take into account what you configured in this section, because you have set the same fieldslike keys in the form section (you will see forward). The foreign keys that you set here, should be setted in theform section. It is because the ETL will use those fields to connect them across the tables, if they wouldn’t match,it won’t import nothing to the database.

• additional: This sheet set for which tables the ETL has to add default information.

Table 3: AdditionalColumn Descriptiontable This column has the list of the database tablesregister_date This column set if for the records imported for the table, it should add information about

created and updated fields. It will take the current date from the system. The values can be0 (not add) or 1 (add)

has_enable This column set by default enable to tables with have this flag. This flag just should beenable for the tables which has the field enable. The values can be 0 (not add) or 1 (add)

15.2 ETL Configuration

The ETL configuration is in the file form.xlsx. This file has the configuration about how to extract information fromthe source, how transform the raw data into database tables. Let’s see what each sheet has:

• form: This sheet has the relationship between datasource and database. It sets how each field from source isrelated with the tables columns. Like you will see, this configuration is just for main tables, it doesn’t take intoaccount a configuration for the surveys.

46 Chapter 15. Configuration


Table 4: FormColumn Descriptionform_sheet This column has the sheet’s name in which is data in the sourceform_field This column has the name of column in the sourceform_key It sets if the column is key in the sourcesoc_associations This column is to set a relationship from source and table soc_associations. The values in

this column are the columns names of the tablecon_countries_1 This data is for identifying technical assistants. This column is to set a relationship from

source and table con_countries. The values in this column are the columns names of thetable

con_countries_2 This data is for identifying farmers. This column is to set a relationship from source andtable con_countries. The values in this column are the columns names of the table

con_states_1 This data is for identifying technical assistants. This column is to set a relationship fromsource and table con_states. The values in this column are the columns names of the table

con_states_2 This data is for identifying farmers. This column is to set a relationship from source andtable con_states. The values in this column are the columns names of the table

con_municipalities_1 This data is for identifying technical assistants. This column is to set a relationship fromsource and table con_municipalities. The values in this column are the columns names ofthe table

con_municipalities_2 This data is for identifying farmers. This column is to set a relationship from source andtable con_municipalities. The values in this column are the columns names of the table

soc_people_1 This data is for identifying technical assistants. This column is to set a relationship fromsource and table soc_people. The values in this column are the columns names of the table

soc_people_2 This data is for identifying farmers. This column is to set a relationship from source andtable soc_people. The values in this column are the columns names of the table

soc_technical_assistantsThis column is to set a relationship from source and table soc_technical_assistants. Thevalues in this column are the columns names of the table

far_farms This column is to set a relationship from source and table far_farms. The values in thiscolumn are the columns names of the table

far_plots This column is to set a relationship from source and table far_plots. The values in thiscolumn are the columns names of the table

far_production_events This column is to set a relationship from source and table far_production_events. Thevalues in this column are the columns names of the table

• survey: This sheet has the configuration of the survey. It relates the blocks and questions, which are required inthe survey with the questions in the database, they are related through identifiers of each side.

Table 5: SurveyColumn Type Descriptionblock string This field refers to blocks of questions. It has the machine name of the block.

The value should be the same in the field name of the table frm_blocksrepeat int This field is required to know of the block repeat inside of the survey. If value

is 0, it means the the block of questions won’t repeat again, otherwise this fieldshould have the value 1.

id int It is the id of the question inside of the database (frm_questions).question string This field refers to blocks of questions. It has the machine name of the ques-

tion. The value should be the same in the field name of the table frm_questionstype string It is the value type from source. The value can be: unique, string, int, double,

date, multiple, key

• transformations: This sheet has the rules to transform raw data in new data. It allows to change the final valueof the surveys questions, with a set of functions available. Those transformation will be applied in the translateprocess.

15.2. ETL Configuration 47


Table 6: TransformationsColumn Type Descriptiontable string it is the table’s name. When table is parted of the form the value is the name of

the table, however when table is parted of survey it should has the value surveyfield string it is the column’s name in which you will apply the transformation. For the

form fields, it will take the column’s name into the database, however whentable is parted of survey it should has the machine name (value of the fieldname inside of the table frm_questions).

type string it is the name of the function which will be applied to the field. See the followingtable to know which are available.

value it will take a different behavior depending of the typetransform it will take a different behavior depending of the typeconditions it will take a different behavior depending of the typeunits it will take a different behavior depending of the type

Table 7: List of type of the transformationsValue Descriptionreplace it will replace a the value in the column value for the column transformsplit it will split the value of the column in two. The pattern to split will be taken of the value

and the second value will be set in the column of transform.add it will add a new column. It will creates a new column (the column name will be taken from

field) and set value transform.unit it will set the units for the columns. It will take the value from the column transform and

set to the unit to the column in fieldmultiply it will multiply the column by a number. It will take the value in field and multiply time

transform, then it will set the value final_value. It will take some consideration accordingto field condition, if the value is unit, it will apply the multiply depending of the value inthe column units.

• validations: This sheet has the rules which the ETL will check before to approve some record. This verificationwill be checked in the translate process.

Table 8: ValidationsColumn Type Descriptiontable string it is the table’s name. When table is parted of the form the value is the name of

the table, however when table is parted of survey it should has the value surveyfield string it is the column’s name in which you will be checked. For the form fields, it

will take the column’s name into the database, however when table is parted ofsurvey it should has the machine name (value of the field name inside of thetable frm_questions).

type string it is the name of the type of validation which will be checked in the field. Seethe following table to know which are available.

conditioncondition_fieldcondition_valueexpression string It is a regular expression which will be validated for the field.message string It is the message that will be showed to user when the record fail the validation

process.



Table 9: List of type of validationsValue Descriptionrequired It will check that the value is not null or empty.reg_exp It will check that the value accomplish the format. The regular expression will be taken

from expression

15.2. ETL Configuration 49

CHAPTER

SIXTEEN

PANDAS (ODK)

It is a implementation of an ETL in Pandas (Python enviroment). Currently, it is just working to process files fromODK in Excel format. It has a set of scripts which are available at: https://github.com/CIAT-DAPA/aeps_platform_etl/tree/dev/src/python

Tip: In the followings diagrams you will see some box in red and others in green color. The boxes with green colorhave some considerations that you should check into of source code.

16.1 conf.py

This script has a set of global parameters which you should pay attention. Let’s to see some important variables whichyou should set before to execute the process:

Table 1: Vars of conf.pyVar Descriptionenv It sets which is the enviroment in which all process will be executed. Depending of this

value it will search the files configuration.xlsx and form.xlsx e.g.: if you set the parameterempty, it will search the files configuration.xlsx and form.xlsx, however if you set .dev,it will search the files configuration.dev.xlsx and form.dev.xlsx

path_root It sets which is root folder for the ETLtables_master It sets a list of tables which are parted of the form. Remember the difference between

form and survey: Form: refers to the overview. The questions about farmers, technicalassistants and farms are in the head of the form, while the Survey: refers to the questionsthat are related with the production events. Also you will see by default that tables likecon_countries, con_states, con_municipalities and soc_people have a _1 or _2, it is just todiscriminate information about farmers and technical assistants, so _1 is to process dataabout technical assistans and _2 is to process data about farmers.

16.2 Translation

It is the process which takes the raw data and transforms them in files in the database structure. Overall, it loads theparameters, then get raw data and set the configurations values, then it stablishes connection with the database. Onceit is ready, it starts to process the form (you can see more information forward), in order to do that, it checks the tablesrelated about the general information, once it has processed the form, starts to process the survey. Those steps arerepeated for all datasources. The following picture show the general process followed by this ETL:

51

https://github.com/CIAT-DAPA/aeps_platform_etl/tree/dev/src/python

https://github.com/CIAT-DAPA/aeps_platform_etl/tree/dev/src/python


• Processing form: This process will take the information from the raw data, it will takes dataabout countries, states, municipalities, people, technical assistants, farms, plots and production events.Then that it transforms and validates data, it will create the files for each table of the database(con_countries, con_states, con_municipalities, soc_associations, soc_people, soc_technical_assistants,far_farms, far_plots, far_production_events). If you want to change the tables which will be processedyou should change the variable tables_master of conf.py).

52 Chapter 16. Pandas (ODK)


16.2. Translation 53


• Processing survey: This process will take the information from the raw data, it will takes data about surveyrelated to the production events. Then that it transforms and validates data, it will create the files for each tableof the database.



16.2. Translation 55


16.3 New

It is the process which takes data from csv files stored in the folder new and save data into database. The files, whichare in the folder new should be csv files, have to have the same structure that database tables.

• Adding records: This process saves all records from CSV files into the database. It checks if a file for the tableexists, then it loads data from the database (in order to know what data it has currently). The next step is loaddata from CSV file. Once it has all raw data, it will start to check dependencies of the current table with theothers tables, it is looking how to set the foreigns keys of the parents tables throught external ids. Once it hasready all dependencies, it will save in a log the issues which were found. Then it will check some parametersadditional about a specific fields. Finally it will clear all empty data and save data into database.



16.3. New 57

CHAPTER

SEVENTEEN

INTRODUCTION

It is a tool which allows see the information of the AEPS Platform. Actually many platforms about visualizationsexist, each of them have advantages and disadvantages. The scope of AEPS Platform is allows users create amazingdashboards, which support decision-making based on real data.

In this chapter, we describe how users can work in some platforms in order to analyze data in a visual way. You canfind the source code of dashboards in the following url: https://github.com/CIAT-DAPA/aeps_platform_visualizations.

59

https://github.com/CIAT-DAPA/aeps_platform_visualizations

CHAPTER

EIGHTEEN

SUPERSET

Superset is a web system which allows people create dashboards to visualize information. The official documentationis in the Official web page.

In the current repository you can find the following files:

• Connection with database

• Tables needed to work

• Dashboards created

In this sections you can find:

• How import dashboard

18.1 Import dashboard

The process to import dashboard in Superset is really easy. You just need go to Manage / Import Dashboards. Onceyou are in this webpage, you just need to select the files from your PC.

61

https://superset.apache.org/index.html

https://github.com/CIAT-DAPA/aeps_platform_visualizations/tree/master/superset


62 Chapter 18. Superset

aeps platform documentation

Documents