Benchmark – Build Analysis Solution Using PowerBI

Assessment Description

Step 1:

The purpose of Step 1 is to build, visualize, and interpret data models using Power BI. 

Review the Canyon Sales dimensional model you built in the Topic 1 assignment “Design a Relational Schema” and perform the following Power BI: 

1. Load the Canyon Sales database into Power BI. 4 dimensions and a fact should be loaded.

2. Use bar chart to visualize units sold by product name. Take a screenshot of the bar chart to be used in Step 3. 

3. Use bar chart to visualize units sold by year. Take a screenshot of the bar chart to be used in Step 3.

4. Use bar char to visualize units sold by store region name. Take a screenshot of the bar chart to be used in Step 3.

5. Use bar chart to visualize units sold by store region name and product vendor name. Take a screenshot of the bar chart to be used in Step 3.

Step 2:

In this step, the student will learn how to perform feature extractions using DAX in Power BI.

In Power BI, create a table named Calendar with the following columns:

· Date column populated with the most recently completed 2 years’ worth of data from January 1st to December 31st.

· Month column to display the name of the month.

· Year column to display the year.

· Month number to display the number of the month (e.g., 1 for January).

· Week number to display the number of the week (e.g., Jan 1 to Jan 7 is week number 1).

· Weekday to display the name of the day (Jan 01 has a weekday name of Sunday).

· Weekday number to display the day number of the weekday (ex: Sunday has a weekday number 7).

· Workday: display 1 if it is a workday, otherwise 0.

Take a screenshot that shows all the information from Step 2 to be used in Step 3.

Step: 3

Write an academic paper, using Microsoft Word, presenting your models and extractions from Steps 1 and 2. Submit your paper through the digital classroom.

The paper will include the following:

· An introductory paragraph

· Presentation, explanation, and interpretation of each of bar chart (Step 1: b-e) as if this report were being presented to your manager and the team. Include the screenshots of each bar chart along with their explanations.

· Presentation of an excerpt of your calendar that shows all the information from Step 2. Include the screenshot.

· A concluding paragraph

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

Rubric Criteria

Expand All Rubric CriteriaExpand All

Introduction

3.25 points

Visualize Units Sold by Product Name and Screenshot with Interpretation (B)

9.75 points

Visualize Units Sold

Date: 02/04/2023

SYM-408 Design a Relational Schema

Schema:

— create a table

CREATE TABLE category (

categoryID TEXT PRIMARY KEY,

categoryName TEXT NOT NULL

);

CREATE TABLE vendor (

vendorID TEXT PRIMARY KEY,

vendorName TEXT NOT NULL

);

CREATE TABLE product(

productID TEXT PRIMARY KEY,

productName TEXT NOT NULL,

price INTEGER,

vendorID TEXT references vendor(vendorID),

categoryID TEXT references category(categoryID)

);

CREATE TABLE region (

regionID TEXT PRIMARY KEY,

regionName TEXT NOT NULL

);

CREATE TABLE store (

storeID TEXT PRIMARY KEY,

storeName TEXT NOT NULL,

regionID TEXT references region(regionID)

);

CREATE TABLE customer (

customerID TEXT PRIMARY KEY,

customerName TEXT NOT NULL,

zipCode TEXT NOT NULL

);

CREATE TABLE salestransaction (

transID TEXT PRIMARY KEY,

customerID TEXT references customer(customerID),

storeID TEXT references store(storeID),

transDate date NOT NULL

);

CREATE TABLE soldvia (

productID TEXT references product(productID),

sales TEXT references salestransaction(transID),

quantity INTEGER NOT NULL

);

Queries:

1. For each product being sold: product ID, product name, and price

Query:

select p.productID, p.productName, p.price from product p join soldvia s on s.productID = p.productID;

Result:

1. For each category of product: Category ID and Category name

Query:

select categoryID, categoryName from category;

Result:

2. For each vendor: Vendor ID and Vendor Name

Query:

select vendorID, vendorName from vendor;

Result:

3. For each customer: Customer ID, name, and Zip code

Query:

select customerID, customerName, zipCode from customer;

Result:

4. For each store: Store ID, and Zip Code

Query:

select storeID, storeName from store;

Result:

5. For each region: Region ID and Region Name

Query:

select regionID, regionName from region;

Result:

6. For each Sales Transaction: Tran