How to Connect Power BI to Your ERP System

Your ERP system contains the most comprehensive operational data in your organization. Orders, invoices, inventory movements, purchase receipts, manufacturing work orders, employee timesheets, customer interactions --- every transactional event that drives your business is recorded there. The problem is that ERP systems are designed for recording transactions, not for analyzing them. The reports built into most ERPs are adequate for basic operational queries but fall apart when you need cross-functional analysis, trend detection, forecasting, or executive-level dashboards that synthesize data from multiple domains.

Power BI bridges this gap. It connects to your ERP's underlying database or APIs, transforms the transactional data into an analytical star schema, and delivers interactive dashboards that reveal patterns your ERP's native reports cannot show. But the connection is not simply "plug Power BI into the database." Each ERP platform has its own data structure, access method, and quirks that affect how you build the connection, model the data, and maintain the integration over time.

This guide covers the practical steps for connecting Power BI to six major ERP platforms: Odoo (our primary specialty), SAP, Microsoft Dynamics 365, Oracle, NetSuite, and QuickBooks. We focus on architecture decisions, connection methods, data modeling, incremental refresh, and the transformation patterns that turn raw ERP data into analytical gold.

Key Takeaways

• Every ERP integration follows the same three-phase pattern: Connect (access the data), Transform (reshape for analytics), Model (build star schema relationships)
• Odoo's PostgreSQL database provides the most direct and flexible Power BI connection --- SQL views and materialized views are the recommended approach for production deployments
• SAP requires the SAP HANA or SAP BW connector; direct database access is rarely permitted in SAP environments
• Dynamics 365 integrates natively through Dataverse, making it the simplest ERP to connect for organizations already in the Microsoft ecosystem
• Incremental refresh is essential for large ERP datasets --- full refresh of multi-million-row transaction tables is unsustainable
• Always create an analytical layer (views, staging tables, or a data warehouse) between the ERP and Power BI rather than querying operational tables directly
• Data transformation in Power Query should handle ERP-specific patterns: multi-currency normalization, fiscal calendar alignment, and status code translation

---

The Universal ERP Integration Architecture

Three-Layer Approach

Regardless of which ERP you use, the integration architecture follows three layers:

Layer 1: Extraction. Pull data from the ERP into Power BI. This happens through database connections (PostgreSQL, SQL Server, Oracle), API calls (REST, OData, SOAP), or intermediate storage (data warehouse, data lake, CSV exports). The extraction method depends on what the ERP supports and what your organization's security policies allow.

Layer 2: Transformation. Raw ERP data is transactional and normalized --- optimized for inserts and updates, not for analysis. Transform it into analytical shapes: aggregate transaction lines into summary tables, pivot status codes into readable labels, convert multi-currency amounts to a base currency, and align dates to your fiscal calendar. This happens in Power Query, SQL views, or a dedicated ETL tool.

Layer 3: Modeling. Structure the transformed data into a star schema with fact tables (sales, purchases, inventory movements) and dimension tables (customers, products, dates, warehouses). Configure relationships, write DAX measures, and build the semantic layer that report authors use.

Direct Database vs. API vs. Data Warehouse

Direct database connection is the fastest to set up and provides the most flexibility. You write SQL queries against the ERP's database and pull exactly the data you need. However, it requires database access (which some ERP vendors discourage or restrict), can impact ERP performance if queries are poorly optimized, and couples your analytics directly to the ERP's schema (which changes with upgrades).

API connection respects the ERP vendor's intended access patterns and does not risk impacting database performance. However, APIs are typically slower than direct database queries, may have rate limits, and often return data in hierarchical formats (JSON/XML) that require more transformation work in Power Query.

Data warehouse provides the cleanest separation. An ETL pipeline extracts data from the ERP nightly, transforms it into an analytical schema, and loads it into a dedicated database (Azure SQL, Snowflake, PostgreSQL) that Power BI connects to. This is the most maintainable long-term architecture but requires the most upfront investment to build the ETL pipeline.

For most organizations, we recommend starting with direct database connections (or APIs where direct access is not possible) and migrating to a data warehouse as the analytics environment matures and the number of data sources grows.

---

Connecting Power BI to Odoo

Why Odoo Is Ideal for Power BI

Odoo stands apart from most ERP platforms in its accessibility for analytics integration. It runs on PostgreSQL, one of the most Power BI-friendly databases. Its schema is well-documented, its table naming is consistent (module_model format), and its open-source nature means there are no licensing barriers to database access. If you operate Odoo, you already have everything you need to build world-class Power BI dashboards.

At ECOSIRE, Odoo-to-Power BI integration is one of our core competencies. We have built analytics solutions across the full Odoo module landscape --- sales, purchasing, inventory, manufacturing, accounting, HR, helpdesk, and project management. The patterns we describe here are battle-tested across implementations serving organizations with millions of ERP transactions.

PostgreSQL Direct Connection

Step 1: Configure PostgreSQL for remote access. If your Odoo instance and Power BI gateway are on different servers, PostgreSQL must allow remote connections. In postgresql.conf, set listen_addresses = '*'. In pg_hba.conf, add a line allowing the gateway server's IP address with MD5 authentication. Restart PostgreSQL to apply changes.

Step 2: Create a read-only database user. Never connect Power BI using Odoo's main database user. Create a dedicated read-only user:

CREATE USER powerbi_reader WITH PASSWORD 'secure_password_here';
GRANT CONNECT ON DATABASE odoo_production TO powerbi_reader;
GRANT USAGE ON SCHEMA public TO powerbi_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powerbi_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO powerbi_reader;

This user can read all tables but cannot modify data, insert records, or alter schema. The ALTER DEFAULT PRIVILEGES line ensures that new tables created by Odoo upgrades are automatically readable.

Step 3: Connect from Power BI Desktop. Open Power BI Desktop → Get Data → PostgreSQL database. Enter your server address, port (default 5432), and database name. Use the powerbi_reader credentials. Select "Import" mode for most tables (data loaded into memory) or "DirectQuery" for very large tables where you want live queries.

Step 4: Write custom SQL queries. Rather than importing raw Odoo tables, use custom SQL queries in the Advanced options to join and filter data at the database level. This is more efficient than importing raw tables and joining them in Power Query.

Essential Odoo Tables for Analytics

Odoo's database schema maps directly to its module structure. Here are the key tables for the most common analytical domains:

Sales Analytics:

Inventory Analytics:

Accounting Analytics:

Manufacturing Analytics:

Building Analytical Views in PostgreSQL

For production deployments, we strongly recommend creating SQL views that pre-join and pre-aggregate Odoo tables into analytical shapes. This moves complexity out of Power Query and into SQL, where it is easier to maintain and performs better.

Sales summary view example:

CREATE VIEW v_sales_analysis AS
SELECT
    so.id AS order_id,
    so.name AS order_reference,
    so.date_order::date AS order_date,
    so.state AS order_state,
    rp.name AS customer_name,
    rp.country_id,
    rc.name AS country_name,
    sol.product_id,
    pt.name AS product_name,
    pc.name AS product_category,
    sol.product_uom_qty AS quantity,
    sol.price_unit,
    sol.price_subtotal AS line_total,
    sol.discount,
    ru.login AS salesperson,
    so.company_id
FROM sale_order so
JOIN sale_order_line sol ON sol.order_id = so.id
JOIN res_partner rp ON rp.id = so.partner_id
LEFT JOIN res_country rc ON rc.id = rp.country_id
JOIN product_product pp ON pp.id = sol.product_id
JOIN product_template pt ON pt.id = pp.product_tmpl_id
LEFT JOIN product_category pc ON pc.id = pt.categ_id
LEFT JOIN res_users ru ON ru.id = so.user_id
WHERE so.state IN ('sale', 'done');

Power BI imports this view as a single table, pre-joined and filtered. No complex Power Query transformations needed. When Odoo's schema changes during upgrades, you update the SQL view once rather than modifying Power Query steps across multiple datasets.

Materialized views go further by pre-computing and storing the results, making Power BI refreshes dramatically faster:

CREATE MATERIALIZED VIEW mv_sales_daily AS
SELECT
    so.date_order::date AS order_date,
    rp.country_id,
    pt.categ_id AS product_category_id,
    so.user_id AS salesperson_id,
    COUNT(DISTINCT so.id) AS order_count,
    SUM(sol.product_uom_qty) AS total_quantity,
    SUM(sol.price_subtotal) AS total_revenue
FROM sale_order so
JOIN sale_order_line sol ON sol.order_id = so.id
JOIN res_partner rp ON rp.id = so.partner_id
JOIN product_product pp ON pp.id = sol.product_id
JOIN product_template pt ON pt.id = pp.product_tmpl_id
WHERE so.state IN ('sale', 'done')
GROUP BY so.date_order::date, rp.country_id, pt.categ_id, so.user_id;

-- Refresh nightly via cron
-- REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_daily;

This pre-aggregated view summarizes millions of order lines into thousands of daily summary rows. Power BI imports the summary for dashboards and uses drill-through to the detail view when users need line-level data.

Handling Odoo-Specific Patterns

Multi-company. Odoo supports multiple companies in a single database. Always include company_id in your queries and configure Power BI row-level security to restrict each user to their company's data.

State fields. Odoo uses text state codes (draft, sent, sale, done, cancel). Map these to user-friendly labels in Power Query or in your SQL view:

CASE so.state
    WHEN 'draft' THEN 'Quotation'
    WHEN 'sent' THEN 'Sent'
    WHEN 'sale' THEN 'Sales Order'
    WHEN 'done' THEN 'Locked'
    WHEN 'cancel' THEN 'Cancelled'
END AS order_status

Multi-currency. Odoo stores amounts in the transaction currency and the company currency. For Power BI dashboards, decide which currency to report in and use the appropriate columns. If you need real-time exchange rate conversion, join with the res_currency_rate table.

Many-to-many relationships. Odoo uses junction tables for many-to-many relationships (product tags, partner categories). These appear as tables named {model1}_{model2}_rel. Handle them with bridge tables in your Power BI data model.

For organizations looking for turnkey Odoo analytics, ECOSIRE's Power BI ERP integration services deliver pre-built Odoo dashboard templates covering sales, inventory, accounting, manufacturing, and HR --- fully customized to your Odoo configuration and business requirements. Our team has deep expertise in both Odoo and Power BI, eliminating the gap that typically exists when BI consultants try to work with an ERP they do not understand.

---

Connecting Power BI to SAP

SAP Connection Options

SAP environments are more restrictive about direct database access than open-source ERPs. Most SAP customers use one of these connection paths:

SAP HANA connector. For SAP S/4HANA customers, Power BI's native SAP HANA connector provides direct access to HANA views (Analytical, Attribute, and Calculation views). This is the highest-performance option and supports both Import and DirectQuery modes. Requires an SAP HANA user with SELECT privileges on the relevant views.

SAP BW connector. For organizations using SAP Business Warehouse, Power BI connects to BW queries (BEx queries or BW/4HANA composite providers). This leverages the analytical structures already built in BW, avoiding the need to model data from scratch in Power BI.

SAP OData services. SAP exposes business data through OData APIs (particularly SAP Gateway and SAP API Business Hub). Power BI's OData connector consumes these services. This approach respects SAP's authorization model but is slower than direct database access and may have pagination limits for large datasets.

Extraction to intermediate storage. For complex scenarios, extract SAP data using SAP's native tools (Open Hub, SLT replication, CDS views) into Azure Data Lake, Snowflake, or Azure SQL. Power BI connects to the intermediate storage. This is the most flexible and scalable approach for enterprise deployments.

SAP Data Modeling Considerations

SAP's data structures are complex and deeply normalized. Tables like VBAK (sales order header), VBAP (sales order items), KNA1 (customer master), and MARA (material master) use short, cryptic column names and store coded values that require join tables for translation.

When building Power BI models from SAP data:

Translate codes early. SAP stores country as a 2-character code, currency as a 3-character code, and material type as a code like "FERT" or "HALB." Join with the text tables (T005T for countries, TCURT for currencies, T134T for material types) in your extraction query, not in Power Query.

Handle SAP's date format. SAP stores dates as 8-digit strings (YYYYMMDD) with "00000000" for null dates. Convert these to proper date types in your transformation layer and handle the null date pattern.

Respect authorization objects. SAP's authorization model controls which data each user can access at a granular level. When extracting data for Power BI, ensure that your extraction respects these boundaries or implement equivalent row-level security in Power BI.

---

Connecting Power BI to Dynamics 365

Dataverse: The Native Path

Dynamics 365 stores data in Microsoft Dataverse, and Power BI has first-class Dataverse integration. This makes Dynamics 365 the easiest major ERP to connect to Power BI, especially for organizations already invested in the Microsoft ecosystem.

Dataverse connector. Power BI Desktop → Get Data → Dataverse. Authenticate with your Dynamics 365 credentials. Browse and select the tables (entities) you need. The connector respects Dataverse security roles, so users see only the data they are authorized to access.

Azure Synapse Link for Dataverse. For large Dynamics 365 datasets, Azure Synapse Link continuously replicates Dataverse data to Azure Synapse Analytics or Azure Data Lake. Power BI connects to Synapse/Data Lake instead of querying Dataverse directly. This eliminates performance impact on Dynamics 365 and provides a better platform for complex transformations.

TDS endpoint. Dataverse exposes a Tabular Data Stream (TDS) endpoint that Power BI can connect to using the SQL Server connector. This is useful for scenarios where you want to write custom SQL queries against Dataverse data.

Dynamics 365 Tables for Analytics

Key Dataverse tables for common analytical scenarios:

Sales: salesorder, salesorderdetail, opportunity, account, contact, product Service: incident (cases), knowledgearticle, entitlement, sla Finance: invoice, invoicedetail, payment, generaljournal Field Service: workorder, bookableresource, agreement

Dynamics 365's table structure is already relatively analytical --- entities like salesorder contain denormalized fields for the account name, owner, and status label. However, for optimal Power BI performance, still build a star schema rather than importing the Dataverse tables as-is.

---

Connecting Power BI to Oracle and NetSuite

Oracle E-Business Suite / Fusion

For Oracle EBS, use Power BI's Oracle Database connector with the Oracle client installed on the gateway machine. Oracle Fusion Cloud applications provide REST APIs that Power BI can consume through the Web connector or OData connector.

Oracle's BI Publisher reports can be configured to output data in formats that Power BI can consume, providing a vendor-supported extraction path that respects Oracle's business logic and security.

NetSuite

NetSuite provides multiple connection paths for Power BI:

SuiteAnalytics Connect (ODBC). NetSuite's ODBC driver allows Power BI to connect using the ODBC connector. This provides SQL access to NetSuite's dataset with a relational schema that is more analytics-friendly than the native NetSuite schema.

SuiteQL API. NetSuite's REST API supports SuiteQL, a SQL-like query language. Power BI can call this API through custom Power Query functions. This is useful for targeted extractions but less efficient than ODBC for large datasets.

Third-party connectors. Tools like CData provide optimized Power BI connectors for NetSuite that handle pagination, authentication, and schema mapping automatically.

---

Connecting Power BI to QuickBooks

QuickBooks Online

QuickBooks Online exposes data through a REST API that Power BI can consume. The connection requires an OAuth2 app registration in the Intuit Developer Portal and a custom Power Query connector or the Web connector with manual OAuth token management.

For most QuickBooks users, the simplest path is a third-party connector (CData, Skyvia, or similar) that handles authentication, pagination, and data type mapping. These connectors appear as native data sources in Power BI and abstract the API complexity.

Key QuickBooks Data for Power BI

Income statement data: Invoices, payments, credit memos, sales receipts Balance sheet data: Account balances, journal entries Operational data: Customers, vendors, products/services, estimates

QuickBooks data volumes are typically small enough that full refresh is fast (under 5 minutes). Incremental refresh is rarely necessary for QuickBooks integrations.

---

Incremental Refresh for ERP Data

Why Incremental Refresh Is Essential

ERP databases grow continuously. A mid-size company generates thousands of transactions daily. After a few years, the sales order table contains millions of rows. Refreshing the entire table every morning wastes gateway resources, database capacity, and time.

Incremental refresh tells Power BI to refresh only recent data (for example, the last 30 days) while keeping historical data cached from previous refreshes. A full refresh that took 45 minutes becomes a 3-minute incremental refresh.

Configuration Steps

Step 1: Create Power Query parameters. Create two parameters named exactly RangeStart and RangeEnd, both of type DateTime. Set default values (these are only used in Power BI Desktop; the service overrides them).

Step 2: Filter your source query. Apply a filter to the date column of your fact table using the parameters:

#"Filtered Rows" = Table.SelectRows(Source, each [order_date] >= RangeStart and [order_date] < RangeEnd)

This filter must fold to the source database for incremental refresh to work. If you are connecting to PostgreSQL (Odoo), the filter generates a WHERE clause that PostgreSQL executes, returning only the matching rows.

Step 3: Define the incremental refresh policy. In Power BI Desktop, right-click the table → Incremental refresh. Configure:

• Archive data starting: How far back to keep historical data (for example, 3 years).
• Incrementally refresh data starting: How recent data is refreshed (for example, 30 days).
• Only refresh complete periods: Check this to avoid partial-day data issues.
• Detect data changes: Enable if your source table has a reliable "last modified" column (reduces refresh time further by skipping unchanged partitions).

Step 4: Publish and configure. After publishing to the Power BI service, configure the scheduled refresh. The service creates time-based partitions and refreshes only the partitions that fall within the incremental window.

ERP-Specific Incremental Refresh Patterns

Odoo: Use write_date as the change detection column. Odoo updates this timestamp on every record modification, making it reliable for detecting changed rows.

SAP: Use the AEDAT (change date) field available on most SAP transaction tables. For HANA, materialized HANA views can provide the change tracking.

Dynamics 365: Dataverse entities have modifiedon timestamps that work well for change detection. Azure Synapse Link provides built-in change data capture.

Oracle: Use Oracle's rowscn or a dedicated last_update_date column. Oracle GoldenGate can provide change data capture for real-time scenarios.

---

Data Transformation Best Practices

Multi-Currency Normalization

Most ERP systems store transaction amounts in the transaction currency. For analytical dashboards, you typically need amounts in a single reporting currency.

Two approaches:

Source-side conversion. If your ERP stores both transaction and base currency amounts (Odoo stores amount_total in transaction currency and amount_total_company_currency in base currency), use the base currency column directly. This leverages the ERP's exchange rates and avoids discrepancies between operational and analytical reporting.

Power Query conversion. If you need to report in a currency different from the ERP's base currency, build an exchange rate table in your Power BI model and use DAX to convert amounts at report time. This approach is more flexible but requires maintaining exchange rate data.

Status Code Translation

ERP systems use internal codes for statuses, types, and categories. Translate these into user-friendly labels in your transformation layer, not in DAX. A visual that groups by "Draft, Sent, Confirmed, Done, Cancelled" is self-explanatory. A visual that groups by "1, 2, 3, 4, 5" is not.

For Odoo, the translation is straightforward since Odoo uses readable text states. For SAP, map the cryptic codes (AUFNR, MATNR, BUKRS) to business-friendly names. For Dynamics 365, use the option set labels rather than the underlying integer values.

Fiscal Calendar Alignment

If your fiscal year differs from the calendar year, build a fiscal calendar dimension that maps each date to its fiscal year, fiscal quarter, and fiscal period. This is essential for financial dashboards where "Q1" means fiscal Q1 (which might be July-September), not calendar Q1 (January-March).

Include both calendar and fiscal attributes in your date dimension so users can switch between perspectives without changing the data model.

For end-to-end assistance connecting Power BI to your specific ERP environment, contact ECOSIRE's analytics team to discuss your requirements. We specialize in Odoo analytics and provide pre-built Power BI template dashboards for every major Odoo module.

---

FAQ

Should I connect Power BI directly to my ERP database or use a data warehouse?

For initial deployments with a small number of reports and moderate data volumes (under 10 million rows), direct database connections are faster to set up and perfectly adequate. As your analytics environment grows beyond 10-15 reports or you start combining data from multiple source systems, a data warehouse becomes worthwhile. The warehouse provides a stable schema for Power BI (insulating it from ERP schema changes), better query performance (through pre-aggregation and indexing), and a single place to implement business logic (currency conversion, fiscal mapping, status translation). Most organizations start with direct connections and migrate to a warehouse within 12-18 months.

Will Power BI queries slow down my ERP system?

They can if not managed properly. Power BI scheduled refreshes execute SQL queries against your ERP database, which consume CPU, memory, and I/O resources. Mitigate this by scheduling refreshes during off-peak hours (early morning, late evening), creating read replicas for analytical queries (PostgreSQL streaming replication for Odoo, Always On for SQL Server), using materialized views that pre-compute results, and implementing incremental refresh to minimize the data scanned. For mission-critical ERPs, a read replica is the safest approach --- Power BI queries the replica while the production database remains unaffected.

How do I handle Odoo module upgrades that change the database schema?

Odoo module upgrades occasionally add, rename, or remove database columns and tables. If Power BI queries reference a renamed or removed column, the refresh fails. Mitigate this by using SQL views as an abstraction layer between the raw Odoo tables and Power BI. When an upgrade changes the schema, update the SQL view to reflect the new structure. Power BI continues to query the view's stable schema without any changes. After every Odoo upgrade, run your Power BI refresh manually to verify that all queries succeed before the next scheduled refresh.

Can I combine data from multiple ERP systems in a single Power BI report?

Yes, and this is one of Power BI's strongest capabilities. Organizations that operate different ERPs in different regions or business units can build unified dashboards that combine data from all systems. The key is building a common analytical schema (star schema) that normalizes the different ERP structures into a shared format. Customer dimension tables merge customers from all ERPs using a common identifier. Product dimensions align product categories across systems. Fact tables standardize amounts to a common currency and statuses to a common vocabulary. Composite models can connect to some sources via Import and others via DirectQuery.

How do I handle Odoo's many-to-many relationships in Power BI?

Odoo uses junction tables (named with the pattern {model1}_{model2}_rel) for many-to-many relationships, such as product tags, partner categories, and access control lists. In Power BI, import the junction table and create two one-to-many relationships: one from the first dimension to the junction table, and one from the second dimension to the junction table. This bridge table pattern correctly handles many-to-many filtering. Be aware that some Odoo many-to-many relationships create rows that complicate aggregation --- always verify totals against Odoo's native reports during validation.