DWH Queries
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.
To enter the frontend to access the worksheet to insert the query to run to create the tables, first access the database as user ADMIN and then change to the STAGING user and STAGING schema with the "Open in new tab" option in a user tile.
As user STAGING, open the SQL Database Actions frontend.
Run the content of the SQL script create_staging_area_tables.sql.
As user CLEANSING, open the SQL Database Actions frontend and run the content of the SQL script create_cleansing_area_tables.sql.
As user CORE, open the SQL Database Actions frontend and run the content of the SQL script create_core_area_tables.sql.
As user MART, open the SQL Database Actions frontend and run the content of the SQL script create_mart_area_tables.sql.
As user MONITORING, open the SQL Database Actions frontend and run the content of the SQL script create_monitoring_area_tables.sql.
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.
First, as user ADMIN, grant the user STAGING some rights by running the following SQL commands: grant_needed_rights_to_staging.sql.
As user STAGING, open the SQL Database Actions frontend and run the content of the PLSQL script load_quiz_file_to_stag.plsql making sure a value for the l_CREDENTIAL_NAME has been filled in in line 9 beforehand. Then, run the content of the SQL script grant_to_load_quiz_file_to_stag.sql in order that it can later be run from a central procedure.
As user CLEANSING, open the SQL Database Actions frontend and run the content of the PLSQL script transfer_quiz_stag_to_clean.plsql. Then, run the content of the SQL script grant_to_transfer_quiz_stag_to_clean.sql in order that it can later be run from a central procedure.
As user CORE, open the SQL Database Actions frontend and run the content of the PLSQL script transfer_quiz_clean_to_core.plsql. Then, run the content of the SQL script grant_to_transfer_quiz_clean_to_core.sql in order that it can later be run from a central procedure.
As user MART, open the SQL Database Actions frontend and run the content of the PLSQL script transfer_quiz_core_to_mart.plsql. Then, run the content of the SQL script grant_to_transfer_quiz_stag_to_clean.sql in order that it can later be run from a central procedure.
Finally, as user MONITORING, run the content of the PLSQL script function_lookup_loadid.plsql to create the lookup_loadid function and run the content of the PLSQL script load_quiz_file.plsql to create the load_quiz_file procedure which steers and overviews the complete data load..