Power BI Integration

This article describes how you can set up Ardoq as a Power BI data source. The major benefit of this approach is that users can keep reports in Power BI in sync with data in Ardoq because it's a direct API-integration, as opposed to using a file as an intermediate format.

Selecting what data to import

Ardoq offers a comprehensive REST-api that allows fine-grained control of what data to expose to Power BI. In this article we'll focus on two use-cases, loading a single workspace into Power BI and loading Graph Reports.

Importing a workspace to Power BI

Step 1 - Find the correct domain and workspace id

To import a workspace to Power BI, you'll first need to find your organizations domain and the correct workspace id.

The simplest way to do that is to log in to Ardoq and open the workspace you want to import to Power BI. Then you'll see the relevant ID's in the URL as shown bellow.

Step 2 - Create an API-token

In Ardoq, go to your user settings and select the "API and Tokens" tab. Give your token a description, click "Create Token" and copy it.

Step 3 - Create a new data source in Power BI

Now that you have the correct domain, workspace id and API-token, you'll need to configure Power BI to fetch data from Ardoq. We do that using a PowerQuery Script.

In Power BI, click the "Get Data" menu and select "Blank Query".

In the new window, click "Advanced Editor".

Paste in the code snippet bellow in the editor, and make sure to replace the <placeholders> with the correct domain, workspaceId and apiToken. Take care not to edit anything else but the placeholders.

let
    BaseUrl = "https://<domain>.ardoq.com",
    Endpoint = "/api/workspace/<workspaceId>/component",
    AuthHeader = "Token token=<apiToken>",
    Source = Json.Document(Web.Contents(BaseUrl, [RelativePath=Endpoint, Headers=[Authorization=AuthHeader, #"User-Agent"="Ardoq-PowerBI"]])),
    AsTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumns = Table.ExpandRecordColumn(AsTable, "Column1", List.Distinct(List.Combine(List.Transform(Table.Column(AsTable, "Column1"),each Record.FieldNames(_)))))
in
   ExpandedColumns

This is how the query should look after you've pasted it in and edited the <placeholders>.

Note: The first time you add a query, you might have to accept this as an "Anonymous Source".

Step 4 - Name query (and optionally repeat Step 3 to include references)

After completing Step 3, you should see a preview of the data loaded from Ardoq. This is a good time to give the Query a descriptive name i.e. Components.

Optional step: So far we've only imported the components from our workspace. If you also need the references, you need to repeat Step 3, but this time replace line 3 in the script with this line (remember to also set the correct workspaceId):

   Endpoint = "/api/workspace/<workspaceId>/reference",

This should leave you with two data sources, one for Components and the other for References.

Importing graph query results to Power BI

So far, we've focused on how to import component and references from a workspace to Power BI. Another powerful use-case is to export the results of Graph traversals to Power BI.

Note: This option is only available for plans including the Analytics engine.

Step 1 - Write graph query,  save it and copy the URL

 In order to expose a Report via the API, you'll need to save a Graph Query. That will expose a link to copy the URL.

Step 2 - Update the Endpoint and follow the same procedure described above

The PowerQuery in Step 3 - Create a new data source in Power BI  works with Graph queries as well, but you'll have to update line 3 with the URL copied in Step 1. i.e:

Endpoint = "/api/graph-search/5a7c2a37b3da0843f48c56df/run",

The data needs to be fetched from the wrapped "result", by modifying this line in the example above (Source[result]):

AsTable = Table.FromList(Source[result], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Next steps

This article covered how to get data from Ardoq into Power BI. Depending on your needs, you might want to perform additional transformation to the data.

Refreshing data from Ardoq

Remember, the big benefit of this style of integration, is that in order to pull down updates from Ardoq, you just click refresh on the data source.

Additional resources

Getting started with Power BI
PowerQuery M Language reference

Did this answer your question?