kdb Insights Enterprise Power Query Connector

This page describes how to import data from kdb Insights Enterpriseinto Power BI.

The certified kdb Insights Enterprise Power Query Connector supports the Import data connectivity mode.

The connector enables Microsoft Power BI users to remotely connect, explore, query data, preview tables, and harness the power and performance of kdb Insights Enterprise analytics before importing datasets into Power BI for visualization.

Prerequisites

Ensure the following prerequisites are met before proceeding.

1. kdb Insights Enterprise 1.11.0 or above is installed and running at least one database.

2. Permissions to query data on the running databases.

3. Access to either:

   • Power BI Desktop

   • Power BI Online

Connect

To connect Power BI Desktop or Power BI Online to kdb Insights Enterprise and import data, take the following steps:

1. Open Microsoft Power BI Desktop or Power BI Online.

2. Open the Get Data screen:

   • For Power BI Desktop, click Get Data -> More from the Home tab in the upper ribbon.

   • For Power BI Online, in the Get Data experience, select the Dataflow category. Refer to Creating a dataflow for instructions.

3. Enter KX kdb into the search box.

4. Select KX kdb Insights Enterprise.

5. Click Connect.

6. Enter the Hostname, or URL for your kdb Insights Enterprise deployment.

   Note

   To get the hostname from an Azure Marketplace deployment

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

       

      

   2. Select Services and Ingresses from the left panel under Kubernetes resources.

   3. Select the Ingresses tab.

      

   4. Copy the value in the Hosts column which like this: xxxx.someregion.cloudapp.azure.com

7. Click OK.

8. You are prompted to sign in if you have not signed in recently or have never signed in:

   1. Click Sign in. The button is named Sign in as a different user if your login credentials have expired or are no longer valid.

       

      

   2. To sign into kdb Insights Enterprise, either:

      • Enter your Username and Password, or

      • Click Microsoft Entra ID if Microsoft Entra integration is enabled.

   3. If your credentials are valid, the Connector popup displays that you are currently signed in and you can click Connect.

       

      

9. The Navigator dialog box displays all the running databases and tables for your chosen host on the left. Clicking on a table returns a preview of the selected data using the kdb Insights Enterprise getData REST API.

    

   

10. Enter the parameters (see table below) and click Apply. This allows you to leverage the power of kdb Insights Enterprise analytics to filter, group, and aggregate via the the kdb Insights Enterprise getData REST API before importing the data. This restricts the data being loaded and brings you the performance of the kdb Insights database.

     

    Parameter	Details
    Start Time	Applies to the partitioned column and are ignored for non-partitioned tables
    End Time	Applies to the partitioned column and are ignored for non-partitioned tables
    Filter	Filters out certain rows
    Aggregation	Filters the columns and/or aggregates the rows being returned
    Group By	Group the results of an aggregation based on specific columns

    These parameters are described in more detail here.

    Note

    We recommend that you set a start and end time for partitioned tables to avoid running queries across the entire dataset.

11. After defining the parameters you require, choose to either:

    • Transform Data: To open the Power Query Editor window and transform the data further. See here for details on the Power Query Editor.

    • Load: To load the data without any further transformations. After loading the data you can still modify a query using the Power Query Editor if required.

Parameters

This section describes how the parameters can be applied in the Navigator dialog box.

Start and End Time

The Start and End time parameters filter the partitioned field on a partitioned table using the times specified where:

• Start Time is the inclusive start time of the period of interest.

• End Time is the exclusive end time of period of interest.

If you specify a date with an incorrect format, the Apply button is disabled and a warning displays asking you to input a DateTime value.

Filter

The Filter parameter specifies the filtering options to be applied before returning the data. The documentation here shows details of all the filtering options that can be applied.

The parameter is specified as a list of triples, where each triple has the form ["operator", "column name", values]. The available operators are documented here.

Examples:

Description	Parameter value
id is less than or equal to 10	[["<=", "id", 10]]
id is any number between 10 and 20	[["within", "id", [10,20]]]
id is greater than 10 and rate is less than 80	[["and",[">", "id", 10],["<", "rate", 80]]]

Aggregation

The Aggregation parameter specifies the columns and/or aggregations to return after selecting the data. The documentation here shows details of the options that can be applied.

The parameter is either specified as:

• A list of symbols ["column name1", "column name2"] to only return the chosen columns.

• A list of lists of symbols [,["new column name1", "operator1", "column name"],["new column name2", "operator2", "column name2"]] to return aggregates with new names.

The available operators are documented here.

Examples:

Description	Parameter Value
Only return the data and id columns from each row	["data", "id"]
The average rate and minimum id value across all rows	[["avgrate", "avg", "rate"],["minid", "min", "id"]]

Group By

The Group By parameter must be used in conjunction with Aggregation parameter to perform aggregations across column values. The documentation here shows details of the options that can be applied.

The parameter is specified as a list of symbols ["column name1", "column name2"].

Examples:

Description	Aggregation parameter	Group by parameter
The average rate value per id value	[["avgrate", "avg", "rate"]]	["id"]
The number of records and the average rate per id value	[["countperid", "count", "id"],["avgrate", "avg", "rate"]]	["id"]

Modifying an existing query

Once you have defined a query you can edit the parameters of a query in the Power Query Editor using Navigation within Applied Steps.

In Power BI Desktop:

1. Select the query from the Data right hand panel.

2. Click on the ellipsis ... and selecting Edit query to open the Power Query Editor.

3. Select Navigation in the Applied Steps section of the Query Settings panel, as shown here.

4. The central pane displays the same parameters as those available in the Get data flow, with the benefit of date pickers for Start Time and End Time and larger text boxes for the other parameters.

    

   

5. Click Invoke to submit the changes.

Analyzing data

To analyze the data once it is loaded:

1. On the left sidebar, select the Report view graph icon.

    

   

2. Under Visualizations, select the icon for the type of chart and enter the data for each axis.

    

   

Summary

As seen above, with just a few clicks you can import kdb Insights Enterprise data directly into Power BI using the certified KX kdb Insights Enterprise Power Query Connector.

By integrating kdb Insights Enterprise data directly into the report creation experience we are making it easier than ever to access, analyze, and collaborate on data.