Connect PowerBI to kdb Insights Enterprise
kdb Insights Enterprise can connect to your PowerBI application through REST API. The following steps will help you connect both applications, so you can retrieve data from kdb Insights Enterprise and analyse it in your PowerBI environment.
- kdb Insights Enterprise is installed
- kdb Insights Enterprise contains data
Versions in use:
product version kdb Insights Enterprise 1.4 (March 2023) PowerBI Desktop 2.114.864.0 64-bit (February 2023)
PowerBI Desktop is already downloaded and configured
- You have downloaded the kdbPowerBI template here
Get PowerBI ready
To load data, you need to update some privacy settings:
- Open the PowerBI desktop application.
- On the top left, access File > Options and Settings > Options > (under Global) Privacy > Set it to "Always ignore Privacy Level settings".
Find the data for the template
The template we are using in this example requires certain fields to be populated:
The value for each field can be identified using the instructions below.
Access your Resource Group in the Azure Portal and select the
Look at the left panel (under Kubernetes resources) and select
Services and Ingresses
Make a note of the value in the Hosts column (it should look like this: xxxx.someregion.cloudapp.azure.com).
You will create a new Client Id for this example.
- Open a web browser.
- Paste the Host Name into the address bar, adding "/auth/" at the end (it should look like this: https://xxxx.someregion.cloudapp.azure.com/auth/).
- You will be taken to the Keycloak Sign in page.
Log in with the credentials from step 3 of the install guide.
Username or e-mail is always "user".
Once inside Keycloak, open the dropdown in the left panel and select Insights
Right below the dropdown, select Clients
Clientsselect Create Client
Type in your
Client ID General Settingsand Save
Capability Configand Save
Open the Service Account Roles tab
Select Assign role
insights.query*and select Assign
Assuming a Client Id has been created and the Client Settings have been adjusted and Saved, the Client Secret can be discovered using the following steps:
- Choose the Access > Credentials tab
Copy the Secret in the box
Make a note of the copied text
This refers to the query that will be executed to retrieve data from kdb Insights Enterprise into PowerBI.
It should follow the KX SQL format similar to "select * from TABLE_NAME".
KX compatibility with ANSI SQL is in progress, check the latest status of the supported functionalities here
This field is optional and does not need to be populated.
Loading data with template
- Open the PowerBI desktop application.
Select the template.
Fill out the fields in the template with the values retrieved in the previous section.
A pop up like the one below will appear, select
Anonymouson the left panel and Connect.
In some cases, PowerBI will complain about Credentials, if this occurs, follow these quick actions to resolve the issue.
From the screen in the previous step:
On the left > select the table icon.
You should see the table populated with its contents.
Filtering using labels
If the loaded data contains labels, those can also be used as a filtering method to obtain more specific results.
With the data loaded into the table:
On the left > select the graph icon.
On the right > select the type of chart and the data for each axis.
Solving Credentials Error
If you followed the instructions to load data PowerBI may give a credential error similar to the one below:
If this occurs follow the steps below to resolve the issue:
- When the error pop-up appears select Cancel
A message will pop-up saying "you'll need to refresh data" select Close
You will now be on the main PowerBI screen with the following yellow message:
Options and settings>
Data source Settings
A new window will appear with your current host, select the host and click (below) Edit Permissions...
A new pop-up will appear. You will probably see under Credentials, "Type: Not Specified". This is the core of the issue as PowerBI did not store the defaulted credentials correctly.
Select Edit > A pop-up will appear (like at the beginning), make sure you select
You´ll see the Permissions are now set to Anonymous. Make sure the Privacy Level is set to
Noneand select OK.
In the toolbar at the top, click Refresh
You should now be able to retrieve data
Modifying the initial query
In case you need to edit the query to retrieve additional data, follow the below steps:
On the Right panel, find getData, select the ...
A new window will open, in the left panel you will see all the information you provided for the template to retrieve data.
At this point, you can update the query.
UserQueryand edit the field on the right side panel > select
getDatato reload > (you'll see the credential message pop up again, but you can ignore it)
On the top panel select
Close & Apply> the window will close and you'll go back to the main screen.
On the main screen, click on the table icon to make sure the updated query has been correctly executed and the respective data is loaded.
Continue to analyse the data