(Solved): CIS 355: Assignment 2 : 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 2 (individual work)

Submission instructions: 

• Please upload a document with your SQL queries as well as query results, charts, or graphs to Canvas

1.    In your Assignment 1, you were asked to write an SQL query to summarize total sales by different product categories over years. Now, using your results from your query and plot a chart of total sales with x-axis being the years (2010-2014) and y-axis being the total sales. Your chart should look like the following but with all three product categories (accessories, clothing, bikes) and total sales. Copy and paste your chart below, and briefly discuss the step-by-step instructions you followed to create your chart.

2.    Use your data warehouse “YourASURITE” to generate a report of total internet sales amount for different products (using English product names) by calendar year. Sort your report by ProductName first, then by CalendarYear. Please copy/paste your SQL query as well as your query results (top 10 rows only) below.

3.    Add calendar quarter in your report from (2). Again sort your report by ProductName first, then by CalendarYear, finally by CalendarQuarter. Please copy/paste your SQL query as well as your query results (top 10 rows only) below. What is the BI concept or terminology for the operation from (2) to (3)? Assuming your report in (2) is correct, what would you check to make sure your report in (3) is consistent with that in (2)?

4.    Use our sample data warehouse “AdventureworksDW2014” to generate a report of total internet sales amount for different product lines (using ProductLine) by calendar year and sales territory country. You will need to use the following tables: DimDate, DimProduct, DimSalesTerritory, and FactInternetSales. Sort your report by ProductLine first, then by CalendarYear, finally by Territory country. Please copy/paste your SQL query as well as your query results (top 10 rows only) below.

5.    [For you to think] Would you be able to create a map with the above data in report (4) overlay on top of the world map (with Total Sales as bubble size, Product Line as categories in each bubble) like something below?