Skip to content

Data sources

A data source is used to retrieve data from connected databases for use by dashboard components. Data sources are edited using the Data Dialog.

  • The Data Dialog is opened by clicking a component’s Data Source property.

    Screenshot

  • Click New to create a new data source. Name it.

    Screenshot

  • Pick its connection or connection group.

    Screenshot

    If you need a new connection, see Connections.

  • Select from:
    Query and enter query text, or
    Builder to build a query using input forms, or
    Analytic and pick from a pre-defined list of functions

  • Click Execute to preview, success will populate the Results table or display an error.
    Performance metrics are also displayed to indicate query performance.

    Screenshot

  • Finally, Click Apply to keep the changes made to the data source and/or Select to choose the selected data source for the component property.

Query

String queries use text-based q to query data from the database.

FXTradeData

will return all column data in FXTradeData from the html5evalcongroup connection.

Screenshot
Simple q query

Builder

Visual queries allow dashboard users with no programming experience to build queries using a range of configurable dialogs.

  • Select Builder; it may be easier to expand the editor to full screen mode Screenshot.

  • Right-click on Result to call up the Add Data Source option

    Screenshot

  • Selecting Add Data Source brings up a menu of available data sources

    Screenshot

  • Right-click on an existing node to add a filter, update, group-by, function, or perform a join.

    Screenshot

  • Each node-type displays its own unique dialog used to define the query.

    Screenshot

    Use a data filter to build a where clause with the option to add view state parameters.

  • Queries can be linked to view state parameters. E.g., the filter node below has a where clause that filters for items equal to the view state parameter "Asset."

    Screenshot

  • Execute - Apply - Select

    Screenshot

    For more on building visual queries

Analytic

Analytics are pre-defined functions with required input parameters exposed in the dashboard.

Screenshot
Sample Analytic

Virtual query

Virtual queries are built using Javascript and utilize data pre-loaded to the client; assigned data sources in a virtual query do not re-query the database.

Virtual queries can meld data from more than one data source, view state parameter or text insert.

function(source, viewstate, text, callback) {

   var result = {
        "columns": [
            "Data Source",
            "View State",
            "Text"
        ],
        "meta": {
            "Data Source": 9,
            "View State": 11,
            "Text": 11
        },
        "rows":[]
    }

    result.rows= _.map(source.rows, function(row) {
        return {
            "Data Source": row.Month,
            "View State": viewstate,
            "Text": text
        };
    });

    callback(result);
}

Screenshot

In the aforementioned example, data from the Month column is returned. Substituting row.columnname; e.g. row.Return would return the values from the Return column in the parent data source.

The meta definitions are the kdb datatypes for each column where "columns" are the names of your table headers.

Lodash for more on supported functions.

Subscription

Static

Default setting. Single request for data

Polling

A client-side poll of the database; this is a direct client request to the database. Server paging can be enabled to limit the amount of data returned to the dashboard.

option      | effect
----------- | ------
Interval    | Time between client poll request
Key         | Select which data source column to define Subscription handling; e.g. to have continual or static update 
Force Reset | By default, for updating data sources Dashboards merges updates from the server with its existing dataset, unless a parameter is changed in which case the existing dataset is cleared. Enabling Force Reset will clear the existing dataset each time it receives an update, regardless of whether a parameter has changed or not.

Streaming

Streaming subscriptions push data to the dashboard in place of a client or server-side poll.

Max Rows

Default is 2,000 rows of data for any subscription type.

Connections

The connection manager can be openend from the Query Editor.

Screenshot

Screenshot

A connection has the following properties:

  • Name
  • Type
  • Host
  • Port
  • Credentials: user and password

Host can be the server name or IP address.

Connection options

button description
New Create new connection
Duplicate Create a copy of a selected connection
Delete Delete selected connection
Save Save details of connection
Close Close the window

Pivot query

Pivot queries are used by the Pivot Grid component.

Pivot data are split between independent variables (Breakdown Cols) and dependent variables (Aggregates).

Screenshot
A pivot query

Case-sensitive names

Names for Breakdown Cols and Aggregates are case-sensitive. If an error occurs, check that column-header names in source database match those used in dashboards.

The order of the Breakdown Cols can be changed using drag-and-drop

Screenshot

Client pivot

Screenshot

In a server pivot the data request is processed server-side and only viewed data is returned to the dashboard; each pivot interaction creates a fresh server-side request. A client pivot requests that all data on dashboard load, and processes all pivot interactions on the client.

For large data sets, the server pivot should be used. For smaller data sets, a client pivot will be faster

Custom binary analytics and client pivots

As a client pivot is not making a server request for data, custom binary analytics Aggregate functions using q will not operate. Use Javascript functions to build custom binary analytics Aggregate functions for client pivots.

Javascript functions

Example of a multi-column client pivot

Screenshot

Screenshot

Pivot breakdown via breadcrumbs

The order of the pivot breakdown can also be controlled by the dashboard user, with the Breadcrumbs component.

Screenshot

  1. Create a View State Parameter (say, breakdown) and associate it with the Breakdown property in the Breadcrumbs component’s Settings.

    Screenshot

  2. In the linked pivot query, set the Breakdown Cols to the breakdown View State Parameter.

    Screenshot

The resulting output shows the breakdown elements laid out in the Breadcrumb component. These can be dragged and repositioned to change the pivot order.

Screenshot

Column label

A two-dimensional pivot query:

{[n]
  ([
      hub:n?`CHIA`XASX;
      country:n?`UK`USA`CANADA`FRANCE;
      account:n?`4;
      displayQueue:n?`NEW`ASSIGNED`CLOSED
    ]
    ncount:n?100) }

Screenshot

Column Label is used to support 2-dimensional pivots. An example query:

Screenshot

Navigation of a OLAP / Pivot control requires enabling Breadcrumbs in a component, or linking with a Breadcrumbs component.

Aggregate functions: unary and binary

Binary Analytic
In the Query Editor, select from the database source
Screenshot
Breakdown Cols
The drill-down buckets, i.e. the independent variables
Aggregates
Functions of the dependent variables. Unary functions are sum, avg, count, min, max.

Custom binary analytics

Screenshot

Example

A standard built-in binary function is available as part of the VWAP Analytic (subVWAP) found in Demo Trading dashboard in the Dashboard evaluation pack. bzise is bid size

q {[a;b] sum[a*b] % (sum[`long $ a])};`bsize

Dynamic aggregate columns

To enable, click the View State icon in the basic pivot query control

Screenshot

Screenshot

Using view states to define Breakdown columns, Aggregate Columns, Aggregate Functions and Labels. Each view state must be of Type list and each Aggregate Column must have a corresponding Aggregate function.

Remember to link to a Breadcrumbs component for navigation control of the pivot.

Update query

An update query allows a User to make permanent changes to source data from the Data Grid; for example, when adding a new client or customer. The update query requires both a query, and a settings check in Data Grid properties.

Screenshot An update query

Force Reset

To ensure updates are returned to the user view, Force Reset must be enabled.

Screenshot
Update query definition example

In addition to using an update query, Data Grid requires Enable Edit Mode and/or Enable Insert/Delete to be checked.

Screenshot

The user can then Edit in her dashboard

Screenshot Activate Edit Mode

Screenshot
Make a change

Screenshot
Submit a change

Permission to edit

If Update Query is enabled, all users with permissions for that dashboard will be able to make changes. If you want only some read-only users to have edit permissions do the following:

  1. Duplicate the dashboard
  2. Uncheck Enable Edit Mode and Enable Insert/Delete from the Data Grid properties
  3. Permission users who shouldn’t have edit control to this duplicate dashboard.

Mapping

Mapping allows the results from a data source to be mapped to View States. Results should include a column that contains keys and a column that contains values.

Mapping configuation is accessible from the Mapping tab in the Results panel.
Screenshot

Key and value columns should be defined in the Key and Value dropdowns.

Mappings can be added using the + button. Within each mapping you can define the Key and the View State that the Value should be mapped to.

Keys in the current results can be added using the dropdowns. Other keys can be added by typing them into the dropdown input field and pressing enter.

The Reset button will populate the list of mappings with all available keys.