Azure PowerBI

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 analyze it in your PowerBI environment.

Prerequisites

1. kdb Insights Enterprise is installed
2. kdb Insights Enterprise contains data

3. Versions in use:

   product	version
   kdb Insights Enterprise	1.4 (March 2023)
   PowerBI Desktop	2.114.864.0 64-bit (February 2023)

4. PowerBI Desktop is already downloaded and configured

5. You have downloaded the kdbPowerBI template here

Get PowerBI ready

To load data, you need to update some privacy settings:

1. Open the PowerBI desktop application.
2. On the top left, access File > Options and Settings > Options > (under Global) Privacy > Set it to "Always ignore Privacy Level settings".

3. Click OK.

   

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.

Host Name

1. Access your Resource Group in the Azure Portal and select the kdbInsightsEnt cluster.

   

2. Look at the left panel (under Kubernetes resources) and select Services and Ingresses

3. Select the Ingress tab.

   

4. Make a note of the value in the Hosts column (it should look like this: xxxx.someregion.cloudapp.azure.com).

Client Id

You will create a new Client Id for this example.

1. Open a web browser.
2. 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/).
3. You will be taken to the Keycloak Sign in page.

4. Log in with the credentials from step 3 of the install guide.

   

   Reminder

   Username or e-mail is always "user".

5. Once inside Keycloak, open the dropdown in the left panel and select Insights

   

6. Right below the dropdown, select Clients

7. Inside Clients select Create Client

   

8. Type in your Client ID General Settings and Save

   

9. Adjust the Capability Config and Save

   

10. Open the Service Account Roles tab

11. Select Assign role

    

12. Search for insights.query* and select Assign

    

Client Secret

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:

1. Choose the Access > Credentials tab

2. Copy the Secret in the box

   

3. Make a note of the copied text

User Query

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

Warning

KX compatibility with ANSI SQL is in progress, check the latest status of the supported functionalities here

Max Records

This field is optional and does not need to be populated.

Loading data with template

1. Open the PowerBI desktop application.

2. Access File > Import > PowerBI template.

   

3. Select the template.

4. Fill out the fields in the template with the values retrieved in the previous section.

   

5. Select Load.

6. A pop up like the one below will appear, select Anonymous on the left panel and Connect.

   

Warning

In some cases, PowerBI will complain about Credentials, if this occurs, follow these quick actions to resolve the issue.

Retrieving data

From the screen in the previous step:

1. On the left > select the table icon.

   

   

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

Analysing data

With the data loaded into the table:

1. On the left > select the graph icon.

   

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

1. When the error pop-up appears select Cancel

2. A message will pop-up saying "you'll need to refresh data" select Close

   

3. You will now be on the main PowerBI screen with the following yellow message:

   

4. Access File > Options and settings > Data source Settings

   

5. A new window will appear with your current host, select the host and click (below) Edit Permissions...

   

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

   

7. Select Edit > A pop-up will appear (like at the beginning), make sure you select Anonymous and Save

   

8. You´ll see the Permissions are now set to Anonymous. Make sure the Privacy Level is set to None and select OK.

   

9. In the toolbar at the top, click Refresh

   

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

1. On the Right panel, find getData, select the ...

   

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

3. Select UserQuery and edit the field on the right side panel > select getData to reload > (you'll see the credential message pop up again, but you can ignore it)

   

4. On the top panel select Close & Apply > the window will close and you'll go back to the main screen.

   

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

   

6. Continue to analyze the data