Power BI Documentation for Skill Collector Web Application

Introduction

Power BI is a powerful data visualization and analytics tool used in conjunction with the Skill Collector web application. While Power BI is not directly integrated into the app, the process owner can leverage the cloud version of Power BI to gain valuable insights from the survey data. Power BI allows for easy data exploration, visualization, and sharing, enabling the process owner to make informed decisions based on the collected data.

Purpose

The purpose of this documentation is to provide an overview of Power BI's usage in the Skill Collector web application and guide the process owner on how to access and utilize Power BI effectively.

Step-by-Step process

Data sources

To create the Power BI report, we connected to three different data sources:

1. Skills Database: This data source is connected via an API and contains information about skills and their descriptions. It provides the foundation for analyzing and visualizing skill-related data in the report.
2. User Response Database (Skill Collector PostgreSQL): This data source is connected via API to gather user responses related to skills. It allows for tracking user ratings of skills and other valuable insights.
3. Excel File on OneDrive: This file is connected to retrieve corresponding user hashes and company information. It enables analysis based on company-specific data.

Below are the steps followed to establish these connections:

a. Clicked on "Get Data" from the Power BI Home tab.

b. Selected the appropriate data source option for connecting to the skills database via API.

c. Provided the necessary details, including the API endpoint and any required authentication or authorization credentials.

d. Explored the available tables or views in the skills database and selected the relevant data for the report.

Transform the data

Next step is to edit the gathered data and keep only the necessary information for the Power BI report.

1. Open Power BI Desktop and navigate to the "Transform Data" (Power Query Editor) window.
2. In the Power Query Editor, you will see the data tables or views from each data source that you connected to. Select the table or view that you want to edit.
3. Right-click on the column header you want to remove. Select "Remove" or "Remove Columns" from the context menu. Repeat this step for all the columns you want to remove.
4. After editing and filtering the data, click on the "Close & Apply" button to apply the changes and load the modified data into the Power BI report.

By following these steps, you can edit and filter the gathered data in Power BI to keep only the necessary information required for your report. Remember to save the Power BI report to preserve the changes made in the Power Query Editor.

Building the relations between the tables

Next it is important to check or build new relations between tables.

1. Skills to User Responses (answers to skills): A relationship is established between the Skills table and the User Responses table based on a common column, such as the skill name and skill id. This relationship enables the correlation of skill data with user responses and ratings. The ralationship are Many-to-one.
2. User Responses to User-Company (answers to skillcollector_main): A relationship is created between the User Responses table and the User-Company table using the shared user hash column. This relationship enables the connection between user responses and their corresponding company information. The ralationship is Many-to-one.

1. Open Power BI Desktop and navigate to the "Relationships" view.

2. In the Relationships view, you will see all the tables or views that you have imported into your Power BI report. Identify the columns that have common values in both tables. These columns will serve as the basis for establishing the relationship.

3. Click on the first table, and then drag and drop the column from that table onto the corresponding column in the second table.
4. Power BI will automatically detect and create the relationship based on the matching column values. Repeat this process for any other tables that require relationships.

5. Once the relationships are created, you can check and manage them:

   1. Click on the "Manage Relationships" button in the Relationships view. This will open the Manage Relationships dialog box.

   2. In the Manage Relationships dialog box, you can see the list of existing relationships between tables.

   3. Review the relationships to ensure they are correctly established and reflect the intended connections between tables.

   4. If needed, you can modify or delete existing relationships using the options available in the Manage Relationships dialog box.

Power BI Report Structure

Power BI provides interactive dashboards that visualize the hard skills and soft skills data collected through the Skill Collector web application. These dashboards offer various charts, graphs, and visuals to represent the distribution, importance, and trends of skills in the IT industry.

The first page of the report provides a comprehensive overview of user participation and company involvement. It showcases the number of users who responded to the survey, indicating the level of engagement and participation. Additionally, it highlights the number of unique companies that have employees participating in the survey, giving insights into the breadth of organizational involvement. Furthermore, it presents the number of soft skills and hard skills being evaluated, offering a comprehensive understanding of the skills under assessment.

The bar chart visualizes the most active companies with the highest number of users who responded to the survey. Overall, this first page sets the stage for understanding the extent of user engagement, company participation, and the scope of skill evaluation within the report.

The second page of the report focuses on the evaluation of hard skills, which are categorized into four distinct categories: Future Need, Valuable, Important, and Very Important. Each category is visually represented by a donut chart, providing a clear and intuitive representation of the distribution of skills within each category. These charts enable stakeholders to quickly identify the relative importance and future needs of different hard skills.

The third page of the report focuses on the analysis of hard skills in the Future Need category over time. It presents a line chart that visualizes the changing skill requirements and the anticipated needs for the future.

This dynamic chart allows stakeholders to track and understand the evolving demands for hard skills. By observing the trends and fluctuations in the Future Need category, organizations can effectively plan and allocate resources to meet future skill requirements. The chart provides valuable insights into the changing landscape of hard skills, helping stakeholders stay proactive and adaptable in their talent development strategies. The page serves as a valuable resource for forecasting and decision-making, ensuring that the organization is well-prepared to address upcoming skill demands.

The fourth page of the report highlights the most popular categories of skills and their corresponding subcategories. It features two graphs that provide valuable insights into the skill preferences. The first graph is showcasing the distribution of skills across the most popular categories. The second graph complements the first by displaying the distribution of skills within the popular subcategories. This graph offers a deeper understanding of the specific areas of expertise within the preferred skill categories.

The fifth page of the report focuses on the evaluation of soft skills, categorized into three distinct categories: Valuable, Important, and Very Important. Similar to the previous page, this page showcases donut charts representing the distribution of soft skills within each category.

Additionally, the page features a chart that highlights the skills that have received the highest number of votes. This chart allows stakeholders to identify the skills that have been voted on the most, regardless of the specific category. By considering the overlapping votes in multiple categories, organizations can gain insights into the skills that hold significant importance across various aspects of the evaluation process.

Best Practices

To effectively utilize Power BI's cloud version in conjunction with the Skill Collector web application, consider the following best practices:

1. Regularly update the data: Ensure that the survey data is regularly updated in the Skill Collector database. This will ensure that the Power BI dashboards reflect the most current and accurate information.

   

2. Explore different visualizations: Power BI offers a wide range of visualizations. Experiment with different chart types, graphs, and visuals to effectively represent the survey data and gain valuable insights.

3. Stay informed about Power BI updates: Power BI continuously introduces new features and enhancements. Stay up-to-date with Power BI's latest offerings to take advantage of new functionalities that can further enhance your data exploration and visualization capabilities.

Accessing Power BI Cloud Version

To access the Power BI cloud version and view the Skill Collector dashboards, follow these steps:

1. Open a web browser and navigate to the Power BI website https://powerbi.microsoft.com.
2. Log in with your credentials. If you don't have a Power BI account, you can create one for free.
3. Once logged in, you will be directed to the Power BI home page.
4. Navigate to the "Workspaces" section and locate the workspace associated with the Skill Collector project.
5. Click on the workspace to access the dashboards and reports.