Connecting a BI tool over SQL
A business intelligence tool such as Power BI, Tableau or Excel can also read the datasets reporting approved straight from the database. The tool connects with its own login, sees only the views opened to it and cannot change anything. This path is meant for Power BI DirectQuery and live Tableau connections.
1. Set up the access in reporting
A reporting administrator adds a BI access that "reads over SQL" on Settings → Reports → Datasets and picks the datasets and units it may see. A different administrator approves it. An approved access gets a user name that starts with akollo_bi_.
2. Set up the login
Open Analytics SQL access
Open Integrations → Analytics SQL access. Every approved SQL access is a card there.
Set up the login
Press Set up login on the card. The password is shown once, right below. Copy it into your BI tool; it is not shown again.
Wait until it is active
The card first says Waiting to be applied and turns Active within a few minutes. From then on the tool can connect.
| Action | Effect |
|---|---|
| Rotate password | Use it if you lose the password or want to change it regularly. The old one stops working at the next apply. |
| Close | Turns the login off and ends its open connections. |
| Access revoked in reporting | The login closes on its own. |
Note
Organisation owners and administrators see this page. It is closed to everyone else.
3. Connection details
The Connection details panel next to the cards shows the host and port.
| Field | Value |
|---|---|
| Host and port | The address shown on the page |
| Database | The database name shown on the page |
| User | The akollo_bi_… name on the card |
| Password | The password shown once at setup |
| SSL | Required, sslmode=verify-full; give the tool your organisation's root certificate |
The views are in the bi schema. bi.catalog lists the datasets open to the access, and each dataset reads as bi.<dataset>_v<version> (for example bi.time_team_daily_v1). A query may run for a few minutes at most, and only a few connections can be open at once. If a long query is cut off, narrow its filter.
4. Power BI DirectQuery
Choose the data source
In Power BI Desktop choose Get data → PostgreSQL database.
Enter server and database
Server: the host and port from the page (host:port). Database: the database name shown on the page.
Choose DirectQuery
Set Data connectivity mode to DirectQuery. Power BI then asks the database on every visual instead of importing a copy.
Sign in
When asked for credentials choose Database and enter the akollo_bi_… user and the password. Encryption must stay on. Install your organisation's root certificate on the machine, and on the on-premises data gateway if the report is published to the Power BI service.
Pick the views
In the navigator open the bi schema and pick the <dataset>_v<version> views you need. bi.catalog lists them.
Tip
Keep visuals summarised, for example by month or by team. Every visual is a query, and a query that runs longer than the limit is stopped.
5. Tableau
Choose PostgreSQL
Choose Connect → To a Server → PostgreSQL.
Enter the connection details
Enter Server and Port from the page and the database name shown on the page as Database. Set Authentication to user name and password and turn Require SSL on.
Choose Live or Extract
Choose a Live connection to read the database on every view, or Extract for a scheduled copy.
Add the views
Under Schema pick bi and drag the <dataset>_v<version> views onto the canvas.
When a dataset gets a new version (_v2), point the report at the new view. The old one keeps working until reporting retires it.
6. For system administrators
- Only the networks the installation allows, only over TLS and only BI logins are admitted. Every other BI connection is rejected.
- Opening the database port to the BI networks is your network team's task.
FAQ
Press Rotate password on the card. The new password is shown once; the old one stops working at the next apply.
The card first says Waiting to be applied. It turns Active within a few minutes, and the tool can connect from then on.
No. The login sees only the views opened to it and cannot change anything.
A query may run for a few minutes at most. Narrow its filter or summarise the visual, for example by month or by team.
A new view such as _v2 appears. Point the report at it; the old view keeps working until reporting retires it.