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 organization's 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 below.

Ardoq API id

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.

Ardoq create API token

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".

Ardoq Power BI create data source

In the new window, click "Advanced Editor".

Ardoq Power BI advanced editor

Paste in the code snippet below 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.

    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(_)))))

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

Ardoq query

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

Ardoq Power BI credentials
Ardoq edit credentials

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.

Ardoq name query

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.

Ardoq data sources

Importing graph query results to Power BI

So far, we've focused on how to import components 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.

Ardoq write graph query

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 an 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.

Ardoq refreshing data in Power BI

Additional resources

Getting started with Power BI
PowerQuery M Language reference

Did this answer your question?