Dolibarr ERP/CRM is widely used by small and medium-sized enterprises to manage everything from invoicing and inventory to customer relations and accounting. While Dolibarr includes built-in reports and dashboards, businesses often require more advanced analytics and data visualizations. Power BI, Microsoft’s powerful business intelligence tool, provides this capability. This guide will walk you through the detailed steps and methods for exporting data from Dolibarr to Power BI for comprehensive and customized reporting—without paid plugins.

Table of Contents

  1. Why Connect Dolibarr to Power BI?

  2. Overview of Available Integration Methods

  3. Understanding Dolibarr’s Data Structure

  4. Preparing Your Dolibarr Environment

  5. Exporting Data via the REST API

  6. Exporting Data Using Direct Database Access

  7. Exporting Data with CSV or Excel Files

  8. Setting Up Power BI for External Data Sources

  9. Importing Dolibarr Data into Power BI

  10. Modeling Data in Power BI

  11. Building Custom Dashboards and Reports

  12. Automating Refreshes and Scheduled Updates

  13. Managing Authentication and Data Security

  14. Common Pitfalls and Troubleshooting Tips

  15. Best Practices and Final Recommendations

1. Why Connect Dolibarr to Power BI?

While Dolibarr includes native reports, they are mostly static and limited in visualization capabilities. Power BI can:

  • Visualize data with interactive charts, maps, and graphs

  • Merge Dolibarr data with other data sources (Excel, SQL, APIs)

  • Enable deep drill-down into key metrics

  • Automate dashboards for sales, finances, or inventory trends

By linking the two, you empower your team with real-time insights.

2. Overview of Available Integration Methods

There are several ways to export data from Dolibarr to Power BI:

  • REST API: Programmatically fetch data in JSON format

  • Direct MySQL/Database access: Query the Dolibarr database

  • CSV/Excel exports: Manual or automated data export

  • ETL tools (e.g., Power Query, Python scripts): For transformation before import

Each method has pros and cons in terms of automation, security, and complexity.

3. Understanding Dolibarr’s Data Structure

Dolibarr’s backend is typically powered by MySQL or MariaDB. Key database tables include:

  • llx_societe: Third parties (clients/suppliers)

  • llx_facture: Invoices

  • llx_product: Products and services

  • llx_commandedet: Order lines

  • llx_user: Users/employees

  • llx_stock_mouvement: Stock movements

Each table has foreign key relationships that must be understood for data modeling in Power BI.

4. Preparing Your Dolibarr Environment

Before exporting:

  • Ensure your Dolibarr installation is up to date

  • Have administrative access to the server or hosting

  • Enable the API module (if using API method)

  • Prepare database credentials for read-only access

  • Identify the tables and metrics you need for reporting

Create a checklist to track which data sets you want to export.

5. Exporting Data via the REST API

Dolibarr’s REST API allows access to various entities:

  • Enable API via Setup > Modules > Web Services

  • Generate an API key

Example to retrieve invoices:

curl https://yourdomain.com/api/index.php/invoices?DOLAPIKEY=yourkey

You’ll receive JSON data, which can be processed using Power Query or a script.

You can paginate, filter, and structure the data before importing it into Power BI.

6. Exporting Data Using Direct Database Access

This is the fastest method for large-scale, low-latency reporting.

Steps:

  1. Enable external access to the MySQL/MariaDB server (securely)

  2. Create a read-only user:

CREATE USER 'powerbi_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON dolibarr.* TO 'powerbi_user'@'%';

  1. Use the MySQL connector in Power BI to connect directly:

Server: yourdomain.com
Database: dolibarr

From here, you can select tables and write SQL queries directly in Power BI.

7. Exporting Data with CSV or Excel Files

Dolibarr allows you to export data from most list views (e.g., Invoices, Products).

Steps:

  1. Go to any module list view

  2. Click “Export”

  3. Choose the fields and export format (CSV/Excel)

  4. Save the file and import into Power BI manually or via folder refresh

This method is less scalable but useful for simple or ad hoc reports.

8. Setting Up Power BI for External Data Sources

Power BI can connect to various data sources:

  • Web (API)

  • SQL Server/MySQL

  • Excel/CSV

  • OData feeds

For API-based methods, use Power Query’s “Web” connector.

Example (JSON input):

let
    Source = Json.Document(Web.Contents("https://yourdomain.com/api/index.php/products?DOLAPIKEY=xyz"))
in
    Source

For SQL, select “MySQL Database” and provide credentials.

9. Importing Dolibarr Data into Power BI

Once connected:

  • Use Power Query to transform and clean data

  • Remove unnecessary columns

  • Merge related tables (e.g., invoices with invoice lines)

  • Convert date formats

  • Set data types properly

Load the cleaned data model into Power BI for visualization.

10. Modeling Data in Power BI

To make your reports useful:

  • Define relationships (e.g., between llx_facture and llx_societe)

  • Create calculated columns (e.g., profit = revenue - cost)

  • Use DAX for KPIs like:

Total Revenue = SUM(llx_facture.total_ttc)
Average Margin = AVERAGE(llx_facture.margin_rate)

  • Build date tables for time intelligence

Modeling is key to unlock advanced reporting.

11. Building Custom Dashboards and Reports

Create interactive reports using visuals such as:

  • Bar charts for monthly revenue

  • Pie charts for product category breakdown

  • Line graphs for customer growth

  • Maps for regional sales

Use filters and slicers for interactivity.

Examples:

  • Sales by region and month

  • Unpaid invoices by customer

  • Top 10 clients by revenue

12. Automating Refreshes and Scheduled Updates

For automated updates:

  • Use Power BI Gateway for SQL connections

  • Set refresh schedules in Power BI Service

  • Automate CSV updates with PowerShell or Python

API-based updates can use scheduled fetch scripts that save data to a shared folder.

Monitor refresh success via Power BI admin panel.

13. Managing Authentication and Data Security

Security practices include:

  • Use a read-only database account

  • Store API keys securely (not in queries)

  • Use parameterized queries in Power BI

  • Avoid exposing data endpoints to public

  • Limit dashboard access via Power BI roles

Data governance is as important as functionality.

14. Common Pitfalls and Troubleshooting Tips

Watch out for:

  • API rate limits (batch requests when needed)

  • Incorrect joins (causing duplication)

  • Time zone mismatches in date fields

  • Mismatched currency formats

  • Excel/CSV encoding issues (UTF-8 vs ANSI)

Use Power BI’s “Refresh Preview” and column profiling tools for diagnosis.

15. Best Practices and Final Recommendations

For smooth Dolibarr to Power BI integration:

  • Start with a clear data plan and schema mapping

  • Use SQL access for large or frequently updated data

  • Use APIs for remote, modular data fetching

  • Clean and model data properly before reporting

  • Monitor refresh and access logs

  • Build your dashboards iteratively with end-users

With careful setup and maintenance, you can turn Dolibarr into a data-rich reporting engine powered by Power BI.