1. Overview
   1. What to expect from this section
2. Connect to a database
   1. Create a default data lakehouse connection
3. Create a database
   1. Physical datasets
   2. Virtual datasets

Overview

BI Navigator supports a variety of databases via an intuitive connection wizard, and comes pre-configured with a connection to the Navigator data lakehouse. This default setting is generally sufficient for most use cases, but there may be situations where an administrator may need to incorporate their own database(s) (or static sources such as CSV files) into BI Navigator. Currently new databases may only be configured by users with Tenant Owner permissions on Commander™.

What to expect from this section

This guide will cover the following topics:

• Connecting to a database.
• Creating a database.

Connect to a database

BI Navigator supports a variety of databases via an intuitive connection wizard, and comes pre-configured with a connection to the Navigator data lakehouse. This default setting is generally sufficient for most use cases, but there may be situations where an administrator may need to incorporate their own database(s) (or static sources such as CSV files) into BI Navigator. Currently new databases may only be configured by users with Tenant Owner permissions on Commander™.

The clip below shows how to configure a new connection. Note that before navigating to the database page one needs to ensure the database is exposed to the environment within which BI Navigator is deployed. In many cases this includes whitelisting the relevant IP addresses on any internal firewall.

As shown in the clip above, apply the following steps:

1. Navigate to the database page. This is located in the Data dropdown. Click on “Databases” (found in the top navigation bar on all of the platform’s pages).

   The option to access the database page is only possible for users with administrator permissions.

2. Select the +DATABASE button in the top right corner of the page and add a new database.
3. A dialog box “Connect a database” will appear. Select the database to which you want to connect.
4. Configure the selected database by inserting relevant information into the form. In most cases this involves building the SQLAlchemy string needed to connect to the database. Further instructions on the various connection strings can be found here. BI Navigator makes use of the display name to control access across clusters. It is therefore important to ensure the display name begins with the tenant name followed by a dash. For example, “Tenant A” could create a database for a data lakehouse named tenant_a-datalakehouse.
5. Click on CONNECT.

Create a default data lakehouse connection

BI Navigator is generally pre-configured with a default connection to the Navigator data lakehouse. If this connection is not present by default, it can be created manually.

Creating a connection to the integrated data lakehouse follows the same initial procedure as other connections, after which the user takes the following steps:

1. When prompted to select the database with which you want to connect, select the “Trino” option from the dropdown.
2. Fill in the display name as {tenant name}-datalakehouse, where the tenant name is the specific tenant’s name.
3. Use “trino://username:password@trino:443/iceberg” as the SQLAlchemy URL. Replace the username and password with tenant-specific details.
4. In the “Advanced” tab, under the “Other” accordion, add the line ”{“connect_args”: {“verify”: false}}” to the “Engine Parameters”. This is to ensure the data lakehouse makes use of a self-signed certificate when it is not externally exposed.

- Test the connection by clicking on the TEST CONNECTION button (found under the basic tab).

- Add the connection to databases by clicking on the CONNECT button.

Create a database

After the organisation’s admin has connected to the data source, users can start building charts by selecting datasets.

A dataset in BI Navigator is an abstraction of the platform that points to selected data in the database which contains a comprehensive collection of relevant metrics. Each BI Navigator chart is built using datasets.

BI Navigator offers two types of datasets: physical and virtual. A physical dataset points to a table in a connected database, and a virtual dataset is a view of data created using SQL Lab (BI Navigator’s collaborative SQL IDE).

Physical datasets

Physical datasets refer to tables that can be accessed directly from a connected database. A physical dataset can be added by following the steps shown in the clip below:

1. Click on the “Data” link found in the top navigation and select “Datasets” from the dropdown.
2. Click on the +DATASET button to add a dataset.
3. Once the +DATASET button has been clicked, a dialog box will appear.
4. Configure the relevant database first (see Create a default data lakehouse connection). This is usually {tenant name}-datalakehouse for the Navigator data lakehouse.
5. Select the required schema from the {tenant name}_blueprints schema. Data exported from Commander is accessible from this schema. This is also the main integration point for dashboards. Note: future releases will include data_quality and exceptions schemas.

1. Select the table that will be used to create the dataset. All exported blueprint names are automatically prefixed with “bp_”.
2. The new dataset has now been added to the list of datasets. Select the dataset from this list to begin building charts.

As shown in the clip below, the buttons under the “Actions” column can be used to Delete, Export and Modify the dataset.

Virtual datasets

Virtual datasets refer to views that result from SQL Queries that are executed on physical datasets. This type of dataset uses physical datasets to create logic that can be applied to create complex charts and other aggregations across different datasets and data schemas.

Virtual datasets are created in the SQL Lab view:

1. Navigate to “SQL Lab” in the top navigation bar and select “SQL Editor”.
2. Develop the desired SQL Query in the “SQL Editor” console.
3. Click on the RUN button to inspect a sample of the results.
4. After running the query it will be possible to explore the query as a virtual dataset. Click on the CREATE CHART button to explore the query as a virtual dataset.
5. A dialog will open that allows the user to save the dataset either as a new virtual dataset or use new properties to overwrite an existing one.