Mathematics. Data Analysis. Learning.

Welcome to my portfolio website!

Divider

Carl Jung

**Skills:** SQL, SQL Server, Power Query,  Power BI (PBI)

**Objective:** The aims of this project I am currently working on are to create a database model, clean the data and explore it in SQL, and then, load all the information into PBI to build a dashboard. The focus here is to develop and consolidate my skills with SQL, but mostly the creation and modeling process of a database on SQL Server. The following points served as guidance for the development:

*   Average salary per department;

*   Top 10 highest salaries of the company and their respective employee names and positions;

*   Top 10 lowest salaries of the company and their respective employee names and positions;

*   The average age of all employees who are currently working in the company;

*   Average time worked on the company for the employees who left;

*   Number of employees hired per recruitment source;

*   Count and percentage (over the total number of employees) of employees per employment status;

*   Number of employees who left the company per termination reason;

*   Percentage of employers per race;

*   Percentage of male and female employees who worked and are still working in the company;

*   Total spent by the company with the salaries of all employees (active and non-active);

*   Average of employment satisfaction for all employees. (This metric is an integer value between 1 and 5);

*   Average of employment satisfaction for employees who left the company and for the ones who are still working there;

*   Average of the number of absences for all employees;

*   Average of the number of absences for employees who left the company and for the ones who are still working there;

*   Average of the number of days (all) employees were late (for the last 30 days);

*   Average of the number of days employees were late (for the last 30 days) for employees who left the company and for the ones who is still working there.

**Data source:** The dataset used for this project brings information about the employees of the HR Department of a fictitious company and can be found here: <https://www.kaggle.com/datasets/rhuebner/human-resources-data-set>

\*\*Development of the project: \*\*In SQL Server, I have created the database, with all the tables and their primary and foreign keys and relationships. I have also cleaned the data correcting divergences. Besides,  I created queries to deeply understand the dataset and answer the questions presented above.

Most of the data cleaning process was made in SQL.  After that, I upload these tables in PBI and built a dashboard, answering all the questions above.

All the work I have developed can be found on my GitHub:  <https://github.com/ds-mariaraujo/HRProject.git>

Below, there is a screenshot of the dashboard:

![](/images/ss.png)


HR Project - Data Exploration and Visualization with SQL and
Power BI

**Skills: **SQL Server, Power Query, DAX, Power BI

**Objective:** The main goal of this project is three-fold - to deal with a different type of dataset, further developing the skills of the first project and also focusing on the Data Analysis Expressions (DAX), constructing measures and calculated columns to consolidate the acquired knowledge with this language. The following points served as a guide to the development of the project:

*   Summarize the booking figures showing: the total revenue, total room revenue, total meal revenue, canceled bookings rate and car space requested rate.

*   In a line chart, show the revenue figures (total revenue, total room revenue and total meal revenue) monthly;

*   Summarize the following average figures: ADR, lead time, discount and number of people per booking. For details about the meaning of these figures refer to the documentation on GitHub;

*   In different bar charts, show: 1) the revenue by market segment, 2) the number of bookings per room type, and 3) the number of bookings per deposit type;

*   Using a donut chart, illustrate the percentage of the meal type and in a pie graph, show the revenue by hotel type (city and resort hotel);

*   In a stacked bar graph, show the top 10 countries with the highest number of bookings.

All this information should be filtered by the type of hotel and year. The only exception is the pie chart which should be filtered only by the year and not by the hotel type.

**Data source:** This dataset shows booking information of two Hotels, the city hotel and the resort hotel, between the years 2018 and 2020. It can be found on <https://www.kaggle.com/datasets/jessemostipak/hotel-booking-demand?resource=download>

**Development of the project:** I used SQL to analyze the data and to add together all the booking information for the three years into one unique table.

Then, I imported the tables into Power BI (PBI) using the advanced options and SQL statement.

The ETL process was done via Power Query and after that, I was able to create a star schema model for this project. The schema had one fact table and four dimension tables, where one of them was a date table.

With all the data organized and ready to be explored, I created some calculated columns and measures in order to answer the questions about the revenue of these hotels.

Finally, the visuals were created in line with the questions raised, and the main insights were highlighted.

The dashboard and SQL file can be found here: [https://github.com/ds-mariaraujo/HotelsRevenueProject.git ](https://github.com/ds-mariaraujo/HotelsRevenueProject.git)

Below you can see the layout of the Hotels Revenue Dashboard:

![](/images/HotelDashboard2.png)


Hotels Revenue Dashboard in Power BI

This interactive dashboard brings global information about Covid-19 all around the world, such as the global figures and infections, deaths, and vaccinations numbers.

**Skills:** SQL, SQL Server, Power Query, Power BI

**Objective: **This was my first project and its main goal was to get my hands dirty, practicing in a structured project all the concepts and topics I had studied so far in SQL and Power BI. I wanted to answer the following questions:

*   What are the global numbers about Covid-19? Show the total number of cases, the total number of deaths, the death percentage in relation to the total number of cases and the percentage of the population who were fully vaccinated; 

*   In a map graph, show the percentage of infected population per country;

*   About death figures: in a pie chart, show the total number of deaths per continent. In a stacked bar chart, show the top 10 countries with the highest number of deaths due to Covid-19;

*   About vaccinations figures: using a Gauge graph, show the number of people fully vaccinated in comparison with the total population. In a stacked bar chart show the top 10 countries with the highest number of people fully vaccinated. 

All the information in the visuals should be filtered by continent and country.

**Data source:** The dataset was extracted from <https://ourworldindata.org/covid-deaths> and accessed on 18/04/2022. It shows information about deaths and vaccinations of Covid-19 from the beginning of the pandemic until 17/04/2022.

**Development of the project: **First, I used SQL to create three views with the relevant information regarding Covid deaths, vaccinations, and the location of the pandemic. After that, I imported these views into the Power BI Desktop (PBI). I decided to do this in SQL, as the closer to the source the data manipulations are done, the lower the load on report/dashboard load/update time.

The ETL process was done via Power Query with the aim to build the PBI data model upon which the visualizations were built. Below follows some tasks that were made in this process:

*   Replacing “null” values to the value “0” in numerical columns, since it will be necessary for future calculations;

*   Changing data types to the correct ones in each column;

*   Removing unnecessary columns to improve performance.

After that, the tables were loaded into PBI and the data model was created using star schema with two fact tables containing the deaths and vaccinations data respectively, and two dimension tables, one representing the dates (with a date hierarchy) and the other one with the locations. Although the date table was not used to create the dashboard itself, I decided to create this table not only with the objective of practicing this essential skill but also for future convenience, allowing me to answer questions and gain insights related with time.

Once the data was clean and the model created, I made use of calculated columns in order to get the correct values about the Covid deaths and vaccinations. It was necessary because these values were cumulative, then, only the values of the last day of each country were needed.

With these correct values, I created measures to be used in the visual, and then I was able to create the visual with the main points about Covid-19, answering the questions presented above.

After the dashboard was finished, I went back to SQL and wrote queries seeking to answer the same questions in order to compare the results obtained in PBI, which also served the purpose of developing further my SQL skills. 

You can access both, the .pbix and .sql files here:  <https://github.com/ds-mariaraujo/CovidPortfolioProject.git>

Below, you can see a preview of the Covid-19 Overview dashboard:

![](/images/Covid.png)


Welcome to my portfolio website!

Recent Projects

HR Project - Data Exploration and Visualization with SQL and Power BI

Hotels Revenue Dashboard in Power BI

Covid-19 Overview - Data Exploration in SQL and Power BI

Home

Welcome to my portfolio website!

Recent Projects

HR Project - Data Exploration and Visualization with SQL and Power BI

Hotels Revenue Dashboard in Power BI

Covid-19 Overview - Data Exploration in SQL and Power BI