CIS 355
Data Warehousing and Dimensional Modeling
Spring 2022
Assignment #1 – 25 Points
See Canvas and/or course schedule for due date/time
Situation:
You work for a retail company that has stores and operations across Arizona and California. 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 company’s 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, California 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 California.
3. Customer Relationship Master System, California Region – this application contains a master list of all of the company’s customers who live in the California 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 company’s customers who live in provinces and territories that are included in the Arizona Region.
5. In-Store Sales System, California Region – Each of the company’s stores has a point-of-sale (POS) system that handles all product purchases, payments, returns, and refunds. All of the stores in the company’s California Region feed data daily from each POS system to a centralized In-Store Sales System for the entire California Region, which summarizes all sales within the California 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 California (combined) – This application handles all Internet sales for all customers regardless of where they live in Arizona or California, and is a separate sales system than either of the in-store sales systems described above. (For purposes of this assignment, assume that only California and Arizona residents can purchase products online.)
_____________________________
Question #1 (15 Points)
For the scenario described above, draw the high-level architecture from the listed source systems through data marts for an enterprise data warehousing
environment that follows the Corporate Information Factory (CIF) architecture approach. Make sure you clearly show your ETL and data delivery data flows from each source to each target. You will decide which specific data marts your environment will include.
After you draw your diagram, you will then:
• List and describe at least two (2) very specific advantages of the CIF approach for enterprise-scale data warehousing for this company, versus the federated approach (not generalities about data warehousing benefits, etc.)
• List and describe at least two (2) very specific challenges/disadvantages of the CIF approach for enterprise-scale data warehousing for this company versus the federated approach (not generalities about data warehousing challenges, etc.)
Question #2 (15 points)
Now presume that your company’s 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: for this question and also for Question #1, defining the data marts might be trickier than you think!)
After you draw your diagram, you will then:
• List and describe at least two (2) very specific advantages of the Federated EDW approach for enterprise-scale data warehousing for this company versus the CIF approach (not generalities about data warehousing challenges, etc.)
• List and describe at least two (2) very specific challenges/disadvantages of the Federated EDW approach for enterprise-scale data warehousing for this
company versus the CIF approach (not generalities about data warehousing challenges, etc.)
Submission Format
Your choice: for example, a Microsoft Word document with your diagrams prepared in PowerPoint and then pasted into your Word document. Or you may use any other software as well. If you use anything other than Microsoft Word and/or PowerPoint, save your file as a PDF and upload your PDF.
