Skip to main content

Adding PostgreSQL Data Warehouses

A PostgreSQL data warehouse contains the tables and views that you want to import as fact datasets and dimension datasets into AtScale models. It also contains aggregate table instances that either you or the AtScale engine creates in a database that you specify.

Prerequisites​

  • You must be logged in as an admin user.
  • You must know the schema to use for building aggregate tables in the data warehouse. AtScale reads and writes aggregate data to this schema, so the AtScale service account user must have ALL privileges for it. BI tool user accounts should not have the select permission.

Add the Data Warehouse​

  1. In Design Center, open the main menu and select Settings. The Settings page opens.

  2. In the Settings panel, click Data Warehouses.

  3. In the Add Data Warehouse section, click the PostgreSQL icon. The Add PostgreSQL Data Warehouse panel opens.

  4. Enter a unique Name for the data warehouse. AtScale displays this name in the Design Center and uses it in log files.

  5. (Optional) Enter the name of the External connection ID. This field defaults to the Name value but can be overridden by enabling the Override generated value? option.

  6. Enter the name of the Database, which AtScale uses when creating aggregate tables. You must create this; AtScale does not create one automatically.

  7. Enter the name of the Aggregate Schema for AtScale to use when creating aggregate tables. You must create this schema; AtScale does not create one automatically.

  8. (Optional) Specify whether the data warehouse is a Read-only source.

    • Select this option if AtScale will be configured to access this database with credentials that do not have permission to create new tables, or if you need to prevent AtScale from placing any aggregate tables in this data warehouse.
    • If Read-only source is enabled upon editing an existing data warehouse with aggregates present, all existing aggregates stored in this data warehouse will be deactivated.

  9. In the Custom function installation mode field, select Customer managed if you set up PostgreSQL in customer managed mode; otherwise, select None.

  10. If you selected Customer managed above, enter the AtScale UDAF Schema.

  11. In the Access Controls section, add the users and groups that you want to be able to access the data warehouse and its data. For more information on how data warehouse security works in AtScale, see Data Warehouse Security.

  12. Click Apply.

Add a PostgreSQL Connection​

After setting up your data warehouse, you must add a connection to the data warehouse before you can run queries. You can establish multiple connections with different configurations to the same data warehouse. If multiple connections are configured, you can assign which types of queries are permitted per connection; for details, see Configuring Query Mapping.

To add a PostgreSQL connection:

  1. On the Data Warehouses page, click the expand icon for your data warehouse.

  2. In the Add Connection field, click the plus icon. The Add PostgreSQL Connection panel opens.

  3. Complete the following fields:

    • Name: The name of the connection.
    • Host: The location of the PostgreSQL server.
    • Port: The port number to route transmitted data.
    • Database: The name of the default database to use after connecting.

  4. In the Authorization section, enter the username AtScale uses when connecting to the data warehouse.

  5. In the Authentication Method field, select the authentication method to use when connecting to the data warehouse, then complete the credential fields as needed.

  6. (Optional) In the Extra jdbc flags field, enter the JDBC flags that are needed for the connection.

  7. Click Test connection to test the connection.

  8. Click Apply.