How to integrate a cloud-based ERP with Microsoft Excel for reporting?

Thank you for reaching out. Please find the answers below.

Based on your requirements, here are the recommended approaches and the reasons why:

1. Power Query / Standard Connector vs. Custom .NET Connector

Recommendation: Use Power Query.
Reasons:

• Native Integration & Maintainability: Power Query is built directly into Excel (Data > Get Data) and designed for this exact purpose. It provides a no-code/low-code interface that business users can eventually maintain and adapt without developer intervention.
• Simplified Authentication: It has built-in support for modern authentication protocols (OAuth 2.0, API keys) commonly used by cloud ERPs like SAP and Odoo, handling secure credential storage.
• Cost and Time Efficiency: Avoids the significant development, testing, and ongoing maintenance overhead of building and deploying a custom .NET connector.

Only consider a custom .NET connector if you require extremely complex, non-standard authentication flows or data transformations that are impossible to achieve within Power Query.

2. OData Feeds vs. Custom Middleware

Recommendation: Prefer the ERP's native OData feeds if available.
Reasons:

• Standardization and Performance: OData is a standardized protocol that works seamlessly with Power Query. It is optimized for querying large datasets (e.g., filtering, sorting, and selecting columns at the source) which drastically reduces the data volume transferred to Excel.
• Reduced Complexity: It eliminates the need to build, host, secure, and maintain a separate middleware application.

Opt for a custom .NET middleware only if the ERP's REST API lacks OData support and requires complex, custom authentication, aggregation, or data shaping that cannot be performed by Power Query alone. The middleware would act as a proxy to simplify the API for Excel.

3. Handling Large Datasets (100k+ Rows)

Recommendation: Use Power Query's query folding capabilities to filter and aggregate data at the source before it is loaded into Excel.
Reasons:

• Source-Side Processing: The critical best practice is to push filters, aggregations (e.g., SUM, GROUP BY), and column selection back to the ERP server via the API call. This means only the final summarized result set is returned to Excel, not the entire 100k+ transaction dataset.
• Excel Data Model (Power Pivot): Load the processed data into the Excel Data Model, not a worksheet. The Data Model can handle millions of rows efficiently and enables powerful reporting with PivotTables and Power Pivot without freezing Excel.

4. Security Best Practices

Recommendation: Leverage Power Query's built-in security features.
Reasons:

• Secure Credential Management: Power Query integrates with the Microsoft Data Gateway and Excel's credential manager to securely store and encrypt authentication secrets (OAuth tokens, API keys), preventing hard-coded secrets in queries.
• OAuth Flow: It supports secure user-centric authentication flows (like OAuth 2.0) where users authenticate directly with the ERP system, ensuring passwords are not handled by Excel directly.
• Encrypted Connections: All connections are made over HTTPS, ensuring data in transit is encrypted.

Architecture Summary

ERP (OData or REST API) 
↓ 
Power BI Dataflow / Middleware (optional) 
↓ 
Semantic Model (with RLS, refresh, aggregations) 
↓
Excel (Analyze in Excel or Power Query)

️ Excel Limitations Note

• Excel worksheets can handle up to 1,048,576 rows, but performance degrades significantly beyond 100k rows.
• Use the Excel Data Model (Power Pivot) for large datasets—it supports millions of rows efficiently and enables fast PivotTable reporting.

Reference Documents

• Here are the reference document links:
• Power Query documentation – Microsoft Learn
  • Power Query OData Feed connector – Microsoft Learn
  • Introduction to dataflows and self-service data prep – Power BI
  • Creating a dataflow – Power BI
  • Dataflows best practices – Power BI
  • https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel
  • https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
  • Integrating External APIs in Excel – Exceldemy

Let me know if you need any further help with this. We'll be happy to assist.
If you find this helpful, please mark this as answered.