Consume xMart API in PowerBI

Public API

If the catalog view is publicly accessible (ie the user ANONYMOUS_PUBLIC is in a role with the DATA_VIEW permission), just use the Get Data > OData Feed connector Choose Anonymous connection for the view URL (no query parameters).

Image showing the selection of the OData option from the Get Data menu

Enter the API URL of the view Image showing an Odata Feed URL request with an xMart URL entered

Secured API

An Azure AD app client is needed to authenticate and get an access token to securely consume xMart API.

Follow the “How To Register client app” to get the required client credentials if you don’t already have an existing app or secret.
Grant this app the appropriate role in your mart.

PowerBI

Create a new PowerBI project.

Select the Get Data > Blank query

Image showing a blank PowerBI query

Click Advanced Editor

Image showing location of Advanced Editor Button

Then copy and paste this query in the editor, and replace the following placeholders:

REMOTECLIENTID -> from Azure AD REMOTECLIENTSECRET -> from Azure AD YOUR_MART -> ex: WIISE YOUR_VIEW -> ex: REF_DISEASES

For the list of the views in a particular mart:

let
    client_id = "REMOTECLIENTID", // your client id
    client_secret = "REMOTECLIENTSECRET", // your secret
    token_uri = "https://login.microsoftonline.com/f610c0b7-bd24-4b39-810b-3dc280afb590/oauth2/token",
    xMartApiUatId = "b85362d6-c259-490b-bd51-c0a730011bef",
    xMartApiProdId = "712b0d0d-f9c5-4b7a-80d6-8a83ee014bca",
    tokenResponse = Web.Contents(token_uri, [Content=Text.ToBinary(Uri.BuildQueryString([
            client_id = client_id,
            client_secret = client_secret,
            grant_type = "client_credentials",
            resource = xMartApiProdId // change here to UAT or PROD if needed, also change the apiResponse url below accordingly
            ])), Headers=[#"Content-type"="application/x-www-form-urlencoded", Accept="application/json"]]),
    jwtBody = Json.Document(tokenResponse),
    jwtResult = if (Record.HasFields(jwtBody, {"error", "error_description"})) then 
            error Error.Record(jwtBody[error], jwtBody[error_description], jwtBody)
         else
            jwtBody,
    access_token = jwtResult[access_token],
    bearerValue = "Bearer " & access_token,
    apiResponse = OData.Feed("https://extranet.who.int/xmart-api/odata/YOUR_MART", null, [Implementation="2.0", Headers = [Authorization = bearerValue]])
in
    apiResponse

Note you need to add Implementation=”2.0” to the apiResponse OData.Feed call for the list of views.

For a particular view:

let
    client_id = "REMOTECLIENTID", // your client id
    client_secret = "REMOTECLIENTSECRET", // your secret
    token_uri = "https://login.microsoftonline.com/f610c0b7-bd24-4b39-810b-3dc280afb590/oauth2/token",
    xMartApiUatId = "b85362d6-c259-490b-bd51-c0a730011bef",
    xMartApiProdId = "712b0d0d-f9c5-4b7a-80d6-8a83ee014bca",
    tokenResponse = Web.Contents(token_uri, [Content=Text.ToBinary(Uri.BuildQueryString([
            client_id = client_id,
            client_secret = client_secret,
            grant_type = "client_credentials",
            resource = xMartApiProdId // change here to UAT or PROD if needed, also change the apiResponse url below accordingly
            ])), Headers=[#"Content-type"="application/x-www-form-urlencoded", Accept="application/json"]]),
    jwtBody = Json.Document(tokenResponse),
    jwtResult = if (Record.HasFields(jwtBody, {"error", "error_description"})) then 
            error Error.Record(jwtBody[error], jwtBody[error_description], jwtBody)
         else
            jwtBody,
    access_token = jwtResult[access_token],
    bearerValue = "Bearer " & access_token,
    apiResponse = OData.Feed("https://extranet.who.int/xmart-api/odata/YOUR_MART", null, [Headers = [Authorization = bearerValue]]), 
    data_table = apiResponse{[Name="YOUR_VIEW",Signature="table"]}[Data]
in
    data_table

Note you do not need to add Implementation=”2.0” to the apiResponse OData.Feed call for the individual views.

Now the data can be browsed within PowerBI

Image showing xMart data in Power BI