SQL Project – Basic Data Warehouse

Fact Tables

In this section, we take a brief look at the levels of aggregation we’re working with and start building our fact tables.

Resources

To create and populate fact.orderitems:

CREATE TABLE 	superstore.fact.orderitems(
orderitemid		INT PRIMARY KEY
,orderid		VARCHAR(50) NOT NULL
,customerid		VARCHAR(25) NOT NULL
,productid		VARCHAR(50) NOT NULL
,orderdate		DATE NOT NULL
,shipdate		DATE NOT NULL
,unitqty		INT NOT NULL
,sales			NUMERIC NOT NULL
,discount		NUMERIC NOT NULL
,profit			NUMERIC NOT NULL
)
;

INSERT INTO 	superstore.fact.orderitems

SELECT		oi.rowid
			,oi.orderid
			,oi.customerid
			,oi.productid
			,cd.cleanorderdate
			,cd.cleanshipdate
			,oi.quantity
			,oi.sales
			,oi.discount
			,oi.profit
FROM		superstore.staging.orderitems AS oi
INNER JOIN	superstore.staging.cleandates AS cd
ON			cd.rowid = oi.rowid	
;
SELECT 	*
FROM 	superstore.fact.orderitems;

And to create fact.orders:

/* This updates the source table, correcting records where customer Harry Olson has multiple customerids */

UPDATE		superstore.staging.orderitems
SET			customerid = 'HO-15230'
WHERE		customername = 'Harry Olson'
;

/* now we create and populate the table */

CREATE TABLE 	superstore.fact.orders(
orderid			VARCHAR(50) PRIMARY KEY
,customerid		VARCHAR(25) NOT NULL
,orderdate		DATE NOT NULL
,shipdate		DATE NOT NULL
,itemqty		INT NOT NULL
,sales			NUMERIC NOT NULL
,discount		NUMERIC NOT NULL
,profit			NUMERIC NOT NULL
)
;

INSERT INTO 	superstore.fact.orders

SELECT		oi.orderid
			,oi.customerid
			,MIN(cd.cleanorderdate)
			,MIN(cd.cleanshipdate)
			,COUNT(oi.rowid)
			,SUM(oi.sales)
			,SUM(oi.discount)
			,SUM(oi.profit)
FROM		superstore.staging.orderitems AS oi
INNER JOIN	superstore.staging.cleandates AS cd
ON			cd.rowid = oi.rowid	
GROUP BY	oi.orderid,oi.customerid
;

SELECT * 
FROM superstore.fact.orders;