Creating Employee Engagement Report with Power BI

Introduction:

Employee engagement is a function of relationship between an organization and its employee. In today’s fast-paced and competitive business landscape, organizations are increasingly recognizing the importance of measuring employee engagement Key Performance Indicators (KPIs) to not only boost retention and satisfaction but also to unlock the full potential of their teams.

Recently, I was tasked by one of my client to create a dynamic report that they can use to effective monitor their Employee Recognition Program. Their objective is to gain insights into the number of eCards sent and received within specific timeframes. Additionally, they are keen on understanding the total count of points that have been redeemed and awarded. Within this context, I’ll guide you through the complete workflow, starting with data extraction from Microsoft SQL Server, creating relevant DAX measures, and ultimately crafting a visually pleasing report.

Background Information & Objectives:

Abstract: with eCards. employees can send digital expressions of appreciation, recognition, and gratitude to their team members. These eCards offer a way to acknowledge outstanding contributions, celebrate milestones, or simply convey a warm “thank you.” Furthermore, employees have the option to attach points to these eCards, allowing the recipients to exchange them for a range of rewards and incentives. This particular system holds a central role in my client’s organization serving as a powerful motivator for their employees to consistently perform well and actively participate in an engagement workplace.

Objectives & Deliverables: My client’s request involves the development of a Power BI report focused on monitoring the performance metrics of their eCards system. However, their expectations extend beyond mere tabular data representation. The client has articulated their objectives as follows:

1. A visual element that provides an overview of the trends in the eCards sent and received.
2. A visual representation of the month-to-month comparison of eCards sent, indicating whether the numbers are on the rise or decline.
3. A visual presentation highlighting the top-performing departments, based on the volume of eCards sent.
4. The inclusion of an interactive feature that allows users to quickly select specific criteria for data filtering. For instance, users should have the ability to choose a particular department and view associated data.

In-Depth Process and Insights:

Data Extraction —

The majority of the client’s data is housed in a Microsoft SQL server. My responsibility is to figure out the appropriate SQL queries to extract the required data for the report. Nevertheless, the database comprises 10 to 20 distinct tables, making the process of connecting them a challenging task. To go over this, I initiate several working sessions with the Database Administrator. These sessions have been invaluable for me as I gain insights into the schema’s structure and the web of table relationships. Fortunately, our efforts leads to development of functional queries that effectively import high-quality data into Power BI. And for those who have not known, you can execute SQL queries directly on Power BI and all you have to do is identify data source, database name and connection credentials.

Below is one example of SQL query that I use to obtain the information on eCards and the result of it in Query Editor:

Suggestion: The technique I apply in this situation is that once I import the primary query into the Power BI environment, I opt for the “Disable Load” feature. This allows me to deconstruct it into more manageable, smaller tables. To illustrate, I derive three separate tables from this query, each containing unique eCard-related information (one for senders/receivers, one for received points, and one for redeemed points). This method helps avoid loading multiple queries, conserving computational resources and significantly speeding up the refresh process.

Data Preparation & Modeling —

The next phase is to ensure that the raw data is prepared for usability. Thankfully, there is minimal need for extensive transformation tasks since the data is already well-structured on the SQL server side. The most crucial task for me is to append an additional column for UTC time to accurately represent the time zone during data collection. Since our SQL server operates in the CST time zone, harmonizing the time zones is important.

Afterwards, I build a practical schema to connect the tables together. I also create an additional Calendar table using the Query Editor and integrate an Employee_Fact table, housing data regarding various employee dimensions, such as Department, Executive Indicator, and Status. This Calendar table, in particular, holds significant importance in this context, not only functioning as a dimension lookup table but also serving as a central hub linking the three tables above. Please refer to the schema below for further details:

DAX Measures —

Now, with the help of DAX measures, I’m able to present all the metrics that align with the client’s expectations. While many of the DAX formulas I’ve employed involve fundamental functions like Count(), Filter() and SUM(), they play a critical role in ensuring accurate calculations and the correct numerical values displayed in the visuals utilized. Here are two examples of the DAX calculations used for the report:

Calculate the number of eCards sent and received. It does this by counting the distinct (unique) Employee IDs and consider the relationship with the Employee_Fact table he “+0” at the end is there to ensure that the result is a number (in case the count is zero) rather than a blank value

Calculates the total number of eCards sent in the previous month by first checking if there’s data for the previous month, and the count is not zero; then it uses DATEADD() function to return the total number of e-cards sent in the defined period

Data Visuals —

This is the phase where data transforms into dynamic visuals. The Employee Recognition Tracking Report primarily focuses on comparative analysis. The client seeks a logical perspective and needs to assess numerous variables. Consequently, I choose a blend of visuals, including Cards, Stacked Bar Charts, Line Charts, and Matrices, to present the data insights as comprehensively as possible.

Suggestion: a recommended approach in this scenario is to begin with a snapshot, represented by a multi-row card, to immediately engage the report’s viewers. Subsequently, all other visual elements should be designed around the figures displayed in the snapshot, and they can be further dissected based on the relevant dimensions as per the client’s specific requirements. Furthermore, when working with qualitative data like comments, there are additional supplementary visuals available in Power BI that users can incorporate. In this instance, I employ a visual known as WordCloud 2.0 to display the most frequently occurring words in the messages accompanying eCards. This effectively illustrates the motivations behind employee recognitions.

Conclusion:

In summary, I find great satisfaction in this project. It has been a valuable learning experience, particularly in addressing timestamp discrepancies between data collection and loading into the report. My primary goal is to provide the client with the means and knowledge necessary to effectively oversee and enhance their Employee Recognition Program, thereby creating a positive and engaging workplace. Fortunately, we achieved this objective, and the report has proven to be a success