Ir al contenido

Connection Strategies (OData vs Native)

Comparative analysis and selection of the optimal method for connecting Power BI and Excel to Business Central analytical endpoints.

0views

At the consumption layer (Data Visualization), the way we connect our tools (Power BI or Excel) to Business Central determines the stability of scheduled updates. In our architecture, we evaluate two main pathways for ingesting data from our native APIs (objects Query in AL).

Microsoft provides a pre-built connector in Power BI Desktop (Get Data -> Online Services -> Dynamics 365 Business Central).

  • Visual Navigation: It allows us to explore the server's folder tree graphically: Entorno -> Empresa -> Advanced APIs -> Nuestro_Publisher -> Nuestro_Grupo -> Endpoint.
  • Integrated Authentication: Manages Azure AD OAuth tokens transparently.
  • The “Black Box”: It acts as an opaque intermediary. We have no exact control over how the Power Query engine builds the underlying HTTP URL it sends to the server.
  • Aggressive Caching: It is extremely prone to storing old API structures in memory. If we from DevSecOps add or rename a column in AL, this connector often crashes with metadata errors until the entire local cache is manually purged.

2. The Pure OData Connector (OData.Feed)

Sección titulada «2. El Conector OData Puro (OData.Feed)»

As data engineers, our standard recommendation is to skip the intermediate graphical interfaces and hit the endpoint directly via the OData Source connector.

  • Transparency: We control the exact URL. We know perfectly which endpoint, version, and company we are calling.
  • Parameter Injection: It allows us to inject flags directly into the Power Query M code, such as strict Timeout handling (Timeout=#duration(...)) or forcing the protocol version (Implementation="2.0").
  • Resilience: It is less prone to structural cache errors, as it is forced to read the OData $metadata document more frequently.

For production data pipelines, we exclusively use the pure OData connection coded in M.

This allows us to treat Power Query queries as if they were small scripts, facilitating their versioning and ensuring that if the Business Central API responds in 30 seconds, Power BI will consume that data in the exact same time without overheads introduced by visual connectors.

In the next article, we will detail the exact code we use for these ingestions.

  • ✓ URL Control: Priority use of the direct OData connector over the native connector for total visibility and debugging.
  • ✓ Parameter Injection: Definition of strict timeouts and protocol versions in the local engine.
  • ✓ Query Versioning: Treating OData queries as versionable M code within the Git repository.