Data warehouse Design Using Oracle

Created Feb. 7, 2024 by Girija Narasimhan

Unit 1- Schema Design Exercise Solution

Create  dimension patientinfo_dim using patient table. The patient table has following columns patient id, patient name, DOB, gender, 

Address, disease, test and medicine.  

The patient table normalized  into doctor table which has following columns doctor id, doctor name, yrs_of_experience and specialization.

Design patientinfo_dim has hierarchy name as "medicine_rollup"  which has following level patient_id, patient_name, disease, test and medicine.  create attributes "patient_info" which has columns are DOB, gender.

Ans:

step 1:

conn hr/hr

create table patient(patient_id number(5) primary key,   patient_name varchar2(15),DOB varchar2(10), gender varchar2(6),

  Address varchar2(20), disease varchar2(15), test varchar2(10),  medicine varchar2(20));

step 2:

 create table doctor(doctor_id number(5) primary key,   doctor_name varchar2(15), yrs_of_experience varchar2(10),  specialization varchar2(15));

step 3:

CREATE DIMENSION patientinfo_dim

  LEVEL pid IS patient.patient_id

  LEVEL pname IS patient.patient_name

  LEVEL pdis IS patient.disease

  LEVEL ptest IS patient.test

  LEVEL pmed IS patient.medicine

    HIERARCHY medicine_rollup

     (pid CHILD OF

      pname CHILD OF

     pdis CHILD OF ptest CHILD OF pmed )

 ATTRIBUTE patient_info level pid DETERMINES (DOB,gender);

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

Question 1

1) Include attribute as "Address" in patientinfo_dim

 ALTER DIMENSION patientinfo_dim ADD

  ATTRIBUTE patient_info level pid DETERMINES patient.Address;

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

1) Include attribute as "Address" in patientinfo_dim

ALTER DIMENSION patientinfo_dim ADD

ATTRIBUTE patient_info level pid DETERMINES patient.Address;

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

2) Display hierarchy  and level of patientinfo_dim

 SET SERVEROUTPUT ON FORMAT WRAPPED;

 EXECUTE DBMS_DIMENSION.DESCRIBE_DIMENSION ('PATIENTINFO_DIM'); 

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

3) Display how many dimensions are created by user hr.

 SET LINESIZE 400;

select object_type,object_name FROM user_objects where user_name='HR';

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

4) Remove the level "test" from patientinfo_dim  

 ALTER DIMENSION patientinfo_dim

  DROP LEVEL ptest cascade;

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

5) Display the patientinfo-dim status

select dimension_name, compile_state from all_dimensions where dimension_name='PATIENTINFO_DIM';  

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

6)  Include "patient_dr_rollup" hierarchy in patientinfo_dim dimension for joining the patient and doctor  table 

 alter table patient 

  add 

  doctor_id number(5) references doctor(doctor_id);

alter dimension patientinfo_dim 

  add

  level did is doctor.doctor_id;

 hierarchy patient_dr_rollup

  (pid child of did

  join key patient.doctor_id references did);

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

7) Remove the gender column from patient_info attribute

 ALTER DIMENSION patientinfo_dim

  DROP ATTRIBUTE patient_info LEVEL pid  COLUMN gender;

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

8) Change the disease column size as varchar2(40) in base table patient.

 Alter table patient modify (disease varchar2(15));

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

9) Check the productinfo_dim validation status and  

Alter dimension productinfo_dim compile;

Write appropriate statement to execute  "compile_state" of patientinfo_dim as valid.

select dimension_name, compile_state from all_dimensions whare dimension_name='PATIENTINFO_DIM';

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

Unit 2- Materialized View Exercise Solution

Question 1

Create materialized view "pplan_mv" to  display plan_id, plan_name, plan_amount, No_of_sms_free. 

Create materialized view pplan_mv from phone_plan as select * from phone_plan

Change plan_id=14 amount as “ 3000" in the phone_ plan table. 

update phone_plan set plan_amount = ‘3000’ where plan_id=14;

Write appropriate  statement, the phone_plan table modification reflects in the "pplan_mv". 

execute dbms_mview.refresh(‘pplan_mv’);

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

Question 2

Write appropriate statement to insert below information only in "pplan_mv1" materialized view not in phone_plan table. 

Plan_id

Plan_name

Plan_amount

Expiry date

No_of_sms_free

15

Sliver plan

400

14-oct-2017

45

Create materialized view pplan_mv1 for update as select * from phone_plan;

INSERT into pplan_mv1 values(15,’Sliver plan’,’400’,’14-oct-2017’,45);

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

Question 3

Create materialized view "pplan_mv2" using refresh complete and using phone_plan table, change plan_amount=900 in the plan_id=11 and use appropriate refresh option in the "pplan_mv2" after refresh also rowid should be same for plan_id=11. 

Create materialized view pplan_mv2 refresh complete as select * from phone_plan;

update phone_plan set plan_amount = ‘900’ where plan_id=11;

Execute DBMS_MVIEW.REFRESH(LIST => 'pplan_mv2', method=>'F');

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

Question 4

Insert comment "this is my second mv" in the "pplan_mv2" 

comment on materialized view pplan_mv2 is 'This is my second mv';

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

delete "pplan_mv" 

drop materialized view pplan_mv;

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

Question 5

Create materialized view log on phone_plan to display row_id, plan_id, plan_name, old and new value after updating No_of_sms_free =150 in plan_id=13. [hint: both create and display statement] 

Create materialized view log on phone_plan with row_id,primary key including new values;

update phone_plan set  No_of_sms_free= 150 where plan_id=13;

Select M_row$$,plan_id,plan_name,old_news$$ from mlog$_phone_plan

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

Question 6

Create materialized view "pplan_mv3" using appropriate build method which don't have result set.

Create materialized view pplan_mv3 build deferred as select * from phone_plan

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

Question 7

Display the status of all the materialized view created earlier and change pplan_mv3 status should be "valid" in the compile_state column.

ALTER MATERIALIZED VIEW pplan_mv3 COMPILE;

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE

  FROM USER_MVIEWS ORDER BY MVIEW_NAME;

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

Question 8

Display the highest No_of_sms_free, plan_id, plan_name in "pplan_mv5" 

Create materialized view  pplan_mv5 as select max(No_of_sms_free),plan_id,plan_name from phone_plan

Delete the comment in pplan_mv2 

comment on materialized view pplan_mv2 is ‘ ‘;

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

Unit 3- OLAP Exercise Solution

Pivot Exercise 1

Write a Pivot statement to display how many times the customer purchased the same product. The product is  categorized based on product_id.

SELECT * FROM

(

  SELECT customer_ref, product_id

  FROM orders

)

PIVOT

(

  COUNT(product_id)

  FOR product_id IN (10, 20, 30)

)

ORDER BY customer_ref;

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

Pivot Exercise 2


Unit 4 - Partition Exercise Solution

Question 1

Using the appropriate partition method to create Plan1 table which has two  partition namely P1 and P2. The partition P1 stores the plan_id values upto 1500. The P2 partition stores the plan_id up to 2500.    

create table  plan1(planid number(10) primary key, pname varchar2(30),pamount number(10),edate date,nosms number(5))

partition by range(planid)

( partition p1 value less than(1501)

partition p2 value less than(2501));

Display the table name, table space name, partition name and their values.

select * from User_tab_partitions;

Include one more partition P3 which has any plan_id value which his any value which is greater than 2500

alter table add partition p3 value less than (maxvalue);

Change the name partition P1 to P1500. 

alter table plan rename partition p1 to p1500;

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

Question 2

 using appropriate partition method to create Plan2 table which has partition namely Pname1, Pname2. The Pname1 partition has values of plan_name "group members" and "monthly gold plan". The Pname2 partition has values "SMS Users"  and "Student Plan".  

create table  plan2(planid number(10) primary key, pname varchar2(30),pamount number(10),edate date,nosms number(5))

partition by list(pname) (

partition by pname1 values(' group members',' monthly gold plan'),

partition by pname2 values(' SMS users,' Student plan)

) ;

Display the partition Pname2 values.  

select * from plan2 partition(pname2);

Remove the "student plan" value from the partition Pname2. 

delete from plan2 where pname='student plan';

alter table plan2 modify partition drop values('student plan');

 From partition Pname1, the value "monthly gold plan" will store in new partition "plangold"  and Pname1 stores only "group members". [Hint: use single statement]

ALTER TABLE plan2

             SPLIT PARTITION pname1 VALUES(' monthly gold plan ')

                    INTO (PARTITION plangold,PARTITION pname1);

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

Question 3

Use appropriate partition method to Plan3 table which has  three  partition namely Planno1, Planno2 and Planno3 based on plan_id. 

create table  plan3(planid number(10) primary key, pname varchar2(30),pamount number(10),edate date,nosms number(5)) partition by hash(planid) (partition planno1 ,partition planno2,partition planno3) ;

Display only Planno2 partition.  

select * from plan3 partition (planno2);

Reduce instead of three(3) partition to two(2) partition but content of the partition should be distributed to remaining partition. 

ALTER TABLE plan3 COALESCE PARTITION;

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

UNIT 5- SQL MODEL Exercise Solution

create table acase_model(aid number(6) primary key,atype varchar2(15),nationality varchar2(15),gender varchar2(10),month varchar2(10),noa number(6));

insert into acase_model values(1001,'Injury','Omani','Male','July',122);

insert into acase_model values(1002,'Death','Omani','Male','July',39);

insert into acase_model values(1003,'Injury','Expatriate','Male','July',60);

insert into acase_model values(1004,'Death','Expatriate','Male','July',13);


SQL> Select aid,noa from acase_model 

  2  Model

  3  Dimension by(aid)

  4  Measures(noa)

  5  Rules(

  6  noa[1017]=noa[1004]+noa[1003]);


       AID        NOA

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

      1001        122

      1002         39

      1003         60

      1004         13

      1017         73


2)

SQL> Select atype, nationality,gender,noa from acase_model

  2  Model

  3  Partition by(atype)

  4  Dimension by(nationality,gender)

  5  Measures(noa)

  6  rules();


ATYPE           NATIONALITY     GENDER            NOA

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

Death           Omani           Male               39

Death           Expatriate      Male               13

Injury          Omani           Male              122

Injury          Expatriate      Male               60




Return to top