The Pushdown Paradigm (ELT)
Architectural evolution from traditional ETL integrations towards the ELT paradigm via Pushdown Computation in Business Central.
In our Data Engineering and DevSecOps operations, one of the biggest challenges we faced when integrating Dynamics 365 Business Central with Power BI was the massive volume of historical data (more than 5 years of records).
Initially, the extraction of this data caused constant locks, extreme latency, and the dreaded network error: "El host remoto forzó la interrupción de la conexión". To resolve this, we had to abandon the traditional ETL architecture and adopt the ELT Paradigm (Extract, Load, Transform) driven by the concept of Pushdown Computation.
1. The Problem: The 'Pages' (Web Services) Bottleneck
Sección titulada «1. El Problema: El cuello de botella de las “Páginas” (Web Services)»Traditionally, the easiest way to extract data from Business Central is to publish a Page as an OData Web Service (for example, Page 38 'Item Ledger Entries').
However, Pages are designed for the User Interface (UI), not for massive data processing. When we request 350,000 rows from a Page, we trigger a lethal chain reaction on the server (NST - Navision Server Tier):
- In-Memory Rendering: The server attempts to construct the visual layout of the page in RAM for every single row.
- Trigger Execution: Graphical interface events are triggered (
OnOpenPage,OnAfterGetRecord). - FlowField Calculation (The Silent Killer): The page attempts to calculate complex formulas (such as current inventory) in real-time, row by row.
Result: The server chokes, exhausts its resources (Timeouts), and cuts the HTTP connection before being able to send the JSON. A single load could take over 15 minutes or fail outright.
2. The Solution: Pushdown Computation and Query Objects
Sección titulada «2. La Solución: Pushdown Computation y Objetos Query»To reduce loading times to mere seconds, we radically changed the architecture. We decided to stop asking Power BI (the destination tool) to filter and model the data, and instead 'pushed down' (Pushdown) that workload to the underlying Business Central engine (Microsoft SQL Server).
To achieve this, instead of consuming Pages, we developed Native APIs based on AL Query objects.
Why is a Query Object extremely fast?
Sección titulada «¿Por qué un Objeto Query es extremadamente rápido?»- Zero UI: A
Queryobject completely bypasses the presentation layer. It does not render windows, does not execute screen triggers, and does not load graphical libraries. - Pure SQL: The Business Central engine compiles our
Queryobject and translates it literally into an optimized SQL ServerSELECTstatement. - Payload Reduction: Only the strictly necessary columns that we have declared transit through the HTTP network.
3. Impact of the Architectural Change (Results)
Sección titulada «3. Impacto del Cambio Arquitectónico (Resultados)»By migrating our data ingestion from standard Web Services to our own Query APIs with server-side embedded filters, we achieved the following metrics:
- Extraction Time: Reduced from +15 minutes (and constant failures) to 30 seconds (100% success rate).
- Economic Viability: Since the full initial load is so efficient, we eliminated the technical need to implement complex hybrid architectures (Power Automate + SharePoint) or pay for Power BI Service Premium licenses to use native incremental updates.
- ERP Stability: By executing direct SQL loads, the ERP production node barely notices the massive extraction, ensuring that other logistics and finance users do not experience slowdowns in their daily operations.
4. Architecture and Best Practices Checklist
Sección titulada «4. Checklist de Arquitectura y Buenas Prácticas»- ✓ ELT Approach: Full initial load delegating heavy filters (
WHERE) to the ERP's SQL Server orchestrator. - ✓ UI Bypass: Absolute omission of
Pageobjects exposed as OData, eliminating rendering latency and graphics threads. - ✓ Stability: Drastic reduction in workload on the production transactional NST.

