Querying the Warehouse

👤 This documentation is intended for SQL Users on sites that have access to the Warehouse Infrastructure. The Sisense Managed Warehouse is only available on the Redshift Cache Infrastructure.

This documentation includes information on how to query the Warehouse and generate a Dependency Map for charts and views. Site administrators can contact their Customer Success Manager if interested in enabling Warehouse for their instance.

Fully Qualifying Data Sources:

In order to properly generate a dependency map, there are two requirements.

1. Make sure the query is pointing to the “Warehouse” as the data source:

2. When querying the Warehouse, fully qualify the table references.

Invalid query examples:

• select * from table_name → Invalid
• select * from schema_name.table_name → Invalid

Valid query example:

• select * from database_name.schema_name.table_name → Valid

Dependency Mapping and Schema Names:

If a table reaches the Warehouse via the Sisense Cache, a schema name is automatically assigned based on the database ID and the schema of the underlying table. Schema names are displayed and translated as the database display name in Sisense to make it more user-friendly. In Sisense, users can call the Sisense-friendly naming convention or the underlying schema name to generate the Dependency Mapping. However, the Sisense-friendly query will fail outside of Sisense.

Users can find the underlying schema name by querying a system table on the cluster:

-- --no_cache
select distinct table_catalog, table_schema, table_name
from information_schema.columns
order by 1,2,3

Dependency Mapping Syntax:

In the screenshot, the “demo_data” database was pushed into the Warehouse via the Sisense Cache options. As a result, it can be queried with the assigned schema or the user-friendly schema. Within Sisense, either of the queries below are valid:

1. select * from demo_data.public.ad_spend
2. select * from db_25760_public.ad_spend

Both (1) and (2) will successfully run and generate a dependency map in Sisense. However, (1) will fail outside of Sisense, as the automatic translation to (2) only happens there.

ETL Process:

As an alternative example, the "db_clinical_trial_asthma_drug" source was ETL’ed into the cache cluster from outside of Sisense. In the Schema Tab, it only has two levels (schema name and table name). The example for “demo_data” above has three levels (database name, schema name, and table name). It can be fully qualified in a query as:

• select * from db_clinical_trial_asthma_drug.effects