https://ncuone.ncu.edu/d2l/le/content/146394/viewContent/1451719/View
You are working as the analytics developer for an educational institution. You will use the Assignment8_Data.csv data. The school wants to determine which degree is in demand right now based on the data mining results. The school also plans to determine all the logistics based on the results.
Step 1: Use the Assignment8_Data.csv file.
Step 2: Create the database in the SQL Server and name the database educationdata.
Step 3: Create an ETL job that should perform the following things:
1. Extract: Perform data extraction. The source is the Excel file.
2. Create an additional column in the table and name it level_of_experience.
3. Transformation: You should apply the following rules in the transformation logic.
a. If Experience (Years) = 0, then level_of_experience = “no experience”
b. If Experience (Years) is between 1 to 5, then level_of_experience= “mid-career”
c. If Experience (Years) is greater than 5, then level_of_experience = “experience”
4. Loading: You need to create the SQL table with the following columns.
d. First Name
e. Last Name
f. Gender
g. Age
h. Email
i. Phone
j. Education
k. Occupation
l. Experience (Years)
m. Salary
n. Marital Status
o. Number of Children
p. level_of_experience
The data should be loaded into this table once the SSIS package execution finishes.
5. You also need to build the following reports.
q. Create tabular reports that show the following details
i. First Name
ii. Last Name
iii. Gender
iv. Age
v. Email
vi. Phone
vii. Education
viii. Occupation
ix. Experience (Years)
x. Salary
xi. Marital Status
xii. Number of Children
xiii. level_of_experience
r. Create bar charts that show the data based on these conditions.
xiv. Occupation
xv. Number of People
Your assignment submission should include the following details.
• Screenshots of the ETL job
• Screenshots of transformation rules
• Screenshots of two reports
• The entire project solution that includes the SSIS project and SSRS project
• Proposal for creating any additional reports that will help your company in staff hiring
You should also answer the questions below.
6. Why are transformation rules important?
7. Why do businesses use the reports instead of looking at the raw data?
8. What are the different visualization techniques for making interactive reports?