Summary
This topic describes the considerations as of March 2023 on projecting the databases used for Odoo accountancy offerings on Invantive SQL partitioning, and vice versa.
Awaiting feedback by users of Odoo accountancy, it is proposed that the Invantive SQL-engine projects each individual Odoo database onto a separate Invantive partition to allow queries to run across all databases and load data into multiple databases.
The data model will be based on the Odoo model of installed apps and their versions of a database selected by the Invantive user. Multiple data containers will be necessary to cover all Odoo databases in a large Odoo accountancy setup when multiple Odoo versions are in use, such as when incrementally upgrading from Odoo 15 to Odoo 16. Typically one data container will be needed for all databases on a major Odoo version.
Introduction
This topic is part of a series on implementing an extensive Odoo driver, in combination with Invantive switching core applications to Odoo. A list of all topics in the series can be found on:
Partitioning
Many platforms partition their data within a subscription to meet business requirements. With partitioning, an individual user can access multiple more-or-less independent environments using the same credentials with typically similar functionality.
Some examples of partitioned environments include:
- Twinfield and Exact Online, with an “Office” or “company” containing the accounting of a legal entity;
- AFAS Profit, Oracle and NetSuite, with an “environment” containing all ERP-functionality of the application;
- Loket and NMBRS, with a “payroll company” containing all staff and payroll information of a legal company.
Invantive SQL and Partitioning
Given the historical background of Invantive SQL in reporting for large enterprises with typically hundreds of legal entities, the management of partitioned data is deeply integrated into the core of Invantive SQL and all related products. Although not all (cloud)platforms provide partitioning, the use statement provides easy selection of the list of partitions to run queries and/or data change statements on.
Invantive SQL improves the execution of SQL statements across a number of partitions in terms of throughput, reliability and manageability by employing a large number of advanced technologies, such as:
- read and write actions are selectively applied to partitions which meet certain SQL filters (example),
- execute read and write actions by a single SQL statement with a controlled degree of parallelism across the selected partitions (example).
Odoo, Accountancy and Partitioning
Entrepreneurs typically use Odoo with a single database for their whole environment, running either online on odoo.com or on-premises/self-hosted. Odoo provides functionality to manage multiple legal entities in a single database. Similar to NetSuite and AFAS Profit, Odoo provides shared data in an environment, such as shared customer data.
The data model for a single database is static as long as no apps are installed, uninstalled or changed. The data model of an Odoo environment may look similar to this:
Pricing of Odoo is even based on a user price per environment (“database”), which can be confusing when compared to price models of competing products.
For accountancy users, Odoo SA has projected their existing price model for enterprise edition, consisting of Free and Paid subscription, on a partitioned model which allows accountancy users to independently manage ledgers of hundreds or thousands companies at a bargain price.
For an accountancy environment, the financial data of each company is stored in a separate environment (“database”) on odoo.com. This Odoo database solely has the Documents and Accounting app, which both are included in the Free subscription with the Accountancy setup.
Databases can be tagged with properties to ease selection; this is similar to Exact Online’s “division classifications” sometimes used by accountants to relate the branch office, account manager or VAT frequency to companies. Tagging is inadequate for extensive data management, such as registering business data, but eases selection.
When a business user wants to increase their business efficiency by implementing other Odoo-apps additional (paid) users can be added. Paid users have access to all 50+ apps of Odoo, including trade, logistics, CRM and manufacturing. The current user-only pricing model makes gradually adding paid users very attractive, with improved revenues and/or savings directly aligning with increased software costs. Each individual employee must add a contribution, directly or indirectly, to the company’s revenue and/or bottom-line, whereas the broadness of the apps ensures that most employees can be served using the standard apps.
To ease the initial setup of an accounting database, the database of a company can be based upon template databases.
Odoo Multi-database API Limitations
As of March 2023, the use of large numbers of databases on Odoo.com is not yet covered very well by the external APIs.
However, the recently introduced “global API keys” for Odoo allow access to all databases a user has access to, without providing the user’s password. With a single global API key the external API of all databases can be accessed to retrieve and change data.
A major limitation is that there currently is no way to reliably and/or quickly acquire elements such as:
- list of databases (like
Officeson Twinfield orSystemDivisionson Exact Online), - list of tags of databases,
- version number representing the whole set all installed standard apps in a database,
- whether a database has customizations,
- environment data such as subscription.
In terms of security, the Odoo external API has major limitations for fine-tuning the contract between an app, Odoo.com and the user community. This is common status quo on platforms such as Odoo, currently still often used as on-premises/self-hosted solution for entrepreneurs. The auditing and model metadata driven approach of Odoo partially compensate for these deviations from industry security standards. It would be great when Odoo would support tripartite agreements using OAuth or another protocol.
Projecting Odoo Accountancy to Invantive SQL Partitioning
For ease of data integration and consolidation it is preferred that each Invantive SQL partition has an identical, or at least similar, data structure.
Also, for fair billing per legal entity and connected app used on Invantive SQL, it is preferred that each partition reflects data solely of one legal entity. This is not always possible, since data can be shared between companies, such as on AFAS Profit.
Given the structure of Odoo for accountancy and the Invantive SQL concepts for partitioning, the proposed approach is to map each database as a single partition into Invantive SQL.
A number of problems have been identified:
- limitations of the external API,
- data model variations between Odoo databases.
For each of the currently identified problems, one of the following sections describes their handling.
Data Model Variations between Odoo databases
To realistically and easily run queries and updates across large numbers of partitions, a data model shared across partitions is required.
For instance, Visma AccountView, despite providing a separate “database” in a file system per company and year, automatically manages large numbers of AccountView companies and years by automatically upgrading all in one upgrade action. You may find some AccountView databases with outdated data models, but these are exceptions in a properly configured AccountView environment.
Each Odoo database can have a different set of apps installed. For instance, database A may have installed a logistic app, whereas database B may have installed a sales app.
Also, a site can make it’s own customizations by adding fields to a table. This is left out of the scope, since it can be considered a kind of “custom app” without formal registration.
From what could be established, each app physically adds new tables. This is different from ERP packages such as Oracle Apps, which by default install thousands of tables, most of which are typically seldom if ever used in most environments.
Even more complex, the same Odoo app can be present in different versions in different databases (environments). For example, an app (standard or custom) can run version 1.2 in database A, but version 1.2.1 in database B, whereas all other apps have identical versions. This scenario is less common to occur in other ERP packages where versioning of individual apps is typically irrelevant, since they are in general released in a specific combination of versions. With good reason; in ERP history it was not uncommon to have major outages due to inconsistent versioning of elements when applying a simple bug fix on a single component.
For an Odoo accountancy environment with a few hundred companies and just as many databases, the setup may consist of:
- 150 databases with Odoo’s latest version of accounting and documents,
- 25 databases with Odoo’s previous version of accounting and documents,
- 2 databases with additionally the Sales app,
- 1 database with the manufacturing and chat app.
There seem to be some alternatives for a data model shared across partitions:
- Merge all data models together.
- Choose a data model.
Merge all data models together
The first option is to query each and every database for it’s metadata to establish a merged data model.
It is possible to query every database for their models and fields.
The query for metadata can cause a major resource consumption with a on-the-wire data volume per database between 100 KB and 9000 KB. Additionally, this query needs to be re-run at least daily since Odoo as of today has limited to no versioning or time travel on table contents (this might be an relatively easy addition to their core framework which would increase usability in low trust environments significantly, as well as increasing usability for ODS applications, paired with reduced load by external APIs on shared components such as the Odoo.com servers). For 500 databases, the volume is expected to be around 250 MB with a download clock time of 60 seconds.
An optimization to reduce the data volume and resources consumed would be to analyse the installed versions of modules first as an alternative for versioning of the table contents, assuming that data model changes only occur between versions. Then only download the data models when the versions have changed. This might probably work, but has not yet been verified in production scenarios.
For the sake of the discussion, we will assume that the data models can be retrieved easily across hundreds or thousands of databases, each with different installed versions.
The next step is merging the data models.
It is assumed that some kind of version management process following ITIL or alike is present, such that only a limited number of versions is present when Odoo achieves production status for the site. However, as more and more end users start using their Odoo database as part of their accountant’s offering, it is not unlikely that tensions grow between end user needs and centrally managed version management and some end users follow a deviating change management process.
It is also not unlikely that over years to come Odoo will reduce the number of versions available for production use, together with reduced use of custom code/Odoo.sh. This would force users into more continuous version upgrades of their odoo.com environments, similar to other SaaS-competitors.
Based on these dynamics it is reasonable for the next years to assume that somewhere between three and five versions of an installed app, and related data model may be present across all databases.
Each of these app versions may alter, extend or drop the semantics of an existing data element (column), or introduce new data elements. In the merging of data model, the choice can be made that identically named tables (models) convey the same meaning, as table names are typically less likely to change due to industrial inertia than their individual data elements. The merging of data models could then take the form of a union of all table names. For each table, all column names found in any of the app versions present would be added.
When upon querying a table is found not to exist in some database’s data model, no rows are returned. When a column is requested known not to exist in some database, it’s value is defaulted to something matching the data type and nullability property.
The merged data model allows cross-database and cross-data model version queries.
A disadvantage is that semantics of the data may vary depending on app version, for instance when the app developer decided to alter the meaning of a column or differentiate existing values into new values. This disadvantage can wreak havoc when the configuration of app versions changes, while external APIs used assume original semantics, be it for reporting or data integration/exchange. This makes the whole setup of a large number of databases more likely to integration issues like the Dutch IRS and the complexity and risks, although low, scales exponential with the number of connected databases.
An advantage of this approach is that all versions can be queried with a single query. This advantage pays off especially for non-financial data such as marketing, where a exact result is seldom needed. With financial accounting data and their typical requirements placed upon them, the gains of this advantage only pay off with exploratory use.
In case over time both the diverging and converging forces described come into play, ultimately the converging force of Odoo.com as vendor is expected to get the upper hand. It is expected that version differences between apps will be reduced, effectively leading to a single consolidated data model.
Choose a data model
The next option is to have the user specify a single database which specifies the data model. The user is responsible for ensuring that all databases selected for cross-database queries conform to the chosen data model.
Ensuring that the database conform to the chosen data model will introduce the need for change management. In this scenario, the diverging force of end users taking other version decisions as established by change management still applies.
The user of Invantive SQL should be explicitly aware of this issue and then has the opportunity to either:
- skip the database,
- accept deviating behavior or
- define a separate data container with another chosen data model, upon which separate queries applicable for that version are run.
When upon query a table is found not to exist in some database’s data model, the SQL engine retrieves the table definitions and checks that the table is indeed not present. It memorizes the table definitions too, so another table being queried on the database can use that knowledge.
Since accountancy queries will typically run across the accounting and documents module, the occurrence of such an event has a low frequency. However, it leaves however space for accountants to run queries on for instance CRM or project time sheets on all their customer’s databases, similar as Exact Online allows accountants access to industry data via the API (but not through the web UI).
Ultimately, no rows are returned when the table is not installed.
When a column triggers an error, the error is propagated to the user executing the query, under the assumption that installing an extra app does not add columns to existing tables. This hypothesis is to be verified, since we currently have insufficient understanding of the inner working of Odoo apps and their deployment scenarios. In case installation of an extra app may add columns to existing tables, a default could be returned.
As in the previous option, it is expected that the converging force of Odoo.com as vendor will get the upper hand and the chosen data model will equal the single consolidated data model over time.
Chosen option
Given the simplicity of the implementation, the expected dynamics in the software life cycle and the risks of unaware and uncontrolled problems, the preferred option is “Choose a data model”.
Handling External API Limitations for Odoo Accountancy
It is assumed that the Odoo external API limitations for accountancy use described will be resolved in a future release.
It is being assumed that there is either a manual or automatic way to specify the list of databases available to a user. For instance, a user can enter all database names (retrieved from the “My databases” form when available or else as described in Where can I find my current Odoo database name in Odoo itself?) in a text file and enter that list in the connection string or log on window for the Odoo SQL / Power BI driver.
Suggestions welcome
This topic describes the current proposed approach to mapping Odoo accountancy environments to Invantive SQL partitions. In case you have questions and/or suggestions for improvement or clarity, please leave them as an answer or send an e-mail to info@invantive.com.