For this project you will create an operational data store (ODS) and a data warehouse for the Garden Glory landscaping business. The data for the Garden Glory data warehouse will be extracted, transformed and loaded from the Garden Glory ODS. After you have completed the operational data store, the data warehouse, and the ETL process you will run several assigned queries in the data warehouse and the ODS. When this work is completed you will create a PowerPoint presentation describing the ODS and the data warehouse. The intent of this project is to introduce you to two different uses of a database: a normalized database for OLTP (online transaction processing) and a denormalized database for OLAP (online analytical processing). The following sections describe the steps for completing this project. The tables for the Garden Glory operational data store and the Garden Glory data warehouse will be contained within a single database instance. Create the Garden Glory Operational Data Store The first step for this project is to create the Garden Glory ODS. Review the Garden Glory project in chapter 3 to understand the requirements for the tables and the referential integrity constraints. The tables in the Garden Glory operational data store are OWNER, OWNED_PROPERTY, EMPLOYEE, GG_SERVICE, and PROPERTY_SERVICE. These tables contain the following columns. OWNER (OwnerID, OwnerName, OwnerEmailAddress, OwnerType) OWNED_PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, State, Zip, OwnerID) GG_SERVICE (ServiceID, ServiceDescription, CostPerHour) EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel) PROPERTY_SERVICE (PropertyServiceID, PropertyID, ServiceID, ServiceDate, EmployeeID, HoursWorked Use data types appropriate for MS SQL Server and for the type of data in the column. These tables have the following referential integrity constraints: OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER PropertyID in PROPERTY_SERVICE must exist in PropertyID in OWNED_PROPERTY ServiceID in PROPERTY_SERVICE must exist in ServiceID in GG_SERVICE EmployeeID in PROPERTY_SERVICE must exist in EmployeeID in EMPLOYEE Define the following primary keys as surrogate keys with values starting at 1 and incrementing by 1: OwnerID in OWNER, PropertyID in OWNED_PROPERTY, ServiceID in GG_SERVICE, EmployeeID in EMPLOYEE, and PropertyServiceID in PROPERTY_SERVICE. MS Server SQL uses the IDENTITY(n,n) syntax to create a surrogate key. MIS 315 Project Fall 2018 Page | 2 Create the Garden Glory Data Warehouse Create the Garden Glory data warehouse using a star schema design. The dimension tables are D_EMPLOYEE, D_PROPERTY, and D_TIMELINE. The fact table is F_SERVICE_HOURS, and the fact table measure is HoursWorked. The following is a diagram of the tables in the Garden Glory data warehouse. These tables contain the following columns: D_EMPLOYEE(EmployeeID, LastName, FirstName, ExperienceLevel) D_PROPERTY(PropertyID, PropertyName, City, State, Zip) D_TIMELINE(TimeID, Date, MonthID, MonthText, QuarterID, QuarterText, Year) F_SERVICE_HOURS( TimeID, PropertyID, EmployeeId, HoursWorked) Create the SQL for the Extract, Transform, and Load (ETL) for the Garden Glory Data Warehouse Consider what transformations will be needed to load the data from the Garden Data operational data store into the Garden Glory data warehouse. Write the SQL statements to load the data from the ODS tables into the data warehouse tables. ETL Specifications D_TIMELINE Table For each unique date in the ODS PROPERTY_SERVICE table create a row in the data warehouse D_TIMELINE table. The TimeID column is the primary key. Calculate the value of the TimeID column using the Excel DATEVALUE function. For example, the result of =DATEVALUE(04-OCT-17) is 43012. MIS 315 Project Fall 2018 Page | 3 MonthID the integer value of the month (for example, January = 1). Calculate the value of this column using the SQL MONTH function on the PROPERTY_SERVICE.ServiceDate column. For example, the result of MONTH(04-OCT-17) is 4. MonthText the name of the month. Calculate the value of this column using the DATENAME SQL function on the PROPERTY_SERVICE.ServiceDate column. For example, the result of DATENAME(mm, 04-OCT-17) is October. QuarterID the integer value of the quarter. Calculate this column by using the DATEPART SQL function on the PROPERTY_SERVICE.ServiceDate. For example, the result of DATEPART(qq, 04OCT-17) is 4. QuarterText a character representation of the quarter. Calculate the value for the QuarterText column by concatenating the literal string Qtr with the result of the DATEPART function on the PROPERTY_SERVICE.ServiceDate. Note that the result of the DATEPART function must be converted to a VARCHAR in order to be concatenated with the string Qtr. For example, the result of Qtr + CAST(DATEPART(qq, 04-OCT-17) AS VARCHAR(2) is Qtr4. Year the four-digit integer of the year. Calculate the value for the Year column by using the DATEPART SQL function on the PROPERTY_SERVICE.ServiceDate. For example, the result of DATEPART(yyyy, 04-OCT-17) is 2017. D_PROPERTY Table For each row in the ODS OWNED_PROPERTY table create a row in the data warehouse D_PROPERTY table. PropertyID is the primary key. Use the surrogate key value in OWNED_PROPERTY for the value of PropertyID in D_PROPERTY. D_EMPLOYEE Table For each row in the ODS EMPLOYEE table create a row in the data warehouse D_EMPLOYEE table. EmployeeID is the primary key. Use the surrogate key value in EMPLOYEE for the value of EmployeeID in D_ EMPLOYEE. F_SERVICE_HOURS Table For each row in the ODS PROPERTY_SERVICE table, create a row in the data warehouse F_SERVICE_HOURS table. This table has a composite primary key composed of TimeID, PropertyID, and EmployeeID. The HoursWorked column is set to the value of PROPERTY_SERVICE.HoursWorked. The F_SERVICE_HOURS table has the following foreign key constraints: The TimeID column must exist in D_TIMELINE. The PropertyID column must exist in D_PROPERTY. The EmployeeID column must exist in D_EMPLOYEE. Create the SQL to Query the Garden Glory Data Warehouse Write and execute SQL queries in the GG data warehouse that produce the following result sets: HoursWorked by all employees in June 2017 HoursWorked in the property that had the most hours worked MIS 315 Project Fall 2018 Page | 4 HoursWorked by all employees in the city of Bellevue Washington in the first quarter of 2017 HoursWorked by senior level employees in the second quarter of 2017 grouped by city and state After you complete these queries in the GG data warehouse, create queries that produce the same result set in the GG operational data store. Are these queries more difficult in the operational data store? Project Deliverables Project teams are expected to complete the following: Create the Garden Glory operational data store (ODS) and data warehouse tables. Insert data into the Garden Glory ODS tables. Create SQL script(s) for the extract, transform, and load (ETL) process for the Garden Glory data warehouse tables. Successfully run the ETL process to load data into the Garden Glory data warehouse tables. Results of running the assigned queries against the Garden Glory data warehouse and ODS tables. SQL scripts for creating tables, loading table, and running queries. PowerPoint presentation about the project