# Connecting a BI tool over SQL (https://docs.akollo.com/en/connections/bi-sql-access)



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.

<Mermaid
  title="From BI access to a working connection"
  chart="`flowchart LR
A[Reporting admin adds a BI access] --> B[Different admin approves]
B --> C[Set up login]
C --> D[Waiting to be applied]
D --> E[Active]
E --> F[BI tool connects]`"
/>

## 1. Set up the access in reporting [#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 [#2-set-up-the-login]

<Steps>
  <Step>
    ### Open Analytics SQL access [#open-analytics-sql-access]

    Open **Integrations → Analytics SQL access**. Every approved SQL access is a card there.
  </Step>

  <Step>
    ### Set up the login [#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.
  </Step>

  <Step>
    ### Wait until it is active [#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.
  </Step>
</Steps>

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

<Callout type="info" title="Note">
  Organisation owners and administrators see this page. It is closed to everyone else.
</Callout>

## 3. Connection details [#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 [#4-power-bi-directquery]

<Steps>
  <Step>
    ### Choose the data source [#choose-the-data-source]

    In Power BI Desktop choose **Get data → PostgreSQL database**.
  </Step>

  <Step>
    ### Enter server and database [#enter-server-and-database]

    **Server**: the host and port from the page (`host:port`). **Database**: the database name shown on the page.
  </Step>

  <Step>
    ### Choose DirectQuery [#choose-directquery]

    Set **Data connectivity mode** to **DirectQuery**. Power BI then asks the database on every visual instead of importing a copy.
  </Step>

  <Step>
    ### Sign in [#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.
  </Step>

  <Step>
    ### Pick the views [#pick-the-views]

    In the navigator open the `bi` schema and pick the `<dataset>_v<version>` views you need. `bi.catalog` lists them.
  </Step>
</Steps>

<Callout type="idea" title="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.
</Callout>

## 5. Tableau [#5-tableau]

<Steps>
  <Step>
    ### Choose PostgreSQL [#choose-postgresql]

    Choose **Connect → To a Server → PostgreSQL**.
  </Step>

  <Step>
    ### Enter the connection details [#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.
  </Step>

  <Step>
    ### Choose Live or Extract [#choose-live-or-extract]

    Choose a **Live** connection to read the database on every view, or **Extract** for a scheduled copy.
  </Step>

  <Step>
    ### Add the views [#add-the-views]

    Under **Schema** pick `bi` and drag the `<dataset>_v<version>` views onto the canvas.
  </Step>
</Steps>

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 [#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 [#faq]

<Accordions type="single">
  <Accordion title="I lost the password. What do I do?">
    Press **Rotate password** on the card. The new password is shown once; the old one stops working at the next apply.
  </Accordion>

  <Accordion title="Why can my tool not connect right after setup?">
    The card first says **Waiting to be applied**. It turns **Active** within a few minutes, and the tool can connect from then on.
  </Accordion>

  <Accordion title="Can the BI tool change data?">
    No. The login sees only the views opened to it and cannot change anything.
  </Accordion>

  <Accordion title="Why was my query stopped?">
    A query may run for a few minutes at most. Narrow its filter or summarise the visual, for example by month or by team.
  </Accordion>

  <Accordion title="What happens when a dataset gets a new version?">
    A new view such as `_v2` appears. Point the report at it; the old view keeps working until reporting retires it.
  </Accordion>
</Accordions>

## Related pages [#related-pages]

* [BI datasets](/en/connections/bi-datasets)
* [Reports](/en/product-guide/reports)