Dear all

your solution is incorrect,please check my solution below and edit the code, I think my issue is the connection between diemansions tables and fact table, because there is no data appear in Q$7$ until Q$11.$

$----------------$

$--$ Project Tasks:

$--$STEP $1$: Create T$\_$SALESMAN Table:

CREATE TABLE T$\_$SALESMAN $($

SALES$\_$REP$\_$ID NUMBER,

SNAME VARCHAR$2(50)$

$)$;

SELECT DISTINCT COALESCE$($SALES$\_$REP$\_$ID$,999)$ AS SALES$\_$REP$\_$ID$,$ 'NAME$-\text{'}||$ COALESCE$($SALES$\_$REP$\_$ID$,999)$ AS SNAME

FROM ORDERS$\_$COPY;

$--------------------------------------------$

$--$Project$3$ Tasks:

$---$Q$2$ : Create all DIMENSION AS MATERIALIZED VIEWS.

$--------1$: Define a materialized View for D$\_$TIME Dimension table.

CREATE MATERIALIZED VIEW MV$\_$D$\_$TIME

BUILD IMMEDIATE

REFRESH COMPLETE ON DEMAND

AS

SELECT

ROW$\_$NUMBER$()$ OVER $($ORDER BY ORDER$\_$DATE$)$ AS ID$\_$T$,--$generate a unique ID for each row based on ORDER$\_$DATE

MONTH$\_$NO$,$

QUARTER,

YYear AS YEAR,

ORDER$\_$DATE AS ORDER$\_$DATE,

Month$\_$Name

FROM T$\_$DATE

$--$ Display the content of the materialized view

SELECT $*$ FROM MV$\_$D$\_$TIME;

$--1\mathrm{.}2$: Define the D$\_$TIME dimension using its created MV$.$

CREATE TABLE D$\_$TIME AS

SELECT DISTINCT

ID$\_$T$,$

MONTH$\_$NO$,$

QUARTER,

YEAR,

ORDER$\_$DATE,

MONTH$\_$NAME

FROM MV$\_$D$\_$TIME;

$--$ Add a primary key constraint to the D$\_$TIME table

ALTER TABLE D$\_$TIME

ADD CONSTRAINT PK$\_$D$\_$TIME PRIMARY KEY $($ID$\_$T$)$;

$--1\mathrm{.}3$: Define the primary key for the MV

ALTER MATERIALIZED VIEW MV$\_$D$\_$TIME ADD PRIMARY KEY $($ID$\_$T$)$;

$-----------------------------------------------------------------$

$--2$: Define a materialized View for D$\_$SALESMAN Dimension table

CREATE MATERIALIZED VIEW MV$\_$D$\_$SALESMAN

BUILD IMMEDIATE

REFRESH COMPLETE ON DEMAND

AS

SELECT

ROW$\_$NUMBER$()$ OVER $($ORDER BY SALES$\_$REP$\_$ID$)$ AS ID$\_$S$,--$generate a unique ID for each row based on ID$\_$S

SNAME

FROM T$\_$SALESMAN;

$--$Display the content of the materialized view

SELECT $*$ FROM MV$\_$D$\_$SALESMAN;

$--2\mathrm{.}1$: Define the D$\_$SALESMAN dimension using its created MV

CREATE TABLE D$\_$SALESMAN AS

SELECT DISTINCT

ID$\_$S$,$

SNAME

FROM MV$\_$D$\_$SALESMAN;

$--$ Add a primary key constraint to the D$\_$SALESMAN table

ALTER TABLE D$\_$SALESMAN

ADD CONSTRAINT PK$\_$D$\_$SALESMAN PRIMARY KEY $($ID$\_$S$)$;

$--2\mathrm{.}3$: Define the primary key for the MV

ALTER MATERIALIZED VIEW MV$\_$D$\_$SALESMAN ADD PRIMARY KEY $($ID$\_$S$)$;

$--------------------------------------------------------------$

$--3$: Define a materialized View for D$\_$CUSTOMERS Dimension table

CREATE MATERIALIZED VIEW MV$\_$D$\_$CUSTOMERS

BUILD IMMEDIATE

REFRESH COMPLETE ON DEMAND

AS

SELECT

ROW$\_$NUMBER$()$ OVER $($ORDER BY ID$\_$C$)$ AS ID$\_$C$,--$generate a unique ID for each row based on ID$\_$C

CUSTOMER$\_$ID$,$

CUST$\_$FIRST$\_$NAME AS C$\_$FName,

CUST$\_$LAST$\_$NAME AS C$\_$LName,

GENDER,

CUST$\_$TYPE AS C$\_$Type

FROM CUSTOMER$\_$COPY;

$--$ Display the content of the materialized view

SELECT $*$ FROM MV$\_$D$\_$CUSTOMERS;

$--$ Define the D$\_$CUSTOMERS dimension using its created MV

CREATE TABLE D$\_$CUSTOMERS AS

SELECT DISTINCT

ID$\_$C$,$

C$\_$FName,

C$\_$LName,

GENDER,

C$\_$Type

FROM MV$\_$D$\_$CUSTOMERS;

$--$ Add a primary key constraint to the D$\_$CUSTOMERS table

ALTER TABLE D$\_$CUSTOMERS

ADD CONSTRAINT PK$\_$D$\_$CUSTOMERS PRIMARY KEY $($ID$\_$C$)$;

$-----------------------------------------$

$--$ Define a materialized View for D$\_$ORDERS Dimension table

CREATE MATERIALIZED VIEW MV$\_$D$\_$ORDERS

BUILD IMMEDIATE

REFRESH COMPLETE ON DEMAND

AS

SELECT

ROW$\_$NUMBER$()$ OVER $($ORDER BY ORDER$\_$ID$)$ AS ID$\_$O$,--$generate a unique ID for each row based on ORDER$\_$ID

ORDER$\_$MODE AS OMode,

ORDER$\_$STATUS AS Status

FROM ORDERS$\_$COPY;

$--$ Display the content of the materialized view

SELECT $*$ FROM MV$\_$D$\_$ORDERS;

$--$ Define the D$\_$ORDERS dimension using its created MV

CREATE TABLE D$\_$ORDERS AS

SELECT DISTINCT

ID$\_$O$,$

OMode,

Status

FROM MV$\_$D$\_$ORDERS;

$--$ Add a primary key constraint to the D$\_$ORDERS table

ALTER TABLE D$\_$ORDERS

ADD CONSTRAINT PK$\_$D$\_$ORDERS PRIMARY KEY $($ID$\_$O$)$;

$--$Q$3$ & $4$ & $5$: Creating the fact table F$\_$ORDERS

$--1$: Create the fact table F$\_$ORDERS

CREATE TABLE F$\_$ORDERS $($

F$\_$ORDERS$\_$ID INTEGER,

ORDER$\_$AMOUNT DECIMAL$(10,2),$

ORDER$\_$DATE DATE,

ID$\_$C INTEGER,

ID$\_$S INTEGER,

ID$\_$T INTEGER,

ID$\_$O INTEGER,

FOREIGN KEY $($ID$\_$C$)$ REFERENCES D$\_$CUSTOMERS $($ID$\_$C$)$ ON DELETE CASCADE,

FOREIGN KEY $($ID$\_$S$)$ REFERENCES D$\_$SALESMAN $($ID$\_$S$)$ ON DELETE CASCADE,

FOREIGN KEY $($ID$\_$O$)$ REFERENCES D$\_$ORDERS $($ID$\_$O$)$ ON DELETE CASCADE,

FOREIGN KEY $($ID$\_$T$)$ REFERENCES D$\_$TIME $($ID$\_$T$)$ ON DELETE CASCADE

$)$;

$--2$: Define PK constraint for F$\_$ORDERS$\_$ID

ALTER TABLE F$\_$ORDERS

ADD CONSTRAINT PK$\_$F$\_$ORDERS$\_$ID PRIMARY KEY $($F$\_$ORDERS$\_$ID$)$;

$--$Q$6$: Create an Oracle Sequence starting with $1$ for F$\_$ORDERS$\_$ID

CREATE SEQUENCE SEQ$\_$F$\_$ORDERS START WITH $1$;SQL Worksheet

There is no

data?