...

The DataWareHouse is a PostgreSQL database where the Moodle data is saved after it has been transformed by NiFi pipelines. It contains very few tables, compared to Moodle original database, but it can easily generate views with critical data, which is used in the different dashboards.

PostgreSQL Installation

1. Download PostgreSQL Docker image.

   Code Block
   docker pull postgres

2. Create the container. Replace <password> with the actual password.

   Code Block
   docker run --name datawarehouse -p 127.0.0.1:5432:5433/tcp -e POSTGRES_PASSWORD=<password> -d postgres

• docker run --name datawarehouse -p 127.0.0.1:5432:5433/tcp -e POSTGRES_PASSWORD=<password> -d postgres

• docker exec -ti <container ID> bash

• psql -h localhost -p 5432 -U postgres -d postgres

• ALTER USER postgres WITH PASSWORD '<password>';

• CREATE DATABASE zwe_analytics;

DataWareHouse Creation

1. Enter the PostgreSQL Docker. First, get the container ID.

   Code Block
   docker ps docker exec -ti <container ID> bash

2. Use psql to access the postgres database.

   Code Block
   psql -h localhost -p 5432 -U postgres -d postgres

3. Change the postgres user password and create the new database.

   Code Block
   ALTER USER postgres WITH PASSWORD '<password>'; CREATE DATABASE zwe_analytics;

Finally, it is needed to download the database file called tables.sql from the following repository: https://github.com/KnowTechTure/ZWE_NiFi. This file contains the definition for 17 tables:

• mdl_certificates

• mdl_cohorts

• mdl_cohorts_x_users

• mdl_course_categories

• mdl_course_completion

• mdl_course_modules_completion

• mdl_courses

• mdl_enrolments

• mdl_feedback_answers

• mdl_feedback_metadata

• mdl_feedback_questions

• mdl_grade_grade

• mdl_grade_items

• mdl_scorm_scoes_track

• mdl_user_roles

• mdl_users

• mdl_course_modules

After the execution of the file make sure that every table is created accordingly.

After creating the tables, download the database file called views.sql from the following repository: https://github.com/KnowTechTure/ZWE_NiFi. The file contains the definition for 4 views:

• zwe_course_grades

• zwe_elearning_certificates_view_dev

• zwe_elearning_enrolmentscourseunifiedview_dev

• zwe_elearning_feebacksview_dev

Finally, execute both definitions into a desired database to create the tables and views.