R & Power BI

In this exercise, you will embed R into Power BI to analyze industry employment data. The following steps are the same as the previous exercise. Make sure you load the same data again:

Step 1: Use Embedded SQL to Import Data

Click Get Data, then choose MySQL.

Enter the following connection details:

Server: dadata.bensresearch.com

Database: industry

Click Advanced Options to embed custom SQL queries. Paste and run each of the queries below one at a time:

SELECT TSE.DataYear, TSE.StateShort, SUM(HHI.Herf * ISE.Emp / TSE.Emp) AS WHHI FROM (SELECT StateShort, DataYear, SUM(ExpEmpValue) AS Emp FROM StateData WHERE IndustryCodeLength = 6 GROUP BY StateShort, DataYear) AS TSE JOIN (SELECT StateShort, DataYear, IndustryCode, ExpEmpValue AS Emp FROM StateData WHERE IndustryCodeLength = 6) AS ISE ON TSE.StateShort = ISE.StateShort AND TSE.DataYear = ISE.DataYear JOIN HHI ON ISE.DataYear = HHI.DataYear AND ISE.IndustryCode = HHI.IndustryCode GROUP BY TSE.DataYear, TSE.StateShort ORDER BY WHHI

SELECT * FROM HHI

SELECT * FROM StateData

SELECT StateShort, DataYear, SUM(ExpEmpValue) AS Emp FROM StateData WHERE IndustryCodeLength = 6 GROUP BY StateShort, DataYear

Rename your resulting tables as follows:

WHHI (for the weighted HHI results)

HHI

StateData

StateEmp (total employment by state)

Step 2: Import NAICS Labels

Click Get Data again.

Browse for the file NAICS.csv and load it.

Your result should look like this:

Step 3: Using R

Once you have these results. Select the R visualization. Drag only DataYear, ExpEmpValue and IndustryCodeLength to it. Then write the code below, which transforms and plot the data:

library(sqldf)
plot

Question

R & Power BI

In this exercise, you will embed R into Power BI to analyze industry employment data. The following steps are the same as the previous exercise. Make sure you load the same data again:

Step 1: Use Embedded SQL to Import Data

Click Get Data, then choose MySQL.

Enter the following connection details:

Server: dadata.bensresearch.com

Database: industry

Click Advanced Options to embed custom SQL queries. Paste and run each of the queries below one at a time:

SELECT TSE.DataYear, TSE.StateShort, SUM(HHI.Herf * ISE.Emp / TSE.Emp) AS WHHI FROM (SELECT StateShort, DataYear, SUM(ExpEmpValue) AS Emp FROM StateData WHERE IndustryCodeLength = 6 GROUP BY StateShort, DataYear) AS TSE JOIN (SELECT StateShort, DataYear, IndustryCode, ExpEmpValue AS Emp FROM StateData WHERE IndustryCodeLength = 6) AS ISE ON TSE.StateShort = ISE.StateShort AND TSE.DataYear = ISE.DataYear JOIN HHI ON ISE.DataYear = HHI.DataYear AND ISE.IndustryCode = HHI.IndustryCode GROUP BY TSE.DataYear, TSE.StateShort ORDER BY WHHI

SELECT * FROM HHI

SELECT * FROM StateData

SELECT StateShort, DataYear, SUM(ExpEmpValue) AS Emp FROM StateData WHERE IndustryCodeLength = 6 GROUP BY StateShort, DataYear

Rename your resulting tables as follows:

WHHI (for the weighted HHI results)

HHI

StateData

StateEmp (total employment by state)

Step 2: Import NAICS Labels

Click Get Data again.

Browse for the file NAICS.csv and load it.

Your result should look like this:

Step 3: Using R

Once you have these results. Select the R visualization. Drag only DataYear, ExpEmpValue and IndustryCodeLength to it. Then write the code below, which transforms and plot the data:

library(sqldf)
plot <- sqldf("SELECT DataYear, SUM(ExpEmpValue) AS Emp FROM dataset WHERE IndustryCodeLength = 6 GROUP BY DataYear")
library(ggplot2)
ggplot(plot, aes(x = DataYear, y = Emp)) +
  geom_line(color = "steelblue", size = 1) +
  geom_point(color = "steelblue", size = 2) +
  labs(title = "Employment Over Time",
    x = "Year",
    y = "Employment"
  ) +
  theme_minimal()

Question: Inspect the resulting graph. Which year had the lowest number of manufacturing jobs nationwide? Ignore years not included in our dataset.

BlackTom AI · Accepted Answer

Initial observation: The question asks to inspect the resulting graph and identify the year with the lowest number of manufacturing jobs nationwide, while noting to ignore years not present in the dataset.

Since the provided data for answer options is empty, there are no explicit choices to evaluate one by one. In situations like this, you should rely on the visual output (the plotted line) to determine which DataYear has the minimum Emp value.

Key idea 1: Read the plotted line accurately. The graph represents Employed values (Emp) over DataYear, so the year with the smallest point along the y-axis corresponds to the minimum employment in manufacturing across the years included in the data.

Key idea 2: Consider only years present in the dataset. The instruction explicitly says to ignore years not included in the dataset, so you should not infer values for missing years or extrapolate beyond the plotted range.

Key idea 3: Be mindful of formatting in the provided answer. The given answer appears as "2，012" with a non-standard comma character; this is intended to represent 2012. When entering numerical answers, ensure you use the standard ASCII digits and punctuation to avoid parsing issues in a system that expects a numeric year.

Practical steps to identify the year on the graph:
- Locate the lowest point on the y-axis (Emp) along the plotted line.
- Read the corresponding DataYear from the x-axis directly beneath that point.
- If multiple years share the same minimum value, note all tied years that appear in the dataset and within the plotted range.

Common potential pitfalls:
- If the dataset skips certain years, do not assume those years exist; only consider the years that appear on the x-axis of the graph.
- Ensure you are looking at the correct metric: the plot shows Employment (Emp) over Time, so the minimum refers to manufacturing employment, not necessarily total employment or other metrics.
- A misread of the axis could lead to selecting the wrong year; zooming or cross-checking the data source (StateData with IndustryCodeLength = 6) can help verify values if you have access to the underlying data table.

If you can provide the actual list of answer options, I can map each option to a justification and indicate which option correctly corresponds to the year with the lowest manufacturing jobs according to the graph. If the intended answer is 2012, as the string "2，012" suggests, the reasoning would be that the plot shows the minimum Emp value at DataYear = 2012 among the years present in the dataset.

ECON3310001.1251 Extra Credit Assignment: Power BI & SQL

View Explanation

Log in for full answers

Similar Questions

What sort of chart could you create to compare data, where the data does not have to be in any specific order?

Lee needs to create a chart comparing sales for 21 regions. What sort of chart would be best?

When creating a dashboard for storytelling, which of the following is the best practice?

True or false? A chart is a visual representation of the data that you have selected.

A large retail chain's quarterly report contains hundreds of pages of tables showing sales data by product, region, and store. Executives struggle to quickly grasp overall performance and identify key trends. Which benefit of data visualization would most directly address this challenge?

What are pie charts, bar graphs, line graphs, and funnels examples of?

There are seven types of data stories. Which type of story is shown in the example below?

More Practical Tools for Students Powered by AI Study Helper

Homework AI Solver

Stylized AI Paper Writer

Plagiarism Checker Assistant

Citation AI Academic Writing Tool

In-Class Translation Assistant

AI Note Generator

AI Quiz Answers

Past Exam Questions from University Test Bank

Smart Practice Assistant

Adaptive Practice

Making Your Study Simpler