Use code below to build the rental fact:

-- * * * * * * * * * * * * * * * * * *

-- "SAKILA_DW"."ANALYTICS".RENTAL_FACT

-- * * * * * * * * * * * * * * * * * *

create or replace TABLE "MYDB"."SAKILA_ANALYTICS".RENTAL_FACT (

RENTAL_DATE_KEY NUMBER(38,0) NOT NULL,

RENTAL_TIMEOFDAY_KEY NUMBER(38,0) NOT NULL,

RENTAL_ID NUMBER(38,0) NOT NULL,

CUSTOMER_KEY NUMBER(38,0) NOT NULL,

FILM_KEY NUMBER(38,0) NOT NULL,

-- STAFF_KEY NUMBER(38,0) NOT NULL,

-- STORE_LOCATION_KEY NUMBER(38,0) NOT NULL,

RENTAL_QUANTITY NUMBER(38,0) NOT NULL,

RENTAL_TOTAL_AMOUNT NUMBER(4,2) NOT NULL

);

Write the SQL to join the dimensions with necessary table to build the fact table.

Please refer to the following dimensions and the ERD diagram at the end for your reference:

Make sure your dimensions are correct for CUSTOMER_DIM, FILM_DIM, STORE_LOCATION_DIM, STAFF_DIM:

CREATE OR REPLACE TABLE "MYDB"."SAKILA_ANALYTICS".CUSTOMER_DIM (

CUSTOMER_KEY NUMBER(38,0) NOT NULL autoincrement,

CUSTOMER_ID NUMBER(38,0) NOT NULL,

FIRSTNAME VARCHAR(50) NOT NULL DEFAULT 'NA',

LASTNAME VARCHAR(50) NOT NULL DEFAULT 'NA',

FIRSTNAME_LASTNAME VARCHAR(100) NOT NULL DEFAULT 'NA',

CUSTOMER_EMAIL VARCHAR(150) NOT NULL DEFAULT 'NA',

CUSTOMER_ADDRESS VARCHAR(100) NOT NULL DEFAULT 'NA',

CUSTOMER_ADDRESS2 VARCHAR(100) DEFAULT 'NA',

CUSTOMER_CITY VARCHAR(100) NOT NULL DEFAULT 'NA',

CUSTOMER_DISTRICT VARCHAR(100) NOT NULL DEFAULT 'NA',

CUSTOMER_ZIP VARCHAR(10) NOT NULL DEFAULT 'NA',

CUSTOMER_COUNTRY VARCHAR(50) NOT NULL DEFAULT 'NA',

CUSTOMER_PHONE VARCHAR(50) NOT NULL DEFAULT 'NA',

IS_ACTIVE VARCHAR(5),

CUSTOMER_REGISTRATION_DATE DATE NOT NULL ,

CUSTOMER_LAST_UPDATE datetime NOT NULL

);

CREATE OR REPLACE SEQUENCE CUST_DIM;

INSERT INTO "MYDB"."SAKILA_ANALYTICS".CUSTOMER_DIM (

CUSTOMER_KEY,

CUSTOMER_ID ,

FIRSTNAME ,

LASTNAME ,

FIRSTNAME_LASTNAME ,

CUSTOMER_EMAIL ,

CUSTOMER_ADDRESS ,

CUSTOMER_ADDRESS2 ,

CUSTOMER_CITY ,

CUSTOMER_DISTRICT ,

CUSTOMER_ZIP ,

CUSTOMER_COUNTRY ,

CUSTOMER_PHONE ,

IS_ACTIVE ,

CUSTOMER_REGISTRATION_DATE ,

CUSTOMER_LAST_UPDATE

)

SELECT cust_dim.nextval as CUSTOMER_KEY ,

c.CUSTOMER_ID,

c.FIRST_NAME,

c.LAST_NAME,

CONCAT (FIRST_NAME, ',',LAST_NAME ) AS firstlastname,

c.EMAIL,

a.address AS customer_address,

a.address2 AS customer_address2,

ci.city AS customer_city,

a.district AS customer_district,

a.postal_code AS customer_postal_code,

co.country AS customer_country,

a.phone AS customer_phone,

CASE c.active

WHEN c.active=1 THEN 'Yes'

ELSE 'No'

END AS is_active,

c.create_date AS registration_date,

to_timestamp(c.last_update) AS customer_last_udpate

FROM "MYDB"."SAKILA"."CUSTOMER" c

JOIN "MYDB"."SAKILA"."ADDRESS" a ON c.address_id = a.address_id

JOIN "MYDB"."SAKILA".city ci ON a.city_id = ci.city_id

JOIN "MYDB"."SAKILA".country co ON ci.country_id = co.country_id ;

---------------

-- * * * * * * * * * * * * * * * * * *

-- "SAKILA_DW"."ANALYTICS".FILM_DIM

-- * * * * * * * * * * * * * * * * * *

create or replace TABLE "MYDB"."SAKILA_ANALYTICS".FILM_DIM (

FILM_KEY NUMBER(38,0) NOT NULL autoincrement,

FILM_ID NUMBER(38,0) NOT NULL,

FILM_NAME VARCHAR(100) NOT NULL DEFAULT 'NA',

FILM_LANGUAGE VARCHAR(50) NOT NULL DEFAULT 'NA',

FILM_CATEGORY VARCHAR(100) NOT NULL DEFAULT 'NA',

RENTAL_DURATION_DAY NUMBER(38,0) NOT NULL DEFAULT 0,

RENTAL_RATE NUMBER(4,2) NOT NULL DEFAULT 0,

REPLACEMENT_COST NUMBER(4,2) NOT NULL DEFAULT 0,

LENGTH_MIN NUMBER(38,0) NOT NULL DEFAULT 0

);

create or replace sequence seq1;

INSERT INTO "MYDB"."SAKILA_ANALYTICS".FILM_DIM (

FILM_KEY,

film_id,

FILM_NAME,

FILM_LANGUAGE,

FILM_CATEGORY,

RENTAL_DURATION_DAY,

RENTAL_RATE,

REPLACEMENT_COST,

LENGTH_MIN

)

SELECT seq1.nextval as film_KEY ,

f.film_id,

f.title AS film_title,

l.name AS LANGUAGE,

c.name AS category_name,

f.rental_duration,

f.RENTAL_RATE,

f.REPLACEMENT_COST,

f.LENGTH

FROM "MYDB"."SAKILA".film f

JOIN "MYDB"."SAKILA".LANGUAGE l ON f.language_id = l.language_id

JOIN "MYDB"."SAKILA".film_category fc ON f.film_id = fc.film_id

JOIN "MYDB"."SAKILA".category c ON fc.category_id = c.category_id;

-- * * * * * * * * * * * * * * * * * *

-- "SAKILA_DW"."ANALYTICS".STAFF_DIM

-- * * * * * * * * * * * * * * * * * *

create or replace TABLE "MYDB"."SAKILA_ANALYTICS".STAFF_DIM (

STAFF_KEY NUMBER(38,0) NOT NULL autoincrement,

STAFF_ID NUMBER(38,0) NOT NULL,

STAFF_FIRSTNAME VARCHAR(50) NOT NULL DEFAULT 'NA',

STAFF_LASTNAME VARCHAR(50) NOT NULL DEFAULT 'NA',

FIRSTNAME_LASTNAME VARCHAR(100) NOT NULL DEFAULT 'NA',

STAFF_EMAIL VARCHAR(100) NOT NULL DEFAULT 'NA',

STAFF_IS_ACTIVE VARCHAR(3)

);

create or replace sequence seq1;

INSERT INTO "MYDB"."SAKILA_ANALYTICS".STAFF_DIM (

STAFF_KEY,

STAFF_ID,

STAFF_FIRSTNAME,

STAFF_LASTNAME,

FIRSTNAME_LASTNAME,

STAFF_EMAIL,

STAFF_IS_ACTIVE

)

SELECT seq1.nextval as STAFF_KEY,

s.staff_id,

s.first_name,

s.last_name,

CONCAT (FIRST_NAME, ',',LAST_NAME) AS firstlastname,

s.email,

CASE s.ACTIVE

WHEN 'TRUE'

THEN 'Yes'

ELSE 'No'

END AS active

FROM "MYDB"."SAKILA".staff s;

-- * * * * * * * * * * * * * * * * * *

-- "SAKILA_DW"."ANALYTICS".STORE_LOCATION_DIM

-- * * * * * * * * * * * * * * * * * *

create or replace TABLE "MYDB"."SAKILA_ANALYTICS".STORE_LOCATION_DIM (

STORE_LOCATION_KEY NUMBER(38,0) NOT NULL autoincrement,

STORE_ID NUMBER(38,0) NOT NULL,

STORE_POSTAL_CODE VARCHAR(10) NOT NULL DEFAULT 'NA',

STORE_CITY VARCHAR(100) NOT NULL DEFAULT 'NA',

STORE_DISTRICT VARCHAR(50) NOT NULL DEFAULT 'NA',

STORE_COUNTRY VARCHAR(50) NOT NULL DEFAULT 'NA',

STORE_PHONE VARCHAR(20) NOT NULL DEFAULT 'NA'

);

create or replace sequence seq1;

INSERT INTO "MYDB"."SAKILA_ANALYTICS".STORE_LOCATION_DIM (

STORE_LOCATION_KEY,

STORE_ID,

STORE_POSTAL_CODE,

STORE_CITY,

STORE_DISTRICT,

STORE_COUNTRY,

STORE_PHONE

)

SELECT seq1.nextval as STORE_LOCATION_KEY ,

s.store_id,

a.postal_code,

c.city,

a.district,

co.country,

a.phone

FROM "MYDB"."SAKILA"."STORE" s

JOIN "MYDB"."SAKILA"."ADDRESS" a ON s.address_id = a.address_id

JOIN "MYDB"."SAKILA"."CITY" c ON a.city_id = c.city_id

JOIN "MYDB"."SAKILA"."COUNTRY" co ON c.country_id = co.country_id;