(Solved): CIS 355: Business Data Warehousing and Dimensional Modeling | Assignment 3 (individual work) ...
CIS 355: Business Data Warehousing and Dimensional Modeling
(Term: Spring 2021, BDA Program)
Assignment 3 (individual work)
1. In your Assignment 2, you were asked to create a visualization (a chart) of total sales by different product categories over years using data from the data warehouse that you have created in class. You were given the option of creating the chart using any tool of your choice. Many of you (including myself) exported the data from SQL MS to Excel, re-arranged/formatted the data, and plotted the chart. That approach works well, but involves a few steps. In this assignment, re-create a report and a visualization using the same data as that in Assignment 2, but using the tool PowerBI. Copy and paste your report and visualization below.
2. Using the sample “AdventureworksDW2014” data warehouse, generate a matrix report (using powerBI or another visualization tool such as Tableau), which contains the total internet sales amount by calendar year in different sales territory regions for different products (using English Product names). In your report, make territory region as your columns, calendar year and product name as your rows. A partial report looks like:
Filter your report to include total sales amount for only product “Mountain-200 Silver, 38” and “Road-250 Black, 58” in calendar year 2012 in the US regions (Central, Northeast, Northwest, Southeast, Southwest). Copy and paste your report below.
3. Create a vertical Stacked bar chart of total internet sales by calendar year and product line. Copy and paste your chart below.
4. Create an impact bubble chart (using scatter chart), plotting “average of product cost” on x-axis, “tax amount” on y-axis, “total sales” as bubble Size, “sale territory region” as the legend, calendar year as the “play axis”. Filter the sales data in only four countries “Australia”, “Canada”, “France”, and “Germany”. Copy and paste your bubble chart in year 2013 below.
5. [For you to think]Use the data cube “zzhan277 CIS355DW” developed in class, and write a MDX query to find internet order quantities (on Columns) by cities (on Rows) for calendar year “2012’’. Copy and paste your MDX query as well as the first 10 rows below.