In our blog series on controlling for consulting projects we have already described the introduction to project controlling and the technical implementation in Power BI using the underlying Odoo system. The interaction of the systems and the run-through of the ETL process are the basis for the preparation of a meaningful reporting and ultimately the achievement of an effective and efficient project controlling.
The final step in this development process is the addition of necessary key figures and an appealing and effective visualization for the management, so that they will soon be able to draw helpful conclusions from the large amount of data.
In retrospect and with reference to the Power BI workflow, the data preprocessing and data modeling phases are now complete with the exception of the key figure enhancement. The process using Power BI Desktop is shown visually again below for easier classification.
With the first two phases, the ETL process described in the preceding blog post is already complete at this point, so that all necessary dimensions in hierarchy levels and fact tables with key figures are ready for possible key figure enrichment, KPI development and report generation.
In order to achieve the goal of project controlling for consulting, it is necessary to develop missing key figures from the given data before the actual creation of the reports. These can then be used to derive statements about the respective consulting projects and general performance across employees, customers, and time.
The following list provides an example of the key figures required for the evaluation, together with their origin. The list is by no means to be regarded as final and/or complete, as it can be modified and, in particular, extended as required. The focus is primarily on the depiction of cumulative key figures that take into account the respective previous values in the selected period in order to enable the depiction of trends.
| Key figure | Abbreviation | Description | Source |
| Accumulated revenue | Erlös_kum | Accumulated revenue in a specified time period. | Fact finances, dimension time |
| Accumulated expenses | Aufwand_kum | Accumulated expenses in a specified time period. | Fact finances, dimension time |
| Accumulated delta | Delta_kum | Net income from cumulative revenue and expenses over a selected period of time. | Key figureErlös_kum und Delta_kum |
| Accumulated project hours | PS_kum | Accumulated project hours in a specified time period. | Fact time tracking, dimension time |
| Billing rate | VSC | Billing rate in a specified time period. | Dimension customer_manaTec and employee, fact time tracking, key figure Delta |
| Total billing rate | VS_gesamt | Quotient of delta value and project hours across all employees and customer projects in the selected time period. | Dimension customer_manaTec and employee, fact time tracking, key figure Delta |
| Accumulated billing rate | VSC_kum | Cumulative billing rate as quotient of cumulative delta and cumulative project hours over a selected time period. | Key figure Delta_kum and PS_kum |
| Employees project contribution | Value | Billing rate multiplied by the respective project hours worked by an employee over a selected period of time. | Fact time tracking |
| Target days | ST | Potential working days according to employment contract, considering holidays. | Fact HR |
| Vacation and sick days | UKT | Vacation and sick days. | Fact HR |
| Attendance days | AT | Actual attendance days. |
With the completion by the remaining key figures, nothing more stands in the way of the creation of evaluations based on the underlying Odoo tables. Reports are generated in Power BI Desktop on various report pages in the last process step of data visualization via the tool's Report View.
The necessary reports can be generated here using drag-and-drop and adjusted at any time. As a self-service tool, the use is very user-friendly and intuitive, so that even users without BI knowledge can easily generate extensive evaluations based on the data model provided after a short training period.
Fast and intuitive report generation with Power BI
In this video, we show how to quickly generate a simple report with the help of Power BI and the appropriate data preprocessing.
To the videoThe corresponding reports can now be prepared from the described and generated dimensions, facts and extended key figures. The following examples are an excerpt of the reports used by manaTec GmbH for project controlling and are exemplary for the possible implementation in the application environment explained in this blog series.
Development of the settlement rate factors
Simple comparison of the cumulative development of project hours, revenues, expenses and the corresponding delta of a customer to the development over all customers on a monthly basis.
Development billing rate
Development of the cumulative billing rate over a selected period of time, specifying project hours and in comparison to the billing delta of the respective month, as well as the project contribution per employee.
Task-oriented evaluation of the project lessons
List of project hours worked per task or overall task of a project by month for reporting to the customer.
Attendance and project times as HR evaluation.
Development of actual attendance days and internal and external project hours per employee over the months of a year as a data basis for further HR evaluations.
The various reports make it possible to obtain an overview of the billing rates development trend and to identify possible outliers in good time. This ensures the determination of trends as well as the identification of any strengths and weaknesses as an indication for selective need for action. In addition to timely countermeasures in the event of visible undesirable developments in billing rates at employee or customer level, this also enables unused potential to be uncovered.
The identification of top customers and projects as well as possible causes for outliers in billing rates or billing deltas can be identified at any time through the parallel comparison with project hours, revenues and expenses. In addition to the customer perspective, the monitoring of individual employee development and project contributions is ensured, which can also serve as an approach for identifying development potential and for optimal internal and external resource allocation.
The holistic view of corporate development, detached from the focus on the individual customer, the individual project or individual employees, also offers added value here in the form of a comprehensive information base for strategic decisions. Furthermore, ready-made standard reports can contribute to the fulfillment of customer needs - for example, by providing regular information on the distribution of project times worked to identify cost drivers on the customer side.
As this blog series illustrates, it is possible to achieve the goal of simple and flexible project controlling for consulting companies in a complete and traceable manner. The prerequisite is seamless digital data maintenance, preferably in an ERP system, as well as a suitable BI tool for processing and preparing the available data.
The implementation at manaTec GmbH shows that the interaction of the modular ERP solution from Odoo as a data source in conjunction with Microsoft Power BI, as an ETL and reporting tool, forms an efficient and effective symbiosis. The integration of the data maintained via the Odoo modules and the subsequent process steps of data pre-processing, modeling and visualization merge smoothly, can be implemented without any hurdles, and soon produce the desired results in the form of sophisticated and meaningful reports for management. It should not go unmentioned that the free use of Power BI Desktop and the Odoo Community Version makes it possible to realize such an implementation free of any licensing costs.
Are you looking for a Business Intelligence solution for your company and are interested in helpful tools like Jedox or Power BI? Contact us now and we are a reliable partner at your side!