Connections
Overview
The Connections section covers the two kinds of links between the system and external systems:
- Database connections — stored access parameters for the external databases that hold the source data of the analytics. They are used by fact tables as the physical foundation of data, and by data marts to execute generated SQL queries and to publish materialized views. Database connections are managed on the Connections page, opened from the main menu
- Git connections — stored access parameters for the Git repositories used when project versions are transferred. They are managed in Settings → Integrations, on the Git connections tab
Supported database types
| Database type | Schema | SSL |
|---|---|---|
| PostgreSQL | Set explicitly, public by default |
Not configurable |
| Microsoft SQL Server | Not entered in the form — the dbo schema is used |
Not configurable |
| ClickHouse | Not entered in the form — the database name is used instead | CA and client certificates on the SSL tab |
Regardless of the type, a connection is defined by the same set of parameters: connection name, database type, host, port, database name, schema (PostgreSQL only), user name, and password. Credentials and certificates are stored encrypted.
What connections are used for
- Storing access parameters. The credentials, network addresses, and SSL certificates of external databases are kept in one place in encrypted form
- Caching the database schema. The list of tables and columns with their data types is read once and stored, which lets the interface work without constant requests to the source
- Executing analytical queries. Data marts use connections to run generated SQL queries and to create views directly in the target databases
Project version context
Database connections belong to the level of the project version: the connections list always shows the connections of the currently open version. When a new version is created, the connections of the parent version are copied into it together with their SSL certificates.
Git connections, in contrast, are not versioned and are not tied to a single project: they belong to the company and are available in all of its projects.
Offline operation
After a connection has been configured, the structure of the database — the metadata of tables, columns, and their types — is stored in the system. All subsequent work in the project (designing fact tables, data marts, and measures) is done on that stored snapshot, without a permanent live connection to the database. The external database is contacted only when a connection is checked, when its schema is refreshed, when a data mart query is executed with a result, and when materialized data mart objects are created, updated, or removed.
Because of this, changes made in the source database appear in the interface only after the connection's schema has been refreshed.
Access permissions
- Every user who has access to the project can see the connections list
- Verifying connections and refreshing the cached schema are additionally available to Analysts
- Creating, editing, deleting, activating, and disabling connections require the role of Developer, Project manager, Administrator, or Super administrator
- Git connections are managed only by users with the Administrator or Super administrator role; users with other roles can select an existing Git connection in the version transfer dialogs
All operations on connections are recorded in the System Journal.