The previous second part of the blog series "Evaluation of ERP projects from a consulting perspective" explained the basis on which the measurement tool is built and how the success factors can be hierarchically subordinated to the four dimensions of finance, process, customers, and learning & development via categories, criteria, and indicators. After the first two blog posts were mainly theoretical in nature, this final post will focus on the practical implementation and visualization of the results. The focus will be on the transfer of the measurable indicators into the Odoo survey module, and the subsequent connection, model mapping and presentation in Power BI.

Mapping of the indicators to be measured via Odoo surveys

With the operationalization of the model indicators, questionnaire templates were created in parallel as templates from which questionnaires can be derived directly with minor adjustments. They are therefore used to transfer the indicators into the surveys of the ERP environment. The questionnaires were thereby revised structurally compared to the measurement table or with the result of the operationalization. This serves the participants of the survey above all for a better overview by the elimination of single questions per criterion and for the better comprehensibility by more understandable criterion designations. In terms of content, the indicators have remained identical, so that in principle there are no changes for the evaluation in the measurement instrument. However, the restructuring requires a mapping of the Odoo survey to the measuring instrument so that the value origins of the survey can be clearly assigned to the measuring instrument accordingly.

Thus, according to the view and level of the addressee, five questionnaires have to be created for filling the measurement instrument:

• Customer-Operative,
• Customer-management,
• Consulting-Operative,
• Consulting-Management,
• Joint management.

he questionnaires are created using the Odoo survey module. These can be used universally for any project and provide the data basis for evaluation in Power BI.

Since the customers are usually already stored in the Odoo contact module , invitation links can be sent directly to the relevant contact persons via e-mail once the survey has been transferred. In the example project, five surveys were generated and sent to the addressees:

• Consultants and managers involved in the project with project knowledge of the manaTec GmbH
• Key users and managers involved in the project with project knowledge of the customer

After all questions have been answered by the addressees, the surveys are stored as complete answers in the survey module and the evaluation according to the measurement tool can begin.

Connection and transformation of tables in Power BI

The connection of Odoo in Power BI fundamentally follows a standardized ETL process. This has already been described in more detail in the preceding blog series in the blog "Power BI & Odoo-based controlling for consulting projects - Controlling for consulting projects: the technical implementation using Power BI and Odoo" and will therefore not be described in detail again here. Suffice it to say that the ETL steps in Power BI (Data Preprocessing, Data Modeling, Data Visualization) cover data preprocessing, transformation, modeling up to visualization.

With the connection of the tables, one is in the query view of Power BI and thus in the area of data preprocessing, in which the data is also cleaned, linked and prepared. The corresponding tables of the survey module of the PostgreSQL database on which the Odoo system is based are connected and made available for the subsequent data modeling. With the Odoo versions, changes can occur in the underlying data model as well as in the structures of the tables themselves (new, omitted or renamed fields), which in part have significant effects on the connection in Power BI. In the example of this integration in version 13, two linked tables are omitted compared to version 12, which significantly changes the ETL process by adapting links. Nevertheless, a problem-free integration is possible for both versions using different Power BI projects.

Creation of the data model and data enrichment in Power BI

Once the tables are connected and transformed as desired, they can be completed via the Modeling View and Data View with the key figure enrichment, addition of the time dimension and creation of the data model. This work covers the Data Modeling step of the ETL process defined in Power BI and directly precedes the report generation. Particularly noteworthy are the fact table Key Figure, which contains all the key figures calculated using the model, and the tables View and Category, which contain the dimensions derived from the BSC and the subcategories assigned to them and enable a multidimensional evaluation.

The indicators are calculated according to the definition of the contributions to success within the categories and dimensions in the measurement tool. Within the views, the actual value of the indicator is formed via the rounded arithmetic mean of the responses in order to be able to assign a unique characteristic value to each view. Between the views, the arithmetic mean of the two values is rounded up to give the indicator for the measurement table a unique characteristic value and an assignable actual value (shown in the following table in the MW_round field). The ratio of this actual value to the maximum possible value results in the score of the indicator, which has a proportional effect on the project success via the categories, views and dimensions. Scores are displayed in detail down to the indicator level via one of the reports generated in the Report View of Power BI, which at the same time reflects the structure of the measurement tool presented in the preceding Blog "Evaluation of ERP Projects from a Consulting Perspective - Introduction of a Measurement Tool: Development Process and Conceptual Implementation".

Visualization via flexible reports and dashboards in Power BI.

As a result, a variety of reports can be generated based on the data model. A possible form of presentation offers the representation of the project success as a key figure in the narrower and broader sense, multidimensionally as a network diagram, in the individual views as well as under visualization of the categories, as exemplarily shown in the following figure.

Via Power BI, elements of different reports can be flexibly combined with each other using dashboards. In an earlier blog series entitled "Power BI & Odoo-based Controlling for Consulting Projects - Controlling for Consulting Projects: Key Figure Development and Reporting in Power BI", we already illustrated some reports in the area of HR and Finance that can be mapped using Power BI and are used by manaTec GmbH. With the measurement of project success from a consulting perspective and the underlying model, the financial perspective and internal processes are already taken into account, but only via the questionnaire and the weighted allocation in the key figures of the measurement tool.

Via a dashboard, it would thus be possible to map explicit financial developments and influences of consultants in parallel to the elements of the measurement instrument. For example, as seen in the figure below, the development of billing rates, which were explained in detail in the aforementioned blog series, as well as the value contributions of employees to a project can be shown alongside the project metrics. Regardless of the project success, it is thus possible to illustrate which employees had which share in a project and how the project developed financially over the course. For example, anomalies over time can be used to draw conclusions that can be projected onto specific characteristics of the project.

With the now closed blog series, an attempt was made to provide insight into the development process of a conceptual model for the evaluation of ERP consulting projects, which moreover is already in use as such at manaTec GmbH. In general, the contents should provide points of contact for consultancies that are considering evaluating their services internally. In addition, the implementation is a case study of how a successful cooperation of science and practice could be implemented via a real project in the course of the development of the measurement tool. In the last section of the blog series, as already shown in the earlier blog series and in particular on the topic "Power BI & Odoo-based controlling for consulting projects - Controlling for consulting projects: the technical implementation using Power BI and Odoo", another useful use case for the interaction of Odoo and Power BI will also be illustrated once again. In the end, added value can be created effectively and efficiently via the connection and evaluation, which is particularly pronounced in sustainable decision support.

Are you interested in evaluating your projects internally or are you looking for a Business Intelligence solution? Contact us now and we will be happy to assist you as a reliable partner!