create diminsions and fact (star schema) using materialized view in sql
Budget: $10 – $30 USD
2. Create Dimensions (MATIRIALIZED VIWE)
Use the CREATE with an embedded SELECT statement (and tables T_DATE, CUSTOMERS_COPY, ORDERS_COPY, and T_SALESMAN) to create and load the four dimensional tables D_ORDERS, D_CUSTOMERS, D_TIME and D_SALESMAN as they are described in the figure shows above.
For each Dimension, define the PK constraint and name it as follows:
PK_D_ORDERS Primary Kay for D_ORDERS
PK_D_SALESMAN Primary Kay for D_SALESMAN
PK_D_TIME Primary Kay for D_TIME
PK_D_CUSTOMERS Primary Kay for D_CUSTOMERS
3. Create Fact Table
CREATE the fact table F_ORDERS (F_ORDERS_ID, ORDER_AMOUNT,…Complete with the other columns….). FROM MV
4. The PK of the fact table F_ORDERS could be either the concatenation of all FKs referencing the dimensions linked to the fact, or the UNIQUE column F_ORDERS_ID. Define a PK constraint called PK_F_ORDERS_ID on the column F_ORDERS_ID.
5. The Fact table F_ORDERS references the four dimensions D_TIME, D_CUSTOMERS, D_ORDERS and D_SALESMAN. Define all its Foreign keys (Follow the naming convention: FK_ORDERS_TO_DATE, …..) 4
6. Create an Oracle Sequence SEQ_F_ORDERS starting with 1. (You will use this sequence to populate F_ORDERS_ID primary key).
7. Develop SQL statement(s) to load F_ORDERS. Note that the measure ORDER_AMOUNT is in EURO and you extract it from the table ORDERS_COPY.UPDATE TASK2
8. Display the SUM of the column ORDER_AMOUNT from the fact F_ORDERS.
9. Display the Turnover (sum of ORDER_AMOUNT) by Customer (ID_C, C_FName) and by Year.
10. COUNT the number of ORDERS having an amount greater that a given value (choose a value).
11. Display the percentage of amount of DIRECT Orders and ONLINE Orders.
Use the CREATE with an embedded SELECT statement (and tables T_DATE, CUSTOMERS_COPY, ORDERS_COPY, and T_SALESMAN) to create and load the four dimensional tables D_ORDERS, D_CUSTOMERS, D_TIME and D_SALESMAN as they are described in the figure shows above.
For each Dimension, define the PK constraint and name it as follows:
PK_D_ORDERS Primary Kay for D_ORDERS
PK_D_SALESMAN Primary Kay for D_SALESMAN
PK_D_TIME Primary Kay for D_TIME
PK_D_CUSTOMERS Primary Kay for D_CUSTOMERS
3. Create Fact Table
CREATE the fact table F_ORDERS (F_ORDERS_ID, ORDER_AMOUNT,…Complete with the other columns….). FROM MV
4. The PK of the fact table F_ORDERS could be either the concatenation of all FKs referencing the dimensions linked to the fact, or the UNIQUE column F_ORDERS_ID. Define a PK constraint called PK_F_ORDERS_ID on the column F_ORDERS_ID.
5. The Fact table F_ORDERS references the four dimensions D_TIME, D_CUSTOMERS, D_ORDERS and D_SALESMAN. Define all its Foreign keys (Follow the naming convention: FK_ORDERS_TO_DATE, …..) 4
6. Create an Oracle Sequence SEQ_F_ORDERS starting with 1. (You will use this sequence to populate F_ORDERS_ID primary key).
7. Develop SQL statement(s) to load F_ORDERS. Note that the measure ORDER_AMOUNT is in EURO and you extract it from the table ORDERS_COPY.UPDATE TASK2
8. Display the SUM of the column ORDER_AMOUNT from the fact F_ORDERS.
9. Display the Turnover (sum of ORDER_AMOUNT) by Customer (ID_C, C_FName) and by Year.
10. COUNT the number of ORDERS having an amount greater that a given value (choose a value).
11. Display the percentage of amount of DIRECT Orders and ONLINE Orders.