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.
Note: If you find the data returned in this approach too limiting, please see the Graph Query approach below.
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 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.
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.
Establishing Ardoq Component to Reference Relationships in Power BI
The Ardoq Component and Reference datasets are not connected in Microsoft Power BI by default. In order to filter or use multidimensional Slicers in Power BI, these components must have relationships defined. Follow these steps to establish the required connections between Components/References, as mapped in Ardoq, to have these shown correctly in Power BI.
Note - For reference, refer to the PBI documentation on relationships.
Example: Business Capability → Application. This will permit you to filter/slice the Power BI dashboard to show all Applications that impact a given Business Capability.
Steps:
In Power BI, navigate to the “Merge Queries” from the top ribbon - see the icon highlighted below:
Find Reference table [PBI BusCap_App Reference].
Rename Source field [Internal ID for Business Capability] to CAPABILITY. Do the same for Target [Internal ID for Application] – rename to APPLICATION.
Go to the Business Capability table and rename the ID field to CAPABILITY.
Go to the Application table and rename the ID field to APPLICATION.
Note the renamed APPLICATION field on the right - This should be a list of internal Ardoq IDs.
Click “Manage Relationships” from the top ribbon.
Click “Create New”.
Structure the relationship as follows – PBI Business Capability >> PBI BusCap_App Reference. Be sure to mimic the screenshot exactly to form the right relationship.
Be sure to specify the new field names [CAPABILITY] and ensure they are selected. The “Cardinality” field and “Cross Filter Direction” values are also important.
Repeat the process for the second relationship [create new] from PBI BusCap_App Reference >> Application.
Be sure to specify the new field names [APPLICATION] and ensure they are selected. The “Cardinality” field and “Cross Filter Direction” values are also important.
Result:
Now, suppose when checked, you’d like to create a Slicer for “Business Capability” and add the “Name” field? In that case, the dashboard/widgets will auto-update to show the Applications referenced by the Business Capability in Ardoq.
This is how you can visualize multidimensional relationships defined in Ardoq using your Power BI widgets. Simply repeat as needed for subsequent References between additional Workspaces.
Note: We offer a step-by-step guide on how to create a Power BI dashboard based on Ardoq data - using slicers - which can be shared for further assistance. Please contact partner@ardoq.com for further information.
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. Using Graph traversals you can tailor the data exactly the way you need it to be, in order to create your reports in Power BI.
Note: This option is only available for plans including the Analytics engine.
Step 1 - Create a Gremlin based Report, save it and copy the URL
First create a Gremlin based report (or create an Advanced Search - second image below) and save it. When viewing the report, there's an option to export the data in the top right corner. Choose Export to JSON, and copy the URL to that page.
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/report/<report-ID>/export",
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.
Additional resources
Getting started with Power BI
PowerQuery M Language reference