Overcoming Dynamics 365 Business Central API Limits in Denmark

1. The Dynamics 365 Business Central Ecosystem in Denmark

Denmark occupies a unique position in the global enterprise resource planning (ERP) landscape. As the historical birthplace of Navision (Navision Software A/S, founded in 1983 in Copenhagen), the Danish market has the highest density of Microsoft Dynamics 365 Business Central (D365 BC) implementations per capita in the world.

For the Danish mid-market (SMV – Små og Mellemstore Virksomheder), D365 BC is not merely a popular software choice; it is the definitive operational standard. The system dictates the data flow across multiple core sectors of the Danish economy:

  • Retail: Subsidiaries and mid-tier operations supporting conglomerates like Salling Group, Coop, and Jysk rely heavily on BC for inventory and financial reconciliation.
  • Manufacturing: The backbone of Danish industrial SMVs utilizes BC for bill of materials (BOM), capacity planning, and supply chain execution.
  • Logistics and Distribution: Order fulfillment, warehousing, and transportation management are deeply integrated into BC modules.
  • B2B Commerce and Services: Professional service firms and wholesale distributors use BC as the single source of truth for invoicing, time registration, and general ledger operations.

Empirical data indicates that 70% to 80% of Danish mid-market enterprises operate on Dynamics 365 Business Central. Consequently, any systemic limitation within this ERP platform automatically becomes a nationwide data engineering bottleneck.

2. The Architectural Bottleneck of the SaaS Migration

Historically, Danish companies operated Navision or older Dynamics NAV versions on-premise. This architecture provided data engineering and Business Intelligence (BI) teams with unrestricted, direct access to the underlying Microsoft SQL Server database. Analytical workflows were straightforward:

  1. Direct extraction using SQL Server Integration Services (SSIS).
  2. Creation of daily batch loads using standard T-SQL scripts.
  3. Direct connection of Power BI or SQL Server Analysis Services (SSAS) to the database views.

The mandatory paradigm shift to the D365 BC SaaS (Cloud) environment fundamentally breaks this legacy workflow. Microsoft strictly isolates the underlying database infrastructure in the cloud. Direct SQL Server access is entirely revoked. The only supported mechanism for data extraction is through application programming interfaces, specifically the OData V4 API.

This transition transforms a simple database query process into a complex web data extraction problem, introducing severe architectural bottlenecks.

3. Deep Dive into OData API Limitations

The D365 BC OData API is engineered for transactional integrations (e.g., pushing a single order from a webshop or retrieving a specific customer record), not for massive analytical data extraction. Microsoft enforces hard operational limits to protect the multi-tenant SaaS infrastructure. These limits are non-negotiable and cannot be expanded via support tickets:

  • Rate Limiting: A hard cap of 6,000 requests per 5-minute rolling window per environment.
  • Concurrency Limits: A maximum of 5 simultaneous API connections. Exceeding this results in immediate connection drops.
  • Pagination Constraints: The API returns a maximum of 5,000 records per response payload (server-driven pagination using @odata.nextLink).
  • Lack of Change Data Capture (CDC): The API does not natively support push-based streaming or robust transaction log reading. Engineers must build custom incremental logic using Last_Date_Modified timestamps, which are notoriously unreliable in Navision’s nested table structures (e.g., changes in a posted sales invoice line might not update the header timestamp).
  • Payload Inefficiency: OData responses are heavily nested JSON objects, carrying significant metadata overhead compared to binary tabular formats, slowing down network transfer speeds.

4. The BI Crisis: Power BI vs. Transactional Systems

The standard reaction of mid-market companies migrating to BC SaaS is to connect Power BI directly to the OData endpoints. This creates an immediate and severe operational crisis.

When a BI developer attempts to refresh a Power BI dataset containing millions of rows from core tables like Item Ledger Entry, Value Entry, or G/L Entry, the following sequence occurs:

  1. Power BI initiates a massive sequential pull via OData.
  2. The D365 BC server allocates significant compute resources to serialize database tables into JSON.
  3. The API hits the rate limit, returning HTTP 429 Too Many Requests.
  4. Power BI dataset refreshes fail randomly after running for hours.

More critically, heavy analytical queries compete for the same compute resources as the ERP’s operational users. This results in the degradation of the ERP’s performance: accountants experience screen freezes during month-end closing, warehouse workers face delays when scanning barcodes, and API endpoints for e-commerce platforms time out.

5. The High Cost of the Azure Default Path

Microsoft’s prescribed solution for this bottleneck is migrating the analytical workload to the Azure ecosystem. The standard reference architecture includes:

  • Azure Data Factory (ADF) for orchestration.
  • Azure Synapse Analytics or Microsoft Fabric for the data warehouse layer.
  • Azure Data Lake Storage (ADLS) for the raw data layer.
  • Azure SQL Managed Instance.

While technically sound for enterprise corporations, this path is highly problematic for the Danish SMV sector. The Azure analytical stack requires significant upfront provisioning (capacity pricing). Running Data Warehouse Units (DWUs) in Synapse, alongside ADF pipelines, introduces high monthly recurring costs (OpEx) even when the system is idle. Furthermore, maintaining this infrastructure requires specialized Azure Data Architects and DevOps engineers—resources that are scarce and highly expensive in the Danish labor market.

1. The Strategic Solution: Smart Pipeline via Google Cloud Lakehouse

To resolve the structural limitations of D365 BC SaaS for the Danish mid-market, the architecture must bypass the direct BI-to-API connection. The solution is an event-driven, decoupled “Smart Pipeline” that extracts data efficiently, stores it cheaply, and transforms it using a serverless data warehouse.

Google Cloud Platform (GCP) provides the exact primitives required to build this solution at a fraction of the cost and complexity of the Azure alternative. The architecture follows a strict Extract, Load, Transform (ELT) pattern utilizing Cloud Run, Cloud Storage, BigQuery, and Dataform.

2. Phase 1: Extraction Engineering (Cloud Run Microservice)

The extraction layer must respect Microsoft’s API limits while maximizing throughput. This is achieved through a serverless microservice deployed on Google Cloud Run. Cloud Run scales dynamically to handle parallel extraction tasks but allows developers to strictly cap maximum concurrency to avoid triggering D365 BC lockouts.

Core Extraction Logic:

  • Pagination Handling: The Cloud Run service programmatically follows the @odata.nextLink tokens, downloading pages of 5,000 records sequentially.
  • $batch Endpoint Utilization: Instead of querying individual endpoints, the service groups multiple queries into a single HTTP request using the OData $batch feature, drastically reducing network round-trips and optimizing the 6,000 requests/5-minute limit.
  • Exponential Backoff and Retry: If an HTTP 429 (Too Many Requests) or HTTP 503 (Service Unavailable) is encountered, the service implements an exponential backoff algorithm with jitter to pause and retry without failing the entire pipeline.
  • Incremental Loading (Delta Pulls): The service stores a high-water mark (the latest SystemModifiedAt timestamp) in GCP and only requests records altered since the last successful execution.

The Critical Architectural Key: Read-Replica Routing

The most important technical feature of this Cloud Run service is the inclusion of a specific HTTP header in every API request:

Data-Access-Intent: ReadOnly

When D365 BC receives this header, the internal load balancer routes the heavy analytical query away from the primary production database (OLTP) and directs it to a secondary, synchronized read-replica.

  • Impact: Zero performance degradation for ERP users. The extraction pipeline can run massive historical data pulls during peak business hours without causing a single interface freeze for accountants or warehouse staff.

3. Phase 2: Raw Storage (Google Cloud Storage)

As the Cloud Run service extracts the JSON payloads from the API, it immediately streams them into Google Cloud Storage (GCS).

  • Format Conversion: The JSON data is converted in-memory to Apache Parquet format before being written to GCS. Parquet is a columnar storage format that is highly compressed and optimized for analytical querying.
  • Partitioning: Data is partitioned in the bucket by date (e.g., gs://bc-raw-data/sales_invoice_lines/year=2026/month=09/).
  • Cost Efficiency: GCS provides almost infinitely scalable storage at pennies per gigabyte. This creates a secure, immutable Data Lake where all raw ERP history is archived indefinitely without impacting database costs.

4. Phase 3: The BigQuery Compute Engine

Google BigQuery serves as the core analytical engine. Unlike Azure Synapse, BigQuery is a true serverless data warehouse.

  • External Tables: BigQuery can read the Parquet files directly from GCS via External Tables. There is no need to run a secondary “Load” process.
  • On-Demand Pricing: This is the most critical financial advantage for the Danish SMV sector. BigQuery operates on an on-demand pricing model. The client pays zero dollars for compute infrastructure when no queries are running. Costs are incurred only per terabyte of data scanned during query execution. For a mid-market company running daily BI refreshes, this translates to monthly compute costs measured in tens of dollars, not thousands.
  • Zero DevOps: There are no clusters to provision, no indexes to rebuild, and no storage arrays to manage. BigQuery auto-scales thousands of workers instantly to process a query and spins them down immediately upon completion.

5. Phase 4: SQL Transformation via Dataform

Raw D365 BC data is notoriously difficult to analyze. It relies on complex, normalized structures (e.g., combining Item Ledger Entry, Value Entry, and Sales Invoice Line to calculate true margins).

To solve this, the architecture utilizes Google Dataform, a free SQL orchestration service built directly into BigQuery. Dataform serves as a direct, free alternative to dbt.

  • SQL Orchestration: Dataform allows data engineers to write modular SQL scripts that transform the raw BC tables into clean, denormalized dimensional models (Star Schema).
  • Version Control: All transformation logic is stored in a Git repository, providing full version control, code review processes, and CI/CD capabilities.
  • Dependency Management: Dataform automatically builds a Directed Acyclic Graph (DAG) to ensure tables are processed in the correct order (e.g., dimensional tables like Customers and Items are updated before the Sales fact table).
  • Data Quality Testing: Assertions are built directly into the SQL code to verify data integrity (e.g., ensuring Invoice_Amount is never null, or Customer_ID always maps to a valid record).

By supplying the client with pre-built Dataform templates for standard BC modules (Finance, Inventory, Sales, Procurement), the deployment time for a fully functional data warehouse is reduced from months to weeks.

6. FinOps and Total Cost of Ownership (TCO): The Economics of Serverless vs. Provisioned Compute

A common critique of cloud analytics architectures is that cost projections often look overly optimistic on paper while introducing unpredictable billing spikes in production. To evaluate the true financial impact for a typical Danish mid-market (SMV) company—defined here as an enterprise with approximately €50M in turnover, 200 employees, and 500GB of historical transactional data in Dynamics 365 Business Central—we must compare the two primary competing architectural paradigms: Provisioned Compute (Azure) versus Serverless On-Demand (Google Cloud).

The Azure Provisioned Model: Paying for Idle Time

In a traditional Microsoft-centric stack utilizing Azure Data Factory (ADF) and Azure Synapse Analytics, the economic model requires provisioning dedicated, reserved computing infrastructure.

  • Synapse Dedicated SQL Pools: To handle analytical workloads and Power BI direct connections, a company must deploy a minimum instance, such as a DW100c compute tier. This tier costs approximately €1.40 per hour, running 24 hours a day to maintain active connectivity for reporting users.
    • Monthly Cost: 730 hours $\times$ €1.40 = ~€1,022/month.
  • Data Factory & Integration Runtimes: Orchestrating pipeline execution, data movement, and scheduled triggers incurs costs based on pipeline run frequency and integration runtime hours.
    • Monthly Cost: ~€150–€200/month.
  • Total Baseline Azure Infrastructure: ~€1,200/month, incurred constantly regardless of whether the business is actively querying data or closed for the weekend.

The Google Cloud Serverless Model: Paying Only for Active Execution

Google Cloud Platform fundamentally alters this financial equation by decoupling storage from compute and utilizing a true serverless consumption model.

Total Baseline GCP Infrastructure: ~€65–€70/month. up IT budgets to invest in data modeling and business insights rather than virtual machines.

Storage Costs (GCS + BigQuery Storage): Storing 500GB of raw JSON/Parquet files in Google Cloud Storage and 500GB of structured, active tables in BigQuery incurs flat storage fees. At standard regional rates, this equals ~€20/month.

Compute Costs (BigQuery On-Demand): BigQuery charges strictly for the volume of data scanned by analytical queries, at a rate of $6.25 (approximately €5.70) per Terabyte scanned. Assuming nightly Dataform incremental models process 50GB of delta changes, and daily Power BI user activity scans 200GB of data, total monthly data scanning reaches roughly 7.5 TB.

Calculation: 7.5 TB $\times$ €5.70 = ~€42/month.

Extraction Compute (Cloud Run): The microservice executing the paginated OData extraction runs for a short duration daily. Because Cloud Run bills strictly down to the nearest 100-millisecond increment for actual CPU and memory utilization, this workflow easily falls within the generous Google Cloud Free Tier (180,000 vCPU-seconds) or costs <€5/month.

Orchestration (Dataform): Built natively into BigQuery, Dataform incurs €0 in infrastructure licensing fees.

7. Native Marketing and E-commerce Integration

Beyond ERP analytics, modern mid-market companies require cross-domain insights. D365 BC handles the backend, but the frontend generates critical data via Google Analytics 4 (GA4), Google Ads, and e-commerce platforms (e.g., Shopify or Magento).

Google Cloud provides a massive strategic advantage here. GA4 features a native, free, and automated export directly into BigQuery. Server-side Google Tag Manager (sGTM) can be deployed natively on Cloud Run.

By placing the D365 BC Lakehouse inside BigQuery, data architects can instantly join ERP financial data with front-end marketing data. This enables advanced use cases previously unavailable to the SMV sector:

  • Calculating true Customer Acquisition Cost (CAC) based on realized gross margins from Navision, not just top-line revenue from GA4.
  • Analyzing return rates and warehouse fulfillment times against specific marketing campaigns.
  • Feeding precise lifetime value (LTV) models back into Google Ads algorithms for optimized bidding.

8. Advanced Extraction Mechanics: Navigating the OData $batch Endpoint

To strictly adhere to the 6,000 requests per 5-minute limit while extracting historical tables containing tens of millions of rows (e.g., G/L Entry), the pipeline cannot rely on standard iterative GET requests. The architectural mandate is the implementation of OData $batch processing.

The $batch Protocol: The $batch endpoint allows clients to combine multiple HTTP requests into a single JSON payload. In the context of D365 BC, a single $batch request can encapsulate up to 100 individual GET operations.

  • Throughput Multiplication: By wrapping pagination requests (@odata.nextLink) inside a $batch envelope, the Cloud Run microservice effectively retrieves 500,000 records (100 requests × 5,000 records per page) while consuming only one API call against the rate limit threshold.
  • Error Isolation: If one sub-request within the batch fails (e.g., due to a temporary database lock on a specific partition), the OData protocol returns a 200 OK for the batch envelope, but embeds specific 4xx or 5xx error codes inside the array of responses. The Cloud Run service must parse the multipart response array, isolate the failed sub-requests, and re-queue them for the next exponential backoff cycle, ensuring zero data loss.

9. Security and Authentication: Entra ID to GCP

A highly secure, zero-trust authentication flow is non-negotiable when extracting financial data. Basic Authentication (Web Service Access Keys) was deprecated by Microsoft in 2022. The only supported mechanism is OAuth 2.0 via Microsoft Entra ID (formerly Azure Active Directory).

Service-to-Service Authentication Flow:

  1. Entra ID App Registration: A dedicated Service Principal (App Registration) is created within the Danish company’s Microsoft tenant.
  2. API Permissions: The App Registration is granted Dynamics 365 Business Central > API.ReadWrite.All application permissions. Consent must be granted by the Global Administrator.
  3. Secret Management: The Client ID and Client Secret generated in Entra ID are securely stored in Google Cloud Secret Manager. They are never hardcoded in the Cloud Run environment variables.
  4. Token Exchange: Before initiating the OData extraction, the Cloud Run service calls the Microsoft identity platform ([login.microsoftonline.com/](https://login.microsoftonline.com/){tenant_id}/oauth2/v2.0/token) to exchange the credentials for a short-lived JSON Web Token (JWT).
  5. Execution: The JWT is passed as a Bearer token in the Authorization header of every request to the D365 BC environment.

10. Handling Navision-Specific Data Anomalies

D365 BC retains legacy architectural patterns from its Navision days, which create specific challenges for modern cloud data warehouses like BigQuery. A robust pipeline must programmatically handle these anomalies during the extraction and loading phases.

  • Default Dates: BC handles “empty” dates not as standard NULL values, but as 1753-01-01 or 0001-01-01. If pushed directly into BigQuery, these can cause out-of-bounds errors for standard DATE or TIMESTAMP columns depending on the schema definition. The extraction microservice must cast these legacy defaults to standard SQL NULL values before writing to Parquet.
  • FlowFields and FlowFilters: BC utilizes calculated fields (FlowFields) that compute aggregations dynamically on the ERP server. Extracting FlowFields via OData is highly compute-intensive for the ERP and often times out. The architectural best practice is to never extract FlowFields. Instead, only raw transactional tables are extracted, and the aggregations (sums, averages) are mathematically reconstructed inside BigQuery using Dataform SQL.
  • Option Strings: BC often stores categorical data as integers in the database (e.g., Document Type: 0, 1, 2, 3), which the application layer translates to strings (Quote, Order, Invoice, Credit Memo). The API exposes these as string enums. The pipeline must ensure strict type mapping, casting these enum strings to BigQuery STRING columns to preserve analytical readability.

11. Dimensional Modeling Strategy in Dataform

Once the raw, nested JSON/Parquet data lands in BigQuery, the true value of the Cloud Lakehouse is realized through Google Dataform. Navision’s normalized table structure (e.g., separating Sales Header, Sales Line, Value Entry, and Item Ledger Entry) is optimized for fast transactional writes (OLTP), but it is hostile to BI read performance.

The Dataform DAG (Directed Acyclic Graph) Strategy: Dataform transforms this complex web of tables into a Kimball-style Star Schema, optimized for Power BI or Looker.

  1. Staging Layer (Views): Raw tables are abstracted into staging views. Here, data types are cast strictly (e.g., converting BC’s Decimal to BigQuery’s high-precision NUMERIC), timezone offsets are standardized to UTC, and columns are renamed from legacy Navision naming conventions (No_, Sell_to_Customer_No_) to modern, readable formats (item_id, customer_id).
  2. Core Layer (Tables/Incremental): Staged data is materialized into persistent tables. For massive tables like G/L Entry, Dataform is configured to use the incremental materialization strategy. Instead of dropping and recreating the table daily, Dataform only processes the delta (new records since the last run) and merges them into the existing BigQuery table. This drastically reduces the terabytes scanned, directly minimizing GCP billing costs.
  3. Mart Layer (Wide Tables): The final output for BI tools. Dataform joins the headers, lines, and dimensions into pre-aggregated “wide tables” (e.g., mart_sales_performance). By pre-joining the data in BigQuery, the analytical engine handles the heavy computational load, allowing Power BI to query the final dataset instantly without exhausting its own memory limits.

12. Infrastructure as Code (IaC) via Terraform

For a consulting firm delivering Architecture Validation Sprints and production deployments, manual configuration in the GCP Console is a severe anti-pattern. The entire “Smart Pipeline” architecture must be codified using Terraform.

Deployment Automation: By maintaining a standardized Terraform module for the D365 BC integration, the deployment time for a new Danish SMV client is reduced from weeks to minutes. The Terraform state defines:

  • The creation of the specific Google Cloud Storage buckets with defined lifecycle rules (e.g., transitioning raw data older than 90 days to Coldline storage to optimize costs).
  • The provisioning of the BigQuery datasets (raw, staging, mart) and precise Identity and Access Management (IAM) roles, ensuring BI tools only have read access to the mart layer.
  • The deployment of the Cloud Run microservice, including the configuration of minimum instances (scaled to zero to save costs) and maximum instances (capped to respect Microsoft’s API concurrency limits).
  • The automated triggering mechanism via Google Cloud Scheduler and Pub/Sub to initiate the extraction pipeline on a defined cron schedule.

13. The Coexistence and Migration Blueprint

Danish mid-market companies are historically risk-averse. A “big bang” switch from legacy reporting to a new BigQuery Lakehouse introduces unacceptable operational risk. The strategy must employ a parallel coexistence phase.

The 90-Day Transition Framework:

  • Month 1: Foundation and Shadow Ingestion. The Terraform architecture is deployed. The Cloud Run service begins extracting historical data and performing daily delta loads into BigQuery. The existing BI infrastructure remains untouched. Data quality engineers validate the row counts and financial totals in BigQuery against the BC frontend.
  • Month 2: Dataform Modeling and Validation. The SQL transformations are implemented in Dataform. The core financial and sales metrics are recreated. A subset of critical Power BI dashboards are cloned, repointed from the old legacy sources (or direct OData connections) to the new BigQuery mart tables. Both dashboards run concurrently.
  • Month 3: User Acceptance and Deprecation. Business users validate the parallel dashboards. Once trust in the BigQuery numbers is established, the organization experiences the massive performance upgrade (reports loading in seconds instead of timing out). The legacy BI connections are severed, and the organization fully standardizes on the GCP analytical layer.

14. Strategic Conclusion: Securing the Data Foundation

For the Danish SMV sector relying on Dynamics 365 Business Central, hitting the OData API limits is not a possibility; it is an inevitability as data volumes grow. Microsoft’s architecture is explicitly designed to protect the ERP, not to serve analytical needs.

Relying on direct BI connections or expensive, over-provisioned Azure stacks creates immediate financial and operational friction. By adopting a decoupled Google Cloud Lakehouse architecture—leveraging Cloud Run for intelligent, rate-limited extraction and BigQuery for serverless, on-demand transformation—companies eliminate the API bottleneck entirely.

This approach transforms the D365 BC constraints from a critical business risk into a solved engineering problem, providing Danish enterprises with a scalable, highly cost-efficient data foundation ready for advanced AI and cross-platform analytics.

Similar Posts