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.

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.

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

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

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 “Data” [table icon] view:

Ardoq Data table icon view

This can be found in the far-left menu in Power BI, under the “chart” icon that is your default landing page - see table icon highlighted above.

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

Ardoq supplement to Power-BI manage and structure relationships example.

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.

Ardoq edit relationship in Power-BI

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.

Ardoq create relationship in Power-BI

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 - 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 Gremlin Graph Search

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.

Additional resources

Getting started with Power BI
PowerQuery M Language reference

Did this answer your question?