Dynamics 365 Finance & Operations: Integration APIs & Data Exchange Guide

Integration Patterns Overview

F&O supports four primary integration approaches:

1. Real-Time Request-Response (OData API)

• Synchronous HTTP calls; query or mutate a single record or small batch.
• Use case: Live dashboard, order lookup, customer validation.
• Latency: 100–500ms per request.
• Volume: 1–100 records per call.

2. Batch Operations (Data Management Framework)

• Asynchronous bulk import/export; schedules recurring jobs.
• Use case: End-of-day syncs, historical data migration, large file imports.
• Latency: Minutes to hours (depends on data volume and job queue).
• Volume: 100–millions of records per job.

3. Event-Driven Integration (Business Events & Power Automate)

• F&O emits events when critical actions occur (invoice posted, order created, shipment confirmed).
• External systems subscribe and react (e.g., “When invoice posted, send to accounting software”).
• Use case: Workflow automation, real-time downstream updates, audit trails.
• Latency: Near-real-time (milliseconds to seconds).

4. Custom Integration Layers

• X++ web services, Azure Functions, Logic Apps, or middleware platforms (Mulesoft, SOAPUI).
• Use case: Complex transformations, legacy system compatibility, hybrid cloud scenarios.
• Latency: Depends on implementation.

OData REST API Fundamentals

OData (Open Data Protocol) is a REST-based API standard. F&O exposes hundreds of entities via OData, allowing any HTTP client (JavaScript, Python, .NET, mobile app, Postman) to query and manipulate data.

OData Endpoint Format:

GET https://{environment}.dynamics.com/data/{entity}

Example: Query all customers:

GET https://mycompany.sandbox.dynamics.com/data/Customers

Query Operators:

• $filter – WHERE clause. Example: $filter=CustomerGroup eq ’VIP’
• $select – Column selection. Example: $select=CustomerId,Name,Email
• $orderby – Sort. Example: $orderby=Name desc
• $skip – Offset pagination. Example: $skip=100
• $top – Limit. Example: $top=50 (returns first 50 records after skip).
• $expand – Join related entities. Example: $expand=Orders($select=OrderId,Amount)

Example Query:

GET https://mycompany.sandbox.dynamics.com/data/Customers?$filter=CustomerGroup eq ’VIP’&$select=CustomerId,Name&$orderby=Name&$top=10

Response: JSON array of VIP customers, top 10, sorted by name.

Create, Update, Delete (CRUD):

• POST – Create new record. Send JSON payload with fields.
• PATCH – Update existing record. Send only fields to change.
• DELETE – Delete record. No payload.

Data Entities & Query Syntax

Not every table in F&O is exposed via OData. Microsoft provides a curated set of data entities—abstraction layers that group related tables and handle business logic.

Common Data Entities:

• Customers (CustTable)
• Vendors (VendTable)
• Sales Orders (SalesOrder)
• Purchase Orders (PurchaseOrder)
• Invoices (CustInvoiceJour)
• General Ledger Transactions (GeneralJournalEntry)
• Inventory On-Hand (InventOnHandEntity)
• Products (EcoResProduct)

To discover available entities, query the metadata endpoint:

GET https://mycompany.sandbox.dynamics.com/data/$metadata

This returns the full schema (XML or JSON) of all exposed entities, their fields, relationships, and constraints.

Query Example: Fetch Sales Orders for a Customer

GET https://mycompany.sandbox.dynamics.com/data/SalesOrders?$filter=CustomerId eq ’CUST-001’&$select=SalesOrderId,OrderAmount,CreatedDate&$expand=Lines($select=LineNum,ItemId,Quantity,Price)

This returns all orders for CUST-001, plus the nested order lines (items, quantities, prices).

Batch Operations with Data Management Framework

The Data Management Framework (DMF) is the enterprise-grade bulk integration tool. It’s designed for high-volume imports, exports, and recurring syncs.

DMF Components:

• Data Projects – Reusable import/export configurations. Define source file format, target entity, field mappings, and transformations.
• Staging Area – Intermediate tables where data lands before validation and import. Review, correct, or reject staging records before committing to production.
• Data Entities – 200+ pre-built entities for common objects (customers, orders, inventory). Can be extended with custom entities via X++ class.
• Job History – Audit trail of all imports/exports. Track success/failure, record counts, errors, and timestamps.

Typical DMF Workflow:

1. Create a data project (e.g., “Daily Customer Import”).
2. Upload source file (CSV, XLSX, or JSON) to blob storage or local.
3. Map source columns to F&O fields.
4. Configure transformations (e.g., convert “US” to country code “USA”).
5. Preview staging data; validate for errors.
6. Execute import (async). F&O queues the job, processes in background.
7. Monitor job status and error logs.
8. On success, data is committed to production tables.

DMF Advantages over OData:

• Handles large files without timeout (OData request-response has ~2-minute timeout).
• Automatic retry and error recovery.
• Staging area allows review before commit (transactional safety).
• Supports custom business logic via plugins and transformations.
• Detailed error logs help troubleshoot data quality issues.

Recurring Integrations & Scheduling

F&O allows you to schedule DMF jobs on a recurring basis (hourly, daily, weekly, or custom schedule).

Recurring Integration Setup:

• Create a data project (as above).
• Publish the project to the Recurring Integrations portal.
• Configure schedule (e.g., “Every day at 2 AM UTC”).
• Set source (e.g., Azure Blob Storage, SFTP, or API endpoint).
• Enable notifications (email on success/failure).

The system automatically pulls the latest file, stages it, and imports. If the import fails, it retries (configurable retry policy) and notifies stakeholders.

Best Practices:

• Schedule during off-peak hours to avoid contention with operational traffic.
• Implement idempotency: if a record already exists, update it (don’t duplicate).
• Validate source data before import (schema, required fields, data types).
• Monitor job history for failures; investigate and resolve root causes quickly.

Authentication & Authorization

F&O uses Microsoft Entra ID (formerly Azure Active Directory) for all authentication. Integration scenarios fall into two categories:

1. User-Based (Interactive) Authentication

• A user signs in via Microsoft Entra ID; F&O validates the user and role permissions.
• Example: Power Automate flow with a user’s account context.
• Tokens expire after 1 hour; user must re-authenticate.

2. Application-Based (Service-to-Service) Authentication

• An application (Azure Function, Logic App, external service) authenticates using client credentials (client ID + client secret).
• No user involved; automation runs on behalf of the application identity.
• Application must have appropriate permissions in Microsoft Entra ID and F&O security roles.

OAuth2 Client Credentials Flow:

1. Register application in Microsoft Entra ID. Obtain client ID and generate client secret.
2. Application requests token: POST https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token
3. Microsoft Entra ID returns access token (JWT) valid for 1 hour.
4. Application sends token in API request: Authorization: Bearer {token}
5. F&O validates token and processes request.

Assigning F&O Permissions:

• Service principal (application) is created in F&O as a user account.
• Assign security roles that match required permissions (e.g., “Sales Order Processor” role for importing orders).
• Test permissions before deploying to production.

Business Events & Event-Driven Integration

Business Events allow F&O to emit notifications when important business actions occur. External systems subscribe and react in real-time.

Pre-Built Business Events (100+ available):

• Customer created, updated, deleted.
• Sales order created, confirmed, invoiced.
• Purchase order received.
• Invoice posted to GL.
• Shipment confirmed.
• Payment received.
• Inventory transfer completed.

Fire-Receive Pattern:

• Fire – F&O emits the event (e.g., “Invoice Posted”).
• Receive – External system receives notification and takes action (e.g., send invoice to accounting system).

Event Payload (Example):

{
  "id": "event-12345",
  "eventType": "SalesOrderCreated",
  "timestamp": "2026-03-19T10:30:00Z",
  "data": {
    "SalesOrderId": "SO-001",
    "CustomerId": "CUST-A",
    "Amount": 5000.00,
    "Status": "Created"
  }
}

Delivery Mechanisms:

• Power Automate – Trigger flow on business event; call external API, send email, or update CRM.
• Azure Event Grid – F&O publishes events to Event Grid; subscribers (Functions, Logic Apps, webhooks) consume.
• Webhooks – Custom HTTP endpoints registered to receive events.

Power Automate Integration

Power Automate (formerly Flow) is Microsoft’s no-code/low-code automation platform. Power Automate provides access to 1,000+ connectors across all integrated services; the dedicated Fin & Ops Apps connector offers 8 actions (create, read, list, update, delete, execute action, list entities, MCP) and a Business Events trigger.

Common Power Automate Patterns:

• Event-Triggered Workflow – When sales order is created in F&O, automatically create task in Project Operations and send email to salesperson.
• Scheduled Job – Every day at 3 PM, query F&O for overdue invoices and export to Excel, send to CFO.
• Multi-System Sync – When customer is created in Salesforce, automatically create customer account in F&O (bidirectional).
• Approval Workflow – When purchase order exceeds $50k, route to manager for approval; if approved, send to F&O; if rejected, notify requester.

Connector Actions (Example):

• List records (customers, orders).
• Get record details.
• Create record.
• Update record.
• Delete record.
• Run action (e.g., post journal, confirm PO).

Custom X++ Web Services

X++ web services are legacy integration points (pre-OData). They allow you to expose custom business logic via SOAP or JSON endpoints.

Why Consider Custom Services:

• Highly specific business logic not covered by standard entities.
• Complex transformations or multi-step processes.
• Legacy system compatibility (older systems may not support OData).

Why Avoid (Prefer OData When Possible):

• Maintenance burden: Custom code must be updated during F&O upgrades.
• Security: Custom code is harder to audit than standard APIs.
• Performance: OData is optimized and cached; custom code may not be.

API Rate Limits & Throttling

F&O enforces rate limits to protect system stability:

Published Limits:

• OData Requests – User-based limits (when enabled) allow up to 6,000 requests per 300-second window, per user per web server. As of version 10.0.36, user-based limits are disabled by default; the active protection mechanism is resource-based throttling, which triggers when server CPU or memory thresholds are exceeded.
• Per-User Limits – When enabled, limits are tracked per user, per application ID, and per web server — each app registration gets its own independent bucket; calls from different registered apps do not share the same quota.

When You Hit the Limit:

• API returns HTTP 429 (Too Many Requests).
• Response header includes Retry-After (e.g., “Retry-After: 10”, meaning retry in 10 seconds).

Mitigation Strategies:

• Batch Requests – Use DMF or batch API calls instead of individual requests.
• Exponential Backoff – Retry after 1s, then 2s, then 4s, etc. Most SDKs do this automatically.
• Pagination – Use $top/$skip to fetch smaller chunks; spread requests over time.
• Caching – Cache frequently-accessed data (products, customers) locally; refresh periodically.
• Async Processing – Use Power Automate or Azure Functions with queues to serialize requests.

Common Integration Patterns

Pattern	Technology	Use Case	Pros	Cons
Real-Time Query	OData API	Dashboard, lookup, validation	Fast, simple, no infrastructure	Small volumes, request timeout ~2 min
Bulk Import	DMF + Recurring Integration	End-of-day sync, migration	Handles large files, staged review, retry logic	Latency (minutes to hours), async
Event-Driven	Business Events + Power Automate	Workflow automation, downstream updates	Real-time, no polling, scalable	Complexity, requires Power Automate license
Middleware	Azure Data Factory, Logic Apps, Mulesoft	Complex transformations, legacy compatibility	Flexible, handles any source/target	Higher cost, operational overhead

Security & Best Practices

• Use Service Principals – Create Microsoft Entra ID service principals for integrations. Never use personal user accounts.
• Principle of Least Privilege – Assign only the minimum security roles required. Don’t grant System Administrator to integration accounts.
• Rotate Credentials – Client secrets expire after 2 years. Rotate before expiry or use certificate-based auth.
• Monitor API Usage – Enable activity logging. Track who called which APIs, when, and from where.
• Secure Secrets – Store client IDs and secrets in Azure Key Vault, not in code or config files.
• Implement Retry Logic – Don’t hammer the API on transient failures; use exponential backoff and circuit breakers.
• Validate Data – Always validate incoming data (schema, required fields, data types) before inserting into F&O.
• Use HTTPS Only – All API calls must use TLS 1.2 or higher. Never send credentials over HTTP.
• Audit Trails – Enable change tracking and audit logging to see who modified records and when.