What Is OData? The OData Feed Explained for Power BI

What is an OData feed?

An OData feed is a web connection that lets Power BI, Excel, and Power Query retrieve structured tables through one stable URL. OData stands for Open Data Protocol. The OASIS standard describes both the records and their schema, including the available tables, fields, and data types.

For reporting, this means the same source can be loaded again without exporting a new file for every period. Power BI and Excel include an OData connector, so a feed does not require a separate database driver or a custom REST API script in the report.

OData in brief: protocol, versions and query options

OData (Open Data Protocol) is an open standard for REST APIs that expose data as tables. Microsoft created the protocol in 2007. OData version 4 became an OASIS standard in 2014 and was also published as ISO/IEC 20802 in 2016. Platforms such as Microsoft Dynamics 365, SAP and Exact Online use OData for their APIs.

An OData service has three parts:

• Service root: the base URL that all tables sit under.
• Metadata ($metadata): a machine-readable description of tables, fields, data types and relationships.
• Entity sets: the tables themselves, such as invoices or customers, which you request as rows and columns.

Query options in the URL let you request only what you need. For example:

https://example.com/odata/Invoices?$select=InvoiceNumber,Amount&$filter=InvoiceDate ge 2026-01-01&$orderby=InvoiceDate desc&$top=100

$select picks columns, $filter limits rows, $orderby sorts and $top caps the number of results. Which options a service supports differs per provider. In Power BI you rarely write these URLs yourself: Power Query passes filters to the source automatically where it can (query folding).

How does an OData feed work?

An OData service has a service-root URL. When Power BI connects to that URL, it reads the metadata document at $metadata to discover the available entity sets, fields, keys, and data types. The Navigator can then show those entity sets as tables.

In Power BI Desktop, select Get Data, choose OData Feed, and enter the service-root URL. Authenticate with the method required by the feed, select the tables you need, and open them in Power Query. There you can filter rows, remove columns, check data types, and prepare relationships before loading the model.

After publishing the report, configure the source credentials and scheduled refresh again in Power BI Service. Credentials stored in Desktop are local and do not automatically become the cloud service credentials.

OData compared with REST, CSV, and SQL

• OData feed: A standardized schema and a connector built into Power Query. It suits recurring table loads when the service supports the filters and volume the report needs.
• REST API: Flexible for custom applications, but often requires code for authentication, pagination, rate limits, response parsing, and errors.
• CSV export: Simple for a one-off check or archive, but each update requires a new file and a consistent manual process.
• Direct SQL connection: Useful when a database is intentionally available for reporting, but it can require network rules, credentials, and a Power BI gateway.

OData is not automatically the best choice for every task. A CSV may be faster for a single check, while a direct API or database connection may suit a custom data platform. OData is a practical option when analysts need structured tables that Power BI or Excel can load repeatedly.

Using an OData feed in Power BI

Start with the reporting question rather than loading every available table. Select the fact tables and reference tables needed for that question, such as transactions and general ledger accounts or invoices and customers. In Power Query, keep the columns the model uses and filter historical rows as early as possible.

Build relationships and business measures after the source tables are clear. An OData feed delivers records and schema; it does not decide what revenue, margin, utilization, or an outstanding balance means for your organization.

Once the model is published, schedule Power BI Service to refresh after the upstream source has synchronized. A successful Power BI refresh only retrieves the data currently available from the feed. It cannot include source changes that have not yet reached that feed.

OData in Excel and Power Query

Excel uses the same Power Query technology. A controller can load a table into a workbook, save filters and transformations, and refresh the query later from the same source. A BI developer can use the feed in Power BI for a governed dashboard.

This allows Excel checks and Power BI reports to start from the same synchronized tables rather than separate manual exports. The workbook and semantic model can still apply different calculations, so teams should document which definitions belong in each output.

Security and ownership

Treat an OData URL and its credentials as access to business data. Share them only with authorized users and review which tables each report actually needs. Access to the source feed and access to a published Power BI report are separate controls.

Document the owner of the report, its source tables, and the expected refresh sequence. This makes failed refreshes easier to investigate and prevents a technically working connection from becoming an undocumented dependency.

Filters, query folding, and large tables

Large OData tables need a deliberate loading strategy. Remove unused columns and filter rows early, especially when a report only needs recent periods. Power Query may translate supported steps into OData query options so the source does part of the work. This behavior is known as query folding.

Not every transformation folds. If a step cannot be sent to the source, Power Query may retrieve more data and process the remaining work locally. Check folding before assuming that a visual filter or a late Power Query step reduces the amount transferred.

Incremental refresh can help with large historical tables by separating older periods from the range refreshed regularly. It is most useful when the date filters are passed to the OData source. Test the actual refresh behavior before relying on it for a large model.

Fix OData feed errors in Power BI

Credential errors and invalid credentials

If a feed works in Power BI Desktop but fails in Power BI Service, check the stored source credentials and always provide the clean service-root URL rather than deep entity paths with query parameters. Microsoft notes that built-in connection tests may ignore or fail on manually appended URL parameters. Always place filters and navigation steps directly within Power Query.

When configuring credentials in Power BI, select Basic authentication. Enter your DigiData OData API key into the Password field. The username field can be set to any non-empty value (such as 'apikey') because DigiData validates the key in the password field and ignores the username. Note that this downstream feed authentication is separate from the OAuth2 connection between DigiData and your upstream accounting or ERP system.

Timeouts and slow merges

Reduce rows and columns before merging large tables. Navigation columns can cause additional requests when they are expanded, so compare that approach with loading the related table separately and creating a relationship in the model.

For a merge that repeatedly reads a small lookup query, buffering the smaller query may help. Test the result rather than treating buffering as a general performance fix. Buffering uses local memory and can prevent later filters from being pushed back to the source.

Scheduled refresh in Power BI Service and Gateway guidelines

Schedule Power BI to refresh after DigiData completes source synchronization (typically early morning). If a cloud refresh fails, first check the saved source credentials and select Basic under Data source credentials in the semantic model settings.

Microsoft documents in the official OData feed connector documentation that Basic authentication in Power BI Service is marked as 'requires Gateway'. While target host capabilities and cloud tenant configurations vary, you should not assume that a cloud OData feed universally bypasses a gateway. Configure your credentials carefully and consult the official Microsoft Learn documentation for cloud connection errors.

Privacy levels and data source settings

Power Query can block a query that combines sources with incompatible privacy settings. Review the privacy level for every OData feed, file, database, and web source used in the model. Set each level deliberately according to the organization's data policy rather than disabling the protection to make an error disappear.

If a composite query fails in the Service, compare its data source and privacy settings with Desktop. The cloud model must have valid credentials and compatible settings for every source involved.

Common misconceptions about OData

An OData feed is not a dashboard. It is a source layer from which Power BI, Excel, or another compatible client retrieves data. Visuals, relationships, calculations, and business definitions belong in the reporting tool or another modeled layer.

OData does not correct inconsistent source data. Customer names, project identifiers, periods, and account structures still need clear ownership. The feed makes the records available in a standard form; the organization determines how those records should be interpreted.

OData also does not guarantee instant data. Report freshness depends on when the underlying source is synchronized and when Power BI refreshes the semantic model. A clear sequence between those two schedules is more useful than refreshing Power BI repeatedly against an unchanged feed.

OData is designed for reporting tools such as Power BI and Excel. To give the same synchronised data to an AI assistant like ChatGPT or Claude, you use MCP. OData and MCP: what's the difference? compares the two, and What is an MCP server for business data? explains how such a server works.

Summary

An OData feed gives Power Query a standard way to discover and reload structured tables through a service-root URL. Reliable reporting depends on selecting only the necessary data, preserving query folding where practical, setting credentials in Power BI Service, and coordinating the refresh schedule with the source. Explore the available integrations, learn more about Power BI reporting, or contact DigiData for help choosing a connection.