Skip to main content

Data Warehouse

Carium optionally exposes a data warehouse that can be used to query the data in the system. The data warehouse is a read-only OLAP database suitable for running complex queries, reports, and ad-hoc exploration. Access is provided through a PostgreSQL-compatible SQL interface and is consumable by most business-intelligence visualization products including Tableau, Looker, PowerBI, and AWS QuickSight.

Please contact your Carium representative to request access to the data warehouse.

Connecting to the Data Warehouse​

Once you have received your credentials, you can connect to the data warehouse using any PostgreSQL-compatible client, such as psql or pgAdmin.

You must use SSL to connect to the data warehouse.

psql -h <hostname> -p <port> -U <username> -d <database>
Password for user username:
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1), server 8.0.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

carium_dbname=> \dt
                           List of relations
 schema |                     name                     | type  | owner
--------+----------------------------------------------+-------+--------
 public | caredb_action_action                         | table | carium
 public | caredb_action_actioncategory                 | table | carium
 public | caredb_action_actionreminder                 | table | carium
 public | caredb_action_category                       | table | carium
 public | caredb_action_dailyachievement               | table | carium
 public | caredb_action_timebucketmute                 | table | carium
 public | caredb_action_weeklyachievement              | table | carium
 public | caredb_aggregation_participant               | table | carium
 public | caredb_article_articleactivity               | table | carium
 public | caredb_article_articlecontent                | table | carium
 public | caredb_article_articleinfo                   | table | carium
 public | caredb_article_individualarticlecontent      | table | carium
...
Note on ERROR: SSL required

If the wrong username, password, or database name is supplied in the connection parameters then you may see two error lines:

psql: error: connection to server at "<hostname>" (1.2.3.4), port 5439 failed: ERROR:  no such database: <database>
connection to server at "<hostname>" (1.2.3.4), port 5439 failed: ERROR:  SSL required

In this case the second error, SSL required, can be ignored. This is caused by a quirk in how the PostgreSQL authentication protocol works. The error that's real and needs to be addressed is no such database: <database>.

Querying Data​

Issue standard SQL queries to the data warehouse to retrieve the data you need.

For example, to retrieve the first random user that's in the organization:

carium_dbname=> \x
Expanded display is on.
carium_dbname=> select * from identity_core_user limit 1;
-[ RECORD 1 ]---------------+--------------------------------------
created_time                | 2023-04-24 16:06:52.062843
last_modified_time          | 2023-10-13 16:13:38.469729
id                          | 003747ef797c4288af43f8e461eae1bf
email                       | example@example.com
service_account             | f
org_user_account            | f
first_name                  | My First
last_name                   | Name
locale                      | en_US
locale_explicit             | t
phone_number                |
email_verified              | t
state                       | disabled
primary_individual_id       | 06728d5ee2ba11ed9bbe5aa2d25c9105
service_account_provenance  |
tz                          | America/Los_Angeles
tz_explicit                 | t
custom_quota                |
registration_completed_time | 2023-04-24 16:06:52.802778
is_not_archived             | t
last_login_time             | 2023-04-24 18:49:55.048022
classification              | customer
enrollment_data             | {}

Details for each of the tables and columns are available in the next section.