5 Answers
We do not provide direct access. It conflicts with our security, performance and uptime SLA's.
If your Use Case is "building reports" then you are better off automating a backup and restore to a data warehouse.
If your Use Case is "auditing" then you are better off enhancing track changes on fields you care about.
Or you can move on premise and be responsible for keeping Odoo running fast and reliably 99.99% of the time.
Hi Ray,
can you direct us to any examples of the solution you propose? We need real time, read-only access to our .sh database. It seems rather wasteful of resources, but how would we mirror the data to a server that is not locked down to the same degree?
Ask around for "PostgreSQL database replication" - since underneath the magic that's all an Odoo database is. The data warehouse can live in a place where Odoo is not even a rumor (ie: a vanilla PostgreSQL database server). The fastest way to bring down an Odoo database on our infrastructure (that is optimized for the kinds of conversations over half a million people have had at over 75,000 organizations ABOVE the database layer) is to start having conversations with it that bypass our ORM layer, even read only ones. If you NEED this, you learn all the skills needed to self-host. I define NEED as "we tried to go without, we failed miserably (not just got hurt, but had to go on life support) and have ruled out every possible workaround we dreamed up as well as all the workarounds we were forced to try from the so called experts".
Thanks Ray, that policy position is clear!
I assume the database replication service would have to be running "inside " the .sh server and sending data out to an external server, and we can't request data from outside. What would be acceptable to Odoo to install on the .sh system? What implications would this have to the server "workers"?
You would SEND the backup to another server. Apps like https://apps.odoo.com/apps/modules/15.0/auto_backup/ can do that straight to a server with an open FTP port. You only need one way replication from SH to your outside server. The work of taking a backup is already something we've tuned the server to do (we do it daily without interrupting users). So you just pick a time when most of them are asleep. You are essentially scripting the creation and moving of that backup (or using the App), then scripting the pickup up and ingesting it on the other server (the one with no knowledge of Odoo). The workers on the Odoo server won't know anything about what you are doing, they will just notice the server a tiny bit slower than normal when the backup is being taken. You can delete the database on your external server prior to each "refresh". Then you can talk all night long to the replica without either the workers or the users on the Odoo server knowing anything about it. Also, as a so called expert, I would recommend you eliminate the Odoo Documents App and the fancy Odoo Spreadsheets that provide real time access to data via an interface that is pixel identical to GSuite, with additional functionality, including things like bidirectionality. If the audience looking at the sheet sees an ratio that is too high or too low, they click to see the transactions impacting the ratio and keep drilling down to find the Customer, Vendor, Employee, Product, Project or Campaign responsible.
One more point. In case you don't already know. Not all data Users see in Odoo is persisted in table fields. Python code is run to calculate values stored only in memory. So before you start your journey be sure the information you want to extract is available. Look at the Fields Menu in Odoo and filter for fields not stored (Stored = False) to check.
35% of the data from the database is not stored in PostgreSQL.
10,524 non-stored fields from a total of 30,172 fields in a full schema with all modules installed. Your percentage could vary depending on which modules/Apps are installed.
You have access to your database in the shell directly on odoo.sh
Look here under "Shell", but basically just type psql and your in
https://www.odoo.com/documentation/user/11.0/odoo_sh/getting_started/branches.html
You can not access through Postgres or any other DBMS, the correct way to access through the API or its default, you could access via shell (psql) and create queries of the entities you want and extract them with /copy
We tried for a while to work this out - instead we made a module that shadow-copies entities (via API) to a SQL Azure database, and then from there get Power BI Reports. Let me know if you'd like more info on that project.
We are (still!) in the same situation as Jan described on 24 May 2018: We want to connect a Business Intelligence tool to the database hosted on odoo.sh. We started looking into an automated download of the odoo.sh database, but a daily dump is too far out of sync. If you have a strategy you are happy to share, I'd greatly appreciate it!
Hi, What is the module to make shadow-copies?
I was looking at this in the context of connecting a Business Intelligence tool to the database hosted on odoo.sh. Unfortunately, it is not possible to connect directly to PostgreSQL (according to odoo support).
In my case the only option would be to build a connector.
Hi everyone,
It'd be very interesting to be able to connect Power BI or Google Data Studio for some additional representations of the data. Is this possible with the current version odoo.sh?
Thanks!
Hi, we are looking for the same use case.
We like to connect our Odoo db through a staging area (ETL process) with the data warehouse and in the end, we like to connect a BI Tool to the DWH.
Was anyone able to connect the Odoo db to a Datawarehouse? or implement odoo to his data infrastructure?
See my answer from yesterday and the discussion with James. Also, if you ANSWER a post, only the OP is notified. I happened to see your QUESTION and converted it into a comment.
35% of the data from the database is not stored in PostgreSQL.
10,524 non-stored fields from a total of 30,172 fields in a full schema with all modules installed. Your percentage could vary depending on which modules/Apps are installed.