Summary
All Invantive UniversalSQL products support Odoo in read and write mode. All tables can be easily loaded and read. For the transition to Odoo, Invantive has its partner list from Invantive Estate automatically synchronized to Odoo. Such synchronization is already running, among other things, towards Exact Online; the current accounting package.
The current (self-imposed) API limit of 60 calls per minute on Odoo does not really work smoothly. Removing this limit led to a loading speed of about 43,000 transactions per hour.
Loading and synchronizing data with Odoo via the external API proved (yet somewhat unexpectedly) to be very stable and predictable. This was a relief; the Odoo support channel is mediocre at best. Being able to create a representative test environment within minutes at no additional cost is definitely an advantage; this allows for better testing of the loading software.
The turnaround time of the project was approximately 60x shorter than on Exact Online, despite Invantive having over 10 years of experience with tens of thousands of Exact Online administrations and virtually no experience with Odoo. The amount of manpower required was approximately 15 times smaller for the same end result.
Odoo migration
Recent 22.1 releases of all Invantive products support efficient reading and writing into Odoo.
Implementation of Odoo within Invantive has since been resumed, after the problem with loading subscription code was resolved.
A number of business apps used by Invantive have since been reviewed and classified:
| App | Status | Explanation |
|---|---|---|
| ActiveCampaign | Pauzed | Replaced with Sendinblue for cost reasons. Later evaluate whether Odoo could also do this reliably, but given Odoo email issue seen as too big a risk for now. |
| Basecone | Candidate | Preparation; see Exact Online. |
| Calendly | Candidate | Preparation; first establish data integration with Invantive Estate. |
| Confluence | Live | Replaced by kennis app. |
| Declaree | Candidate | After accounting. |
| Discourse | Out-of-scope | No realistic alternative on Odoo. |
| DizzyData | Candidate | Preparation; see Exact Online. |
| Dlvr.it | Pauzed | Social Marketing app seems too buggy as reliable alternative. Over the course of year see if quality has improved. |
| Exact Online | Candidate | Preparation; first data integration with Invantive Estate and Exact Online for gradual implementation. |
| Invantive Estate | Out-of-scope | No realistic alternative on Odoo. |
| JIRA Service Desk | Live | Replaced by Projects app. |
| JIRA Software | Live | Replaced by Projects app. |
| Sendinblue Chat | Out-of-scope | Standard Odoo Chat is functionally and technically too limited for meaningful chat support. |
| Teams | Out-of-scope | Out-of-scope for now given high quality and real-time requirements, and limited support. |
Challenge: data loaded by mistake
During the implementation, the calendars of all employees were also linked to attempt to eventually replace Calendly with Odoo as well. In accordance with project objectives, Odoo’s booklets and videos were not extensively studied beforehand and the integration was turned on. This turned out to be wonderfully easy to make and also worked well.
A side effect was that contacts were also loaded if they were involved in a calendar appointment. In itself understandable; it gives a better picture of the organization. But actually the intention was to automatically synchronize the partner file with Odoo via a data integration with Invantive Estate.
What are Invantive Estate and Invantive Studio?
Invantive Estate is a back-office system, which centrally controls and captures Invantive’s administrative process, combined with an integrated real-time data warehouse. Invantive Estate is technologically based on Invantive Studio; Invantive Studio is similar to Odoo’s core layer where metadata-based business logic, data elements and processes are defined that automatically lead to a working business application with the same layer structure as Odoo.
However, there are differences between Invantive Estate and Odoo, and the underlying layers.
Details
The main difference is the database platform: Invantive Estate is based on Oracle RDBMS, while Odoo relies on PostgreSQL. Also, Invantive Studio’s focus is more on compliance and regulatory reporting given its typical application areas with real-time data warehousing and time travel functions, while Odoo Studio is much better at modeling user interfaces.
Compliance is important, but especially for personal data and financial transactions. Odoo does not offer time travel features, but it offers a significantly better audit trail than Exact Online in terms of both depth and breadth, allowing Invantive to realize improvements in being In-Control (with zero ambition to write thick ISO books) when making the switch.
Invantive Studio has remained mainly active as a Microsoft Windows product in combination with Oracle, while Odoo has developed further towards web technology. This can also be seen in the user interfaces.
Invantive Studio is traditional:
While Odoo Studio and apps are primarily browser-based:
One fest of recognition was also that even concepts had the same names, such as “Additional Business Rules.”
“Additional Business Rules” in Invantive Estate look like this:
And in Invantive Studio as:
In Odoo the contents are similar:
Synchronize partner file
We would like to align the contacts loaded into Odoo via employee calendars with the contacts as they are registered in the central back office Invantive Estate partner file. This means there needs to be a relationship between the contacts in the two packages, plus a bit of addition and/or clearing.
A contact in Odoo has two forms:
- a company
- a person
The form is indicated on the contact card through a radio button. This data is split in Invantive Estate between “Organizations” and “Persons.”
The data integration of the authoritative Invantive Estate toward Odoo thus requires merging two types of data into Odoo contacts. The relationship should ultimately ensure that each member of the group of persons and organizations in Invantive Estate appears at most (and preferably “exactly”) once in the Odoo. Conversely, each contact in Odoo should relate to exactly one person or exactly one organization in Invantive Estate.
Briefly, contacts may exist in Odoo that do not yet exist in Invantive Estate. By missing the relationship, these can be identified and then merged with an existing relationship, deleted in Odoo or registered in Invantive Estate.
For now, establishing the relationship is done only on the Odoo side by adding a field on contacts. This field contains a unique reference to the source system Invantive Estate.
For organizations from Invantive Estate, this is the text “LVR-,” plus the value of the organization’s technical key. For individuals from Invantive Estate, it is the text “GBR-,” plus the value of the individual’s technical key. By choosing the technical key, identification remains possible even if the name of a person or organization changes.
Cleansing already loaded contacts
Suddenly there were about 1,000 contacts in Odoo, with no relation to Invantive Estate. To test the viability of data integration with Invantive UniversalSQL on Odoo, a copy of the production database was made. This took several minutes.
Next step was to use Odoo Studio on contacts to add a field on the res.partner model. The field is called x_studio_orig_system_reference and is up to 240 characters long:
The field value is not included when the row is copied and is deliberately kept modifiable for all users until the process runs smoothly. The field is indexed because it will often be searched for with a point query whether a particular value already exists.
A programmatic check for uniqueness has been added as an automated action so that duplicates cannot occur without generating an error message:
using the following code:
newosr = str(record.x_studio_orig_system_reference)
if newosr is not None:
existing_records = env['res.partner'].search([('x_studio_orig_system_reference', '=', newosr)])
if len(existing_records) > 1:
raise UserError('OSR value ' + newosr + ' must be unique.')
A database was then defined on Invantive that contains both the Odoo backup (with alias ‘odoob’) and Invantive Estate (with alias ‘ora’):
In this database, the custom field can also be found on the table res.partner that Invantive UniversalSQL makes available for the Odoo model of the same name:
Then, using the next block of Invantive PSQL for all contacts registered in Odoo, the field x_studio_orig_system_reference was filled with the value of a person from Invantive Estate with the same e-mail address:
begin
for r in
( select pnr.id
, 'GBR-' || to_char(gbr.gbr_id) orig_system_reference
from res.partner@odoob pnr
join bubs_gebruikers_v@ora gbr
on gbr.gbr_email_adres = pnr.email
where pnr.company_type = 'person'
and pnr.x_studio_orig_system_reference is null
order
by pnr.id
)
loop
update res.partner@odoob
set x_studio_orig_system_reference = r.orig_system_reference
where id = r.id
;
end loop;
end;
It turned out that about 85% of all contacts could be matched in this way. The remainder was found to consist of different groups:
- known contacts using multiple e-mail addresses; these were merged in Odoo under the leading e-mail address with the “Merge” action.
- contacts that were not in the back-office system but were in the calendars. These were added to Invantive Estate.
Labels for Odoo contacts
Within Invantive, “labels” are used extensively. Labels are used to flexibly characterize objects so that, for example, an email about a driver can be sent specifically and targeted to individuals who are also likely to actually benefit from this news.
Labels are recorded in Invantive Estate as “Classifications”. Classifications in Invantive Estate have a tree structure, with the decimal point indicating a deeper level. An example: the classifier “Organization.School.Elementary” has three levels that zoom in further and further:
- it concerns an organization,
- which is also additionally a school, and
- even more strongly is also an elementary school.
Odoo also works on different parts of the apps with labels. Technically they are sometimes called differently (“categories”, “tags”,etc.) and sometimes they have a tree structure and sometimes it is just a flat list.
For contacts in Odoo, there are labels with a tree structure.
The following SQL code loads some main levels and then the corresponding values from Invantive Estate into Odoo:
create or replace table PartnerCategories1@InMemoryStorage
as
select 'KLE-' || to_char(kle.kle_id) x_studio_orig_system_reference
, true Active
, kle.kle_code_sub name
from bubs_klassificaties_v@ora kle
where 1=1
and kle.kle_diepte = 1
and ... criteria op welke kle.kle_code ...
insert into res.partner_category@odoop
( x_studio_orig_system_reference
, Active
, name
)
select x_studio_orig_system_reference
, Active
, name
from PartnerCategories1@InMemoryStorage
create or replace table PartnerCategories2@InMemoryStorage
as
select 'KLE-' || to_char(kle.kle_id) x_studio_orig_system_reference
, true Active
, kle.kle_code_sub name
, pnt.id parent_id
--
, kle_code
, pnt.name
, pnt.parent_id_label
from bubs_klassificaties_v@ora kle
join res.partner_category@odoop pnt
on pnt.name = ... filter op kle.kle_code ...
where 1=1
and kle.kle_diepte = 2
and ... filter op kle.kle_code ...
insert into res.partner_category@odoop
( x_studio_orig_system_reference
, Active
, name
, parent_id
)
select x_studio_orig_system_reference
, Active
, name
, parent_id
from PartnerCategories2@InMemoryStorage
Here the loading is performed once via the insert-statement.
It is also possible to use the synchronize-statement for this purpose:
synchronize PartnerCategories1@InMemoryStorage
to res.partner_category@odoop
with insert or update
identified by name
continue on first 500 errors
This also updates changes and limits the number of API calls required by executing changes only when necessary.
For example, the resulting list of contact labels in Odoo then looks like this:
Test Environment
With Odoo, it is possible to create a representative test environment within minutes at no additional cost. This is definitely an advantage when testing data integration; it allows for better testing of the software and reduces the risk of data errors during production release.
Contacts
All in all, these steps went smoothly. Loading the Organizations and People from Invantive Estate into Odoo contacts was then executed. Preliminary steps were done by the code below.
Note that Odoo only allows the selection of contact languages that are also installed, while Invantive Estate allows any language to be chosen from.
Note further that Odoo allows detail data to be loaded along with a contact, such as the labels. In two in-memory tables gke and lke a list of the labels per person and organization are therefore prepared in advance.
--
-- Odoo chooses contact language from installed languages, whereas
-- Invantive Estate allows any language.
--
create or replace table lang@InMemoryStorage
as
select code
, substr(code, 1, 2) lge_code
from lang@odoop
--
-- Labels on persons per person.
--
create or replace table gke@inmemorystorage
as
select gke.gbr_id
, listagg(distinct pcy.id, ', ') category_id
from bubs_gbr_kle_v@ora gke
join bubs_klassificaties_v@ora kle
on kle.kle_id = gke.kle_id
join res.partner_category@odoop pcy
on pcy.display_name = replace(kle.kle_code, 'Invantive.', '', '.', ' / ')
group
by gke.gbr_id
--
-- Labels on organisations per organisation.
--
create or replace table lke@inmemorystorage
as
select lke.lvr_id
, listagg(distinct pcy.id, ', ') category_id
from bubs_lvr_kle_v@ora lke
join bubs_klassificaties_v@ora kle
on kle.kle_id = lke.kle_id
join res.partner_category@odoop pcy
on pcy.display_name = replace(kle.kle_code, 'Invantive.', '', '.', ' / ')
group
by lke.lvr_id
create or replace table gbr@InMemoryStorage
as
select gbr.*
, 'LVR-' || to_char(gbr.lvr_id) lvr_osr
from bubs_gebruikers_v@ora gbr
Even if companies and contacts are united in 1 table in Odoo, it is necessary to load the companies in Odoo first. Contacts cannot be linked to their companies otherwise upon initial load.
The following table prepares the organization data from Invantive Estate for loading as companies in Odoo:
create or replace table PartnerSoll1@InMemoryStorage
as
select 'company' company_type
, 'LVR-' || to_char(lvr.lvr_id) x_studio_orig_system_reference
, lvr.lvr_naam
name
, lvr.lvr_email_adres email
, lvr.lvr_website_url website
, l.code lang
, lvr.lvr_werk_tel phone
, lvr.lvr_mobiel_tel mobile
, case
when lvr.lvr_btw_nummer = 'UNKNOWN'
then null
when lvr.lvr_btw_nummer = 'N/A'
then null
else lvr.lvr_btw_nummer
end
vat
, case
when lvr.lvr_kvk_nummer = 'UNKNOWN'
then null
else lvr.lvr_kvk_nummer
end
l10n_nl_kvk
, null function
, lvr.lvr_adres_regel_1 street
, lvr.lvr_adres_regel_2 street2
, lvr.lvr_plaats city
, cse.id state_id
, lvr.lvr_postcode zip
, cty.id country_id
, lke.category_id
, null parent_id
from bubs_leveranciers_v@ora lvr
left
outer
join lke@inmemorystorage lke
on lke.lvr_id = lvr.lvr_id
left
outer
join lang@InMemoryStorage l
on l.lge_code = lvr.lge_code
left
outer
join country@odoop cty
on cty.code = lvr.lvr_land
left
outer
join ( select * from country_state@odoop cse ) cse
on cse.country_id = cty.id
and cse.code = lvr.lvr_provincie
where 1=1
The above code calculates country_state in an inner-view to prevent a bug in Invantive’s current Odoo driver from leading to an error when applying a join set.
This table can be loaded into Odoo with a synchronize to res.partner.
Persons in Invantive Estate can be loaded as Odoo contacts using the following table:
create or replace table PartnerSoll2@InMemoryStorage
as
select 'person' company_type
, 'GBR-' || to_char(gbr.gbr_id) x_studio_orig_system_reference
, replace(trim(gbr.gbr_voornaam || ' ' || gbr.gbr_tussenvoegsel || ' ' || gbr.gbr_achternaam), ' ', ' ') name
, gbr.gbr_email_adres email
, gbr.gbr_website_url website
, l.code lang
, gbr.gbr_werk_tel phone
, gbr.gbr_mobiel_tel mobile
, null vat
, null l10n_nl_kvk
, gbr.gbr_functie function
, gbr.gbr_adres_regel_1 street
, gbr.gbr_adres_regel_2 street2
, gbr.gbr_plaats city
, cse.id state_id
, gbr.gbr_postcode zip
, cty.id country_id
, gke.category_id
, pnt.id parent_id
from gbr@InMemoryStorage gbr
left
outer
join res.partner@odoop pnt
on pnt.x_studio_orig_system_reference = gbr.lvr_osr
left
outer
join gke@inmemorystorage gke
on gke.gbr_id = gbr.gbr_id
left
outer
join lang@InMemoryStorage l
on l.lge_code = gbr.lge_code
left
outer
join country@odoop cty
on cty.code = gbr.gbr_land
left
outer
join ( select * from country_state@odoop cse ) cse
on cse.country_id = cty.id
and cse.code = gbr.gbr_provincie
where 1=1
Performance
For the first group of 30,000 contacts, the proprietary API limit of 60 calls per minute was maintained during loading. However, this took a long time even though the labels were included already in the data load.
Odoo does not provide an external API which can bundles multiple inserts and/or updates as one API call. Therefore, after 30,000 contacts, the decision was made to disable the API rate limit. Roughly 100,000 contacts were loaded without rate limit. The loading speed increased by factor 12 (12 contacts per second in serially executed SQL-code). Even with this significantly higher rate of API calls, the use of the external API was not restricted or blocked by the Odoo hosting environment.
For the future, we are still considering whether and how to limit the number of Odoo external API calls per minute. Odoo apparently has a better than average implementation of the API that can handle high numbers of transactions.
Odoo and Exact Online compared
Loading the classifications, persons and organizations from Invantive Estate into Odoo with an Invantive data integration query was very successful.
On Exact Online, a similar loading action took approximately 60x as much calendar days due to API limits, bugs and functional limitations of Exact Online. The difference in man-hours was smaller between Exact Online and Odoo; it took about 15-fold more man-hours to get the data loaded on Exact Online compared to Odoo.
Especially noticeable was the lack of bugs in general and absence of irrational automatic data changes in particular when loading Odoo. This was a concern beforehand though; Odoo has always been quite technically oriented and the concern was that we would have to spend months in conversation with the mediocre Odoo support channel to get bugs fixed. However, it turned out that in terms of external API, Odoo works extremely stable and predictable, especially compared to the baseline of Exact Online.