17 May 2017

Replicate a Competencies Matrix in Power BI

Regarding the lack of knowledge about our employees and their skills, an internal project was developed using Microsoft technologies, more specifically Visual Studio and PowerBI. This article expects to demonstrate how this development was accomplished.

It should be noted that ETL focused mainly on transforming an Excel file, and the visualization of the dashboards intended most of all, to be clear, friendly, and able to generate better researches.

1.     The ETL Process

In order to transform an Excel file in a PowerBI dashboard, it was necessary use an ETL process to extract, process and load the data into the data warehouse. In this article, we will explain the phases of this ETL process in detail.

 

 

SQL – TRUNCATE MATRIX_COMPETENCIES;

FST FILES MOVED WITH SUCCESS;

 

 

1.1.1       Data Extraction

In order to be able to successfully extract data from Excel to a database (ODS), we used a script instead of an Excel source, since a competency matrix is constantly changing. An excel source is not dynamic, so it cannot follow those changes, however, the use of a script makes the whole process more dynamic, allowing the loading of different types of matrix with different numbers of competencies.

The use of a foreach loop container allows the loading of several files at the same time, allowing the visualization of the collaborators’ evolution in time.
Next, we present the process of data extraction in use:

 

 

 

1.1.2       Data Transformation

After the loading of the data into the ODS, it was necessary to transform the data into the format wanted and, in this case, was necessary create metrics and new fields to match the needs identified. It was also necessary to change the datatypes (as default they are all in varchar (255)) and allow the visualization of the column/line of the employee’s competencies – instead of having two columns as it exists in excel by default.

 

 

1.1.3       Loading of Data

After the transformation of the data, they are loaded into a data warehouse, which will have all the information in a cleaner and less redundant way.

It’s necessary to be careful while loading the data to the Data Warehouse and to validate data to avoid duplicates. If there are changes in data, it is the last version that is loaded. This checking is done using one script that generates a key to each record: if the record changes, the key will change. This allows the ability to compare the old key with the new one, and only load new data when the key is different.

 

 

 

1.1.4       Master

After loading the data, it is a best practice to use a job to run the whole ETL process. To do this, it was necessary to create a package master (which includes all the project packages) to run the whole process whenever is necessary.

 

 

2.      Power BI

After processing the data, and already having the Data Warehouse, it is possible to visualize the data. PowerBI was the chosen tool due to its simplicity and, mainly, due to its performance and visual presentation and dynamism.

To respond to the needs identified, we created four different dashboards.

 

2.1.1       General Dashboard

This dashboard allows the general analysis of the competencies of all employees. This way, the company is able to know which the stronger competencies are and which need to be developed. It also allows seeing which people have more capacity in a certain competency.

 

 

 

2.1.2       Employee Dashboard

This dashboard focuses on the employee. It allows seeing the technologies in which the employee has an adequate level and the ones in which he/she has more difficulties. It also allows comparing the level of the employee regarding the company, and show if he/she is above or below average.

As Microsoft and Cognos are two core tools at BI4ALL, the dashboard allows seeing at which ones the employees stands out.

 

 

2.1.3       Skills Dashboard

The skills dashboard makes it very easy to search for skills in the company. To know if employees have a certain competency; it is only necessary to search for that skill.

 

 

Dashboard 2015 vs 2016

The last dashboard allows knowing the progress of the employees as well as knowing what technology they are currently using in their job (since this can change from one year to another).

 

 

Some DAX functions were used while creating these dashboards. We also used the Power BI community https://app.powerbi.com/visuals/ to upload some visualizations.

 

 
.
.
.
.
.
.
     Hugo Parreira
       Consultant
Blog