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.
Power BI and the Ardoq API
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 importing a single workspace into Power BI using the Ardoq API as a data source.
Take a look at our Developer Portal for more details about our REST API.
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, open the workspace you want to import to Power BI, and take note of the parameters in the browser's URL. For instance, in the URL below:
https://api-testing.ardoq.com/app/view/pagesView/workspace/e2305eb947b8f51291c3edc3
The domain is api-testing and the workspace ID is e2305eb947b8f51291c3edc3.
Step 2 — Create an API token
From the main menu, navigate to Preferences > Your account settings and click on the API and Tokens tab.
Give your token a name and click Create token. You will be given an alphanumeric string and you should copy it and save it to a place where you can retrieve it later.
Step 3 — Create a new data source in Power BI
Now that you have the domain, workspace ID, and an API token, you'll need to configure Power BI to fetch data from Ardoq. We can do that using a PowerQuery Script.
In Power BI, under the Home tab, click Get Data and select Blank Query.
Next, click on 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.
Take the code snippet below and replace the placeholder values with your domain, workspace ID, and API token as follows:
<domain> = your domain (e.g. api-testing)
<workspaceId> = your workspace ID (e.g. e2305eb947b8f51291c3edc3)
<apiToken> = your generated API token (e.g. the generated alphanumer token)
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
Finally, run the query.
Note: The first time you add a query, you might have to accept this it as coming form an anonymous source.
Step 4 — Name the query (and optionally repeat Step 3 to include references)
After completing the previous step, you should see a preview of the data loaded from Ardoq. This is a good time to give the query a descriptive name.
(Optional) Step 5 — Create a new connection to include references
With steps 1 to 4, we have imported components from our Ardoq workspace. If you would also like to import references between components, you'll need to repeat Step 3 but with a slight change to the code snippet.
Use the code snippet from the previous step, keeping the domain, workspace ID, and API token unchanged. However, you will need to change the line below:
Endpoint = "/api/workspace/<workspaceId>/component",
To the following line:
Endpoint = "/api/workspace/<workspaceId>/reference",
Then run the query.
This will leave you with two data sources: one for components and one for references.