Power BI & Odoo-based controlling for consulting projects
Controlling for consulting projects: the technical implementation using Power BI and Odoo
18 June, 2020 by
Power BI & Odoo-based controlling for consulting projects
manaTec GmbH, Gerald Berndt
 

After the focus in the first blog part on controlling for consulting projects lay on the thematic introduction to project controlling in consulting, as well as its delimitation and the introduction of the systems involved, the second part of the blog series deals with the technical implementation with the help of these systems in order to be able to guarantee the goal of effective and efficient project controlling.

To connect the data from the Odoo tables managed via the modules, it is first necessary to enter the appropriate configuration parameters from Odoo into Power BI Desktop. Once the server, database, user and password details have been stored, access to all released tables from the Odoo database is available.

Data maintenance in Odoo at the level of the user interface and within the modules automatically creates the data in the tables at database level and thus notes any changes. In addition, cross tables for linking individual table fields are also created automatically in the background at the database level. For the desired representation of the project times and billing rate development, about 20 of the several hundred tables are necessary in order to capture all information and to be able to evaluate it multi-dimensionally. With the connection of these tables the initiation of the ETL steps begins, the core process behind every BI evaluation, so also in Power BI.

ETL, short for "extraction, transformation, loading", refers to the data preprocessing of the data obtained from the external sources (in this case from Odoo) in the course of the needs-based presentation in the later report. The extraction of the data is largely completed with the connection of the tables, so that the transformation of the tables and fields can take place in the next step. In this step, the actual data preprocessing and preparation is handled in a number of interrelated steps in order to be able to link, clearly assign and hierarchically arrange the data for evaluation in the final step.

For example, tables and their fields are created, calculated, eliminated, linked and mapped, and designations and data types are adjusted. After completing the basic logical structuring, the final tables can be loaded into the Power BI data model as so-called dimensions (hierarchy levels according to context) and facts (detailed data for aggregation and key figure development). In this, the tables are linked via unique IDs and prepared for the provision of cross-table queries, filtering and key figure development.

Extract, transform and load data from Odoo via Power BI.
Extract, transform and load data from Odoo via Power BI.

The described steps in the ETL process follow the classic Power BI workflow The query and preprocessing of the data already described are implemented in the Data Preprocessing step via the Query View of the tool. The prepared tables of the previous system can be supplemented with dimensions, key figures or columns via the Data View as part of the Data Modeling.

In the use case, for example, a generic time dimension is created, which ensures a hierarchical mapping of the time horizon from the individual day, through calendar weeks and months, to the year. In addition, key figures are created here that are relevant for the evaluation and are based on the entries of the fact tables generated from the Odoo data (attendance times, project times, invoice amounts, etc.). The linking of the final tables described below completes the data modeling and is implemented via the Modeling View of the tool.

Process of data extraction, pre-processing, linking and visualization in the Power BI Desktop.
Process of data extraction, pre-processing, linking and visualization in the Power BI Desktop.

The data model below shows the tables linked and prepared for project controlling based on the Odoo basic data, which are available for generating the reports in the last step. From the ten modules and underlying database tables mentioned in the first blog post, two dimension tables are created for filtering and narrowing down (employee, customer_manaTec) as well as three fact tables (HR, time recording, finance) for selecting, generating and comparing the corresponding desired key figures.

The dimension Time is additionally created via the Data View and completes the dimension tables. Here, project times and sales can be limited to a period of time to be compared. The Employees dimension can be used to break down the development of the key figures to the employees and subdivide them into departments. The Customer_manaTec dimension is used to differentiate between projects and customers, as well as to assign the key figures to billable or non-billable or internal projects.

The fact dimensions themselves provide the corresponding key figures. For example, the fact Finance provides all incoming and outgoing invoices. The Time Recording fact provides all hours written internally by employees, which can be attributed to corresponding tasks, higher-level tasks, projects, and finally customers, depending on the case. The HR fact is intended for comprehensive working time evaluations and provides for this use case the actual as well as the potential attendance time, taking into account holidays and vacation, depending on the employees' employment contract. With the linking of the tables via unique IDs, nothing stands in the way of the evaluation in the final step of the workflow.

Power BI Modeling View: Link between the transformed and loaded tables.
Power BI Modeling View: Link between the transformed and loaded tables.

The next blog post will discuss which key figures are ultimately mapped and how they can be prepared in the reports to serve the goal of simple project controlling. As part of the data visualization via the Report View of the tool, the Power BI workflow will be completed in this course.

You are 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!

 
Reduction of value added tax as part of the Corona recovery package
Configuration of the taxes in Odoo