BC Query VS API page

 

Query vs API Page in Dynamics 365 Business Central: Which One for Power BI?

When connecting Power BI to Dynamics 365 Business Central, one of the first questions we ask is:

“Should I use a Query or an API Page?”

Great question! Choosing between Query and API/Page in Dynamics 365 Business Central depends on what you want to achieve—performance, structure, flexibility, or ease of use.

Both options let you pull data out of Business Central, but they serve different purposes.

Here’s a clear breakdown to help you decide:

---

1. Query Objects

Best for:

• Optimized performance
• Custom data shapes (joins, filters, fields)
• Power BI reports or external analytics

Features:

• Read-only

• Can join multiple tables

• You define exactly which fields you need

• Better performance than page APIs (less metadata, leaner)

• Exposed via OData (e.g., .../ODataV4/QueryName)

Use case example:
You want to build a Power BI report that shows sales by item and customer. You can create a Query that joins Sales Header, Sales Line, Item, and Customer—only selecting the fields you need.

---

2. API Pages (Standard or Custom)

Best for:

• Full CRUD operations (Create, Read, Update, Delete)
• Integrations with external apps
• Working with a single entity at a time (like customers or orders)

Features:

• Supports full operations (GET, POST, PATCH, DELETE)

• Usually maps to a single table (API Page)

• Comes with metadata and business logic

• Exposed via API endpoints (.../v2.0/companies({id})/customers)

Use case example:
You’re building an app that integrates with BC and needs to create or update customer records or post a new sales order—this is where APIs shine.

---

Comparison Table

Feature	Query	API/Page
Supports joins	✅ Yes	❌ No (single entity per endpoint)
Read-only	✅ Yes	❌ No (can write too)
Performance	✅ High (leaner payloads)	🚫 Moderate (more metadata overhead)
Suited for Power BI	✅ Best choice	🚫 Less ideal
Suited for integrations	🚫 Not really	✅ Perfect
Customizable via AL code	✅ Yes	✅ Yes
Authentication	Azure AD / OAuth2	Azure AD / OAuth2

Performance Comparison: API Page vs Query Object (as API)

Aspect	API Page	Query Object (API)
Response Time	Slower — due to metadata, UI behavior, and FlowField evaluations	Faster — leaner, no UI overhead, no FlowField logic
Memory/CPU Load	Higher — loads full page object, triggers, and field definitions	Lower — executes SQL-like read without UI or triggers
Payload Size	Larger — includes layout metadata, sometimes extra fields	Smaller — only selected fields, clean structure
Throughput (API calls/sec)	Lower — especially under heavy load or with large datasets	Higher — better scaling with data volume
Joins / Multi-table Access	Handled via subpages or navigation (costly)	Efficient joins via DataItemLink (executed as a single SQL join)
Optimized for Data Extraction	No — designed for entity interaction	Yes — ideal for fast, flat data exports
Client (e.g. Power BI) Performance	May cause delays or refresh failures with large volumes	Much faster for datasets with 10k+ rows

---

---

When to Use What?

Scenario	Use
You need to create or update data from an app	API Page
You need a fast Power BI report from multiple tables	Query
You want only specific fields from a table	Query
You're integrating a web service to manage inventory	API Page
You want to expose a custom business view to users	Query

---

That's a wrap! Share your experiences using Queries and API Pages in the comments below.