Connect a PostgreSQL or MySQL database

Connect the database your business already runs on, and its tables appear in Sterodata beside your own. Summaries, views, pages and charts read them like any other table. Nothing is copied: Sterodata keeps the tables' definitions and the connection's password, encrypted in a secret store, and reads the database each time a table, a summary or a page opens.

Before you start: a user that can only read

Ask whoever runs the database for a user that can read the tables you need, and nothing else. Sterodata checks: Test Connection refuses a user that can write, with Write privileges must be removed and the privileges it found, and refuses a PostgreSQL superuser. If the database has a read replica, connect to that, so your reports never compete with the database's daily work.

Connect it

1. At the top of the workspace, beside the database names, select the link icon (Connect External DB). The Connect External Database dialog opens, in four steps: Connection, Tables, Mapping and Confirm.
2. Choose the Database Type: PostgreSQL or MySQL. The Port fills in (5432 or 3306).
3. Enter the Host, the Database Name, the Username and the Password, then choose Test Connection. When it reads Connection successful. Permissions validated., choose Next.
4. Tick the tables to read. Search tables... and Select All help in a large database. A table marked No PK has no primary key and cannot be picked.
5. On Mapping, open each table and give it the name your team uses in Display Name in Sterodata. You also see each column and the field type it becomes. A column that points at another table becomes a link to that table's records; pick both tables to follow it.
6. On Confirm, check Database Name in Sterodata (it starts as the database's own name) and choose Import.

The database appears as a new tab at the top, with a cloud icon, holding the tables you picked with every row the database holds.

Make the fields read as your team reads them

A database names its columns for programs. Open a field's header and choose Edit field to rename it, or to show it differently: a number as a currency or a percentage, text as an email or a URL, a date without its time. These changes are Sterodata's only; the database keeps its names and its data. You can also add formula, lookup and rollup fields. You cannot add or delete the database's own columns, and a connected table cannot hold amounts in several currencies. See Fields.

Turn on sorting, filtering and grouping

Every sort, filter and grouping on a connected table runs as a query on your database. So each field has Enable Sort, Filter and Grouping in Edit field. It starts on for the columns your database has an index for, and off for the rest. While it is off, nobody can sort, filter or group by that field, and a summary cannot group or pivot by it. Turn it on for each field you report by. Turning it on for a column without an index shows a warning: on a large table, those queries may be slow.

Good to know

• Read-only. Sterodata refuses to add, change or delete rows in a connected table, whoever asks.
• Live. A change made in the database shows the next time a table or page opens. If the database is out of reach, nothing can be read: there is no copy to fall back on.
• Removing it. Right-click the database's tab and choose Delete Database. Sterodata forgets the tables and the password; your database is not affected.

Only base tables are listed, not views, and in PostgreSQL only those in the public schema. The tables are picked when you connect: there is no way to add one later or to change the host or password of a connection. Connect the database again, under another name in Sterodata, or delete it and connect again.

For a worked example, from connecting a sales database to a dashboard for sales managers, see How to build a sales dashboard on the database you already have.