This project allows Power BI to query data from Odoo through the JsonRPC API.
Before this connector, the only way to query Odoo from Power BI was to connect directly to the PostreSQL database. This is impossible if, for example, the Odoo instance is hosted on odoo.sh or on some other platform which doesn't expose the database to the internet.
- Create a
[Documents]\Power BI Desktop\Custom Connectors
directory. - Download Odoo.mez and place it in that directory.
- Open Power BI Desktop and enable loading unsigned connectors (File > Options and settings > Options > Security > Data Extensions > Allow any extension to load without warning or validation)
- Restart Power BI Desktop
Read the PBI documentation if you have any trouble.
Currently a big limitation of this connector is that it doesn't support query folding. This means that if you load a table and filter it through the Power Query Editor UI, Power BI will download the whole table and then filter it locally instead of sending the filter definition to the server and downloading just the needed columns and rows. This is inefficient at best and can lead to an Odoo Server Error: Out of memory exception
at worst.
Because of this, the current recommended way to get data is through the search_read
function.
search_read
allows us to query an Odoo model. For more information see the Odoo documentation.
search_read(
model as text,
optional search_domain as list,
optional params as record,
optional set_schema as bool
)
Where:
-
model
: Technical name of the model to query. Examples: "res.partner", "account.invoice". -
search_domain
: An Odoo Search Domain.It's important to remember that lists in the M Language are enclosed in cuvy brackets (
{...}
). So the following python search domain[('name','=','ABC'), ('language.code','!=','en_US'), '|',('country_id.code','=','be'), ('country_id.code','=','de')]
should be written in M like
{{"name","=","ABC"}, {"language.code","!=","en_US"}, "|",{"country_id.code","=","be"}, {"country_id.code","=","de"}}
-
params
: A record containing any keyword parameter that we want to pass tosearch_read
. Can include:offset as Int64.Type
limit as Int64.Type
order as text
fields as list
context as record
Note: If
fields
is not specified, the default behaviour is to read all fields except thoseone2many
fields which reference models the user doesn't have permission to read. -
set_schema
: Whether or not to set the column types according to the field definition on Odoo. Defaults totrue
.
search_read(
"res.partner",
{ {"parent_name", "=", "Azure Interior"} },
[ fields = {"name", "email"}, order = "name" ]
)
name | id | |
---|---|---|
[email protected] | Brandon Freeman | 26 |
[email protected] | Colleen Diaz | 33 |
[email protected] | Nicole Ford | 27 |
- Install Visual Studio
- Install the Power Query SDK
- Clone this repository and open
Odoo.sln
- Optional: Install and configure the Auto Deploy extension to copy
Odoo.mez
to%USERPROFILE%\Documents\Power BI Desktop\Custom Connectors
after every build. - Compile (Build > Build Solution / Ctrl+Shift+B)
Unit tests can be executed by pressing Start on the Visual Studio toolbar or by pressing F5.
By default the unit tests assume there's a non-empty Odoo instance running at http://localhost:8069 with a database named db
. A docker-compose.yml
file is provided to easily set up such an instance. Just execute docker-compose up
, wait a bit, go to http://localhost:8069 and set up the database with sample data.
If you want to use some other server for the tests, simply edit test-server.json
.