How the Rational BI data model works¶
Rational BI is fundamentally a client-side Business Intelligence system. Much of the business logic and many of the queries are executed in the web browser instead of on the server.
The user interface uses the latest web technologies to embed a sophisticated data engine and a full SQL database into the web browser. This means that much of the work that was previously performed on a server can now be performed directly where the data is processed without slowing down for server or network congestion.
Functionally, data is accessed through two ways:
- Data that is transferred from the server in the form of self-contained full relational databases with queries performed in the browser.
- Remote data connections where data queries are forwarded to a customer-controlled remote database.
The two models have different and complementary benefits and can be used simultaneously and complementary. It is, for example, possible to make a query to a remote database, perform some data manipulation, store the results in the in-browser database and use the constructed table data to drive visualizations and filtering.
Rational BI uses SQLite as the database running in the web browser. SQLite is an open source database that is extremely robust and is built into billions of devices such as phones, custom applications and operating systems.
When the Rational BI user interface is loaded, the system loads an optimized copy of the SQLite database engine into a separate browser thread and makes it available to the reporting system.
When data from a dataset is requested, such as data from an open dataset, the client asks the server for a copy of the active database associated with that dataset. Since datasets come in many flavors, the server may need to construct a database, or if possible use one that is already stored and marked active.
The server will transfer the database to the client in the form of a compressed SQLite database file. The client will attach the downloaded database to the in-memory database engine and start executing queries.
Since the downloaded database can contain multiple tables and a full set of columns, it is possible to construct databases that contain sufficient data to allow client-side filtering, drill-downs and data crosstabs without requiring additional rountrips to the server.
Multiple databases can be attached at the same time and the database can perform joins across databases and tables. Since the database is a fully featured SQLite database file, you can take advantage of indexing, views, SQL window functions, common table expressions (CTEs) and many other advanced database features directly in the web browser.
SQL supports ACID transactions, most of SQL-92 and generally follows PostgreSQL standards.
The database is both read and write from the web browser client which means that you can create tables, store, update and delete data and generally do anything you would be able to do in a server-based RDBMS.
When a report is unloaded, the database engine is fully recycled to ensure that no data is retained and accessible between reports.
Database files are immutable¶
Rational BI databases are formatted as SQLite database files. Databases are created or uploaded in a single transaction and are immutable once uploaded. This means that they can’t be modified in any way. If you would like to change any of the data in a database, replace it with a new database and mark it active. The old database can be kept for archival purposes or deleted.
Subscription plans come with storage limits.
Delete old database files to reduce storage usage.
Immutable database files allow data to be efficiently cached and reused across a broad report consumer audience. If your data changes very frequently and you need to report on the absolute latest data, consider using a remote SQL connection instead.
How databases are built from files¶
When a data file such as a spreadsheet, a CSV, or another data file is uploaded to Rational BI, either directly through the user interface or indirectly through a data connection, the file will eventually be loaded by the system, inspected and converted into a database.
Database files in Rational BI are SQLite objects which store databases as self-contained single files.
Processing differs a little bit depending on the type of the source files. Some files, such as spreadsheet files, contain a lot of metadata with information about column formats and formatting while others contain raw textual data.
The system will apply various heuristics to guess the source data type and format when actual formatting information is unavailable and this is the case for many text-formatted files such as CSV (comma-separated values) and TSV (tab-separated values) files. The system will look at the data in the columns and try to fit it to known patterns of dates, numbers and other formats. When the system is reasonably certain that a column conforms to a particular format, it will use that for parsing the data in that column and register the corresponding data types in the dataset metadata.
Some types of files (primarily spreadsheet files) can have multiple sheets. These will be translated into tables by the system where each sheet corresponds to a table. The sheet names will be used to name the tables. When the file only contains a single table of data, which is the case for most files, such as CSV files, the system will use the file name as the table name of the single table.
Assuming that the data file is acceptable and that the dataset metadata is correct, the system will build a database from the source data by creating a new SQLite database, issuing CREATE TABLE statements with the appropriate DDL and start loading the tables with the source data.
When all data has been loaded, the database will be unmounted, integrity checked and compressed. It is then assigned a globally unique identifier and stored on replicated storage.
The database will be listed in the data tab of the dataset. For datasets where this process is automatic, the data tab will not be visible but the general process is similar.
Constructing a database to upload¶
Rational BI databases are SQLite files and to upload a database via the API, you’ll first need to make a database file. This is generally straightforward since virtually every platform and computer system has access to SQLite in some form.
You will need to create a database schema that conforms to the metadata configured in the dataset. Be sure to pay attention to the formatting of dates and currencies since SQLite has limitations on how they can be expressed and the user interface expects data in a certain form.
It is not necessary to strictly create the same tables as defined in the metadata schema as long as the database can satisfy the same queries. In other words, you can, for example, create any variation of the schema and expose views that follow the expected schema.
Databases can be indexed and normalized but keep in mind that indexing increases the size of the database file and since the entire database is loaded into the web browser’s memory space, it may be a better trade-off to not index data in the reporting databases. It’s usually a good idea to do some benchmarking in this area.
When the SQLite database has been populated with the required reporting data, save it as a file.
The API allows you to do a HTTP POST to upload the new database to the Rational BI servers where it will be loaded, integrity-checked and validated to make sure that it conforms to the required schema. If all checks pass, the database will be compressed and stored.
You can optionally mark the database active as part of the upload API call. If you do so, it will, if accepted, be the new default database to be served to report users. If you don’t mark it active, you can control the active database through the data tab in the dataset page in the user interface or set mark the dataset active through the API at a later time .