SQL Endpoint

The SQL endpoint lets a BI tool connect to an Atoti cube as if it were a PostgreSQL database. Each cube is exposed as a table. It runs a listener that speaks the PostgreSQL wire protocol. It is built on a component called the SQL Bridge, shipped as the Maven artifact activepivot-server-sql-bridge. Clients authenticate with an Atoti username and password. The Atoti Tableau Adapter connects Tableau to Atoti through the SQL endpoint. See its how-to guide for the Tableau-specific connection steps. Power BI also connects to Atoti through the SQL endpoint.

Which SQL is supported?

The SQL endpoint speaks the PostgreSQL wire protocol, so any PostgreSQL client can connect to it. However, only the SQL that Tableau and Power BI generate is supported. Queries written by hand or sent by other tools might not be supported.

How to enable the SQL endpoint

This feature requires a license that enables the bi-adapter component.

Include the following dependency in the pom.xml file of an Atoti project:

<dependency>
    <groupId>com.activeviam.activepivot</groupId>
    <artifactId>activepivot-server-sql-bridge</artifactId>
    <version>${atoti-server.version}</version>
</dependency>

Then set atoti.server.sql-bridge.enabled to true in the Atoti application’s configuration. This property defaults to false. The SQL endpoint opens a PostgreSQL-protocol listener and two REST endpoints that generate the .tds and .pbit files. It stays off by default because those need the application’s security configuration to be reviewed for them.

How to choose the listening port

By default, the server listens for SQL queries on port 5432. This port number can be changed by setting the atoti.server.sql-bridge.port property of the Atoti application. Only one process on a machine can hold a given port. Port 5432 is unavailable if the machine also runs a real PostgreSQL, or a second Atoti server. Set the property to another fixed port in that case, for example 5433. A deployment should use a fixed port. The generated .tds and .pbit files embed that port, and so does a client configured by hand. None of them need to change across restarts. Set the property to 0 instead for a test. This also helps on a machine where no single fixed port can be guaranteed free. The operating system then picks a free port at startup, but a different one at every restart. The .tds and .pbit files must be regenerated, and any hand-configured client reconfigured, after each restart.

Binding the configured port happens in the background. If another process already holds the port, the application still starts. The sqlBridge health component then reports DOWN with the cause, and an error is logged. The .tds and .pbit endpoints answer 503 Service Unavailable.

How to secure the file generation endpoints

The SQL endpoint exposes two REST endpoints that generate connection files. POST /activeviam/bi-adapter/rest/v1/tableau/tds/{cubeName} generates the Tableau .tds file. POST /activeviam/bi-adapter/rest/v1/powerbi/pbit/{cubeName} generates the Power BI .pbit file. Each response describes the cube’s measures and levels, together with the SQL endpoint’s host and port. The SQL endpoint is enabled when atoti.server.sql-bridge.enabled is true and a license enables the bi-adapter component. See How to enable the SQL endpoint. Whenever that happens, the Spring Boot starter secures both endpoints. A caller must be authenticated and hold one of the roles configured by atoti.server.security.roles-user or atoti.server.security.roles-admin. Atoti Server authenticates the request the same way as its other machine-to-machine endpoints, through the Machine to Machine DSL. Setting atoti.server.endpoint.bi-adapter.security.enabled to false removes the starter’s filter chain. Use this for an Atoti application that already secures these two endpoints with a filter chain of its own.

How to encrypt the SQL endpoint

This optional step encrypts the SQL endpoint independently of the web server. The PostgreSQL wire protocol negotiates SSL inside the connection. A client opens a plain TCP connection, sends an SSLRequest, then starts the TLS handshake on that same connection. A load balancer or reverse proxy cannot terminate TLS on the SQL port the way it terminates HTTPS. The port must pass through unchanged, and Atoti Server must encrypt the endpoint itself. HTTPS for Atoti UI and the REST endpoints can still be terminated at the load balancer, with the web server left in clear in that case. The SQL endpoint’s encryption is configured through the atoti.server.sql-bridge.ssl.* property block:

When no property of the block is set, the SQL endpoint follows the web server. It is encrypted whenever server.ssl.* enables HTTPS, using the same material.
When any property of the block is set, it replaces the web server’s configuration for the endpoint as a whole. There is no per-key merging. For example, atoti.server.sql-bridge.ssl.enabled=false keeps the endpoint in clear next to an HTTPS web server, and a keystore or bundle of its own encrypts the endpoint next to a web server that stays in clear.

The block accepts the same keys as Spring Boot’s server.ssl.*: enabled, bundle, key-store, key-store-password, key-store-type, key-alias, key-password, trust-store, trust-store-password, trust-store-type, certificate, certificate-private-key, trust-certificate, protocol, enabled-protocols, ciphers, and client-auth (none, want, need). The material is loaded through an SSL bundle, the same way Spring Boot loads it for the web server. Anything that works for server.ssl.* works here too. A trust store is required only to verify client certificates. The following properties encrypt the SQL endpoint with its own certificate, while the web server stays in clear:

atoti.server.sql-bridge.ssl.key-store=file:/etc/atoti/sql-bridge.p12
atoti.server.sql-bridge.ssl.key-store-password=changeit

Unusable SSL material, such as a missing keystore, a wrong password, or an unknown bundle, stops the application from starting with an error naming the property block to fix. Certificate rotation without a restart applies when atoti.server.sql-bridge.ssl.bundle names a bundle declared with reload-on-update=true. Spring Boot watches the bundle’s files, and a renewed certificate is taken into use by the SQL endpoint for every connection opened from then on; connections already open keep the certificate they negotiated. The following properties declare such a bundle and reference it from the SQL endpoint’s SSL block:

spring.ssl.bundle.jks.sql-bridge.keystore.location=file:/etc/atoti/sql-bridge.p12
spring.ssl.bundle.jks.sql-bridge.keystore.password=changeit
spring.ssl.bundle.jks.sql-bridge.reload-on-update=true
atoti.server.sql-bridge.ssl.bundle=sql-bridge

If the renewed material cannot be loaded, for example a corrupt or half-written keystore, the SQL endpoint keeps serving the previous certificate and logs an error, so a failed rotation never takes the endpoint down. A key-store, certificate, or other store named directly in the block is still read once at startup, as it is for the web server, and a restart is needed to pick up its renewal.

Encrypting the endpoint lets a client encrypt its connection. A client that does not ask for SSL is still served in clear. Configure the client to require encryption: sslmode=require (or stricter) for JDBC and psql, or the “Require SSL” option in Tableau.

Configuration reference

The SQL endpoint is configured through the following properties of the Atoti application:

Property	Default	Description
`atoti.server.sql-bridge.enabled`	`false`	Enables the SQL endpoint. A license that enables the `bi-adapter` component is also required. See How to enable the SQL endpoint.
`atoti.server.sql-bridge.port`	`5432`	Port the SQL Bridge listens on, or `0` to let the operating system pick a free one. Whichever port it ends up listening on is the one embedded in the generated file.
`atoti.server.endpoint.bi-adapter.security.enabled`	`true`	Secures the two REST endpoints that generate the `.tds` and `.pbit` files, requiring an authenticated user holding one of the roles configured by `atoti.server.security.roles-user` or `atoti.server.security.roles-admin`. Set to `false` for an application that secures these endpoints with a filter chain of its own. See How to secure the file generation endpoints.
`atoti.server.sql-bridge.levelNameMode`	`SHORTEST`	How the exposed columns name the cube levels. `SHORTEST` uses the level name alone when no other level shares that name, and falls back to the complete `level@hierarchy@dimension` name otherwise. `COMPLETE` always uses the complete name.
`atoti.server.sql-bridge.ssl.*`	follows `server.ssl.*`	Encrypts the SQL endpoint independently of the web server. See How to encrypt the SQL endpoint.

Monitoring

The Atoti BI adapter relies on BI tools sending SQL queries to Atoti Server: a component called the SQL Bridge translates each query and forwards it to the cube. That SQL layer is an implementation detail, but it shows up in logs and traces.

Health

The SQL Bridge contributes a sqlBridge component to Spring Boot Actuator’s health endpoint. It appears under components.sqlBridge in the /actuator/health response when details are shown. The application must include Spring Boot Actuator (spring-boot-starter-actuator) and expose the health endpoint. Without Actuator, nothing changes for the application. The component reports one of three statuses:

UP: the SQL endpoint’s listener is bound. Details: port, the port clients must target. This matters when atoti.server.sql-bridge.port is 0 and the operating system picked the port. Also ssl, true when the endpoint is encrypted.
DOWN: the listener could not bind its port, for example because another process already holds it. Details: error, the failure. Also configuredPort and ssl.
UNKNOWN: the listener has not yet bound its port. This lasts a few milliseconds after application startup. Details: configuredPort and reason. This status does not degrade the application’s aggregated health.

Details such as port and error are shown only when management.endpoint.health.show-details is always or when-authorized. The component can be turned off with management.health.sqlbridge.enabled=false.

Logging

Two loggers are available:

atoti.server.query.sqlbridge: logs the query processing. This includes the logical and physical query plans built from the incoming SQL. It also includes the cube, levels, and measures targeted by each underlying query. This logger also reports, at ERROR, a port the listener could not bind.
atoti.server.query.sqlbridge.protocol: logs the protocol exchange, including received and sent messages, session lifecycle, and authentication. It also logs any error encountered while handling a query.

Tracing

Tracing is used to provide insight into the SQL queries received from BI tools. Atoti Server tracing must be enabled and configured before SQL query traces become available. See Tracing for setup instructions. Every query received is wrapped in a Sql Bridge query span, tagged with:

sql-query: the raw SQL query text sent by the BI tool.
sql-query-params: the parameters bound to the query, when it is a parameterized (prepared) query.

Releases and upgrades

Getting started

Concepts

Step 1: Bring your data into Atoti

Step 2: Design your Cube

Step 3: Build an Atoti application

Step 4: Query an Atoti application

Configure Atoti

Secure Atoti

Operate Atoti

Reference

Which SQL is supported?

How to enable the SQL endpoint

How to choose the listening port

How to secure the file generation endpoints

How to encrypt the SQL endpoint

Configuration reference

Monitoring

Health

Logging

Tracing

​Which SQL is supported?

​How to enable the SQL endpoint

​How to choose the listening port

​How to secure the file generation endpoints

​How to encrypt the SQL endpoint

​Configuration reference

​Monitoring

​Health

​Logging

​Tracing

Which SQL is supported?

How to enable the SQL endpoint

How to choose the listening port

How to secure the file generation endpoints

How to encrypt the SQL endpoint

Configuration reference

Monitoring

Health

Logging

Tracing