Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
--Uber Revenue Data Mart Developed and Written by Annie Vellon and Kelsey Heyvaert
--------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.databases
WHERE name=N'UberDW1')
CREATE DATABASE UberDW1
GO
USE UberDW1
--------------------------------------------------------------------------
--Delete Existing Tables:
IF EXISTS(
SELECT *
FROM sys.tables
WHERE name=N'FactRevenue'
)
DROP TABLE FactRevenue;
IF EXISTS(
SELECT *
FROM sys.tables
WHERE name=N'DimTrip'
)
DROP TABLE DimTrip;
IF EXISTS(
SELECT *
FROM sys.tables
WHERE name=N'DimLocation'
)
DROP TABLE DimLocation;
IF EXISTS(
SELECT *
FROM sys.tables
WHERE name=N'DimService'
)
DROP TABLE DimService;
IF EXISTS(
SELECT *
FROM sys.tables
WHERE name=N'DimDate'
)
DROP TABLE DimDate;
IF EXISTS(
SELECT *
FROM sys.tables
WHERE name=N'DimDriver'
)
DROP TABLE DimDriver;
IF EXISTS(
SELECT *
FROM sys.tables
WHERE name=N'DimWeather'
)
DROP TABLE DimWeather;
--------------------------------------------------------------------------
--Create Tables
CREATE TABLE DimDriver
(DriverSK INT NOT NULL IDENTITY (1,1) CONSTRAINT pk_DimDriver PRIMARY KEY,
DriverAK NVARCHAR(255) NOT NULL,
Rating INT,
YearsExperience INT,
CommissionRate INT NOT NULL
);
CREATE TABLE DimDate
(Date_SK INT NOT NULL IDENTITY (1,1) CONSTRAINT pk_DimDate PRIMARY KEY,
Date DATETIME,
DayOfMonth VARCHAR(2), -- Field will hold day number of Month
DayName VARCHAR(9), -- Contains name of the day, Sunday, Monday
DayOfWeek CHAR(1),-- First Day Sunday=1 and Saturday=7
DayOfWeekInMonth VARCHAR(2), -- 1st Monday or 2nd Monday in Month
Month VARCHAR(2), -- Number of the Month 1 to 12{}
MonthName VARCHAR(9),-- January, February etc
Quarter CHAR(1),
Year CHAR(4),-- Year value of Date stored in Row
Holiday VARCHAR(50),--Name of Holiday in US
);
CREATE TABLE DimService
(ServiceSK INT NOT NULL IDENTITY (1,1) CONSTRAINT pk_DimService PRIMARY KEY,
ServiceAk NVARCHAR(255),
Size NVARCHAR(1) NOT NULL,
LuxaryLevel NVARCHAR(10) NOT NULL
);
CREATE TABLE DimLocation
(LocationSK INT NOT NULL IDENTITY (1,1) CONSTRAINT pk_DimLocation PRIMARY KEY,
LocationAK NVARCHAR(255) NOT NULL,
[State] NVARCHAR(2) NOT NULL,
City NVARCHAR(30) NOT NULL,
ZipCode NVARCHAR(5) NOT NULL
);
CREATE TABLE DimTrip
(TripSK INT NOT NULL IDENTITY (1,1) CONSTRAINT pk_DimTrip PRIMARY KEY,
TripAK NVARCHAR(255) NOT NULL,
StartTime DateTime NOT NULL,
EndTime DateTime NOT NULL
);
CREATE TABLE DimWeather
(WeatherSK INT NOT NULL IDENTITY (1,1) CONSTRAINT pk_DimWeather PRIMARY KEY,
[Date] DATETIME,
Season NVARCHAR(25),
Conditions NVARCHAR(35),
PredictedAvgTemp INT,
ActualAvgTemp INT,
PrecipitationProbability DECIMAL
);
CREATE TABLE FactRevenue
(DriverSK INT NOT NULL,
Date_SK INT NOT NULL,
LocationSK INT NOT NULL,
ServiceSK INT NOT NULL,
TripSK INT NOT NULL,
WeatherSK INT NOT NULL,
BaseFee Decimal,
SurgeRate Decimal,
Discount Decimal,
TotalCharge Decimal,
TripDuration DateTime,
CONSTRAINT pk_FactRevenue PRIMARY KEY (DriverSK, Date_SK, LocationSK, ServiceSK,TripSK,WeatherSK),
CONSTRAINT fk_DimDriver FOREIGN KEY (DriverSK)
REFERENCES DimDriver(DriverSK),
CONSTRAINT fk_DimDate FOREIGN KEY(Date_SK)
REFERENCES DimDate(Date_SK),
CONSTRAINT fk_DimLocation FOREIGN KEY(LocationSK)
REFERENCES DimLocation(LocationSK),
CONSTRAINT fk_DimTrip FOREIGN KEY(TripSK)
REFERENCES DimTrip(TripSK),
CONSTRAINT fk_DimWeather FOREIGN KEY(WeatherSK)
REFERENCES DimWeather(WeatherSK)
);
--------------------------------------------------------------------------
--List table names and row counts for confirmation
GO
SET NOCOUNT ON
SELECT 'DimLocation'"Table", COUNT(*) "Rows" FROM DimLocation UNION
SELECT 'DimService', COUNT(*) FROM DimService UNION
SELECT 'DimDate', COUNT(*) FROM DimDate UNION
SELECT 'DimDriver', COUNT(*) FROM DimDriver UNION
SELECT 'DimTrip', COUNT(*) FROM DimTrip UNION
SELECT 'FactRevenue', COUNT(*) FROM FactRevenue
ORDER BY 1;
SET NOCOUNT OFF
GO
--DimLocation
SELECT
LocationAK=UberOLTP1.dbo.Trip.TripID,
[State]=UberOLTP1.dbo.Trip.TripState,
City=UberOLTP1.dbo.Trip.TripCity,
Zipcode=UberOLTP1.dbo.Trip.PickupZipCode
FROM UberOLTP1.dbo.Trip
GO
--DimService
SELECT
ServiceAK=UberOLTP1.dbo.Car.LicensePlate,
Size=UberOLTP1.dbo.Car.NumberOfSeats,
LuxaryLevel=UberOLTP1.dbo.Car.LuxuryLevel
FROM UberOLTP1.dbo.Car
GO
--DimDate
/*****************************************************************************************/
--DimDate
/*****************************************************************************************/
USE UberDW1
GO
-- Specify start date and end date here
-- Value of start date must be less than your end date
DECLARE @StartDate DATETIME = '01/01/2010' -- Starting value of date range
DECLARE @EndDate DATETIME = GETDATE() -- End Value of date range
-- Temporary variables to hold the values during processing of each date of year
DECLARE
@DayOfWeekInMonth INT,
@DayOfWeekInYear INT,
@DayOfQuarter INT,
@WeekOfMonth INT,
@CurrentYear INT,
@CurrentMonth INT,
@CurrentQuarter INT
-- Table data type to store the day of week count for the month and year
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
-- Extract and assign various parts of values from current date to variable
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
-- Proceed only if start date(current date ) is less than end date you specified above
WHILE @CurrentDate < @EndDate
BEGIN
-- Begin day of week logic
/*Check for change in month of the current date if month changed then change variable value*/
IF @CurrentMonth <> DATEPART(MM, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
END
/* Check for change in quarter of the current date if quarter changed then change variable value*/
IF @CurrentQuarter <> DATEPART(QQ, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
END
/* Check for Change in Year of the Current date if Year changed then change variable value*/
IF @CurrentYear <> DATEPART(YY, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART(YY, @CurrentDate)
END
-- Set values in table data type created above from variables
UPDATE @DayOfWeek
SET
MonthCount = MonthCount + 1,
QuarterCount = QuarterCount + 1,
YearCount = YearCount + 1
WHERE DOW = DATEPART(DW, @CurrentDate)
SELECT
@DayOfWeekInMonth = MonthCount,
@DayOfQuarter = QuarterCount,
@DayOfWeekInYear = YearCount
FROM @DayOfWeek
WHERE DOW = DATEPART(DW, @CurrentDate)
-- End day of week logic
/* Populate your dimension table with values*/
INSERT INTO DimDate
SELECT
--CONVERT (char(8),@CurrentDate,112) AS Date_SK,
@CurrentDate AS [Date],
DATEPART(DD, @CurrentDate) AS DayOfMonth,
DATENAME(DW, @CurrentDate) AS DayName,
DATEPART(DW, @CurrentDate) AS DayOfWeek,
@DayOfWeekInMonth AS DayOfWeekInMonth,
DATEPART(MM,@CurrentDate) AS Month,
DATENAME(MM, @CurrentDate) AS MonthName,
DATEPART(QQ, @CurrentDate) AS Quarter,
DATEPART(YEAR, @CurrentDate) AS Year,
NULL AS Holiday
SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END
--Update values of holiday as per USA Govt. Declaration for National Holiday
-- THANKSGIVING - Fourth THURSDAY in November
UPDATE [dbo].[DimDate]
SET Holiday = 'Thanksgiving Day'
WHERE
[Month] = 11
AND [DayOfWeek] = 'Thursday'
AND DayOfWeekInMonth = 4
-- CHRISTMAS
UPDATE [dbo].[DimDate]
SET Holiday = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
-- 4th of July
UPDATE [dbo].[DimDate]
SET Holiday = 'Independance Day'
WHERE [Month] = 7 AND [DayOfMonth] = 4
-- New Years Day
UPDATE [dbo].[DimDate]
SET Holiday = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
-- Memorial Day - Last Monday in May
UPDATE [dbo].[DimDate]
SET Holiday = 'Memorial Day'
FROM [dbo].[DimDate]
WHERE Date_SK IN
(
SELECT
MAX(Date_SK)
FROM [dbo].[DimDate]
WHERE
[Month] = 'May'
AND [DayOfWeek] = 'Monday'
GROUP BY
[Year],
[Month]
)
-- Labor Day - First Monday in September
UPDATE [dbo].[DimDate]
SET Holiday = 'Labor Day'
FROM [dbo].[DimDate]
WHERE Date_SK IN
(
SELECT
MIN(Date_SK)
FROM [dbo].[DimDate]
WHERE
[Month] = 'September'
AND [DayOfWeek] = 'Monday'
GROUP BY
[Year],
[Month]
)
-- Valentine's Day
UPDATE [dbo].[DimDate]
SET Holiday = 'Valentine''s Day'
WHERE
[Month] = 2
AND [DayOfMonth] = 14
-- Saint Patrick's Day
UPDATE [dbo].[DimDate]
SET Holiday = 'Saint Patrick''s Day'
WHERE
[Month] = 3
AND [DayOfMonth] = 17
-- Martin Luthor King Day - Third Monday in January starting in 1983
UPDATE [dbo].[DimDate]
SET Holiday = 'Martin Luthor King Jr Day'
WHERE
[Month] = 1
AND [DayOfWeek] = 'Monday'
AND [Year] >= 1983
AND DayOfWeekInMonth = 3
-- President's Day - Third Monday in February
UPDATE [dbo].[DimDate]
SET Holiday = 'President''s Day'
WHERE
[Month] = 2
AND [DayOfWeek] = 'Monday'
AND DayOfWeekInMonth = 3
-- Mother's Day - Second Sunday of May
UPDATE [dbo].[DimDate]
SET Holiday = 'Mother''s Day'
WHERE
[Month] = 5
AND [DayOfWeek] = 'Sunday'
AND DayOfWeekInMonth = 2
-- Father's Day - Third Sunday of June
UPDATE [dbo].[DimDate]
SET Holiday = 'Father''s Day'
WHERE
[Month] = 6
AND [DayOfWeek] = 'Sunday'
AND DayOfWeekInMonth = 3
-- Halloween 10/31*/
UPDATE [dbo].[DimDate]
SET Holiday = 'Halloween'
WHERE
[Month] = 10
AND [DayOfMonth] = 31
-- Election Day - The first Tuesday after the first Monday in November
BEGIN
DECLARE @Holidays TABLE (ID INT IDENTITY(1,1),
DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
INSERT INTO @Holidays(DateID, [Year],[Day])
SELECT
Date_SK,
[Year],
[DayOfMonth]
FROM [dbo].[DimDate]
WHERE
[Month] = 11
AND [DayOfWeek] = 'Monday'
ORDER BY
YEAR,
DayOfMonth
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @MINDAY INT
SELECT
@CURRENTYEAR = MIN([Year]),
@STARTYEAR = MIN([Year]),
@ENDYEAR = MAX([Year])
FROM @Holidays
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([Year])
FROM @Holidays
WHERE [Year] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM @Holidays
WHERE
[Year] = @CURRENTYEAR
AND [Week] IS NULL
UPDATE @Holidays
SET [Week] = @POS
WHERE
[Year] = @CURRENTYEAR
AND [Day] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE [dbo].[DimDate]
SET Holiday = 'Election Day'
FROM [dbo].[DimDate] DT
JOIN @Holidays HL ON (HL.DateID + 1) = DT.Date_SK
WHERE
[Week] = 1
END
--Code Used to Fill Dim Tables
SELECT * FROM [dbo].[DimDate]
/*****************************************************************************************/
--DimDriver
SELECT
DriverAK=UberOLTP1.dbo.Driver.DriverLicenseNumer,
Rating=UberOLTP1.dbo.Driver.Rating,
YearsExperience=DATEDIFF(mm,UberOLTP1.dbo.Driver.StartDate,GETDATE()),
CommissionRate=UberOLTP1.dbo.Driver.CommissionRate
FROM UberOLTP1.dbo.Driver
GO
--DimTrip
SELECT
TripAK=UberOLTP1.dbo.Trip.TripID,
StartTime=UberOLTP1.dbo.Trip.StartTime,
EndTime=UberOLTP1.dbo.Trip.EndTime
FROM UberOLTP1.dbo.Trip
GO
--Code Used to Fill Fact Table
---------------------------------------------------------------
--FactSales
SELECT
UberDW1.dbo.DimDriver.DriverSK,
UberDW1.dbo.DimDate.Date_SK,
UberDW1.dbo.DimLocation.LocationSK,
UberDW1.dbo.DimService.ServiceSK,
UberDW1.dbo.DimTrip.TripSK,
UberDW1.dbo.DimWeather.WeatherSK,
BaseFee=UberOLTP1.dbo.Payment.BaseFare,
SurgeRate=UberOLTP1.dbo.Payment.SurgeRate,
Discount=UberOLTP1.dbo.Payment.Discount,
TotalCharge=UberOLTP1.dbo.Payment.BaseFare,
TripDuration=CONVERT(INT, (DATEDIFF(mi,UberOLTP1.dbo.Trip.StartTime,UberOLTP1.dbo.Trip.EndTime)))
FROM UberDW1.dbo.DimDate
INNER JOIN UberOLTP1.dbo.Trip
ON UberDW1.dbo.DimDate.[Date]=UberOLTP1.dbo.Trip.TripDate
INNER JOIN UberOLTP1.dbo.Driver
ON UberOLTP1.dbo.Trip.DriverLicenseNumer=UberOLTP1.dbo.Driver.DriverLicenseNumer
INNER JOIN UberOLTP1.dbo.Car
ON UberOLTP1.dbo.Car.DriverLicenseNumer=UberOLTP1.dbo.Driver.DriverLicenseNumer
INNER JOIN UberOLTP1.dbo.Payment
ON UberOLTP1.dbo.Payment.PaymentID=UberOLTP1.dbo.Trip.PaymentID
INNER JOIN UberDW1.dbo.DimDriver
ON UberDW1.dbo.DimDriver.DriverAK=UberOLTP1.dbo.Driver.DriverLicenseNumer
INNER JOIN UberDW1.dbo.DimLocation
ON UberDW1.dbo.DimLocation.LocationAK=UberOLTP1.dbo.Trip.TripID
INNER JOIN UberDW1.dbo.DimTrip
ON UberDW1.dbo.DimTrip.TripAK=UberOLTP1.dbo.Trip.TripID
INNER JOIN UberDW1.dbo.DimService
ON UberDW1.dbo.DimService.ServiceAk=UberOLTP1.dbo.Car.LicensePlate
INNER JOIN UberDW1.dbo.DimWeather
ON UberDW1.dbo.DimWeather.[Date]=UberOLTP1.dbo.Trip.TripDate
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.