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.
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>.
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 (
AsTable = Table.FromList(Source[result], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
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.