Microsoft Dynamics NAV and Google BigQuery integration

What moves between them

Purchase orders, sales orders, invoices, general ledger entries, and master data (vendors, customers, accounts, items, dimensions) are read from Microsoft Dynamics NAV on a schedule you control and appended to BigQuery tables as new rows. Each row includes a source timestamp and record ID so you can track when data arrived and detect duplicates. BigQuery serves as the analytics source; ml-connector does not write financial transactions back into NAV, since NAV general ledger entries are immutable once posted. Reference dimensions like customers and vendors are synchronized bidirectionally if you define a mapping layer in BigQuery and want NAV to adopt validated or enriched codes.

How ml-connector handles it

ml-connector stores OAuth credentials for both systems encrypted and refreshes them as needed. For NAV, it accepts your tenant ID and company ID (online) or server instance and port (on-premises) and polls the OData endpoints directly on your schedule. For BigQuery, it exchanges a Google Cloud Service Account private key for an access token, then streams or batch-inserts rows into your chosen dataset and tables. If you enable NAV webhooks, ml-connector subscribes to supported entities and receives push notifications, but it still manages the subscription renewal before the 3-day expiry window closes so you never lose notifications. BigQuery has no pre-defined schemas, so the integration maps each NAV entity to a BigQuery table structure you design; field names and types are specified in the integration config. NAV general ledger entries are read-only in the standard API, so reconciliation and cross-checks happen in BigQuery via SQL queries. Retries on both sides follow exponential backoff, and every record ingested carries a full audit trail for compliance and debugging.

A real-world example

A regional mid-market distributor runs Microsoft Dynamics NAV for procurement and general ledger operations across multiple locations. The finance team needs to analyze spending by vendor, cost center, and product line, and prepare monthly variance reports comparing budget vs. actual GL accounts. Previously, they exported CSV files from NAV monthly, manually cleaned them in Excel, and uploaded them to an on-premises reporting database. With NAV and BigQuery connected, every purchase order and invoice lands in BigQuery as soon as it's posted in NAV. The finance team runs SQL queries directly on the live data in BigQuery, refreshes reports with a button click, and can drill from a high-level spend summary down to individual invoices, all without re-keying or manual exports. Year-end audit compliance is faster because the audit trail in BigQuery proves when each record arrived and from which NAV instance.

What you can do

• •Load purchase orders, sales orders, invoices, and general ledger entries from Microsoft Dynamics NAV into BigQuery on a schedule tied to your financial close or reporting cycle.
• •Maintain read-only replicas of your NAV master data (vendors, customers, accounts, items, dimensions) in BigQuery for use as lookup tables in reporting and analysis queries.
• •Authenticate Microsoft Dynamics NAV via OAuth 2.0 and Google BigQuery via Service Account, with automatic credential refresh as tokens expire.
• •Detect changes in NAV data via webhooks (if enabled) or polling, and handle the 3-day NAV webhook subscription renewal automatically.
• •Track every loaded record in an audit trail with source timestamp, record ID, and ingestion time for compliance, duplicate detection, and error replay.

Questions

Does the integration support both Microsoft Dynamics NAV Business Central online and on-premises deployments?

Yes. For Business Central online, ml-connector uses OAuth 2.0 credentials to connect to the cloud OData endpoints. For on-premises deployments, it connects directly to your server instance, port, and company ID, provided the OData service is enabled in the NAV Server Administration tool and port 7048 is open through your firewall. Both modes use the same REST OData protocol.

Can the integration push data from BigQuery back into Microsoft Dynamics NAV?

ml-connector can read calculated or transformed data from BigQuery and send updates to NAV dimensions like vendors or customer codes, but it cannot write general ledger entries or financial transactions back to NAV, since NAV general ledger entries are immutable once posted. The main flow is unidirectional from NAV to BigQuery, with optional reverse flow for reference data only.

How does the integration handle NAV's 3-day webhook subscription expiry?

If you enable webhooks in NAV, ml-connector automatically manages the subscription lifecycle by monitoring the expiry date and renewing the subscription before the 3-day window closes. This ensures you never miss push notifications. Alternatively, ml-connector can poll NAV's OData endpoints on a schedule you control, giving you flexibility if webhooks are unavailable or unreliable in your environment.