Skip to content

Uploading Data with the API

It is possible to use the Rational BI API to upload databases for use with datasets. This can be beneficial in certain scenarios such as when automating construction of reporting datamarts. By having an ETL process create and upload databases according to a schedule, it’s possible to tightly control the reporting data that is made available to users, to build targeted databases for specific uses and allow very wide usage of data and queries against the uploaded data without placing any load on the original data stores.

Rational BI will manage the uploaded databases and enforce security based on the ACLs applied to the dataset. Since a single dataset can contain multiple databases, it’s easy to maintain a robust reporting system by maintaining a trail of historical databases that are only replaced when a new database has been uploaded and integrity checked.

When a dataset has more than one database uploaded it will serve up the database marked active to report consumers. If a problem is detected, an user with sufficient privileges can navigate to the data tab of the dataset in the user interface and mark an alternative database active.

The Rational BI API is a REST API with a simple structure and can easily be integrated in any language.

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.

Uploading a database with the API

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 .

import requests

def main():
  session = requests.session()

  base_url = "https://app.rationalbi.com/api/v1"

  # Get a session with our service account
  r = session.post(base_url + "/user/session", json={ 'uuid': '1c7dccb2-c477-47e8-8695-9d0da4e5c438', 'password': 'cdc3f419-84f3-5cc6-983a-86bc77261e0b'})

  if r.status_code != 200:
    print("Login failed: " + r.text)

  # Upload the database
  url = base_url + "/dataset/77cb9b1b-d941-4e47-a523-be753fa11085/45005a87-cd82-453c-a16a-aa1d8c03cb50/database/new/data"
  r = session.post(url, data=open('temp.db', 'rb'), params={'description': 'Generated by GA-Loader', 'activate': 'true'})

  if r.status_code != 200:
    print("Upload failed: " + r.text)

  pass


if __name__ == '__main__':
  main()