1 CIS 355 Data Warehousing and Dimensional Modeling Summer 2022 Assignment #1 30 Points See Canvas and/or course schedule for due date/time Submission Format Your choice: for example, a Microsoft Word document with your diagrams prepared in PowerPoint or a similar drawing program. If you use anything other than PowerPoint (Visio, draw.io, etc.), save your file as a PDF and upload your PDF. If you use PowerPoint, you may submit either a native PPTX file or convert to PDF to upload; your choice there. YOU ARE NOT PERMITTED TO HAND-DRAW YOUR DIAGRAMS AND SUBMIT A HAND-DRAWN SCANNED DOCUMENT IF YOU DO, YOU WILL RECEIVE A ZERO FOR THIS ASSIGNMENT. YOU MUST USE POWERPOINT, VISIO, DRAW.IO, OR A SIMILAR ONLINE DRAWING TOOL AND THEN SUBMIT EITHER A PPTX OR PDF FILE.

Situation: You work for a retail company that has stores and operations across Arizona and Colorado. Currently your company has no data warehouses, data marts, operational data stores, or any other integrated stores of data used for reporting, BI, and analytics. Your company also has an Internet presence with a website from which customers can purchase products. For purposes of this assignment, you are interested in the following seven applications:

1. Supply Chain Operations, Arizona Region this application manages all supply chain functions for the companys Arizona Region. The functions managed by this application include materials and inventory management; vendor management; materials ordering and defective material return; shipping and logistics; warehouse management; etc.

2. Supply Chain Operations, Colorado Region this application is identical to the Supply Chain/Arizona Region application described above, except that it performs the supply chain functions for all of Colorado.

3. Customer Relationship Master System, Colorado Region this application contains a master list of all of the companys customers who live in the Colorado Region. For each customer we also a complete sales history, the names of their top officers and key employees, and other important information that gives us a complete picture of everything we need to know about that customer.

4. Customer Relationship Master System, Arizona Region this application is identical to the one described above, except that it contains the aforementioned information about all of the companys customers who live in provinces and territories that are included in the Arizona Region.

5. In-Store Sales System, Colorado Region Each of the companys stores has a point-of-sale (POS) system that handles all product purchases, payments, returns, and refunds. All of the stores in the companys Colorado Region feed data daily from each POS system to a centralized In-Store Sales System for the entire Colorado Region, which summarizes all sales within the Colorado Region and also maintains a history of past sales as well.

6. In-Store Sales System, Arizona Region This application is identical to the one described above, except that it consolidates and organizes sales data from all of our stores in the Arizona Region.

7. Online Sales System, Arizona and Colorado (combined) This application handles all Internet sales for all customers regardless of where they live in Arizona or Colorado, and is a separate sales system than either of the in-store sales systems described above. (For purposes of this assignment, assume that only Colorado and Arizona residents can purchase products online.) _____________________________

Question #1 (15 Points) For the scenario described above, draw the high-level architecture for a centralized enterprise data warehouse (EDW).

Grading Rubric: Grading Criteria Points for Full Credit Correct source systems 5 Correct ETL lines 3 Correct data warehousing environment structure 7

Question #2 (15 points) Now presume that your companys Chief Information Officer (CIO) is a strong believer in smaller-scale efforts rather than large, enterprise-intensive ones such as CIF or the Dimensional Data Warehouse Bus Architecture. The CIO directs you to define the architecture for a Federated Enterprise Data Warehouse comprised of multiple independent data marts. Draw the high-level architecture from the source systems listed at the beginning of this assignment through the data marts for a Federated EDW. Make sure you clearly show your ETL data flows from each source to each target. You will decide which specific data marts your environment will include. (Hint: defining the data marts might be trickier than you think they need to make sense!)

Grading Rubric: Grading Criteria Points for Full Credit Correct source systems 5 Correct ETL lines 3 Correct data warehousing environment structure 7