Result Set Caching

Drillbridge 3.1.0 and later offer a result caching feature. This is a per-report setting that allows for Drillbridge to cache the results of a SQL query. The most common use of this feature is to provide fast performance and paging through large result sets.

For example, consider a drill-through query that returns 100,000 or more rows. Certain browser/version combinations are not adept at showing this many rows of data without very sluggish performance for the client. It may be more practical to show the results as a series of pages, such as 1,000 or 10,000 rows of data at a time. Relational database systems all have different mechanisms for paging through rows, and all offer differing levels of performance. In many cases, it would be impractical to try and rely on the database to page the results as it might require the user to wait for too long in between different pages of data.

For this reason, Drillbridge offers the ability to cache the results of a query. Based on a given report’s page size setting, Drillbridge will break the results of a query up into several pages. Drillbridge then compresses the data into a binary object in order to conserve as much memory as possible. The first page is shown to the user performing the query. If there are more pages, then the user can simply click on the Next button to go to the next page. This data is shown to the user almost instantaneously as Drillbridge does not need to issue a potentially long running query to fetch more data. It consults its "BLOB server for the compressed data, uncompresses it, and then sends it back to the user.

BLOB Servers

A BLOB object is a "binary large object". As it pertains to Drillbridge, the compressed rows of data are turned into a binary object. Additionally, BLOB is a column type in a relational data store. Drillbridge can be configured to store BLOB (temporary result sets) in one of two places: either locally (using memory), or in a relational database. If Drillbridge is configured to store BLOB/cache data in a database, then a connection must be specified and tables must be created for Drillbridge to use.

BLOB Server Configuration

Create a database as you normally would using the tool for your RDBMS. You may wish to create a new user specifically for Drillbridge caching. Then, create tables using the DDL for your database vendor as noted below. There is no initial data that needs to be populated into the tables. Drillbridge will populate the tables as needed as cached results need to be stored.

H2 Database Configuration

` PAGE_NUM int(11) NOT NULL,`
` DATA blob NOT NULL,`
` );`

` CREATED timestamp NOT NULL,`
` );`

Microsoft SQL Server

Send an email to support


Send an email to support

Drillbridge Cached Result Set Setup

After the database/schema has been created/configured to store BLOB results, some configuration changes must then be made to Drillbridge to activate the use of the schema and turn on the caching feature. Note that database configuration does not need to be performed if the "In Memory" result caching feature is used. That said, it is not recommend to use the In Memory option in a production environment.

In order to configure result caching, edit the file in the /config subfolder of your Drillbridge installation.

Find the following configuration entries:

` dtb.cache.pov.max-entries=10000`
` dtb.cache.pov.expire-minutes=15`
` `
` dtb.blobserver.cleanup-interval=600000`
` dtb.blobserver.cleanup-enabled=true`
` dtb.blobserver.initial-delay=300`
` dtb.blobserver.period=60`
` `
` # Choose INMEM or JDBC or CONN`
` dtb.blobserver.type=INMEM`
` dtb.blobserver.url=jdbc:mysql://`
` dtb.blobserver.username=drilluser`
` dtb.blobserver.password=drillpass`

Note that Drillbridge must be stopped and started in order for changes to the configuration to take effect.

Report Status

Drillbridge allows for specifying blocks of custom HTML (which can include JavaScript) in both the header area and the footer area. This technique allows you to show the status of a long-running report is finished or proccessing.

To use the code Copy and paste the below tags in Reports → Advanced → HTML/CSS page

Custom After Header

<p id="pagestatus"><span class="glyphicon glyphicon-time"> </span> Your report is being processed, please wait... </p>
window.onload = function() {
var currentPageStatus = document.getElementById("pagestatus");
currentPageStatus.innerText = "Report is processed";
const button = document.createElement('button');
button.setAttribute('class', 'close');
button.setAttribute('type', 'button');
button.setAttribute('data-dismiss', 'alert');
button.innerHTML = 'x';

Custom CSS

#pagestatus {
margin: auto;
width: 40%;
padding: 15px;
color: #ffffff;
text-align: center;
font-size: 15px;
border: 1px solid transparent;
border-color: #24a528;
margin-bottom: 15px;
background-color: #28b62c;
border-radius: 4px;
border-width: 0 1px 4px 1px;