Appboard/2.5/builder/dynamic queries

Overview

A solid understanding of AppBoard in general, and in particular Data Sources and Data Collections is recommended before covering this topic.

This document covers Data Source entities where the output remains consistent (same set of columns) but the actual query changes depending on a number of factors, but typically:

• User specific information (user, role, domain)
• Some kind of dynamic SHIM expression (e.g. calculating a date or session variables)
• A query parameter (SHIM Query) which has a default but can be manipulated through the use of Server Side Filters.

The Database, Shell Command, and Web data adapters support these methods to dynamically construct the queries at runtime.

Typical Use Cases

Multi Tenancy

In many environments a particular user, or group (role), or customer may have access to AppBoard but should only be presented with data relevant to them.

SHIM expressions allow for user specific details such as their username, role, domain to be used, or manually configured session variables associated to users or domains, or even user attributes pulled from LDAP.

As an example, a simple SQL query could be modified to use a manually configured session variable called CompanyID associated to the Domain of the user.

select * from tickets where company_id=${shim:session.var.get('CompanyID')}

Data Source Interface Requirements

While a database source may give them freedom to select as much or as little data as needed, the Shell Command and Web Service data adapters may require parameters to function correctly.

Take for example a drill-down dashboard showing information about a particular device. This information might come from a variety of sources, but at least some is only available through a command line tool that requires a device identifier to run. In other words it's not possible to run this tool to get data back for all devices (which may also be bad for performance) and then filter with AppBoard. Instead, use a query parameter called selectedDeviceID as one of the Command Line Parameters when configuring the Shell Command data adapter:

--device_id=${shim:query.selectedDeviceID('default_value')}

In this example the query must have a default value shown above as default_value which is executed when creating the data source, this default value must return valid results so the entity has the correct columns and can be configured appropriately for column types and primary key field(s).

SHIM query parameters are special in that an extra column will be added to the result set with the same name as the parameter. The purpose is not to view that value but to allow the parameter to be set via a Server Side Filter.

In use, still following the example, when drilling down to this dashboard the widget with the Switch to Board action would also need to have a Apply a Server Side Filter to a Data Collection action which explicitly sets the selectedDeviceID to something, usually based on what the user clicked (i.e. a Widget Interactive Comparison).

Performance

For performance reasons it is best to bring as little data into AppBoard as necessary, and send as little data to a client as possible. This reduces memory overhead, processing time on both the server and client, and latency with data transfer from the server to client.

Similar to the example above, perhaps the device information is in an SQL data source and we have access to all devices, perhaps tens of thousands of devices or more. If the dashboards are constructed such that a user is only ever looking at summary information, or drills down to a particular device, then there is no reason to retrieve and store all that information within AppBoard. Instead the summaries can be had using appropriate SQL statements, and the drill-down details can be fetched using dynamic queries in almost the same was as the example above except using the Database Query data adapter:

select * from device_details where device_id=${shim:query.selectedDeviceID('default_value')}

Additional Information

• SHIM Expressions: covers SHIM expressions in more detail with examples and links to the SHIM Reference.