A01_MLuong - mluong56/BIDD330_Spring2024 GitHub Wiki

Module 01 Assignment

Assignment 01 is an introduction to Power BI utilizing the class dataset hosted on Azure SQL Server (this is a dataset your professor built). This dataset can be easily searched for utilizing a search engine and entering in Microsoft Bing Covid Dataset Update 2025: The dataset was moved to GutHub than Azure Open Datasets. You can find where the dataset is located by reading the final.

The dataset is hosted by Microsoft Corporation, one of the largest tech companies in the world. However, even the largest company in the world has problems with their data. As developers, we use an ETL process to meet the needs of our customers. Your homework includes a technical screen as a Business Intelligence Developer. This is meant to mimic the workplace. The technical screen is part of your homework too. In fact, the technical screen is a real-life screen to be a BI Developer 3 at Microsoft as a contractor. The last part of the homework is introducing yourself to the class. We have a group project and want everyone to be aware of their teammate’s strengths. The assignment deliverables which are due by midnight the next class. The format is not changed between the other two course you took (BIDD 310 & BIDD 320)

  1. Read Some Articles
  2. Turn in the Power BI HW1 – 40 points
  3. Turn in the Screen. – 25 points
  4. Turn in a paper about what you learned. – 20 points
  5. Post your introduction to the canvas discussions introductions – 15 points
  6. Submit your work to the Canvas site.

This activity will take you about 3 to 4 hours, so plan accordingly! READ SOME ARTICLES I have included some articles that covers Power BI • Power BI https://powerbi.microsoft.com/en-us/what-is-power-bi/ • DAX Query Language (go through tutorial) https://docs.microsoft.com/en-us/power-bi/guided-learning/introductiontodax?tutorial-step=1 Graded Turn in the Power BI HW1 – 40 points Turn in the Screen. – 25 points Turn in a paper about what you learned. – 20 points Post your introduction to the canvas discussions introductions – 15 points

CREATE A POWER BI DESTOP APPLICATION

Please build your file structure as follows

Deploy – where you publish to Power BI .com service Dev – as you build your pbi. I save once every 30 minutes Prod – when u download from Power Bii .com service and make updates Model – is just optional. This is where I saved my screenshot

Step 1: download Power BI Desktop Application https://powerbi.microsoft.com/en-us/desktop/ Step 2: Setup a Github and fork the Bing Covid Dataset and fork BIDD320_Fall • timothyjpauley/BIDD330_Spring2024: This is a dedicated Wiki Page to help student learn (github.com) • timothyjpauley/Bing-COVID-19-Data: A repo for coronavirus related case count data from around the world. The repo will be regularly updated. (github.com) Instructor: Tim Pauley GitHub: https://github.com/timothyjpauley *Please follow me so you can receive updates on the Covid Bing Dataset

Bing Covid Dataset: https://github.com/microsoft/Bing-COVID-19-Data Step 3: Review SQL Data on the Databases on the PROD SQL Server.

Note: We don’t recommend you use a work computer on a domain. Yet if you are and can’t get it connected. You will need to turn off the VPN or domain to connect

Server Name: uwc-studentsql.continuum.uw.edu\uwcbiddsql Username: BICert Password: BICert Standard B8ms (16 vcpus, 32 GiB memory) Database: Black_Unemployment

  1. Database name: Black_Unemployment a. below are the objects you will need to bring into PBI. You will not need the objects in green. Please do not execute the objects in red i. View FactCovid: [dbo].[vw_Fact_Covid_2023] ii. FactUnemployment: [dbo].[Fact_Unemployment] iii. DimState: [dbo].[Dim_US_State] iv. DimCountry: .[dbo].[DIM_Country] v. DimDate: [dbo].[DimDate]
  2. Below I included the objects in red I used to build the database. You are welcome to generate the scripts for reference. However, they are very large, and we have a limited budget for Azure. Please do not execute them. Green tables are for your first homework assignment.

Step 4: Connect tables and view using SQL Server. You can do this by accessing the class server and entering in the SQL Server credentials

Select: Database User Name: BICert Password: BICert Step 5: Bring all the data together in the Power BI Application. The model should look like the below screen shot. Here are your joins

Homework output

Below are some hints for you: DAX Code • Please use variables to name your functions. This should be done four times for each table ***Note: There are some spelling errors. Pls fix or do not. In class I will show you how quickly DAX can fix those problems

Dim Country Total Row Count = VAR total = CALCULATE(COUNTROWS(DimCountry)) RETURN IF( total = BLANK(),0,total)

Dim Date Total Count Of Month = VAR _start = STARTOFYEAR( FactCovid[Updated]) -1 VAR _end = ENDOFMONTH( FactCovid[Updated]) VAR MonthCount = DATEDIFF( _start, _end, MONTH ) + 1 RETURN IF (MonthCount = BLANK(),0,MonthCount)

Dim Date Total Row Count = VAR dimdaterow = COUNTROWS(DimDate) RETURN dimdaterow

Dim State States Count = VAR statecount = CALCULATE(COUNT(DimStates[State])) RETURN statecount

Dim State Total Row Count = VAR total = CALCULATE(COUNTROWS(DimStates)) RETURN IF( total = BLANK(),0,total)

Fact Covid Country Count = VAR countCountry = CALCULATE(COUNT(FactCovid[Country_Region])) RETURN IF(countCountry = BLANK(),0,countCountry)

Fact Covid Country Distinct Count = VAR countCountry = CALCULATE(DISTINCTCOUNT(FactCovid[Country_Region])) RETURN IF(countCountry = BLANK(),0,countCountry)

Fact Covid States Distinct Count = VAR statecount = CALCULATE(DISTINCTCOUNT(DimStates[State])) RETURN statecount

Fact Covid Total Row Count = VAR binngRow = COUNTROWS(FactCovid) RETURN binngRow

Fact Unemployment Total Row Count = VAR unemploymentcount = COUNTROWS ( FILTER ( FactCovid, --when there is a match from this table CONTAINS ( FactUnemployment, [State], FactCovid[Admin_Region_1] ) --otherwizard return 0 ) ) RETURN IF(unemploymentcount = BLANK(),0,unemploymentcount)

Total Row Count of Entrie Dashboard = VAR totalrowcount = CALCULATE( [Fact Covid Total Row Count] +[Fact Unemployment Total Row Count] +[Dim Date Total Row Count] +[Dim State Total Row Count] +[Dim Country Total Row Count] ) RETURN totalrowcount

Submit to PBI & Publish

File name: BIDD330 HW1 canvasid Example: BIDD330 HW1 tjpauley <>

Link I need to grade your homework https://app.powerbi.com/groups/777f92d5-bef3-4145-92de-7ca2a66d192b/reports/8fab13e0-6eb0-4e40-8e8f-633aff52fe43/ReportSection?experience=power-bi

WRITE A PAPER Create a Word document that outlines how you built your Power BI Application. This document should be written as a technical support document that new hires can read to understand what the process does and technicians might use to troubleshoot the process. Include 2 minimal screenshots, please. The document should be about 2 to 4 pages (including any pictures) in length. Please use Microsoft Word or something compatible with your paper. As you do so, capture screenshots of these and place them in your Word document. GitHub Wiki Submission: Important. The last step is to copy and paste the document into this website: Home · timothyjpauley/BIDD330_Spring2024 Wiki (github.com)

Below is a screenshot of how to do this. There is a video at HOME where a youtuber explains how to do this step by step

After you ether create the module or fork it, we will copy/paste our work doc into the module 1

You can override the picture. Each week we will submit the write up here. In the comments please copy and paster the link into the comments. You instructor will demonstrate this in class Module 1: Intro to Power BI, Tableau, & Jupyter Notebook · timothyjpauley/BIDD330_Spring2024 Wiki (github.com) SUBMIT A FILE TO THE CANVAS SITE After you have created your updated Assignment01YourFnameLname, place them into one folder and ZIP that folder. Then upload the resulting ZIP file to the Canvas web site.

You’re done! To make things easy for me to grade I would like your file structure like this