Connect Power BI to Business Intelligence API

This feature is coming soon!
DecisionRules allow to generate logs from each rule solver run and get them via the Business Intelligence API. The obtained data can be then used in BI tools to build analyses and visualizations. In this article, we describe this process for the case of Power BI.
How to Connect Power BI to Business Intelligence API?
There are a couple of steps to follow.
If you would like to go step by step or download a sample report, you may also take advantage of our Power BI Tutorial.
Get Data
Open new project in Power BI Desktop and click the Transform data button in the Queries section of the menu (under the Home tab). This will open the Power Query Editor. In the Queries box on the left-hand side of the editor, right-click and select New Query -> Blank Query from the drop-down menu. Then right-click the new Query1 item and select Advanced Editor from the drop-down menu.
Now, copy paste the following code.
(Page as number) =>
let
    Source = Json.Document(Web.Contents("https://bi.decisionrules.io/audit?bi_key=your-api-key-here&page="&Number.ToText(Page)))[audits]
in
    Source
Do not forget to copy paste your own Business Intelligence API Key into the URL above. You can generate your Business Intelligence API Key in the API Keys section of the app.
Next right-click the created query, select Rename and enter fAudits as the name. Note that this is only a helper function preparing our source. We are now going to use this function to loop over pages of audit data and load all data that are available.
Create new Blank Query (in the same way as before), open the Advanced Editor again and copy paste the following code.
let
   Source = List.Distinct(List.Generate(()=>
[Result = if List.IsEmpty(fAudits(1)) then null else fAudits(1) , Page=1],
each  [Result] <> null,
each [Result =  if List.IsEmpty(fAudits([Page])) then null else fAudits([Page]) , Page=[Page]+1],
each [Result])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "audit"}}),
    #"Expanded audit" = Table.ExpandListColumn(#"Renamed Columns", "audit"),
    #"Expanded audit1" = Table.ExpandRecordColumn(#"Expanded audit", "audit", {"guid", "baseId", "version", "correlationId", "type", "status", "outputSchema", "createdIn", "inputSchema", "tags", "name", "lastUpdate", "apiKey", "inputData", "outputData", "timestamp"}, {"guid", "baseId", "version", "correlationId", "type", "status", "outputSchema", "createdIn", "inputSchema", "tags", "name", "lastUpdate", "apiKey", "inputData", "outputData", "timestamp"})
in
    #"Expanded audit1"
Save the query.
Power Query will probably show you a warning and ask you to Please specify how to connect. If this happens, just click the Edit credentials button, choose Anonymous, do not change anything and click Connect.
You should see a table with your audit data. You can apply arbitrary further steps, e.g. expand the inputData and the outputData columns. Your data is ready now!
If you are getting the Expression.Error: The column 'Column1' of the table wasn't found. error, it means that there are no audits matching your query.
Apply Limits or Filters
If you have a lot of audit logs and downloading them takes too long, you can use a limit on the number of records or specify the time period of the solve that is of your interest. This can be done easily by including suitable query parameters in the request URL. For example, to limit the number of records to 100, we can edit the fAudits function to read
(Page as number) =>
let
    Source = Json.Document(Web.Contents("https://bi.decisionrules.io/audit?bi_key=your-api-key-here&limit=100&page="&Number.ToText(Page)))[audits]
in
    Source
Do not foget to copy paste your own Business Intelligence API Key into the URL above. You can generate your Business Intelligence API Key in the Api keys section of the app.
More options can be found in the Business Intelligence API section.
Visualize Data
When you are satisfied with the loaded data, click Close & Apply. This will bring you back to the Power BI Desktop. Here you can add graphs and create various visualizations of any part of the audit logs you desire.

Business Intelligence - Previous

Audit Logging

Next - Business Intelligence

Connecting from Power BI (deprecated)

Last modified 5d ago

Copy link

Outline

How to Connect Power BI to Business Intelligence API?

Get Data

Apply Limits or Filters

Visualize Data