Connect any REST API to your Azure data warehouse and Power BI
Any REST API as a source
Yres connects any REST API that returns JSON through one generic form. You enter the base URL, choose how the API authenticates (anonymous, Basic or OAuth2 client credentials) and how it paginates, and define the endpoint per table. Yres fetches the full response, parses the JSON inside your Azure SQL database and creates the table itself, with no field mapping. From there the data follows the normal load route with history (SCD2), ready for Power BI. This is also how you connect systems that are not in the source list.
At a glance
- Connection
- Generic REST connector for any API that returns JSON. One base URL per source, one endpoint per table
- Authentication
- Anonymous, Basic (username and password) or OAuth2 client credentials (token endpoint, client ID, client secret, scope and optionally resource). Extra HTTP headers, such as an API key header, are added separately
- Pagination
- No pagination, Link headers per RFC 5988, a next-page URL in the response body, offset and limit, page number, or page number with limit. The loop stops once a page returns no rows
- Choosing endpoints
- If the API publishes an OpenAPI 3 specification (JSON or YAML), you pick endpoints from that specification. Without one, you enter the endpoint per table yourself
- JSON to table
- The response is parsed in the database. Yres locates the list of records itself, flattens nested objects up to 10 levels deep, keeps arrays inside a record as JSON text and adds a column whenever a new field appears
- Where it runs
- As Azure Data Factory pipelines in your own Azure tenant. A self-hosted integration runtime is only needed when the API is on-premises or behind a firewall
- Base URL and credentials
- Written to the Key Vault in your own Azure environment; the Yres web application does not store secrets itself
What you arrange yourself
- 1Get the API documentation of your system and note the base URL (without a trailing slash) and, if there is one, the URL of the OpenAPI specification.
- 2Establish how the API authenticates: anonymous, Basic or OAuth2 client credentials. Ask the vendor for the username and password, or for the client ID, client secret, token endpoint and scope. Also note mandatory headers such as an API key header.
- 3Look up how the API splits results into pages and what the parameters are called (for example offset and limit, or page).
- 4Add the REST API source in Yres, enter these details and choose the integration runtime.
- 5Add a table per endpoint. Leave the path to the records on automatic, or enter it yourself if the API uses an unusual structure.
When you need this — and when you don't
A standalone connector is enough if…
If it is a single endpoint with a short list used in one report, Power BI can call that API directly. That is quicker to set up than a data warehouse.
Yres adds value if…
Yres earns its place once the API paginates or requires tokens, the JSON is nested or changes over time, you want to keep what the data looked like earlier, or the API data has to sit next to your ERP and HR figures. You then avoid writing and maintaining a script of your own.
Frequently asked questions: REST API
My system is not in your source list. Can I still connect it?
In most cases, yes. If the system offers a REST API that returns JSON, you connect it with the generic REST connector; if it offers an OData feed, with the OData connector. Several sources on the list work the same way under the hood. If you are unsure whether your API fits, get in touch and we will look at the documentation with you.
What do I need to know about my API before I start?
Four things, all found in the API documentation: the base URL, how it authenticates (anonymous, Basic or OAuth2 client credentials, plus any mandatory headers), how it paginates including the parameter names, and which endpoints you want to read. If the API has an OpenAPI specification, that largely takes care of the last point.
How does Yres handle nested JSON?
Nested objects are flattened into columns, up to 10 levels deep; address.city becomes a column of its own, for example. An array inside a record is not split out but kept as JSON text in one column; you can unpack it later in a SQL view. Columns and data types are derived from the first 10,000 records of a response, so pagination is advisable for large results: each page is then parsed separately.
How does this differ from the OData connector?
An OData feed describes itself: the service states which entities and fields exist and supplies the link to the next page. A plain REST API has no such convention. There you specify which endpoint to read and how paging works, and Yres derives the columns from the JSON that arrives. If your system speaks OData, choose the OData connector.
Full technical description in the knowledge base →·Last reviewed:
Data warehouse automation that runs in your own Azure
Built for organisations on Azure and Power BI. Yres connects your sources, keeps the history and promotes changes in a controlled way, without manual work.