> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Endpoint

> How to enable and configure the SQL endpoint that lets Tableau and Power BI query Atoti cubes via the SQL Bridge, covering the `bi-adapter` license, the `atoti.server.sql-bridge.enabled` property, and the port (`atoti.server.sql-bridge.port`).

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](./tableau/tableau_adapter) connects Tableau to Atoti through the SQL
endpoint. See its [how-to guide](./tableau/tableau_adapter_how_to) 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

<Info>
  This feature requires a license that enables the `bi-adapter` component.
</Info>

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

```xml theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
<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.

<Warning>
  Binding the configured port happens in the background, and a failure to bind it is only logged. If
  another process already holds the port, the application still starts and reports itself healthy
  while no SQL client can reach it. In that state the `.tds` and `.pbit` endpoints answer `503 Service
    Unavailable`.
</Warning>

## 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](#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.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. |

## 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.

### 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.
* `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](../../../monitoring/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.