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 have the datawarehouses_admin role assigned in the Identity Broker.
• 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 Datasources panel.

2. Click the + icon and select PostgreSql.

   

   The Add Data Warehouse panel opens.

3. Enter a unique Name for the data warehouse.

4. Enter the External connection ID for the data warehouse. You can optionally enable the Override generated value? toggle to default this value to the name of the data warehouse.

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

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

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

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

9. 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 About Data Warehouse Security.

10. Add a connection to the data warehouse. You must have at least one defined to finish adding the data warehouse.

    1. In the Connections section, click Connection Required. The Add Connection panel opens.

    2. Complete the following fields:

       • Name: The name of the connection.
       • Host: The location of the Databricks server.
       • Port: The port number to route transmitted data.
       • Username: The username AtScale uses when connecting to the data warehouse.
       • Authentication: Select Password.
       • Password: The password AtScale uses when connecting to the data warehouse.
       • Extra JDBC flags: Optional. Any JDBC flags that are needed for the connection.

    3. (Optional) Click Test Connection to test the connection.

    4. Click Add

       The connection is added to the data warehouse. You can add more connections as needed.

11. (Optional) If your data warehouse has multiple connections, you can configure their query role mappings. This determines which types of queries are permitted per connection. A query role can be assigned to only one connection at a time.

    In the Query Mapping section, select the connection you want to assign to each type of query:

    • Small Interactive Queries: Small user queries. These are usually BI queries that can be optimized by AtScale aggregates, or ones that involve low-cardinality dimensions.
    • Large Interactive Queries: Large user queries. These are usually BI queries involving full scans of the raw fact table, or ones that involve high-cardinality dimensions.
    • Aggregate Build Queries: Queries made when building aggregate tables. This mapping should be assigned to a connection that has more compute power, and that is not subject to resource limitations that would prevent it from building aggregate tables.
    • System Queries: AtScale system processing, such as running aggregate maintenance jobs and querying system statistics.

12. Click Save Data Warehouse.