CS 689 BU Programming Steps to Develop a Data Warehouse Design Project

MET CS 689 Designing and Implementing a Data Warehouse
Assignment 2: Warehouse Design
Overview of the Assignment:
This assignment will go through steps to develop a data warehouse design.
Part 1 – Review the business requirements
CityTour is a national chain of metropolitan tours (yes they have double decker tour busses!). City Tours
specializes in themes, for example haunted tours, tours in the river and the harbor (you may want to
review Boston Duck boat tours, and Boston trolly tours for ideas)
CityTour would like to introduce data warehousing and analytics to build their business. You have been
hired as a data architect to create an initial Constellation data warehouse design. On the next page is
CityTour’s relational OLTP data model. In addition, they would like to correlate the tour data with
weather data to see if there are trends from weather which might impact tours.
CityTour OLTP Data Model on next page
External Weather data is to come via an API such as https://openweathermap.org Here is a sample
JSON API response to give you a sense of the data returned:
https://openweathermap.org/current#current_JSON
{ “coord”: { “lon”: 10.99, “lat”: 44.34 }, “weather”: [ { “id”: 501,
“main”: “Rain”, “description”: “moderate rain”, “icon”: “10d” } ], “base”:
“stations”, “main”: { “temp”: 298.48, “feels_like”: 298.74, “temp_min”:
297.56, “temp_max”: 300.05, “pressure”: 1015, “humidity”: 64, “sea_level”:
1015, “grnd_level”: 933 }, “visibility”: 10000, “wind”: { “speed”: 0.62,
“deg”: 349, “gust”: 1.18 }, “rain”: { “1h”: 3.16 }, “clouds”: { “all”: 100
}, “dt”: 1661870592, “sys”: { “type”: 2, “id”: 2075663, “country”: “IT”,
“sunrise”: 1661834187, “sunset”: 1661882248 }, “timezone”: 7200, “id”:
3163858, “name”: “Zocca”, “cod”: 200 }
Updated 8/29/2023
Copyright 2023 Boston University; All rights reserved
1 of 5
MET CS 689 Designing and Implementing a Data Warehouse
Assignment 2: Warehouse Design
CityTour OLTP Data Model
Updated 8/29/2023
Copyright 2023 Boston University; All rights reserved
2 of 5
MET CS 689 Designing and Implementing a Data Warehouse
Assignment 2: Warehouse Design
Part 1 – Business Rules
1. Determine four business questions your data warehouse design will answer. Keep these
questions in mind as you move on to the rest of the assignment. One of the questions needs to
consider some sort of correlation with external weather data.
Your response of four business questions goes here
Part 2 – Design a constellation schema warehouse
1. Create and insert an ERD showing the constellation ERD schema below. Requirements are as
follows:
i.
Determine four to five (non-date/time) SCDs – make sure to include at least one type 2
and one type 3
ii.
Determine two to three date dimensions of different grains, consider a role-playing or
bitemporal date dimensions. You will use table in question 2b to outline the facts and
explain your design choice for the temporal dimensions
iii.
Determine two to three fact tables, your design should include at least one Snapshot
(transactional) fact, and at least one cumulative fact.
Your dimensional model design goes here
2. Questions on Dimensions
a. For EACH non-date/time SCD use the following table explain your SCD design
Table Name and Screenshot
SCD Type
Design explanation
b. Outline the fact tables which contain role-playing or bitemporal design
Table Name and Screenshot
Temporal
Design explanation
Type
Updated 8/29/2023
Copyright 2023 Boston University; All rights reserved
3 of 5
MET CS 689 Designing and Implementing a Data Warehouse
Assignment 2: Warehouse Design
3. Questions on Fact tables
a. Question For each of your business questions in part 1, how are the measures tied to
your questions? Highlight the dimensions, facts and measures involved.
Question 1:
Screenshot of tables answering question
How measures are tied to your question
Question 2:
Screenshot of tables answering question
How measures are tied to your question
Question 3:
Screenshot of tables answering question
How measures are tied to your question
Question 4:
Screenshot of tables answering question
How measures are tied to your question
b. Question: Outline the fact tables and explain why they are cumulative or snapshot
Table Name and Screenshot
Fact Type
Design explanation
c. Question: Which attributes in the OLTP schema will transform to measures and what
measures can be derived/calculated that should be included?
Table and Attribute
Fact and Measure How is it transformed
name from OLTP
name
Part 3 – Assumptions/Appendix (optional)
You may need to make some assumptions in order to work on this assignment. Clearly state any
assumptions you needed to make here, along with your reasoning why the assumption was appropriate
(one or two sentences should be sufficient in most cases). Do not remove any functionality, or trivialize,
any of the assignment requirements. Feel free to augment.
Updated 8/29/2023
Copyright 2023 Boston University; All rights reserved
4 of 5
MET CS 689 Designing and Implementing a Data Warehouse
Assignment 2: Warehouse Design
Use the Ask the Teaching Team Discussion Forum if you have any questions regarding the
how to approach this assignment.
Save your assignment as lastnameFirstname_assign2_0.docx and submit it in the
Assignments section of the course.
For help uploading files please refer to the Technical Support page in the syllabus.
Criterion
A
B
C
D
F
Technical
mastery
(50%)
Evidence of
excellent
mastery
throughout
Evidence of
good mastery
throughout
Evidence of
basic mastery
throughout or
good mastery
intermittently
Minimal
mastery
evidenced
Virtually no
mastery
evidenced
Good depth
and coverage
of significant
topics and
issues
Basic depth
and coverage
of significant
topics and
issues
Minimal
depth and
coverage of
significant
topics and
issues
Virtually no
depth and
coverage of
significant
topics and
issues
Ideas and
designs are
clear and
organized
throughout
Ideas and
designs are
somewhat
clear and
organized
throughout
Ideas and
designs are
mostly
obscure and
disorganized
Ideas and
designs are
entirely
obscure and
disorganized
Depth and
thoroughness
of coverage
(25%)
Clarity in
presentation
(25%)
Excellent
depth and
coverage of
significant
topics and
issues
Ideas and
designs are
exceptionally
clear and
organized
throughout
Assignment
Grade:
Updated 8/29/2023
Copyright 2023 Boston University; All rights reserved
5 of 5
Letter
Grade
Jack Polnar
CS689
10/15/2023
PK
PK,FK
ProjectID
PK,FK
ActualCompletionMonthDim
PK,FK
CustomerDimID
ProjectID
AverageDaysToComplete (integer)
ProjectName
AverageEstimatedRevenue (decimal)
ProjectType2023
AverageActualRevenue (decimal)
ProjectType2022
RevenueDifference (decimal)
ProjectType2021
Cumulative (Periodic fact)
Month
Year
SCD0
CustomerDim
PK
ProjectType2020
CustomerDimID
CustomerID
SCD3
ProjectFact
CustomerContactName
PK,FK
ProjectID
CustomerAddress1
PK,FK
EstimatedCompletionDateID
CustomerAddress2
PK,FK
ActualCompletionDateID
CustomerCity
PK,FK
CustomerDimID
CustomerState
FinishedOnTime (bit)
CustomerZipCode
DaysToComplete (integer)
EffectiveDate
EstimatedRevenue (decimal)
ExpireDate
ActualRevenue (decimal)
CurrentFlag
DateDim
PK
MonthDimID
Quarter
PercentageFinishedOnTime (decimal)
ProjectDim
PK
MonthDim
ProjectCumFact
DateDimID
RevenueDifference (decimal)
SCD2
Date
Snapshot (Transactional fact)
Day
ProjectStageDim
Quarter
PK
Month
ProjectStageID
ProjectName
Year
ProjectType
ProjectStageFact
SCD0
PK,FK
ProjectStageID
PK,FK
ActualStartDateID
PK,FK
ActualCompletionDateID
PK,FK
CompletionProcessedDateID
PK,FK
EmployeeDimID
ProjectStageDescription
EffectiveDate
ExpireDate
CurrentFlag
SCD2
HoursTakenToComplete (integer)
DaysStartedLate (integrer)
EmployeeDim
PK
TeamMemberDimID
NumberOfDaysWorked (integer)
EmployeeID
FinishedOnTime (bit)
EmployeeName
DaysToComplete (integer)
TeamMemberID
EstimatedCost (decimal)
RoleName
ActualCost (decimal)
HourlyPayRate
CostDifference (decimal)
HourlyBillRate
ActCostToCompleteEmployee
(decimal)
EmployeeCity
Snapshot (Transactional fact)
EmployeeState
EffectiveDate
ExpireDate
CurrentFlag
SCD2