PIVOT TABLE
help-science20
attachment_15.pdf
Restricted
Practical Project 2
Year
Spring 2023
Course Code:
SEC210
Course Name SSDEC Systems and Technologies
Submission Deadline April 9th 2023
Deliverables Zip File containing
Excel Dashboard File
External Workbook Files stored in a Folder
MP4 Recording
Percentage of final grade 25%
CLOs Tested Weight
CLO3: Discuss the data phases and tools, including the data life cycle and its elements 50%
CLO4: Utilize various technologies in the context of Safety, Security, Defence, Emergency
Preparedness and Crisis Management. 50%
Academic Honesty Reminder:
Students suspected of plagiarism, either of published work or work from unpublished sources, including the work of
other students, or of collusion will be dealt with according to Rabdan Academy guidelines.
Restricted
Practical Project 2 Overview
The goal of the project is to create a dashboard in MS Excel based on multiple external data sources. The end product
will be an MS Excel file that will showcase data pertaining to your area of interest. This file will be the dashboard and it
will visualise external workbooks using various charts and graphs in conjunction with Pivot Tables and incorporating
the Data Model within Excel.
The completed dashboard will contain, as mentioned above, charts and graphs in addition to having dynamic
functionality through the use of MS Excel Slicers.
Practical Project 2 Timeline (Project Deadline)
The time line for the project can be seen below. This will give you more than 3 full weeks to complete this practical
project.
It is very important that you stick to the time line as any submission after the deadline will incur a late penalty. This will
be 10% for every day it is late after the project deadline shown above.
Practical Project 2 Instructions
The project will follow the main areas laid out below. It is very important that all of the areas area followed closely so
as to produce the best possible final product, the MS Excel Dashboard.
Identifying the area of visualisation using the Dashboard
Please identify the area of interest that you would like to focus on the Dashboard on. It would be practical to focus it
on your program of study as it is easier to identify data source and draw conclusions from your data.
Obtaining the External MS Excel Workbooks
After identifying your area of interest, locate at least 4 to 5 external datasets that you might be able to use in your
Dashboard.
For example, if you were creating a Dashboard to look at the Unemployment in an Area you would probably need to
source datasets on
Unemployment Rate
o This is the percentage of the labour force that is unemployed and actively seeking employment in the
area.
Employment-to-population Ratio
o This is the percentage of the population that is employed in the area.
Labour Force Participation
o This is the percentage of the population that is in the labour force (i.e., either employed or actively
seeking employment) in the area.
Duration of Unemployment
o This is the length of time that unemployed individuals in the area have been without work.
Industry Specific Employment
o Unemployment rates broken down by specific industries can give you an idea of which sectors are
being hit hardest by unemployment in the area.
Job Vacancies
Project release: date and time Friday March 17th 15:00
Project deadline: date and time Sunday April 9th 23:59
Restricted
o This is the number of available job openings in the area.
Wage Growth
o This is the percentage change in wages in the area over a specific period.
You will need to locate similar datasets for the area you are considering for your Dashboard
Cleaning the Data
Before we can start visualising the data to obtain information we need to make sure that the data is clean. Below are
some areas you may consider
Removing duplicate values
o Check your data to make sure there are no duplicate entries.
o If you find duplicates, remove them so that they do not skew your results.
Check for missing values
o Identify any missing values in your data, and decide how to handle them.
o You may want to
remove records that contain missing values
put values in for those missing values
leave them as is if you are not using that attribute.
Standardize the data
o Ensure that all data is in the same format.
o For example, if you are analysing unemployment rates, make sure that all rates are expressed as
percentages and have the same number of decimal places.
Address and outliers
o Identify any outliers in your data and determine whether they are valid data points or errors.
o Think back to Project 1 where we use GPS coordinates. How would we check if we had an error
here?
Address inconsistencies in the data
o Check for any inconsistencies in your data, such as inconsistent date formats or inconsistent labels
for categories.
Validate the data sources
o Ensure that the data sources you are using are reliable and accurate.
Linking the MS Excel Workbooks
Import all of the External MS Workbooks
into the Dashboard file. Link up the
workbooks by the primary/foreign key
concept.
The snippet on the right was obtained from
Chat GPT in relation to this. Please
remember that you can use Chat GPT but it
should be cited correct in any writing
products created.
Figure 1 (Chat GPT, Mar 15th 2023)
Restricted
Creating the MS Excel Dashboard
Please create a one single sheet Dashboard. Label the sheet as Dashboard.
A dashboard is a visual representation of important data and metrics that provides an at-a-glance view of key
performance indicators (KPIs) for a specific area or function.
Whilst creating the dashboard it may be useful to look at the following below is a sample of what makes a very
impactful Dashboard
Clear Layout
o A dashboard that is well designed should be very easy to read and understand at a glance.
o The layout should highlight the most important information
Not all the attributes from your external datasets need to be visualised
Interactive Visualisations
o Interactive visualisations can help the users quickly understand complex (difficulty) data.
o The user should be able to interact with these visualisations through the use of menus
This is what we mean by having a dynamic dashboard
User-friendly interface
o The dashboard should be easy to use, with intuitive navigation and a user-friendly interface.
o Users should be able to access the information they need quickly and easily.
Project Showcase – Interview with Instructor
Students will create a 6-minute video going over all of the points raised above. The video be created using the
information provided below in Practical Project 2 Deliverables. It is important that the correct video is generated.
The video itself should have both the student’s voice and the student’s screen.
The presentation will cover a step by step run through of the project following the instruction headings outlined in this
section, Practical Project 2 Instructions. It is imperative that the presentation highlights all steps as failure to do so will
see a reduction in the allocation of marks for the presentation.
Practical Project 2 Deliverables
Each student will be asked to submit a zip file containing the folder structure and two files shown below.
All external Excel workbooks should be stored in the folder Datasources. The dashboard should be named using the
following convention Dashboard_StudentID#. The video should be saved as a .mp4 file extension and should be
named using the convention Presentation_StudentID#.
Both the folder and file above should be zipped (.zip file extension) and named using the following convention
Practical_Project_2_StudentID# as show below. Only this file should be submitted to the Practical Project 2
Assignment.
Please make sure you create the file above correct and that it is a .zip file and not a .rar file extension
Restricted
Please upload the Practical_Project_2_StudentID#.zip file to the assignment located at the top of the Moodle Course
shell for your section name Practical_Project_2_Submission.
Practical Project 2 Submission Guidelines
Late submission will be penalized as follows:
Late submissions are allowed within a maximum of 5 working days of the deadline of the assessment activity. The
student grade is reduced by 10% for each working day during the aforementioned 5-day period. If the assignment is
submitted beyond the 5-day period, the student receives a grade of 0 in the assessment activity in question.
Restricted
Individual Student Grading Rubric for Practical Project 2 – CLO 3 (50 Marks) – CLO 4 (50 Marks)
Criteria Fail
0%-59%
Pass
60%-69%
Satisfactory
70%-76%
Good
77%-83%
Very good
84%-89%
Excellent
90%-100%
Follows Instructions
5 Marks – CLO 3
Submission follows the
instructions regarding file
extensions, file naming
and file number
The student’s submission
is deemed to be at an
unsatisfactory level. No
steps were completed and
the naming convention is
incorrect.
The student’s submission
is deemed to be at a
passing level. Less than 3
of the instructional steps
were included in the
project and the naming
convention was
misaligned.
The student’s submission
is deemed to be at a
satisfactory level. Less
than 4 of the instructional
steps were included in the
project and the naming
convention correctly
aligned.
The student’s submission
is deemed to be at a good
level. Less than 5 of the
instructional steps were
included in the project and
the naming convention
correctly aligned.
The student’s submission
is deemed to be at a very
good level. All 6 of the
instructional steps were
included in the project and
the naming convention
was misaligned.
The student’s submission
is deemed to be at an
excellent level. All 6 of the
instructional steps were
included in the project and
the naming convention
was aligned.
Overall Excel Structure
5 Marks – CLO 4
Submission has required
# of external sources,
required number of files in
submission, correct
structure of Dashboard
and linkage of external
datasets
The student’s submission
is deemed to be at an
unsatisfactory level. All 4
components of the criteria
were completed in the
submission.
The student’s submission
is deemed to be at a
passing level. Only 1 of
the 4 components of the
criteria were completed
correctly in the
submission.
The student’s submission
is deemed to be at a
satisfactory level. Only 2
of the 4 components of
the criteria were
completed correctly in the
submission.
The student’s submission
is deemed to be at a good
level. Only 3 of the 4
components of the criteria
were completed correctly
in the submission.
The student’s submission
is deemed to be at a very
good level. All 4 of the
components of the criteria
were completed in the
submission. There may
have been minor errors in
one or two of the
components.
The student’s submission
is deemed to be at an
excellent level. All 4 of the
components of the criteria
were completed correctly
in the submission. There
may be some very minor
error evident in the
submission to stop max
mark.
Content Relevancy
5 Marks – CLO 3
Dashboard is aimed at a
relevant area in relation to
the student’s program of
study
The student’s submission
is deemed to be at an
unsatisfactory level.
The student’s submission
is deemed to be at a
passing level.
The student’s submission
is deemed to be at a
satisfactory level
The student’s submission
is deemed to be at a good
level
The student’s submission
is deemed to be at a very
good level
The student’s submission
is deemed to be at an
excellent level.
Dataset Import
10 Marks – CLO 4
The external datasets
were correctly imported
into the MS Excel
Dashboard as
PivotTables
The student’s submission
is deemed to be at an
unsatisfactory level. There
is no evidence of
importing of external
datasets into the
Dashboard Excel file.
The student’s submission
is deemed to be at a
passing level. There is
evidence of importing of
external datasets into the
Dashboard Excel file.
However, it is not for all
The student’s submission
is deemed to be at a
satisfactory level. There is
evidence of importing of
external datasets into the
Dashboard Excel file.
However, it is not for all
The student’s submission
is deemed to be at a good
level. There is evidence of
importing of external
datasets into the
Dashboard Excel file.
However, it is not for all
The student’s submission
is deemed to be at a very
good level. There is
evidence of importing of
external datasets into the
Dashboard Excel file. All
of the worksheets
The student’s submission
is deemed to be at an
excellent level. All of the
worksheets required are
there and there are no
errors in the process.
Restricted
worksheets as required
and there are some major
errors in the process.
worksheets as required
and there are some minor
errors in the process.
worksheets as required
but no errors exist.
required are there and
there are some minor
errors in the process.
Cleaning the Data
20 Marks – CLO 3
Data cleaning was carried
out in accordance with
the project brief.
The student’s submission
is deemed to be at an
unsatisfactory level. There
is no evidence of data
cleaning in line with the
project brief.
The student’s submission
is deemed to be at a
passing level. There is
some evidence of data
cleaning in line with the
project brief. However,
less than 3 of the steps
were incorporated and
displayed in the
dashboard or outlined in
the presentation.
The student’s submission
is deemed to be at a
satisfactory level. There is
some evidence of data
cleaning in line with the
project brief. However,
less than 4 of the steps
were incorporated and
displayed in the
dashboard or outlined in
the presentation.
The student’s submission
is deemed to be at a good
level. There is evidence of
data cleaning in line with
the project brief. However,
less than 5 of the steps
were incorporated and
displayed in the
dashboard or outlined in
the presentation.
The student’s submission
is deemed to be at a very
good level. There is
evidence of data cleaning
in line with the project
brief. All of the steps were
incorporated and
displayed in the
dashboard or outlined in
the presentation. Errors
exist in the processes.
The student’s submission
is deemed to be at an
excellent level. There is
evidence of data cleaning
in line with the project
brief. All of the steps were
incorporated and
displayed in the
dashboard or outlined in
the presentation.
Creating the Data Model
20 Marks – CLO 3
Creating a valid data
model based on the
external Datasources
imported into the
Dashboard
The student’s submission
is deemed to be at an
unsatisfactory level. No
evidence of usage of Data
Modelling to link the
external Datasources.
The student’s submission
is deemed to be at a
passing level. Whilst there
is no evidence of linkage,
the datasets were all
combined into one single
table.
The student’s submission
is deemed to be at a
satisfactory level. There is
evidence of usage of Data
Modelling to link the
External Datasources.
However, it is not
correctly carried out and
therefore doesn’t work
correctly for all tables.
The student’s submission
is deemed to be at a good
level. There is evidence of
usage of Data Modelling
to link the External
Datasources. Some of the
possible linkages were
incorporated.
The student’s submission
is deemed to be at a very
good level. There is
evidence of usage of Data
Modelling to link the
External Datasources.
Most of the possible
linkages were
incorporated for the
dashboard.
The student’s submission
is deemed to be at an
excellent level. There is
evidence of usage of Data
Modelling to link the
External Datasources. All
of the possible linkages
were incorporated for the
dashboard.
Creating the Dashboard
20 Marks – CLO 4
Creating a Dashboard
that follows the
instructions within the
project brief
The student’s submission
is deemed to be at an
unsatisfactory level. There
is no real dashboard with
regards to visualisations
all in one place.
The student’s submission
is deemed to be at a
passing level. Whilst a
dashboard exists only 1 to
2 visualisations exist on
that page. Visualisations
do not incorporate all
available external
Datasources.
The student’s submission
is deemed to be at a
satisfactory level. All
visualisations are on one
single sheet (Dashboard)
however there is no
interactivity on the
dashboard using slicers.
The student’s submission
is deemed to be at a good
level. All visualisations are
on one single sheet
(Dashboard). Whilst there
is interactivity on the
dashboard using slicers,
not all charts and tables
are linked to the slicers.
1 of the 3 characteristics
outlined in the project
The student’s submission
is deemed to be at a very
good level. All
visualisations are on one
single sheet (Dashboard).
All charts and tables are
linked to the slicer(s). All
external data may not be
incorporated in the
submission and displayed
on the Dashboard.
The student’s submission
is deemed to be at an
excellent level. All charts
and tables are linked to
the slicer(s). All external
data incorporated in the
submission is displayed
on the Dashboard in
some form or another.
All 3 characteristics
outlined in the project
Restricted
were evident in the
Dashboard.
2 of the 3 characteristics
outlined in the project
were evident in the
Dashboard.
were evident in the
Dashboard.
Presentation
15 Marks – CLO 4
Data cleaning was carried
out in accordance with
the project brief.
The student’s
presentation is deemed to
be at an unsatisfactory
level. There is very little
understanding of the
processes involved
throughout the project.
The student’s
presentation is deemed to
be at a pass level. Whilst
there is some
understanding shown in
the presentation, the
student still in makes
major errors not in line
with the work submitted.
The student’s submission
is deemed to be at a
satisfactory level. Overall
the student presentation
showcases the work
completed however the
student still in makes
major errors in line with
the work submitted.
The student’s submission
is deemed to be at a good
level. The presentation
showcases the work in
line with the submitted
submission. There may
be one step of the project
that is omitted or the
student may have some
inaccuracies in the
commentary.
The student’s submission
is deemed to be at a very
good level. The
presentation showcases
the work in line with the
submitted submission. All
steps in the project are
evident but there may be
one inaccuracy in the
commentary.
The student’s submission
is deemed to be at an
excellent level. The
presentation aligns
perfectly with the
submission. No
inaccuracies exist in the
commentary.
