Skip to content

Working with remote databases

Remote datasets act differently from the other types in the sense that Rational BI stores no database. Instead, data requests and queries are proxied by Rational BI through the dataset to a source server that evaluates the query and returns a response to the requester.

This type of dataset does not have any databases to transfer which means that the amount of data sent to the data consumer’s web browser is reduced and that the latest data is always used. However, remote datasets can put additional strain on the source database.

Concurrency

Remote datasets enforce a limit on the number of concurrent queries that can be processed through the database connection in order to protect the remote data source. The default limit tries to strike a balance between query throughput and database load.

The maximum concurrency can be adjusted to balance these factors, and a large number of report consumers may require additional query slots assuming that the remote database is able to service the connections.

Query Cancellations

Remote datasets will attempt to cancel queries as they are superseded by new ones. This can happen when a report element or data cell is refreshed multiple times in quick succession before a prior resultset has been returned.

Security Considerations

Direct database connections should be evaluated from a security perspective. Connections should be configured with read-only user credentials with minimal permissions required to perform queries.

Rational BI does not validate the queries issued through programmatic code and even if a user does not have the means to directly create or edit reports, it is still possible to interact with a remote dataset through the API. In other words, when evaluating the security considerations of a remote dataset, you should assume that any user that has access to read from the remote dataset is also able to issue any types of queries.

To reduce any risk associated with direct database queries, consider creating a user with limited access to only specific tables, or define a set of views that only expose a subset of the data in the database.

Performance Considerations

Direct database connections can put a high load on a connected database. The specifics of the load will be dependent on several factors such as:

  • The number of concurrent report consumers
  • The complexity of the issues queries
  • The format of the database such as indexes applied and server specifications.

If database performance is a concern, you can instead consider creating a stored dataset with datamarts designed for high performance reporting. In this scenario, you can use an ETL tool of your choice to copy data from your database to a SQLite database on a scheduled interval. The Rational BI API makes it possible to upload the resulting database file to the system and the system will store and serve the reporting database when the data is referenced in a report. Uploading custom datamarts in the form of SQLite databases fully removes the real-time query load on a remote database and enables very efficient filtering and querying since operations are performed directly in the report user’s web browser without having to wait for the network or server to respond, enabling virtually infinite simultaneous users while still maintaining extremely performant reporting experience on the client.

Connecting to database types not listed

Rational BI uses JDBC to connect to remote SQL data sources and is generally able to connect to any data store with a published JDBC driver. Contact Rational BI support if your database isn’t listed among the databases currently listed and we’ll be happy to add support for your data store.