SQLi interface

Incorta supports external integrations with various business intelligence and analytical applications using the PostgreSQL protocol. Applications such as Microsoft Excel, Power BI, and Tableau can query data within Incorta using Incorta’s SQLi interface and a PostgreSQL supported connector. The Incorta SQLi interface exposes both physical schemas and business schemas to external tools as a PostgreSQL database including metadata that emulates a PostgreSQL 10 implementation. For further information about the PostgreSQL protocol please refer to https://www.postgresql.org/docs/10/protocol.html .

Important Terms

Before proceeding, it is important that you become familiar with the following terms:

Loaded Source Schema, contains all the tables from the data source (e.g. Oracle, MySQL). This schema can be loaded:

  • Fully in memory.
  • Partially in memory, and partially in the staging area.
  • Fully in the staging area.

Loaded Query Schema. This schema contains only the query tables for Analyzer users when building dashboards. These queries will depend on the business user’s requirements, and must be created by users with the “Schema Manager” role.

Connect to the SQLi interface from external BI tools

In order to connect to Incorta’s SQLi interface from external BI tools (e.g. Tableau), use the following steps:

  1. Create a connection to the SQLi Interface
  2. In the external BI tool, provide the following parameters:

    • Data Source type: Choose “PostgreSQL” as your data source type.
    • Data Source Name: Provide the tenant name used in Incorta.
    • Username/Password: Provide Incorta’s login credentials in the appropriate fields.
    • Connection string: Provide a connection string including Incorta’s host server, port number (5436 to connect to Incorta’s engine, or 5442 to connect to Apache Spark), and the tenant name.
  3. Discover the source schema tables created in Incorta.

Now you are ready to build dashboards in your BI tool.

Create a New Data Source Connection

In order use the SQLi interface, the first step is to create a connection with Incorta as PostgreSQL, using the following steps:

  1. Select +New to open the Add New Data Source window.
  2. Provide the following parameters:
  3. Data Source type: Choose “PostgreSQL” as your data source type.
  4. Data Source Name: Provide the tenant name used in Incorta.
  5. Username/Password: Provide Incorta’s login credentials in the appropriate fields.
  6. Connection string: Provide a connection string including Incorta’s host server, port number (5436 to connect to Incorta’s engine, or 5442 to connect to Apache Spark), and the tenant name.
  7. Select Add Data Source. The Test Connection button appears.
  8. Select Test Connection. The connection test status window appears.
  9. Select OK.

Create a query schema

As mentioned before, a query schema contains query tables. Depending on the business user’s requirements, users with the “Schema Managers” role can build query schemas based on the query tables they create, and load them.

In this step, Schema Manager can use the schema wizard to discover tables located in the source schema initially created in Incorta.

Once the query schema is properly defined and loaded in Incorta’s memory, users with “Analyzer” role can easily use it to build Insights.

© Incorta, Inc. All Rights Reserved.