Modeling

1. Dimensions are combined in fact (or aggregate) tables only
2. Dimensions are a verticle hierarchy that can only divide at a attribute higher level.
3. Be careful not to be fooled into thinking that just because "hour" is a measurement of time, that it belongs in the "time" dimension.

 

Logical Model of the warehouse

Legend:

	Dimensions are labeled at the top of each hierarchy.
	Dimensional attributes (hierarchy levels) are light green.
	Supporting attributes are in dark green.
	Fact tables are in blue.

 

Physical model of dimensions

 

 

 

 

 

 

 

 

Fact tables

 

 

DDL to build this warehouse

--********** FACTS ************

CREATE TABLE Fact_Transaction(
	Geo_Outlet_ID INT, Cust_ID INT, Pmt_Trans_ID INT, 
	Time_Date DATE, Hour_Item_Scan_ID INT, 
	Qty INT, Amt FLOAT(126), Recieved_Amt FLOAT(126), 
	PRIMARY KEY(Geo_Outlet_ID, Pmt_Trans_ID, Time_Date, Hour_Item_Scan_ID));
CREATE TABLE Fact_Transaction_Item(
	Geo_Outlet_ID INT, Cust_ID INT, Pmt_Trans_ID INT, 
	Time_Date DATE, Hour_Item_Scan_ID INT, Prod_ID FLOAT(126), 
	Qty INT, Amt FLOAT(126), 
	PRIMARY KEY(Geo_Outlet_ID, Prod_ID, Pmt_Trans_ID, Time_Date, Hour_Item_Scan_ID))

--********** Geography ************

CREATE TABLE Geography_Region(
	Geo_Reg_ID INT, Geo_Reg_Desc VARCHAR2(50), 
	PRIMARY KEY(Geo_Reg_ID));
CREATE TABLE Geography_State(
	Geo_State_ID INT, Geo_Reg_ID INT, Geo_State_Desc VARCHAR2(50), 
	PRIMARY KEY(Geo_State_ID));
CREATE TABLE Geography_Outlet(
	Geo_Outlet_ID INT, Geo_State_ID INT, Geo_Outlet_Desc VARCHAR(50), 
	PRIMARY KEY(Geo_Outlet_ID));

--********** Customer ************

CREATE TABLE Customer_State(
	Cust_State_ID INT, Cust_State_Desc VARCHAR2(50), 
	PRIMARY KEY(Cust_State_ID));
CREATE TABLE Customer_City(
	Cust_City_ID INT, Cust_State_ID INT, Cust_City_Desc VARCHAR2(50), 
	PRIMARY KEY(Cust_City_ID));
CREATE TABLE Customer_Info(
	Cust_ID INT, Cust_City_ID INT, Cust_Acct VARCHAR2(20), Cust_Name VARCHAR2(50), 
	Cust_Gender CHAR(1), Cust_Birth DATE, Cust_Income NUMBER, 
	Cust_Join_Date DATE, Cust_Zip VARCHAR2(15), 
	PRIMARY KEY(Cust_ID));

--********** Product ************

CREATE TABLE Product_Division(
	Prod_Div_ID INT, Prod_Div_Desc VARCHAR2(50), 
	PRIMARY KEY(Prod_Div_ID));
CREATE TABLE Product_Department(
	Prod_Dept_ID INT, Prod_Div_ID INT, Prod_Dept_Desc VARCHAR2(50), 
	PRIMARY KEY(Prod_Dept_ID));
CREATE TABLE Product_Group(
	Prod_Group_ID INT, Prod_Dept_ID INT, Prod_Group_Desc VARCHAR2(50), 
	PRIMARY KEY(Prod_Group_ID));
CREATE TABLE Product_Info(
	Prod_ID FLOAT(126), Prod_Group_ID INT, Prod_Desc VARCHAR2(50), 
	PRIMARY KEY(Prod_ID));

--********** Payment ************

CREATE TABLE Payment_Type(
	Pmt_Type_ID INT, Pmt_Type_Desc VARCHAR2(50), 
	PRIMARY KEY(Pmt_Type_ID));
CREATE TABLE Payment_Transaction(
	Pmt_Trans_ID INT, Pmt_Type_ID INT, 
	PRIMARY KEY(Pmt_Trans_ID));

--********** Time ************

CREATE TABLE Time_Month(
	Time_Month_ID INT, Time_Month_Desc VARCHAR2(12), 
	PRIMARY KEY(Time_Month_ID));
CREATE TABLE Time_Date(
	Time_Date DATE, Time_Month_ID INT, 
	PRIMARY KEY(Time_Date));

--********** Cart ************

CREATE VIEW Cart_Product_Group_View AS 
	SELECT Prod_Group_ID as Cart_Prod_Group_ID, 
	Prod_Group_Desc as Cart_Prod_Group_Desc
	FROM Product_Group;
CREATE VIEW Cart_Product_View AS 
	SELECT Prod_ID as Cart_Prod_ID, Prod_Group_ID as Cart_Prod_Group_ID, 
	Prod_Desc as Cart_Prod_Desc
	FROM Product_Info;
CREATE VIEW Cart_Prod_Trans_Relate_View AS 
	SELECT Prod_ID as Cart_Prod_ID, Pmt_Trans_ID
	FROM Fact_Transaction_Item;

--********** Hour ************

CREATE TABLE Hour_Item_Scan(
	Hour_Item_Scan_ID INT, Hour_Item_Scan_Desc VARCHAR2(50), 
	PRIMARY KEY(Hour_Item_Scan_ID));