Model Context Protocol (MCP) finally gives AI models a way to access the business data needed to make them really useful at work. CData MCP Servers have the depth and performance to make sure AI has access to all of the answers.
Try them now for free →
Use the API Server and Odoo ADO.NET Provider to Access Odoo Data in Microsoft PowerPivot
Use the API Server to connect to live Odoo data in the PowerPivot business intelligence tool.
This article will explain how to use the API Server and the ADO.NET Provider for Odoo (or any of 200+ other ADO.NET Providers) to provide Odoo data as OData services and then consume the data in Microsoft Excel's PowerPivot business intelligence tool. Follow the steps below to retrieve and edit Odoo data in Power Pivot.
About Odoo Data Integration
Accessing and integrating live data from Odoo has never been easier with CData. Customers rely on CData connectivity to:
- Access live data from both Odoo API 8.0+ and Odoo.sh Cloud ERP.
-
Extend the native Odoo features with intelligent handling of many-to-one, one-to-many, and many-to-many data properties. CData's connectivity solutions also intelligently handle complex data properties within Odoo. In addition to columns with simple values like text and dates, there are also columns that contain multiple values on each row. The driver decodes these kinds of values differently, depending upon the type of column the value comes from:
- Many-to-one columns are references to a single row within another model. Within CData solutions, many-to-one columns are represented as integers, whose value is the ID to which they refer in the other model.
- Many-to-many columns are references to many rows within another model. Within CData solutions, many-to-many columns are represented as text containing a comma-separated list of integers. Each value in that list is the ID of a row that is being referenced.
- One-to-many columns are references to many rows within another model - they are similar to many-to-many columns (comma-separated lists of integers), except that each row in the referenced model must belong to only one in the main model.
- Use SQL stored procedures to call server-side RFCs within Odoo.
Users frequently integrate Odoo with analytics tools such as Power BI and Qlik Sense, and leverage our tools to replicate Odoo data to databases or data warehouses.
Getting Started
Set Up the API Server
Follow the steps below to begin producing secure Odoo OData services:
Deploy
The API Server runs on your own server. On Windows, you can deploy using the stand-alone server or IIS. On a Java servlet container, drop in the API Server WAR file. See the help documentation for more information and how-tos.
The API Server is also easy to deploy on Microsoft Azure, Amazon EC2, and Heroku.
Connect to Odoo
After you deploy the API Server and the ADO.NET Provider for Odoo, provide authentication values and other connection properties needed to connect to Odoo by clicking Settings -> Connections and adding a new connection in the API Server administration console.
To connect, set the Url to a valid Odoo site, User and Password to the connection details of the user you are connecting with, and Database to the Odoo database.
You can then choose the Odoo entities you want to allow the API Server access to by clicking Settings -> Resources.
Additionally, click Settings -> Server and set the Default Format to XML (Atom) for compatibility with Excel.
Authorize API Server Users
After determining the OData services you want to produce, authorize users by clicking Settings -> Users. The API Server uses authtoken-based authentication and supports the major authentication schemes. Access can also be restricted based on IP address; by default, only connections to the local machine are allowed. You can authenticate as well as encrypt connections with SSL.
Import Odoo Tables in Power Pivot
Follow the steps below to import tables that can be refreshed on demand:
- In Excel, click the PowerPivot Window icon in the PowerPivot tab to open PowerPivot.
- Click Home -> Get External Data -> From Data Service -> From OData Data Feed.
-
Add authentication parameters. Click Advanced and set the Integrated Security option to Basic. You will need to enter the User Id and Password of a user who has access to the CData API Server. Set the password to the user's authtoken.
-
In the Base URL box, enter the OData URL of the CData API Server. For example, http://localhost:8032/api.rsc.
-
Select which tables you want to import and click Finish.
You can now work with Odoo data in Power Pivot.
