100|SQL&POWERBI – Bike Shop Income Statement

[Andrew]

BYU Student Author: @Andrew
Reviewers: @Parker_Sherwood, @Alex_Garrett
Estimated Time to Solve: 60 Minutes

We provide the solution to this challenge using:

• SQL Azure Data Studio
• Power BI

Need a program? Click here.

This is an add-on to Weekly Challenge 69|Bike Shop Shipping Delays.

Overview
You are an accountant for a small bicycle company. Your company owns and operates three different locations. Recently, you received an email from the company’s COO asking for a table of all available inventory located in each store. You completed creating the report and placed it into a view (completed in Weekly Challenge 82|Bike Shop Available Inventory), but need to create a dashboard, so Power BI employees can view the table.

She also asked for a dashboard displaying a waterfall chart of the company’s gross revenue, discounts, and cost of goods sold for each store by year. You know that your company list price for products are at a 10% markup from cost.

Instructions
First, follow the instructions below to install Azure Data Studio and then load the database. If you have already installed Azure Data Studio and setup a local instance of Microsoft SQL Server, then skip to loading the database. If you have already loaded the database from Weekly Challenge 69|Bike Shop Shipping Delays, then skip to the next instructions.

Azure Data Studio Installation

1. Install Microsoft SQL Server (if not already installed) found here. Download the developer version (seen in the image below) and install package. When prompted select the basic installation package.
   

2. Install Azure Data Studio (if not already installed). Installation instructions can be found here.

3. Open Azure Data Studio and set up a new local server instance (if not already done):
   

   

   

Database Loading Instructions

1. Download the database setup file: Challenge100_Database.sql.
2. Open the database setup file with Azure Data Studio, make sure the selected server is localhost, and run the file. Running the file will create a database named BikeStores, then create tables and insert data into those tables.

For more information on the database structure, tables, and fields see SQLServerTutorial.net’s data dictionary here.

This database, ERD, and data dictionary are all owned by SQLServerTutorial.net and used within this challenge under their terms of use.

Second, review the entity-relationship diagram for the BikeStores database. The ERD will be useful in writing your queries.

Third, create a table displaying the available inventory in each store for employees to view in Power BI. Your final dashboard should look like this:

Detailed Instructions:

Create View

• You will use a view created in Weekly Challenge 82|Bike Shop Available Inventory. If you have not completed this challenge, you should try it first. Regardless, run this file to create the view you will use to connect to Power BI with: Challenge100_Available_Inventory.sql.

Connect to Power BI

• In Power BI, use the “Import data from SQL Server Option” to connect to the BikeStores database. When prompted provide the name of the server (if created using the instructions above, it will be ‘localhost’) and the ‘BikeStores’ database. Select the ‘production.available_inventory’ view.

Create the Visualization

• The brand name and product name are slicers. The rest of the columns are displayed in the image above.

Fourth, create a visualization in power BI to demonstrate revenue, discounts, cost of goods sold, and gross profit in a waterfall chart. Your final dashboard should look like this:

There are many ways of creating this chart, but these instructions walk through one way of making it.

Detailed Instructions:

Create SQL View of Income Statement

• First, you need to write a select query in SQL server to retrieve the total amount of gross revenue (list price * quantity), discounts (discounts*quantity), and cost of goods sold (remove the 10% markup from list price) for each year by store.

• Second, create a view of this table named ‘sales.income_statement’. The view should have 5 columns: Store Name, Year, Gross Revenue, Discounts, and Cost of Goods Sold.

Connect to Power BI

• Similar to the last visualization you created, connect to SQL server and select the ‘sales.income_statement’ view (it should appear in the list of tables).

Unpivot Your Data

• Select the ‘Home => Transform Data’ option. Select the ‘income_statement’ table and highlight every column except store name and year. Select the unpivot option under the “Transform” tab in the “Any column” group.

Create the Visualization

• The store name and year are slicers. The chart type is waterfall. The y-axis data is the ‘value’ field, and the category option is the ‘attribute’ field.

Solution

Solution Code

CREATE VIEW sales.income_statement 
AS 

SELECT  
    store_name 
    , YEAR(order_date) AS year 
    , SUM(list_price*quantity) AS 'Gross Revenue' 
    , -SUM(discount*quantity) AS 'Discounts' 
    , -SUM(list_price/1.1) AS 'Cost of Goods Sold' 
    FROM  
        sales.orders so 
        JOIN sales.order_items soi ON so.order_id = soi.order_id 
        JOIN sales.stores ss ON so.store_id = ss.store_id 

    GROUP BY store_name, YEAR(order_date) 
; 

Challenge100_Solution.pbix
Solution Video: Challenge 100|SQL&POWERBI – Bike Shop Income Statement