SQL Data Warehouse ETL and Aggregate Table Examples

INSERT INTO Donors(DonorName, DonorSex, DonorID)

SELECT Iname, Isex, Iid FROM [HHSourceDB].INDIVIDUAL;

INSERT INTO HHDW.Donors(DonorKey, DonorName, DonorSex, DonorID)

SELECT DonorKey, DonorName, DonorSex, DonorID FROM Donors;

-- One example of a One-Way Aggregate:


CREATE TABLE OneWayAggregateByGenderFactTable ASSELECT rm.CalendarKey, rm.CityKey, rm.VehicleKey, rm.DriverKey, SUM(rm.MilesDriven) AS TotalMilesDriven, SUM(rm.AmountCharged) AS TotalAmountCharged, p.PassengerGenderFROM RevenueandMileageFactTable rm, PassengerDimension pWHERE p.PassengerKey = rm.PassengerKeyGROUP BY rm.CalendarKey, rm.CityKey, rm.VehicleKey, rm.DriverKey, p.PassengerGender;

-- Creating daily city snapshot

CREATE TABLE DailyCitySnapshot ASSELECT rm.CityKey, rm.CalendarKey, SUM(rm.MilesDriven) AS TotalDailyMilesDriven, SUM(rm.AmountCharged) AS TotalDailyAmountChargedFROM RevenueandMileageFactTable rmGROUP BY rm.CityKey, rm.CalendarKey;

CREATE TABLE imd ASSELECT rm.CityKey, rm.CalendarKey, COUNT(rm.TransactionID) AS TotalDailyNumberofLocalRidesFROM RevenueandMileageFactTable rm, CityDimension cd, CalendarDimension caWHERE LEFT(cd.CityZip, 2) = LEFT(pd.PassengerZip, 2)AND ca.CalendarKey = rm.CalendarKeyAND cd.CityKey = rm.CityKeyGROUP BY rm.CityKey, rm.CalendarKey;

ALTER TABLE DailyCitySnapshotADD TotalDailyNumberofLocalRides INT;

UPDATE DailyCitySnapshot ds, imd iSET ds.TotalDailyNumberofLocalRides = i.TotalDailyNumberofLocalRidesWHERE ds.CityKey = i.CityKeyAND ds.CalendarKey = i.CalendarKey;

--- Part 1:


INSERT INTO StoreDimension(StoreID, StoreName, StoreLocation)

SELECT StoreID, StoreName, StoreLocation

FROM [CoffeeShopDB].Stores;

--- Part 2:


INSERT INTO ProductDimension(ProductID, ProductName, ProductDescription, ProductPrice)

SELECT SKU, Coffee Name, Description, PricePerCup

FROM [CoffeeShopDB].Products;

--- Part 3:

CREATE TABLE IntermediateFactTable AS

SELECT s.StoreID, r.Date, p.SKU, r.ReceiptID, p.PricePerCup * r.Qty AS Revenue, r.Qty AS QtySold, r.Time

FROM Products p, Stores s, Receipts r

WHERE s.StoreID = r.StoreID AND p.ProductID = r.ProductID;

INSERT INTO RevenueFactTable(StoreKey, CalendarKey, ProductKey, ReceiptID, Revenue, QtySold, Time)

SELECT sd.StoreKey, cd.CalendarKey, pd.ProductKey, ift.ReceiptID, ift.Revenue, ift.QtySold, ift.Time

FROM StoreDimension sd, CalendarDimension cd, ProductDimension pd, IntermediateFactTable ift

WHERE sd.StoreID = ift.StoreID AND cd.FullDate = ift.Date AND pd.SKU = ift.SKU;

--- Part 4:

INSERT INTO targetDW.StoreDimension(StoreKey, StoreID, StoreName, StoreLocation)

SELECT StoreKey, StoreID, StoreName, StoreLocation

FROM StoreDimension;

INSERT INTO targetDW.ProductDimension(ProductKey, ProductID, ProductName, ProductDescription, ProductPrice)

SELECT ProductKey, ProductID, ProductName, ProductDescription, ProductPrice

FROM ProductDimension;

INSERT INTO targetDW.RevenueFactTable(StoreKey, CalendarKey, ProductKey, ReceiptID, Revenue, QtySold, Time)

SELECT StoreKey, CalendarKey, ProductKey, ReceiptID, Revenue, QtySold, Time

FROM RevenueFactTable;

--- Part 5:


7, SKU1001, 'Dark Magic', 'Extra Bold Coffee', 2.5, '02/15/2016', 'No'


--- Part 6:


One row, as shown in Part 5. This row accounts for the only change in the Product Dimension on that day, since no other products have changed any of their values, and no new products have been added.

CREATE TABLE PassGenderAggregate AS

SELECT dd.DriverKey, gd.PassengerGenderKey, vd.VehicleKey, cald.CalendarKey, cd.CityKey, SUM(rmf.MilesDriven) AS MilesDriven, SUM(rmf.AmountCharged) AS AmountCharged

FROM DriverDimension dd, PassengerDimension pd, VehicleDimension vd, CalendarDimension cald, CityDimension cd, GenderDimension gd, RevenueMileageFact rmf

WHERE rmf.DriverKey = dd.DriverKey AND rmf.VehicleKey = vd.VehicleKey AND rmf.CalendarKey = cald.CalendarKey AND rmf.CityKey = cd.CityKey AND rmf.PassengerKey = pd.PassengerKey

AND pd.PassengerGender = gd.PassengerGender

GROUP BY dd.DriverKey, gd.PassengerGenderKey, vd.VehicleKey, cald.CalendarKey, cd.CityKey;

CREATE TABLE ZipCodeVehicleTypeAggregate AS

SELECT dd.DriverKey, pzd.PassengerZipCodeKey, vtd.VehicleType, cald.CalendarKey, cd.CityKey, SUM(rmf.MilesDriven) AS MilesDriven, SUM(rmf.AmountCharged) AS AmountCharged

FROM DriverDimension dd, PassengerDimension pd, VehicleDimension vd, CalendarDimension cald, CityDimension cd, PassengerZipDimension pzd, VehicleTypeDimension vtd, RevenueMileageFact rmf

WHERE rmf.DriverKey = dd.DriverKey

AND rmf.VehicleKey = vd.VehicleKey

AND rmf.CalendarKey = cald.CalendarKey

AND rmf.CityKey = cd.CityKey

AND rmf.PassengerKey = pd.PassengerKey

AND pd.PassengerZipCode = pzd.PassengerZipCode

AND vd.VehicleType = vtd.VehicleType

GROUP BY dd.DriverKey, pzd.PassengerZipCodeKey, vtd.VehicleType, cald.CalendarKey, cd.CityKey;

CREATE TABLE MonthlyStateSnapshot AS

SELECT calmd.CalendarMonthKey, std.StateDimensionKey, SUM(rmf.MilesDriven) AS MilesDriven, SUM(rmf.AmountCharged) AS AmountCharged, COUNT(rmf.TransactionID) AS NumberOfRides, AVG(rmf.MilesDriven) AS AverageMilesDrivenPerRide

FROM CalendarDimension cald, CalendarMonthDimension calmd, CityDimension cd, StateDimensions std, RevenueMileageFact rmf

WHERE rmf.CalendarKey = cald.CalendarKey

AND rmf.CityKey = cd.CityKey

AND cd.CityState = std.State

AND cald.MonthYear = calmd.MonthYear;

CREATE TABLE PetBreedAggregate AS

SELECT pbd.PetBreedKey, td.TreatmentKey, cald.CalendarKey, cld.ClinicKey, SUM(rtf.EncounterDuration) AS TotalTime, SUM(rmf.AmountCharged) AS TotalAmountCharged

FROM PetBreedDimension pbd, PetDimension pd, TreatmentDimension td, CalendarDimension cald, ClinicDimension cld, RevenueTimeFact rtf

WHERE rtf.TreatmentKey = td.TreatmentKey

AND rtf.CalendarKey = cald.CalendarKey

AND rtf.ClinicKey = cld.ClinicKey

AND rtf.PetKey = pd.PetKey

AND pbd.PetBreed = pd.PetBreed

GROUP BY pbd.PetBreedKey, td.TreatmentKey, cald.CalendarKey, cld.ClinicKey;

----- Two-Way Aggregate by CalendarMonth and PayeeOwnership:

---- Version one: with PayeeOwnership dimension

CREATE TABLE TwoWayAggregateMonthOwnership AS

SELECT md.MonthKey, posd.OwnerStatusKey, td.TreatmentKey, cld.ClinicKey, SUM(rtf.EncounterDuration) AS TotalTime, SUM(rtf.AmountCharged) AS RevenueGenerated

FROM MonthDimension md, PetOwnerStatusDimension posd, PayeeDimension pd, TreatmentDimension td, CalendarDimension cald, ClinicDimension cld, RevenueTimeFact rtf

WHERE rtf.TreatmentKey = td.TreatmentKey

AND rtf.CalendarKey = cald.CalendarKey

AND md.MonthYear = cald.MonthYear

AND rtf.ClinicKey = cld.ClinicKey

AND rtf.PayeeKey = pd.PayeeKey

AND posd.OwnerYNFlag = pd.OwnerYNFlag

GROUP BY md.MonthKey, td.TreatmentKey, posd.OwnerKey, cld.ClinicKey;

--- Version two: with OwnerYNFlag as a degenerate dimension in the fact table:

CREATE TABLE TwoWayAggregateMonthOwnership AS

SELECT md.MonthKey, pd.OwnerYNFlag, td.TreatmentKey, cld.ClinicKey, SUM(rtf.EncounterDuration) AS TotalTime, SUM(rtf.AmountCharged) AS RevenueGenerated

FROM MonthDimension md, PayeeDimension pd, TreatmentDimension td, CalendarDimension cald, ClinicDimension cld, RevenueTimeFact rtf

WHERE rtf.TreatmentKey = td.TreatmentKey

AND rtf.CalendarKey = cald.CalendarKey

AND md.MonthYear = cald.MonthYear

AND rtf.ClinicKey = cld.ClinicKey

AND rtf.PayeeKey = pd.PayeeKey

GROUP BY md.MonthKey, pd.OwnerYNFlag, td.TreatmentKey, cld.ClinicKey;

---- MonthlyStateSnapshot

CREATE TABLE MonthlyStateSnapshot AS

SELECT calmd.CalendarMonthKey, std.StateDimensionKey, SUM(rft.EncounterDurationMinutes) AS TotalTreatmentTime,

SUM(rft.AmountCharged) AS TotalAmountCharged,

COUNT(rft.MedicalEncounterID) AS TotalNumberofTreatments,

SUM(rft.AmountCharged - t.TreatmentCost) AS TotalTreatmentProfit

FROM CalendarDimension cald, CalendarMonthDimension calmd, ClinicDimension cd, StateDimensions std, RevenueTimeFact rft, TreatmentDimension t

WHERE rft.CalendarKey = cald.CalendarKey

AND rft.CityKey = cd.CityKey

AND cd.CityState = std.State

AND cald.MonthYear = calmd.MonthYear

AND rft.TreatmentKey = t.TreatmentKey

GROUP BY calmd.CalendarMonthKey, std.StateDimensionKey;

INSERT INTO ChinookDW.Billing_Location_Dimension(BillingLocationKey, BillingLocationAddress, BillingLocationCity, BillingLocationState, BillingLocationCountry, BillingLocationPostalCode)

SELECT BillingLocationKey, BillingLocationAddress, BillingLocationCity, BillingLocationState, BillingLocationCountry, BillingLocationPostalCode

FROM Billing_Location_Dimension;

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

Number of rows in the Track_Dimension is: 350

Number of rows in the Customer_Dimension is: 10

Number of rows in the Billing_Location_Dimension is: 20

Number of rows in the Core fact table is: 500

---------

-- a) Create CalendarMonth Dimension

CREATE TABLE Calendar_Month_Dimension

(CalendarMonthKey INT AUTO_INCREMENT,

CalendarMonth INT,

CalendarYear INT,

PRIMARY KEY (CalendarMonthKey));

-- b) Create Genre Dimension

CREATE TABLE Genre_Dimension

(GenreKey INT AUTO_INCREMENT,

GenreID INT,

GenreName VARCHAR(120),

PRIMARY KEY (GenreKey));

-- c) Populate CalendarMonth Dimension

INSERT INTO Calendar_Month_Dimension

SELECT DISTINCT CalendarMonth, CalendarYear

FROM Calendar_Dimension;

-- d) Populate Genre Dimension

INSERT INTO Genre_Dimension

SELECT DISTINCT GenreID, GenreName

FROM TRACK_Dimension;

-- e) Create and Populate Monthly_Genre_Snapshot

CREATE TABLE Monthly_Genre_Snapshot AS

SELECT SUM(cf.UnitsSold), SUM(cf.RevenueGenerated), cm.CalendarMonthKey, g.GenreKey

FROM Core AS cf, Genre_Dimension AS g, Calendar_Month_Dimension cm, Calendar_Dimension c, Track_Dimension t

WHERE cf.CalendarKey = c.CalendarKey

AND c.CalendarMonth = cm.CalendarMonth

AND cf.TrackKey = t.TrackKey

AND t.GenreID = g.GenreID

GROUP BY cm.CalendarMonthKey, g.GenreKey;