PART I: Use the OLTP logical schema below to build data warehouse tables in SQL:
Question 1: CEO of Entertainment Inc. wants to keep track of performance of engagement that each
customer and agent have engaged. For each customer and agent, he wants to know total contract
length by year, and contract success ratio (calculated by total number of contracts expired each year
divided by total amount of contract price each year). In addition, for each customer and agent he wants
to know total numbers of contracts expired each week, and total number of contracts still active each
week. Provide 4-step dimensional model in the SQL comment section and create data warehouse
solution in SQL for the dimensional model that you propose.
Question 2: CEO of Entertainment Inc. wants to keep track of the life cycle of engagement contract.
Particularly, he wants to know the total number of agents involved for each engagement contract
phrase when the contract is authorized, engaged, monitored and closeout. Provide 4-step dimensional model in the SQL comment section and create data warehouse solution in SQL for the dimensional model that you propose.
PART II: Import the midterm data code for the OLTP logical schema in Part I and develop ETL process to create data warehouse tables in SSIS. Your dimensional and fact tables should meet these requirements:
- DimDate has DateKey values ranging from 20120901 to 20130312 (YYYYMMDD integer data type).
DimDate should contain IsMartinLutherKingHoliday attribute in boolean data type, LastDayOfMonth
attribute in boolean data type, and other required attributes. If you use Flat File or Excel Data Source to import datekey data, include the date source file in the submission also. - DimEntertainer should contain entertainer’s full address, entertainer’s last 4 digit of SSN, entertainer’s current year of active until today, and other required attributes. For example, for Entertainer ID 1 you should return “4110 Old
Redmond Rd., Redmond, WA 98052” as full address, and 22 as year of active. - DimEntertainer should be able to handle slowly changing dimension issues. - FactEngagement should contain DateKey,
EntertainerKey, and CustomerID. - Correct ETL process to create 4 fact measures. The following table
shows the descriptions of each fact measure:
