Corresponding Code To Illustrate ETL on Oracle DW.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3f99f24a",
   "metadata": {},
   "source": [
    "# Corresponding Code To Illustrate ETL on Oracle DW"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20b3bba0",
   "metadata": {},
   "source": [
    "#### Important Note: Kindly run the cells one by one. Some cells give output, some don't."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9633c0b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "### SET UP THE PYTHON QUERYING ENVIRONMENT ###\n",
    "\n",
    "## Run this cell only once. Expect no output ##\n",
    "\n",
    "!pip install cx_Oracle\n",
    "import cx_Oracle as cx\n",
    "import pandas as pd\n",
    "import warnings as wn\n",
    "wn.filterwarnings('ignore')\n",
    "conn = cx.connect(user=\"SYS as SYSDBA\", password=\"myPASS1234\", dsn=\"orcl\")\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# By running this cell, connection is made from this Python environment to an Oracle database instance already on the machine or network."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92b2ba6c",
   "metadata": {},
   "source": [
    "### Transform Phase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "068d853f",
   "metadata": {},
   "outputs": [],
   "source": [
    "### EXAMINE THE dw_water_quality STAGING TABLE THAT HAS BEEN PREPARED ###\n",
    "\n",
    "## You can run this cell more than once, but don't re-run this cell (and subsequent ones) after the LOAD PHASE. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# 2348 records. 7 fields."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "054ccb23",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE samplesamplingPointlabel COLUMN FOR DISTINCT VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT DISTINCT samplesamplingPointlabel\n",
    "    FROM dw_water_quality;\n",
    "    \n",
    "    \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# 81 distinct locations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f329c26",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE samplesamplingPointlabel COLUMN FOR NULL VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect an empty dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE samplesamplingPointlabel IS NULL;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# No null values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c48a2f24",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE samplesamplingPointlabel COLUMN FOR INCONSISTENT VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect an empty dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE samplesamplingPointlabel NOT IN \n",
    "        (\n",
    "        SELECT DISTINCT samplesamplingPointlabel\n",
    "            FROM dw_water_quality\n",
    "        );\n",
    "        \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# No inconsistent records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "839a5a86",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE samplesamplingPointlabel COLUMN FOR OUTLIERS ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT samplesamplingPointlabel, COUNT(samplesamplingPointlabel) cnt\n",
    "    FROM dw_water_quality\n",
    "GROUP BY samplesamplingPointlabel \n",
    "ORDER BY cnt;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# Up to 20 distinct locations appear less than three times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6342c71",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE samplesampleDateTime COLUMN FOR DISTINCT VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT DISTINCT samplesampleDateTime\n",
    "    FROM dw_water_quality;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# 1077 distinct timestamps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ad01716",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE samplesampleDateTime COLUMN FOR NULL VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect an empty dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE samplesampleDateTime IS NULL;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# No null records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13ae8bc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE determinandlabel AND determinanddefinition COLUMNS FOR DISTINCT VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT DISTINCT determinandlabel, determinanddefinition\n",
    "    FROM dw_water_quality;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# 173 distinct sensor types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c816b038",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE determinandlabel AND determinanddefinition COLUMNS FOR NULL VALUES\n",
    "\n",
    "## You can run this cell more than once. Expect an empty dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE determinandlabel IS NULL\n",
    "    OR determinanddefinition IS NULL;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# No null records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "834e3b6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE determinandlabel COLUMN FOR INCONSISTENT VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect an empty dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE determinandlabel NOT IN \n",
    "        (\n",
    "        SELECT DISTINCT determinandlabel\n",
    "            FROM dw_water_quality\n",
    "        );\n",
    "        \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# No inconsistent records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "224f119e",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE determinandlabel COLUMN FOR OUTLIERS ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT determinandlabel, COUNT(determinandlabel) cnt\n",
    "    FROM dw_water_quality\n",
    "GROUP BY determinandlabel \n",
    "ORDER BY cnt;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# Up to 95 sensors out of 173 appear less than three times. Click to the left of the dataframe to scroll."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "594b8a27",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE determinandunitlabel COLUMN FOR DISTINCT VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT DISTINCT determinandunitlabel\n",
    "    FROM dw_water_quality;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# 12 unique units."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e4b8a87",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE determinandunitlabel COLUMN FOR NULL VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect an empty dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE determinandunitlabel IS NULL;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# No null records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07d3432a",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE determinandunitlabel COLUMN FOR INCONSISTENT VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect an empty dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE determinandunitlabel NOT IN \n",
    "        (\n",
    "        SELECT DISTINCT determinandunitlabel\n",
    "            FROM dw_water_quality\n",
    "        );\n",
    "        \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# No inconsistent records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "547d1c63",
   "metadata": {},
   "outputs": [],
   "source": [
    "### EXAMINE THE determinandunitlabel COLUMN IN THE STAGING TABLE ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT determinandunitlabel, COUNT(determinandunitlabel) cnt\n",
    "    FROM dw_water_quality\n",
    "GROUP BY determinandunitlabel\n",
    "ORDER BY cnt;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# 5 out of 12 units appear in less than 50 records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c326fd90",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE determinandunitlabel COLUMN FOR TOTAL OUTLIERS ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE determinandunitlabel = 'unitless'\n",
    "        UNION\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE determinandunitlabel = 'text'\n",
    "        UNION\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE determinandunitlabel = 'coded';\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# These 49 records are to be deleted because they seem not to be important considering \n",
    "# their corresponding values in the determinanddefinition column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43ab1307",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE determinanddefinition COLUMN INFO OF determinandunitlabel COLUMN ENTRIES THAT LOOKS ODD ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT DISTINCT s.determinanddefinition FROM\n",
    "    (\n",
    "    SELECT * FROM dw_water_quality\n",
    "        WHERE determinandunitlabel = 'unitless'\n",
    "            UNION\n",
    "    SELECT * FROM dw_water_quality\n",
    "        WHERE determinandunitlabel = 'text'\t\n",
    "            UNION\n",
    "    SELECT * FROM dw_water_quality\n",
    "        WHERE determinandunitlabel = 'coded'\n",
    "    ) s;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# The result summary below shows the reason why these 49 records seem not to be important."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0bce524",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE result COLUMN FOR DISTINCT VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT DISTINCT result\n",
    "    FROM dw_water_quality\n",
    "ORDER BY result;\n",
    "        \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# 1190 unique results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b26a763",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE result COLUMN FOR NULL VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect an empty dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE result IS NULL;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# No null records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37cf9946",
   "metadata": {},
   "outputs": [],
   "source": [
    "### CHECK THE result COLUMN FOR INCONSISTENT VALUES ###\n",
    "\n",
    "## You can run this cell more than once. Expect an empty dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality\n",
    "    WHERE result NOT IN \n",
    "        (\n",
    "        SELECT DISTINCT result\n",
    "            FROM dw_water_quality\n",
    "        );\n",
    "        \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# No inconsistent records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac7d1682",
   "metadata": {},
   "outputs": [],
   "source": [
    "### DATA CLEANING: TO REMOVE UNNECESSARY RECORDS ###\n",
    "\n",
    "## Run this cell only once. Expect no output ##\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "\n",
    "DELETE FROM dw_water_quality \n",
    "    WHERE id IN\n",
    "        (\n",
    "        SELECT s.id FROM\n",
    "            (\n",
    "            SELECT * FROM dw_water_quality\n",
    "                WHERE determinandunitlabel = 'unitless'\n",
    "                    UNION\n",
    "            SELECT * FROM dw_water_quality\n",
    "                WHERE determinandunitlabel = 'text'\t\n",
    "                    UNION\n",
    "            SELECT * FROM dw_water_quality\n",
    "                WHERE determinandunitlabel = 'coded'\n",
    "            ) s\n",
    "        );\n",
    "        \n",
    "            \"\"\")\n",
    "\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8f61273",
   "metadata": {},
   "outputs": [],
   "source": [
    "### EXAMINE THE RESIDUAL STAGING TABLE SO FAR ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dw_water_quality;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df\n",
    "\n",
    "# 49 out of 2348 records were deleted. 2299 records remaining."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce4ad9d6",
   "metadata": {},
   "source": [
    "### Load Phase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f721df13",
   "metadata": {},
   "outputs": [],
   "source": [
    "### TO CREATE DIMENSION TABLES AND FACT TABLE AND LOAD THEM ###\n",
    "\n",
    "## Run this cell only once. Expect no output ##\n",
    "\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "\n",
    "--- To rename the columns ---\n",
    "\n",
    "ALTER TABLE dw_water_quality \n",
    "    RENAME COLUMN samplesamplingPointlabel TO measurementLocation;\n",
    "    \n",
    "ALTER TABLE dw_water_quality \n",
    "    RENAME COLUMN samplesampleDateTime TO measurementDateTime;\n",
    "\n",
    "ALTER TABLE dw_water_quality \n",
    "    RENAME COLUMN determinandlabel TO sensorType;\n",
    "\n",
    "ALTER TABLE dw_water_quality \n",
    "    RENAME COLUMN determinanddefinition TO sensorTypeDefinition;\n",
    "\n",
    "ALTER TABLE dw_water_quality \n",
    "    RENAME COLUMN result TO measurement;\n",
    "\n",
    "ALTER TABLE dw_water_quality \n",
    "    RENAME COLUMN determinandunitlabel TO measurementUnit;\n",
    "\n",
    "            \"\"\")\n",
    "\n",
    "conn.commit()\n",
    "\n",
    "\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "\n",
    "--- To add measurementYear (INTEGER), measurementWeek (INTEGER), and measurementMonth (VARCHAR2) columns ---\n",
    "\n",
    "ALTER TABLE dw_water_quality ADD (\n",
    "    measurementYear INTEGER,\n",
    "    measurementWeek INTEGER,\n",
    "    measurementMonth VARCHAR2(255)\n",
    "    );\n",
    "\n",
    "            \"\"\")\n",
    "\n",
    "conn.commit()\n",
    "\n",
    "\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "\n",
    "--- To populate the measurementYear column with year data from measurementDateTime column ---\n",
    "\n",
    "UPDATE dw_water_quality SET measurementYear = EXTRACT(YEAR FROM TO_DATE(SUBSTR(measurementdatetime, 1, 10), 'YYYY-MM-DD')) WHERE ID = ID;\n",
    "                \n",
    "--- or simply, UPDATE dw_water_quality SET measurementYear = SUBSTR(measurementdatetime, 6, 2) WHERE ID = ID;\n",
    "\n",
    "\n",
    "--- To populate the measurementWeek column with week data from measurementDateTime column ---\n",
    "\n",
    "UPDATE dw_water_quality SET measurementWeek = TO_CHAR(TO_DATE(SUBSTR(measurementdatetime, 1, 10), 'YYYY-MM-DD'), 'IW') WHERE ID = ID;\n",
    "\n",
    "\n",
    "--- To populate the measurementMonth column with month data from measurementDateTime column ---\n",
    "\n",
    "UPDATE dw_water_quality SET measurementMonth = TO_CHAR(TO_DATE(SUBSTR(measurementdatetime, 1, 10), 'YYYY-MM-DD'), 'MONTH') WHERE ID = ID;\n",
    "\n",
    "            \"\"\")\n",
    "\n",
    "conn.commit()\n",
    "\n",
    "\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "\n",
    "--- To create 7 dimension tables and a fact table ---\n",
    "\n",
    "DROP TABLE factMeasurementsTable;\n",
    "DROP TABLE dimLocationTable;\n",
    "DROP TABLE dimSensorTable;\n",
    "DROP TABLE dimUnitTable;\n",
    "DROP TABLE dimTimeTable;\n",
    "DROP TABLE dimYearTable;\n",
    "DROP TABLE dimWeekTable;\n",
    "DROP TABLE dimMonthTable;\n",
    "\n",
    "CREATE TABLE dimLocationTable (\n",
    "    locationID INTEGER GENERATED ALWAYS AS IDENTITY,\n",
    "    measurementLocation VARCHAR2(255) NOT NULL,\n",
    "    CONSTRAINT pk_locationID PRIMARY KEY (locationID)\n",
    "    );\n",
    "\n",
    "CREATE TABLE dimSensorTable (\n",
    "    sensorID INTEGER GENERATED ALWAYS AS IDENTITY,\n",
    "    sensorType VARCHAR2(255) NOT NULL,\n",
    "    sensorTypeDefinition VARCHAR2(255) NOT NULL,\n",
    "    CONSTRAINT pk_sensorID PRIMARY KEY (sensorID)\n",
    "    );\n",
    "\n",
    "CREATE TABLE dimUnitTable (\n",
    "    unitID INTEGER GENERATED ALWAYS AS IDENTITY,\n",
    "    measurementUnit VARCHAR2(255) NOT NULL,\n",
    "    CONSTRAINT pk_unitID PRIMARY KEY (unitID)\n",
    "    );\n",
    "\n",
    "CREATE TABLE dimYearTable (\n",
    "    yearID INTEGER GENERATED ALWAYS AS IDENTITY,\n",
    "    measurementYear INTEGER NOT NULL,\n",
    "    CONSTRAINT pk_yearID PRIMARY KEY (yearID)\n",
    "    );\n",
    "\n",
    "CREATE TABLE dimWeekTable (\n",
    "    weekID INTEGER GENERATED ALWAYS AS IDENTITY,\n",
    "    measurementWeek INTEGER NOT NULL,\n",
    "    CONSTRAINT pk_weekID PRIMARY KEY (weekID)\n",
    "    );\n",
    "\n",
    "CREATE TABLE dimMonthTable (\n",
    "    monthID INTEGER GENERATED ALWAYS AS IDENTITY,\n",
    "    measurementMonth VARCHAR2(15) NOT NULL,\n",
    "    CONSTRAINT pk_monthID PRIMARY KEY (monthID)\n",
    "    );\n",
    "\n",
    "CREATE TABLE dimTimeTable (\n",
    "    dateTimeID INTEGER GENERATED ALWAYS AS IDENTITY,\n",
    "    measurementDateTime VARCHAR2(255) NOT NULL,\n",
    "    yearID INTEGER REFERENCES dimYearTable(yearID),\n",
    "    weekID INTEGER REFERENCES dimWeekTable(weekID),\n",
    "    monthID INTEGER REFERENCES dimMonthTable(monthID),\n",
    "    CONSTRAINT pk_dateTimeID PRIMARY KEY (dateTimeID)\n",
    "    );\n",
    "\n",
    "CREATE TABLE factMeasurementsTable (\n",
    "    factID INTEGER GENERATED ALWAYS AS IDENTITY,\n",
    "    locationID INTEGER REFERENCES dimLocationTable(locationID),\n",
    "    dateTimeID INTEGER REFERENCES dimTimeTable(dateTimeID),\n",
    "    sensorID INTEGER REFERENCES dimSensorTable(sensorID),\n",
    "    unitID INTEGER REFERENCES dimUnitTable(unitID),\n",
    "    measurement FLOAT NOT NULL,\n",
    "    CONSTRAINT pk_factID PRIMARY KEY (factID)\n",
    "    );\n",
    "  \n",
    "            \"\"\")\n",
    "\n",
    "conn.commit()\n",
    "\n",
    "\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "\n",
    "--- To add reference ID columns with INTEGER datatype to the staging table from the 7 dimension tables ---\n",
    "\n",
    "ALTER TABLE dw_water_quality ADD (\n",
    "    locationID INTEGER,\n",
    "    dateTimeID INTEGER,\n",
    "    sensorID INTEGER,\n",
    "    unitID INTEGER,\n",
    "    yearID INTEGER,\n",
    "    monthID INTEGER,\n",
    "    weekID INTEGER\n",
    "    );\n",
    "\n",
    "            \"\"\")\n",
    "\n",
    "conn.commit()\n",
    "\n",
    "\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "\n",
    "--- To load each of the 7 dimension tables with data as well as update the staging table with their IDs ---\n",
    "\n",
    "INSERT INTO dimLocationTable(measurementLocation)\n",
    "    SELECT DISTINCT measurementLocation \n",
    "        FROM dw_water_quality \n",
    "    ORDER BY measurementLocation;\n",
    "\n",
    "MERGE INTO dw_water_quality\n",
    "USING dimLocationTable\n",
    "ON (dw_water_quality.measurementLocation = dimLocationTable.measurementLocation)\n",
    "WHEN MATCHED THEN UPDATE\n",
    "SET dw_water_quality.locationID = dimLocationTable.locationID;\n",
    "\n",
    "INSERT INTO dimUnitTable(measurementUnit)\n",
    "    SELECT DISTINCT measurementUnit \n",
    "        FROM dw_water_quality \n",
    "    ORDER BY measurementUnit;\n",
    "\n",
    "MERGE INTO dw_water_quality\n",
    "USING dimUnitTable\n",
    "ON (dw_water_quality.measurementUnit = dimUnitTable.measurementUnit)\n",
    "WHEN MATCHED THEN UPDATE\n",
    "SET dw_water_quality.unitID = dimUnitTable.unitID;\n",
    "\n",
    "INSERT INTO dimSensorTable(sensorType, sensorTypeDefinition)\n",
    "    SELECT DISTINCT sensorType, sensorTypeDefinition \n",
    "        FROM dw_water_quality \n",
    "    ORDER BY sensorType;\n",
    "\n",
    "MERGE INTO dw_water_quality\n",
    "USING dimSensorTable\n",
    "ON (dw_water_quality.sensorType = dimSensorTable.sensorType)\n",
    "WHEN MATCHED THEN UPDATE\n",
    "SET dw_water_quality.sensorID = dimSensorTable.sensorID;\n",
    "\n",
    "INSERT INTO dimYearTable(measurementYear)\n",
    "    SELECT DISTINCT measurementYear \n",
    "        FROM dw_water_quality \n",
    "    ORDER BY measurementYear;\n",
    "\n",
    "MERGE INTO dw_water_quality\n",
    "USING dimYearTable\n",
    "ON (dw_water_quality.measurementYear = dimYearTable.measurementYear)\n",
    "WHEN MATCHED THEN UPDATE\n",
    "SET dw_water_quality.yearID = dimYearTable.yearID;\n",
    "\n",
    "INSERT INTO dimWeekTable(measurementWeek)\n",
    "    SELECT DISTINCT measurementWeek \n",
    "        FROM dw_water_quality \n",
    "    ORDER BY measurementWeek;\n",
    "\n",
    "MERGE INTO dw_water_quality\n",
    "USING dimWeekTable\n",
    "ON (dw_water_quality.measurementWeek = dimWeekTable.measurementWeek)\n",
    "WHEN MATCHED THEN UPDATE\n",
    "SET dw_water_quality.weekID = dimWeekTable.weekID;\n",
    "\n",
    "INSERT INTO dimMonthTable(measurementMonth)\n",
    "    SELECT s.measurementMonth FROM \n",
    "        (\n",
    "        SELECT DISTINCT measurementMonth, \n",
    "    TO_CHAR(TO_DATE(SUBSTR(measurementdatetime, 1, 10), 'YYYY-MM-DD'), 'MONTH'),  \n",
    "    EXTRACT(MONTH FROM TO_DATE(SUBSTR(measurementdatetime, 1, 10), 'YYYY-MM-DD')) c\n",
    "FROM dw_water_quality) s \n",
    "    ORDER BY c ASC;\n",
    "    \n",
    "MERGE INTO dw_water_quality\n",
    "USING dimMonthTable\n",
    "ON (dw_water_quality.measurementMonth = dimMonthTable.measurementMonth)\n",
    "WHEN MATCHED THEN UPDATE\n",
    "SET dw_water_quality.monthID = dimMonthTable.monthID;\n",
    "\n",
    "INSERT INTO dimTimeTable(measurementDateTime, yearID, weekID, monthID)\n",
    "    SELECT DISTINCT measurementDateTime, yearID, weekID, monthID \n",
    "        FROM dw_water_quality \n",
    "    ORDER BY yearID ASC;\n",
    "\n",
    "MERGE INTO dw_water_quality\n",
    "USING dimTimeTable\n",
    "ON (dw_water_quality.measurementDateTime = dimTimeTable.measurementDateTime\n",
    "    AND dw_water_quality.yearID = dimTimeTable.yearID\n",
    "    AND dw_water_quality.monthID = dimTimeTable.monthID\n",
    "    AND dw_water_quality.weekID = dimTimeTable.weekID)\n",
    "WHEN MATCHED THEN UPDATE\n",
    "SET dw_water_quality.dateTimeID = dimTimeTable.dateTimeID;\n",
    "\n",
    "INSERT INTO factMeasurementsTable (\n",
    "    locationID, dateTimeID, sensorID, unitID, measurement\n",
    "    ) \n",
    "    SELECT \n",
    "        l.locationID, t.dateTimeID, s.sensorID, u.unitID, wq.measurement\n",
    "    FROM dw_water_quality wq\n",
    "        INNER JOIN dimLocationTable l \n",
    "    ON wq.measurementLocation = l.measurementLocation\n",
    "        INNER JOIN dimSensorTable s \n",
    "    ON wq.sensorType = s.sensorType\n",
    "        INNER JOIN dimUnitTable u \n",
    "    ON wq.measurementUnit = u.measurementUnit\n",
    "        INNER JOIN dimTimeTable t \n",
    "    ON wq.measurementDateTime = t.measurementDateTime\n",
    "        INNER JOIN dimYearTable y \n",
    "    ON wq.measurementYear = y.measurementYear\n",
    "        INNER JOIN dimWeekTable w \n",
    "    ON wq.measurementWeek = w.measurementWeek\n",
    "        INNER JOIN dimMonthTable m \n",
    "    ON wq.measurementMonth = m.measurementMonth;\n",
    "\n",
    "            \"\"\")\n",
    "\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf5d334e",
   "metadata": {},
   "source": [
    "##### The journey so far: 7 dimension tables (including the Time table), and the fact table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e32678a",
   "metadata": {},
   "outputs": [],
   "source": [
    "### THE factMeasurementsTable ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM factMeasurementsTable;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7fbad15d",
   "metadata": {},
   "outputs": [],
   "source": [
    "### THE dimLocationTable ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dimLocationTable;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b930678",
   "metadata": {},
   "outputs": [],
   "source": [
    "### THE dimSensorTable ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dimSensorTable;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22f2ea9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "### THE dimUnitTable ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dimUnitTable;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21309308",
   "metadata": {},
   "outputs": [],
   "source": [
    "### THE dimTimeTable ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dimTimeTable;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10a404c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "### THE dimYearTable ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dimYearTable;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "249ed81a",
   "metadata": {},
   "outputs": [],
   "source": [
    "### THE dimWeekTable ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dimWeekTable;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa536161",
   "metadata": {},
   "outputs": [],
   "source": [
    "### THE dimMonthTable ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT * FROM dimMonthTable;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a0163e8",
   "metadata": {},
   "source": [
    "### Querying our Data Warehouse Implementation To Get Information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8c2ff40",
   "metadata": {},
   "outputs": [],
   "source": [
    "### The list of water sensors measured by type by month ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT \n",
    "    s.measurementMonth, s.sensorType, s.numberOfSensors FROM\n",
    "    (\n",
    "    SELECT \n",
    "        mt.measurementMonth, \n",
    "        st.sensorType, \n",
    "        COUNT(measurement) numberOfSensors\n",
    "    FROM \n",
    "        dimSensorTable st \n",
    "        INNER JOIN factMeasurementsTable fm ON st.sensorID = fm.sensorID\n",
    "        INNER JOIN dimTimeTable tt ON fm.dateTimeID = tt.dateTimeID\n",
    "        INNER JOIN dimMonthTable mt ON tt.monthID = mt.monthID\n",
    "    GROUP BY \n",
    "        st.sensorType, mt.measurementMonth \n",
    "    ) s \n",
    "JOIN dimMonthTable mt ON s.measurementMonth = mt.measurementMonth\n",
    "ORDER BY \n",
    "    mt.monthID, s.sensorType;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce77826a",
   "metadata": {},
   "outputs": [],
   "source": [
    "### The number of sensor measurements collected by type by week ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT \n",
    "    wt.measurementWeek, \n",
    "    st.sensorType, \n",
    "    COUNT(measurement) numberOfMeasurements\n",
    "FROM \n",
    "    dimSensorTable st \n",
    "    INNER JOIN factMeasurementsTable fm ON st.sensorID = fm.sensorID\n",
    "    INNER JOIN dimTimeTable tt ON fm.dateTimeID = tt.dateTimeID\n",
    "    INNER JOIN dimWeekTable wt ON tt.weekID = wt.weekID\n",
    "GROUP BY \n",
    "    st.sensorType, wt.measurementWeek\n",
    "ORDER BY\n",
    "    wt.measurementWeek, st.sensorType;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7f359d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "### The number of measurements made by location by month ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT \n",
    "    s.measurementMonth, s.measurementLocation, s.numberOfMeasurements FROM\n",
    "    (\n",
    "    SELECT \n",
    "        mt.measurementMonth, \n",
    "        lt.measurementLocation, \n",
    "        COUNT(measurement) numberOfMeasurements\n",
    "    FROM \n",
    "        dimLocationTable lt \n",
    "        INNER JOIN factMeasurementsTable fm ON lt.locationID = fm.locationID\n",
    "        INNER JOIN dimTimeTable tt ON fm.dateTimeID = tt.dateTimeID\n",
    "        INNER JOIN dimMonthTable mt ON tt.monthID = mt.monthID\n",
    "    GROUP BY \n",
    "        lt.measurementLocation, mt.measurementMonth\n",
    "    ) s\n",
    "JOIN dimMonthTable mt ON s.measurementMonth = mt.measurementMonth\n",
    "ORDER BY \n",
    "    mt.monthID, s.measurementLocation;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2cd07042",
   "metadata": {},
   "outputs": [],
   "source": [
    "### The average number of measurements covered for pH by year ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT \n",
    "    yt.measurementYear, \n",
    "    COUNT(measurement) numberOfMeasurements\n",
    "FROM \n",
    "    dimSensorTable st \n",
    "    INNER JOIN factMeasurementsTable fm ON st.sensorID = fm.sensorID\n",
    "    INNER JOIN dimTimeTable tt ON fm.dateTimeID = tt.dateTimeID\n",
    "    INNER JOIN dimYearTable yt ON tt.yearID = yt.yearID\n",
    "WHERE \n",
    "    st.sensorType = 'pH'\n",
    "GROUP BY \n",
    "    yt.measurementYear\n",
    "ORDER BY \n",
    "    yt.measurementYear;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f0a2bd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "### The average value of nitrate measurements by locations by year ###\n",
    "\n",
    "## You can run this cell more than once. Expect a dataframe ##\n",
    "\n",
    "query = \"\"\"\n",
    "\n",
    "SELECT \n",
    "    yt.measurementYear,\n",
    "    lt.measurementLocation,\n",
    "    ROUND(AVG(measurement), 2) averageValuesOfNitrate\n",
    "FROM \n",
    "    dimLocationTable lt \n",
    "    INNER JOIN factMeasurementsTable fm ON lt.locationID = fm.locationID\n",
    "    INNER JOIN dimSensorTable st ON fm.sensorID = st.sensorID\n",
    "    INNER JOIN dimTimeTable tt ON fm.dateTimeID = tt.dateTimeID\n",
    "    INNER JOIN dimYearTable yt ON tt.yearID = yt.yearID\n",
    "WHERE \n",
    "    st.sensorType = 'Nitrate-N'\n",
    "GROUP BY \n",
    "    lt.measurementLocation, yt.measurementYear\n",
    "ORDER BY \n",
    "    yt.measurementYear, lt.measurementLocation;\n",
    "    \n",
    "        \"\"\"\n",
    "\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc5b0cd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Endeavour to run this cell in order to close the connection to the database.\n",
    "\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6e5e7b7",
   "metadata": {},
   "source": [
    "##### End of demonstration. Thank you for staying with me on this!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}