AlayaCare External Integrations

Home

AlayaCare Data Warehouse and IPSec Guide

AlayaCare’s data warehouse setup, content, and configuration.

Contents

Overview

The purpose of this guide is to provide a summary of AlayaCare’s data warehouse setup, content, and configuration. This guide also provides a summary of the steps needed to establish a secure connection (via IPSec tunnel) to AlayaCare’s data warehouse for use by external systems or integrations.

Data Warehouse

Our warehouse is restored every morning at 8 AM ET. The warehouse is created using a backup of the production source database (the database linked to the app) at midnight.

The warehouse does not contain the same tables as the source database. It is built as a snowflake schema composed of facts and dimensions. The warehouse is built with Type1 data, meaning you will get the last value of each entity (no historical values are stored apart some outlined exceptions). The warehouse does not contain all the information accessible through the app; instead, it is a subset of the key entities and measurements from AlayaCare’s core workflows.

Facts, tables, or cubes contain principally numeric measurements and have multiple joins to the “dimensions.” For example, the visit fact table contains one row per visit and is linked to multiple dimensions such as clients, services, and employees. The client dimension contains one row per client and is composed of demographics values. In short, a visit fact table can pull client demographic details because the client dimension is linked to the visit fact table.

The content of the warehouse is accessible through AlayaCare’s web app via the Data Exploration (DE) tool.

Supporting Warehouse Documentation

Upon request, AlayaCare can provide the create table and constraints statement for the warehouse (SQL format.)

IPSec and the Warehouse

AlayaCare uses a private database server running PostGres with TLS and basic authentication enforced for security purposes. We create a VPN connection between a client’s external infrastructure and ours. After that, we are able to grant clients access the PostGres DB containing the warehouse.

In order to be able to access the DB, an IPsec tunnel has to be configured. This is implemented by the AlayaCare DevOps team based on contractual requirements.

IPSec Tunnel Setup