Tuesday, 9 September 2014

Create Star Schema from Snowflake Schema

Create Star Schema from Snowflake Schema

Most of the OLTP systems are having snow flake schema and most of the times warehouse is in star schema. Here we will try to convert a snowflake schema into a star schema.

Here we will discuss queries required in Initial Load

Source Tables

  1. Sales_header
  2. Sales_details
  3. Store_master
  4. City_master
  5. State_master
  6. Country_mater
  7. Item_master
  8. Item_category_master
  9. Employee_master
  10. Department_master
  11. Order_header_status
  12. Times_calender_master
  13. Line_Status
  14. Customer_Master


Few Points
  1. Customer_Master and Time_calender_master table are de-normalized tables 
  2. For Employee we need to create slow changing dimension and at source there is no reference




Tables Scripts 

   sales_header

create table sales_header
(
order_id number,
order_date date,
customer_id number,
employee_id number,
store_id number,
order_status_id number,
created_at date,
updated_at date
);

sales_details

create table sales_details
(
line_id number,
order_id number,
item_id number,
line_status_id number,
order_qty number,
item_price number,
item_cost number,
order_value number,
line_discount number,
dis_head_discount number,
created_at date,
updated_at date
);

Store_master

create table Store_master(
store_id number,
store_name varchar2(255) ,
city_id number ,
created_at date,
updated_at date
);

City_master

create table City_master
(
city_id number,
city_name varchar2(255) ,
state_id number ,
created_at date,
updated_at date
);


State_master

create table State_master
(
State_id number,
State_name varchar2(255) ,
Country_id number ,
created_at date,
updated_at date
);


Country_mater

create table Country_mater
(
Country_id number,
Country_name varchar2(255) ,
created_at date,
updated_at date
);

Item_master
create table Item_master
(
item_id number,
Item_name varchar2(255)
last_purchse_price number,
last_sell_price number,
item_type number,
item_cat_id number,
created_at date,
updated_at date
);



Item_category_master

create table Item_category_master
(
item_cat_id number,
item_cat_name varchar2(255),
created_at date,
updated_at date
);

Employee_master

create table Employee_master
(
emp_id number,
emp_name varchar2(255);
DOB Date,
DOJ Date,
Mgr_id varchar2(255),
department_id number,
created_at date,
updated_at date
);

Department_master
create table Department_master
(
department_id number ,
department_name varchar2(255),
created_at date,
updated_at date
);

Order_header_status
create table Order_header_status
(
Order_header_status_id number,
Order_header_status varchar2(255),
created_at date,
updated_at date
);

Times_calender_master
create table Time_calender_master
(
date_id number,
cal_date date,
cal_week varchar2(255),
cal_month varchar2(255),
cal_qtr varchar2(255),
cal_year varchar2(255),
created_at date,
updated_at date
)


Line_Status
create table Line_Status
(
Line_Status_id number,
Line_Status varchar2(255),
created_at date,
updated_at date
);

Customer_Master

create table customer_master
(
customer_id number,
customer_name varchar2(255),
customer_type varchar2(255),
customer_group varchar2(255),
created_at date,
updated_at date
);

----------------------------------------DW---------------------------------------

Store_Dim

CREATE TABLE STORE_DIM_STG
   (
STORE_ID NUMBER,
STORE_NAME VARCHAR2(255 BYTE),
CITY_ID NUMBER,
CITY_NAME VARCHAR2(255 BYTE),
STATE_ID NUMBER,
STATE_NAME VARCHAR2(255 BYTE),
COUNTRY_ID NUMBER,
COUNTRY_NAME VARCHAR2(255 BYTE),
CREATED_AT DATE,
UPDATED_AT DATE,
LOAD_DATE DATE,
LOAD_STATUS VARCHAR2(255 BYTE)
   ) ;

CREATE SEQUENCE Store_Dim_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

create table Store_Dim(
store_unique_id number,
store_id number,
store_name varchar2(255) ,
city_id number ,
city_name varchar2(255) ,
state_id number ,
State_name varchar2(255) ,
Country_id number ,
Country_name varchar2(255) ,
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);


Item_dim

create table Item_dim_stg
(
item_id number,
Item_name varchar2(255)
last_purchse_price number,
last_sell_price number,
item_type number,
item_cat_id number,
item_cat_name varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

CREATE SEQUENCE Item_dim_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

create table Item_dim
(
item_unique_id number,
item_id number,
Item_name varchar2(255)
last_purchse_price number,
last_sell_price number,
item_type number,
item_cat_id number,
item_cat_name varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);


Employee_dim

CREATE SEQUENCE Employee_dim_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

 create table Employee_dim_stg
(
emp_id number,
emp_name varchar2(255);
DOB Date,
DOJ Date,
Mgr_id varchar2(255),
Mgr_name varchar2(255),
department_id number,
department_name varchar2(255),
start_date date,
end_date date,
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

create table Employee_dim
(
emp_unique_id number,
emp_id number,
emp_name varchar2(255);
DOB Date,
DOJ Date,
Mgr_id varchar2(255),
Mgr_name varchar2(255),
department_id number,
department_name varchar2(255),
start_date date,
end_date date,
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

Order_header_status_dim

CREATE SEQUENCE Order_header_status_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;


 create table Order_header_status_dim_stg
(
Order_header_status_id number,
Order_header_status varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);
create table Order_header_status_dim
(
Order_header_st_Unq_id number,
Order_header_status_id number,
Order_header_status varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

Times_calender_dim

CREATE SEQUENCE Time_calender_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

create table Time_calender_dim
(
date_unique_id number,
date_id number,
cal_date date,
cal_week varchar2(255),
cal_month varchar2(255),
cal_qtr varchar2(255),
cal_year varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
)


Line_Status_dim

CREATE SEQUENCE Line_Status_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

 create table Line_Status_dim_stg
(
Line_Status_id number,
Line_Status varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);


create table Line_Status_dim
(
Line_Status_unq_id number,
Line_Status_id number,
Line_Status varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

Customer_dim

CREATE SEQUENCE Customer_dim_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

create table Customer_dim
(
customer_id number,
customer_name varchar2(255),
customer_type varchar2(255),
customer_group varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

create table Customer_dim
(
customer_unq_id number,
customer_id number,
customer_name varchar2(255),
customer_type varchar2(255),
customer_group varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

create table sales_fact_stg
(
order_id number,
order_date date,
customer_id number,
employee_id number,
store_id number,
order_status_id number,
hd_created_at date,
hd_updated_at date,
line_id number,
order_id number,
item_id number,
line_status_id number,
order_qty number,
item_price number,
item_cost number,
order_value number,
line_discount number,
dis_head_discount number,
created_at date,
updated_at date,
STORE_UNIQUE_ID number,
item_unique_id number,
emp_unique_id number,
Order_header_st_Unq_id number,
date_unique_id number,
Line_Status_unq_id number,
customer_unq_id number
);

create table sales_fact
(
order_id number,
order_date date,
customer_id number,
employee_id number,
store_id number,
order_status_id number,
hd_created_at date,
hd_updated_at date,
line_id number,
order_id number,
item_id number,
line_status_id number,
order_qty number,
item_price number,
item_cost number,
order_value number,
line_discount number,
dis_head_discount number,
created_at date,
updated_at date,
STORE_UNIQUE_ID number,
item_unique_id number,
emp_unique_id number,
Order_header_st_Unq_id number,
date_unique_id number,
Line_Status_unq_id number,
customer_unq_id number
);
--------------------------------------------------------




INSERT
INTO STORE_DIM_STG
  (
    STORE_ID,
    STORE_NAME,
    CITY_ID,
    CITY_NAME,
    STATE_ID,
    STATE_NAME,
    COUNTRY_ID,
    COUNTRY_NAME,
    CREATED_AT,
    UPDATED_AT,
    LOAD_DATE,
    LOAD_STATUS
  )
select sm.STORE_ID,
sm.STORE_NAME,
                    sm.CITY_ID,
                    cm.CITY_NAME,
                    cm.STATE_ID,
                    stm.STATE_NAME,
                    stm.COUNTRY_ID,
                    ctm.COUNTRY_NAME,
                    sm.CREATED_AT,
                    sm.UPDATED_AT,
                    sysdate,
'New'
                   

from STORE_MASTER sm left outer join CITY_MASTER on(sm.city_id=cm.city_id)
 left outer join STATE_MASTER stm on(cm.state_id=stm.state_id)
left outer join COUNTRY_MATER ctm on(stm.country_id=ctm.country_id);


Insert into Store_Dim
select Store_Dim_seq.nextval ,
STORE_ID,
    STORE_NAME,
    CITY_ID,
    CITY_NAME,
    STATE_ID,
    STATE_NAME,
    COUNTRY_ID,
    COUNTRY_NAME,
    CREATED_AT,
    UPDATED_AT,
    LOAD_DATE,
    LOAD_STATUS
from STORE_DIM_STG;
------------------------------------------------------------
Item_dim

insert into item_dim_stg

(
item_id ,
Item_name ,
last_purchse_price,
last_sell_price,
item_type ,
item_cat_id ,
item_cat_name ,
created_at ,
updated_at ,
Load_date,
load_status)
   select distinct

  (    
         im.item_id ,
    im.Item_name ,
      im.last_purchse_price ,
         im.last_sell_price ,
         im.item_type ,
         im.item_cat_id ,
         im.created_at ,
         im.updated_at ,
         icm.item_cat_name,
         icm.created_at,
         icm.updated_at,
         sysdate,
        'New' load_status
 from Item_master im left outer join Item_category_master icm on (im.item_cat_id =icm=item_cat_id );


insert into item_dim
Select Item_dim_seq.nextval item_unique_id,
item_id ,
Item_name ,
last_purchse_price,
last_sell_price,
item_type ,
item_cat_id ,
item_cat_name ,
created_at ,
updated_at ,
Load_date,
load_status
from insert into item_dim;

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

Employee_dim


insert into Employee_dim_stg

(
emp_id ,
emp_name ,
DOB ,
DOJ ,
Mgr_id ,
Mgr_name ,
department_id,
department_name ,
start_date ,
end_date ,
created_at ,
updated_at ,
Load_date ,
load_status
)
select distinct
(
em1.emp_id,
em1.emp_name,
em1.DOB ,
em1.DOJ ,
em1.Mgr_id,
em2.mgr_name,
em1.department_id ,
em1.DOJ,
'31-Dec-2199' End_date,
em1.created_at ,
em1.updated_at,
dm.department_id ,
dm.department_name ,
em1.created_at ,
em1.updated_at
sysdate,
'New'load_status
from
Employee_master em1 join Employee_master em2 on(em1.mgr_id=em2.emp_id)
left outer join Department_master dm on(em1.department_id =dm.department_id )


insert into Employee_dim
select Employee_dim_seq.nextval emp_unique_id ,
emp_id ,
emp_name ,
DOB ,
DOJ ,
Mgr_id ,
Mgr_name ,
department_id,
department_name ,
start_date ,
end_date ,
created_at ,
updated_at ,
Load_date ,
load_status
from Employee_dim_stg
---------------------------------------------------

Order_header_status_dim


insert into Order_header_status_dim_stg
(
Order_header_status_id ,
Order_header_status ,
created_at ,
updated_at ,
Load_date ,
load_status
)
(select

         Order_header_status_id ,
         order_header_status ,
         created_at ,
        updated_at,
        sysdate,
        'New' load_status
from
Order_header_status
 );

 insert into Order_header_status_dim
select Order_header_status_seq.nextval Order_header_st_Unq_id ,
Order_header_status_id ,
Order_header_status ,
created_at ,
updated_at ,
Load_date ,
load_status
from Order_header_status_dim_stg
--------------------------------------------------------------
Times_calender_dim


insert into Time_calender_dim
(
date_unique_id ,
date_id ,
cal_date ,
cal_week ,
cal_month ,
cal_qtr,
cal_year ,
created_at ,
updated_at ,
Load_date ,
load_status
)
(select distinct
Time_calender_seq.nextval
date_id ,
cal_date ,
cal_week ,
cal_month ,
cal_qtr ,
cal_year ,
created_at ,
updated_at,
sysdate,
'y'load_status

from Time_calender_master);


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

Line_Status_dim



insert into Line_Status_dim_Stg
(
Line_Status_id ,
Line_Status ,
created_at ,
updated_at ,
Load_date ,
load_status
)
(select distinct

Line_Status_id ,
Line_Status ,
created_at ,
updated_at,
sysdate,
'New'load_status
from
Line_Status);


insert into Line_Status_dim
select
Line_Status_seq.nextval, Line_Status_unq_id ,
Line_Status_id ,
Line_Status ,
created_at ,
updated_at ,
Load_date ,
load_status
from Line_Status_dim_Stg
-------------------------------------------------------------------


Customer_dim



insert into Customer_dim_stg
(
customer_id ,
customer_name ,
customer_type ,
customer_group ,
created_at,
updated_at ,
Load_date ,
load_status
)
(select distinct
         
           customer_id ,
           customer_name ,
           customer_type ,
           customer_group ,
           created_at ,
           updated_at,
          sysdate,
            'New'load_status
from Customer_Master
);

insert into Customer_dim
select Customer_dim_seq.nextval customer_unq_id ,
customer_id ,
customer_name ,
customer_type ,
customer_group ,
created_at,
updated_at ,
Load_date ,
load_status
from Customer_dim_stg


----------------------------Sales Fact-----------------------------------------------------
----Keep Original Id from fact ------------------------------

fact

Insert into Sales_fact_stg
(
order_id ,
order_date ,
customer_id ,
employee_id ,
store_id ,
order_status_id ,
hd_created_at ,
hd_updated_at ,
line_id ,
order_id ,
item_id ,
line_status_id ,
order_qty ,
item_price ,
item_cost ,
order_value ,
line_discount ,
dis_head_discount ,
created_at ,
updated_at ,
STORE_UNIQUE_ID ,
item_unique_id ,
emp_unique_id ,
Order_header_st_Unq_id ,
date_unique_id ,
Line_Status_unq_id ,
customer_unq_id
)

select
sh.order_id ,
sh.order_date ,
sh.customer_id ,
sh.employee_id ,
sh.store_id ,
sh.order_status_id ,
sh.created_at  hd_created_at,
sh.updated_at  hd_updated_at,
sd.line_id ,
sd.order_id ,
sd.item_id ,
sd.line_status_id ,
sd.order_qty ,
sd.item_price ,
sd.item_cost ,
sd.order_value ,
sd.line_discount ,
sd.dis_head_discount ,
sd.created_at ,
sd.updated_at ,
std.STORE_UNIQUE_ID,
id.item_unique_id,
ed.emp_unique_id,
ohd.Order_header_st_Unq_id ,
tcd.date_unique_id,
lsi.Line_Status_unq_id,
cd.customer_unq_id

from sales_details sd
left outer join sales_header Sh
left outer join STORE_DIM_STG std on(sd.store_id=std.store_id)
left outer join item_dim id on (sd.item_id=id.item_id)
left outer join Employee_dim on ed (sd.emp_id=ed.emp_id)
left outer join Order_header_status_dim ohd (sd.Order_header_status_id= ohd.Order_header_status_id)
left outer join Time_calender_dim tcd on (sd.date_id=tcd.date_id)
left outer join Line_Status_dim lsi on(sd.Line_Status_id=lsi.Line_Status_id)
left outer join Customer_dim cd on(sd.customer_id =cd.customer_id );


Insert into Sales_fact
(
order_id ,
order_date ,
customer_id ,
employee_id ,
store_id ,
order_status_id ,
hd_created_at ,
hd_updated_at ,
line_id ,
order_id ,
item_id ,
line_status_id ,
order_qty ,
item_price ,
item_cost ,
order_value ,
line_discount ,
dis_head_discount ,
created_at ,
updated_at ,
STORE_UNIQUE_ID ,
item_unique_id ,
emp_unique_id ,
Order_header_st_Unq_id ,
date_unique_id ,
Line_Status_unq_id ,
customer_unq_id
)
select
order_id ,
order_date ,
customer_id ,
employee_id ,
store_id ,
order_status_id ,
hd_created_at ,
hd_updated_at ,
line_id ,
order_id ,
item_id ,
line_status_id ,
order_qty ,
item_price ,
item_cost ,
order_value ,
line_discount ,
dis_head_discount ,
created_at ,
updated_at ,
STORE_UNIQUE_ID ,
item_unique_id ,
emp_unique_id ,
Order_header_st_Unq_id ,
date_unique_id ,
Line_Status_unq_id ,
customer_unq_id
from Sales_fact_stg;





** Scripts not validated, might need correction 


Posted by at
Labels: , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)