BI Adapter SQL Endpoint

The BI Adapter 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. A client authenticates with a username and password. See How are SQL clients authenticated?. SQL is how Tableau and Power BI talk to Atoti, not a feature on its own, but it shows in the setup: the server opens a PostgreSQL port, Tableau needs the PostgreSQL JDBC driver, and the Atoti logs show the SQL queries the tools send. The Atoti Tableau Adapter connects Tableau to Atoti through the SQL endpoint. See its how-to guide for the Tableau-specific connection steps. The Atoti Power BI Adapter connects Power BI to Atoti through the SQL endpoint. See its how-to guide for the Power BI-specific connection steps.

Which SQL is supported?

The BI Adapter SQL endpoint is not a general-purpose SQL endpoint. It speaks the PostgreSQL wire protocol, but the only supported clients are Tableau and Power BI, connected through the .tds or .pbit file that Atoti generates for the cube. Those files set how the tool queries Atoti. Any other connection is not supported: queries written by hand, other PostgreSQL clients, and Tableau or Power BI connected without the generated file.

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 are SQL clients authenticated?

Setting atoti.server.sql-bridge.anonymous to true serves every SQL client as the anonymous user, without asking for a password. Reserve this setting for a development environment. It defaults to false. Otherwise, the SQL endpoint always asks the client for a password. It checks the username and password with the application’s AuthenticationManager bean, Spring Security’s org.springframework.security.authentication.AuthenticationManager. A UserDetailsService bean is no longer used to authenticate a SQL client. An application that declares no AuthenticationManager bean has the SQL endpoint refuse every client with an Authentication failed error. Atoti Server logs one error at startup explaining why. The application still starts. Declare an AuthenticationManager bean able to authenticate a username and password. A ProviderManager over a DaoAuthenticationProvider, or an LDAP provider, is one way to do this. An application that only supports token-based login, such as JWT or OAuth2, needs this bean for SQL clients. Tableau and Power BI connect with a username and password.

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.anonymous`	`false`	Serves every SQL client as the anonymous user, without asking for a password. Reserve this for a development environment. See How are SQL clients authenticated?.
`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.pbit.decimal-precision`	`3`	Number of decimal places Power BI displays for the measures of the generated `.pbit` file. `0` shows integer values, up to a maximum of `18`. `-1` keeps the full precision, but makes Power BI use the `MEDIAN` aggregation function, which is not recommended for performance reasons. Regenerate the `.pbit` file after changing it. See How to configure the number of decimal places.
`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

BI Adapter SQL Endpoint

Which SQL is supported?

How to enable the SQL endpoint

How are SQL clients authenticated?

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 are SQL clients authenticated?

​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 are SQL clients authenticated?

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