DWH Queries - lucaboesch/moodle-report_datawarehouse GitHub Wiki

Queries to be run in the Data Warehouse part of the system using the Data Warehouse report

Prepare the Data Warehouse areas to receive data from the Moodle Datawarehouse report

If set up according to the documentation so far, a data warehouse with five schemas, "MONITORING", "STAGING", "CLEANSING", "CORE" and "MART" does exist.

In those schemas, in order to receive the data from the runs of the Moodle queries for the quiz and assignment activity, the according database tables have to be set up beforehand. This section of the documentation deals with those preliminary proceedings.

As user ADMIN, do run the queries create_all_tables.sql.

Prepare the Data Warehouse procedures for transferring data from one area to the next one.

There should be no reason those steps would not work using the Oracle Cloud frontend. Nonetheless, the Oracle SQLDeveloper standalone application might be more suited to perform the following steps.

To connect the application to the Oracle Cloud instance, a connection is necessary, which is done through a wallet. In order to download that wallet, click the "Database connection" button on the Oracle Cloud Autonomous Data Warehouse.

Download the wallet whilst giving it a password.

Then, on SQLDeveloper, create a new database connection and give it a name, provide the ADMIN credential, choose "Connection Type": "Cloud Wallet" and provide the downloaded wallet's ZIP file.

Make a connection for each one of the users "MONITORING", "STAGING", "CLEANSING", "CORE" and "MART" as well in order to be able to user SQLDeveloper also on behalf of those users.

As user ADMIN, then, run create_all_procedures.sql

Process data received to the Object Storage Bucket to be imported into the Data Warehouse

In SQLDeveloper, as user MONITORING, run the content of the PLSQL script load_quiz_file.plsql resulting in the LOAD_QUIZ_FILE procedure providing as parameters the first part and the second part of the Object Storage Bucket file to create the load_quiz_file procedure which steers and overviews the complete data load.