(Solved): CIS 355: Business Data Warehousing and Dimensional Modeling (Term: Spring 2021, BDA Program)...

CIS 355:  Business Data Warehousing and Dimensional Modeling 
(Term: Spring 2021, BDA Program)

Assignment 1 (individual work)

1.    In this assignment, you need to continue building your DW (named YourASURITE) on the SQL Server. Your finished DW will include 4 tables (an InternetSales fact table and three dimension tables: Customers, Dates, and Products). The star schema of your DW will look like below.

Please refer to the lecture notes “CIS 355 - DW hands-on CreateDW&ETL.pptx” slides 9-15 for the SQL scripts to create your DW.
2.    After you build your DW in (1), you need to load data into your DW tables. Please refer to the lecture notes “CIS 355 - DW hands-on CreateDW&ETL.pptx” slides 17-21 for the SQL scripts to load data into your DW (4 tables).  When running the SQL scripts, please pay attention to (1) the order of the 4 tables where data will be inserted, and (2) execute the SQL scripts only once; otherwise you will have repeated observations (with just different SK fields).

3.    Develop an SQL query to find out the order quantity of various products (using product name) by customers (including customer name, occupation, and age). Your query results need to be first sorted by customer name, then by product name.  Please copy/paste your SQL query as well as your query results (top 10 rows only) below.

4.    Write an SQL query to summarize total sales by different product categories over years. Your total sales information needs to be first sorted by product categories, then by years. Please copy/paste your SQL query as well as your query results below.

Submission instructions: 

• For part (1) and (2) above, you do not need to submit anything with this document; we will check your DW tables to grade your work.
• If successfully loaded, Customers table will have 18,484 records; Dates table will have 3,652 records; InternetSales table will have 60,398 records; and Products table will have 397 records.