Data access and the Data API

The Data API is how Power BI, Excel and scripts read loaded data back out of Cloud Connector: one read-only HTTP endpoint per dataset, paged, with the dataset's description and relationships as metadata. It is part of Business Plus, a feature set Datawareness switches on per installation. Until it is on, nothing about it appears in the UI.

The Data API is closed by default. Two things open it, and both are needed:

The Cloud Connector Data app registration. Create it once (see App registrations), then enter its client ID under Setup → Access → Data API. Only tokens issued for that app are accepted. A Cloud Connector sign-in token is refused, and API keys never reach the Data API.
A role on the Data access page that names who may read what, for example "customers and customerGroups, only from the datawareness instance". Roles are per environment: switch environment to set up another one.

info

The Data API is part of Business Plus, an add-on Datawareness switches on for your installation. Until it is on, nothing about the Data API appears in Cloud Connector: not the setup under Setup → Access, not Data access, not the Data API URL in a dataset preview.

The Data access page

Data access appears under Configuration once the Data API is on. Roles belong to the environment you are in; each environment has its own. The page lists roles with their Data, Members and Status, and has New role and Power BI template.

A role has a Name, an Enabled switch, a Description, and:

Data — per source system, which datasets (all, including new ones, or specific ones) crossed with which instances (all, including new ones, or specific ones). The role reads the chosen datasets, and only the rows the chosen instances loaded. Ticking the source system itself picks all of both. A source system with one side empty reads nothing, and the dialog says so.
Members — users, security groups (nested members included) and apps, found with Search Entra ID. An app member admits only app-only tokens; a user member admits only users' tokens.

A caller reads everything their enabled roles cover, together. Changes apply to the next request. Delete role takes the access away from the next request on.

Reading data through the Data API

GET /api/data — the datasets you may read, each with its metadata (description and relationships).
GET /api/data/:schema.Dataset — rows, paged. Parameters: pageSize (default 5000, max 10000), fields (comma-separated), count=true (total on the first page), environment (another environment's data). Follow @odata.nextLink until it is absent; a page can be empty and still have a next one.

Dataset metadata

The Data API's list of datasets carries each dataset's description and its relationships to other datasets (which fields join to which). For Business Central, both come from BI Endpoints. They're copied when you add a dataset from the Data catalogue. After a BI Endpoints upgrade, click Apply to datasets on the source system's data model page to copy them again. A description you wrote yourself is kept.

Reading from Power BI — template

On Data access, click Power BI template, tick the datasets and click Download. Each dataset becomes a table, its columns typed from a sample (numbers, true/false and dates; anything else is text).
Open the .pbit in Power BI Desktop. It asks for CloudConnectorUrl and Environment, filled in for the environment you downloaded from; leave Environment blank for the default one.
Sign in with Organizational account at the level of the Cloud Connector Data base URL, as below.
To add another dataset later: Transform data, select CloudConnectorDataset, pick the dataset and click Invoke.

Columns that appear in a dataset after the template was made load as text.

Reading from Power BI — one query

On Setup → Access → Data API, click Copy Power Query template.
In Power BI Desktop: Get data → Blank query → Advanced Editor, paste, and set Collection to the dataset (schema.Dataset, as shown in the dataset preview). Set Environment to read another environment.
When asked, choose Organizational account, sign in, and apply it at the level of the Cloud Connector Data base URL, https://<backend host>/api/data.
After publishing, set the same credential (OAuth2) on the semantic model in the Power BI service. Scheduled refresh needs no gateway.

Don't use the OData feed connector: the Data API has no $metadata.

Power Query (M)
let
    Base = "https://<backend host>/api/data",
    Collection = "<schema>.<Dataset>",
    Environment = null,
    FirstQuery = [pageSize = "5000"] & (if Environment = null then [] else [environment = Environment]),
    GetPage = (q as record) => Json.Document(Web.Contents(Base, [RelativePath = Collection, Query = q])),
    NextQuery = (page as record) =>
        let link = Record.FieldOrDefault(page, "@odata.nextLink", null)
        in if link = null then null else Uri.Parts("https://<backend host>" & link)[Query],
    Pages = List.Generate(
        () => GetPage(FirstQuery),
        each _ <> null,
        each let q = NextQuery(_) in if q = null then null else GetPage(q),
        each _[value]),
    Rows = List.Combine(Pages),
    Columns = List.Distinct(List.Combine(List.Transform(Rows, Record.FieldNames))),
    Result = Table.FromRecords(Rows, Columns, MissingField.UseNull)
in
    Result

Reading from a script

Request a client-credentials token for https://<backend host>/.default and send it as a bearer token. Add the app's service principal to a role on the Data access page (pick it by name or paste its client ID).

The Data access page​

Reading data through the Data API​

Dataset metadata​

Reading from Power BI — template​

Reading from Power BI — one query​

Reading from a script​