HR Dashboard
In this section, you’ll learn how to create HR Dashboard by using raw data from SQL Server, that turn raw data into an interactive HR Dashboard.
The HR Dashboard is a dynamic and interactive solution designed to provide a comprehensive view of key metrics and insights related to the organization’s human resources. It consolidates data from multiple HR domains to enable better decision-making, track the total number of employees categorized by department, job role and active or terminated and monitor workforce trends. This dashboard empowers HR teams and management to evaluate and address workforce challenges effectively. Try it yourself first! Build the dashboard and, if you get stuck, refer to the course video for help.
Download data source file Adventure_Works_2019.
Follow steps below to add data source file in SQL server or you can refer the video:
Connecting Adventure_Works_2019 to SSMS
1) Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
2) Right-click Databases in the Object Explorer and select Restore Database.
3) Under Source, select Device and click … (browse button).
4) Click Add, select your .bak file, and click OK.
5) Check the Restore box in the backup list.
6) Confirm the database name under Destination. Click OK to start restoring.
7) Expand Databases in Object Explorer to see your restored database.
1. Connect Power BI to your data source file.
2. Understand the Tables of your data set, we have eight tables in the data set:
Employee: The Employee table contains information about employees in a company. It includes details like their ID (BusinessEntityID), login credentials (LoginID), job role (JobTitle), date of birth (BirthDate), gender (Gender), hire date (HireDate), and the hours they can take off for vacation or sick leave (VacationHours and SickLeaveHours). This table helps track employee details and manage their records.
EmployeeDepartmentHistory: The EmployeeDepartmentHistory table contains information about employees history in a company. It includes details like their ID (BusinessEntityID), DepartmentID , ShiftID, StartDate (Hired), EndDate (Retired). This table helps track employee history details and manage their records.
EmployeePayHistory: The EmployeePayHistory table contains information about employees pay history in a company. It includes details like their ID (BusinessEntityID), RateChangeDate , Rate and PayFrequency. This table helps track employee pay history details and manage their records.
Shift: The Shift table contains information about shifts for employee in a company. It includes details like their ShiftID, and Name.
Department: The Department table contains information about department in a company. It includes details like their DepartmentID, Name and GroupName. This table helps track department details and manage their records.
Person: The Person table contains information about employee in a company. It includes details like their BusinessEntityID, FirstName, MiddleName and LastName. This table helps track employee name details and manage their records.
PersonPhone: The PersonPhone table contains information about employee phone number. It includes details like their BusinessEntityID, PhoneNumber and PhoneNumberTypeID.
PhoneNumberType: The PhoneNumberType table contains information about employee phone number type. It includes details like their PhoneNumberTypeID and Name.
EmailAddress: The EmailAddress table contains information about employee login email address. It includes details like their BusinessEntityID, EmailAddressID and EmailAddress.
Password: The Password table contains information about employee login password. It includes details like their BusinessEntityID, PasswordHash, and PasswordSalt.
3. Build relationships between different tables to show data connections.
1. Employee to Employee Department History
• Join Type: One-to-Many
• Key: Business Entity ID
• Description: This relationship links each employee in the Employee table to multiple department in the Employee Department History table, helping track
employee department details associated with each employee.
2. Employee Department History to Department
• Join Type: Many-to-One
• Key: Department ID
• Description: This relationship links each department to multiple records in the Employee Department History table, allowing to get department name.
3. Employee Department History to Shift
• Join Type: Many-to-One
• Key: Shift ID
• Description: This relationship links the shift table to multiple records in the Employee Department History table, helping to get this shift associated with
department and employee.
4. Employee to Employee Pay History
• Join Type: One-to-Many
• Key: Business Entity ID
• Description: This relationship links Employee records to multiple records of Employee Pay History, enable to track the pay history of each employee.
5. Employee to Person
• Join Type: One-to-One
• Key: Business Entity ID
• Description: This relationship connects Employee records to each Person, allow to get each person details.
6. Person to Password
• Join Type: One-to-One
• Key: Business Entity ID
• Description: This connection links each Person to each record in Password table, help to get the password of Person.
7. Person to Person Phone
• Join Type: One-to-One
• Key: Business Entity ID
• Description: This connection links each Person to each record in Person Phone table, help to get the phone number of Person.
8. Phone Number Type to Person Phone
• Join Type: One-to-Many
• Key: Phone Number Type ID
• Description: This connection links each record of Phone Number Type to multiple records in the Phone Number table, help to get the phone number type of
Person.
9. Person to Email Address
• Join Type: One-to-One
• Key: Business Entity ID
• Description: This connection links each Person to each record in Email Address table, help to get the email address of Person.
4. Create Visualisations to display important information in charts, tables, and maps. Your dashboard should match the below layout.
