At ventriloc, one of our core objectives is to increase transparency in sharing our internal data with our employees. In this article, we will explore how we have improved the management of the vacation bank through the use of Power BI.
The Challenge Faced by Many
Too often, managing vacation banks is done manually with Excel files and endless formulas. This is followed by a series of emails to communicate the information to the concerned employee. The next day, another employee sends a request, and the process starts all over again. Employees rarely have a quick and up-to-date view of their vacation balance, and the process of communicating it to them is cumbersome.
To address this real issue, we decided to use Power BI to share real-time vacation data with our employees. This approach automates the process and centralizes all vacation management in one place.
The Process for Making Data Available
As the saying goes, “garbage in, garbage out”. It is crucial to have a clear and strict process for recording vacations. Ideally, vacations are entered into a time tracking software. To this end, ventriloc has chosen to use MinuteDock as our time tracking tool, including vacations and leave. MinuteDock is very user-friendly, offering an intuitive interface and an API with which we can extract data for analysis (if you don’t have a time tracking tool, we highly recommend it!). To clearly identify vacations, we have defined specific codes in MinuteDock that employees can use to enter their vacation time.
Next, to make the data available to users, we developed a report using Microsoft Power BI. As shown in Figure 1, employees have access to a specific view that allows them to quickly check their vacation statistics. You will quickly notice that our Chief Operating Officer, Mathieu Leblanc, likes to take it easy in July 😂.
With this view, each employee now has access to the number of vacation days, health and wellness leave, and birthday leave they have used. They are also able to see their balance and, most importantly, understand it clearly. If they wonder which days they took their vacations, they can hover over the bottom chart, and a tooltip will appear showing the daily details, as seen in Figure 2. Hooray for custom SVG visuals! My colleague Marc-Antoine Dumas is the biggest fan! Many thanks for your help, Marc😉.
I can already hear you asking, “I have multiple employees; how can I ensure that my employees only see their data and not their colleagues?” Well, know that we have implemented dynamic row-level security (RLS) in Power BI to ensure that employees only have access to their own data. We have created a specific role for management with full permissions, allowing them to see all the data.
This centralization of data is not only beneficial for employees. As shown in Figure 3 (data anonymized for confidentiality reasons), the management committee also benefits from a centralized view of vacation bank balances, with analysis tailored to their needs. This helps them anticipate project slowdowns and alert our partners in advance, thus reducing the impact of unexpected absences. Additionally, on a financial level, this Power BI report allows us to quantify the amount to be paid at the end of the year for unused vacation balances.
Final Words
With the implementation of this tool, we have now moved past Excel files and email chains for communicating vacation balances to employees. We have also eliminated the risk of errors that could occur during data manipulation. The feedback from our employees following the implementation of this tool has been very positive; they have even asked for more features. That’s why I’m excited to announce that we’re not stopping here! Our upcoming initiatives in data transparency include making available data on hours worked per project versus initial budgets, as well as expense reimbursement requests. These topics will be covered in future blog posts, so stay tuned 😊.
