Ontopic Docs

# Consuming Semantic SQL endpoints

Starting from Ontopic Suite 2025.1.0

# Overview

Semantic SQL endpoints expose all data from the default graph of your knowledge graph through auto-generated virtual tables. These tables are organized into three distinct schemas:

  • classes: contains one table for each class in the knowledge graph
  • data_properties: contains one table for each property used as a data property in the knowledge graph
  • object_properties: contains one table for each property used as an object property in the knowledge graph

Class tables include as many relevant columns as possible while ensuring that the identifier column for each class instance serves as a primary key. These tables are denormalized by default to minimize the need for SQL users to deal with joins (see the corresponding parameter for more details). Since these are virtual tables, there's no performance penalty for including unused columns in your queries, thanks to Ontopic Suite's built-in optimizations.

What do we mean by semantic SQL?

Semantic SQL refers to queries expressed over the knowledge graph structure rather than the underlying data source structure. Ontopic Suite reformulates semantic SQL queries into source SQL queries that are executed directly by the data source.

# Foreign keys (beta)

Ontopic Suite automatically infers foreign keys between tables to recreate the linked data experience within SQL. This enables several powerful capabilities:

  • Automatic generation of Entity-Relationship Diagrams using generic SQL tools (e.g., DBeaver)
  • Table navigation features in clients like Metabase
  • Enhanced SQL query generation by LLMs through better understanding of table relationships

This feature is currently in beta as some foreign keys may not be inferred in certain edge cases. Expect improved inference capabilities in upcoming releases.

# Connecting to the PostgreSQL interface

You can find the connection details for the PostgreSQL interface on the Query > SQL page in Ontopic Suite's UI. To establish a connection, you'll need to:

  1. Create a Personal Access Token (used as the authentication password)
  2. Download the CA certificate to secure TLS connections

# Generic connection details

The PostgreSQL interface supports connections from various SQL clients, dashboarding tools, and BI platforms. Below are example connection details similar to those found on the Query > SQL page:

  • Dialect: PostgreSQL
  • Driver: org.postgresql.Driver
  • Host: yourserver.dev
  • Port: 4300
  • Database: your-database
  • Username: user-3decfcff-180e-888d9889a5db
  • Access Token: pwd-hMZffHW2skmaC8HGeBXg (to be generated in the UI)

Generally, a PostgreSQL connection string follows this format:

postgresql://<username>:<password>@<host>:<port>/<database>?key1=value1&key2=value2

For example, to connect to the example database using TLS:

postgresql://user-3decfcff-180e-888d9889a5db:pwd-hMZffHW2skmaC8HGeBXg@yourserver:4300/your-database?sslmode=verify-ca&sslrootcert=/path/to/ca.crt

Special characters in username or password

If there are special characters in the username or password (e.g., @, :, /, etc.), they must be percent-encoded (opens new window). For example, if the password is p@ss/w:rd, it should be encoded as p%40ss%2Fw%3Ard.

# Supported clients

# Power BI

Power BI (opens new window) is a business intelligence and data visualization platform that supports PostgreSQL connections through native drivers or ODBC.

# Native Connection

Data can be consumed in two ways:

  • Import mode: Data is imported into Power BI
  • DirectQuery mode: Queries are executed directly on the data source without importing data
Screenshot of Power BI native connection page

Disabling TLS for the native connection

Power BI requires the TLS certificate of native connections to be validated by a system-installed Certificate Authority. Unfortunately, at the moment you need to disable TLS. To do so, turn off encryption in the Power BI settings File > Options and settings > Data source settings > Edit Permissions and uncheck the Encrypt connections option.

Screenshot of Power BI disable encryption page

# ODBC Connection

You can also connect using ODBC drivers (such as psqlODBC). Note that ODBC connections only support import mode — direct querying is not available.

Screenshot of Power BI ODBC connection page

In order to use the verify-ca SSL mode, you'll need to add the CA certificate to the Users\<user>\AppData\Roaming\postgresql\ directory and name it root.crt.

# DBeaver

DBeaver (opens new window) is a free database management tool that supports connecting to PostgreSQL databases through JDBC drivers. It provides a visual interface to explore the database schema, run SQL queries, and manage database objects. You can use it to explore the data and generate Entity Relationship Diagrams.

Screenshot of DBeaver connection page

DBeaver also allows you to easily set up an TLS connection by specifying the mode and, if necessary, the path to the CA certificate.

Screenshot of DBeaver SSL configuration page

# Tableau

Tableau (opens new window) is a visual analytics platform that connects to various data sources, including PostgreSQL databases. When creating a new Virtual Connection, you can configure each table as either:

  • Live: Queries the data source directly (default)
  • Extract: Imports data into Tableau's internal storage

Screenshot of Tableau connection page

# Metabase

Metabase (opens new window) is an open-source business intelligence platform that supports PostgreSQL connections. Metabase leverages foreign key relationships to enable intuitive data exploration and automatic question suggestions.

Screenshot of Metabase connection page

Compatibility with recent versions

Metabase versions 0.51.0 and above are not currently supported by the semantic SQL endpoint. To use Metabase with Ontopic Suite, please install version 0.50.36 or earlier.

# Superset

Apache Superset (opens new window) is an open-source data exploration and visualization platform with native PostgreSQL support.

Screenshot of Superset connection page

Note that the SSL checkbox in the connection page is equivalent to the sslmode=require option. To use other SSL modes, you can specify them in the additional parameters field. For example, to use the verify-ca mode, you need the following parameters: sslmode=verify-ca&sslrootcert=/path/to/ca.crt.

# Model Context Protocol (MCP) Server

The Model Context Protocol (MCP) (opens new window) is an open protocol that standardizes how applications provide context to Large Language Models (LLMs). The MCP defines two roles:

  • MCP Server: external resource (e.g., a database, API, file system, etc.) that provides context data through a set of tools that the LLM can invoke
  • MCP Client: LLM application that requests context data from an MCP server to enhance its responses

In the context of Ontopic Suite, the PostgreSQL interface can act as a the datasource for an MCP server to provide information to the client. For example, you can use Claude Desktop (opens new window) as an MCP client and configure the claude_desktop_config.json file to define the MCP servers to connect to. Below is an example configuration to use the original Anthropic's PostgreSQL MCP server (opens new window) implementation, using the connection details to the PostgreSQL interface:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://user-3decfcff-180e-888d9889a5db:pwd-hMZffHW2skmaC8HGeBXg@yourserver:4300/your-database"
      ]
    }
  }
}

# Query Reformulation

Semantic SQL queries are fully reformulated into source SQL queries optimized for execution by your data source. The reformulated queries preserve the same column names and data types as the original semantic queries.

This capability enables several advanced use cases:

  • Pure reformulation service: Use the Web API to generate optimized source queries without routing data through the semantic endpoint
  • Design-time knowledge graphs: Use the knowledge graph for query design, then execute predefined queries at runtime for optimal performance

This capability is particularly valuable when working with large datasets, as it reduces network overhead and eliminates intermediate data transformations.

# Configuration parameters

Configuration parameters can be set in Settings > SQL Configuration entries.

# Auto-generated column depth

The parameter autogeneratedColumnDepth controls the level of denormalization of class tables. With its default value of 1, properties of resources one step away are also added as columns in the class table. For instance, the class table Hotel includes the column geo.latitude, which corresponds to the latitude of the GeoCoordinate instance linked to the hotel through the object property geo. With autogeneratedColumnDepth set to 0, only direct properties of hotels (e.g., name, geo and city) are included as columns. With the value set to 2, it includes columns like city.state.name for the name of the state where the hotel's city is located.

For clean Entity-Relationship Diagrams generated by SQL tools like DBeaver, we recommend setting this value to 0. For usage in BI tools like Tableau and Power BI, we recommend setting it to 1 or 2 to save users from handling joins themselves.

← Quickstart →