Cube.dev Semantic Layer

Cube.dev provides a pure semantic layer on top of the dbt-transformed data, enabling consistent metrics, caching, and multi-protocol access for analytics and AI agents.

Enterprise Feature

Cube.dev semantic layer is available in Enterprise editions.

---

Overview

Cube sits between the Gold layer and consuming applications, providing:

• Consistent Metrics: Single source of truth for measures
• Caching: Pre-aggregations for fast queries
• Multi-Protocol Access: REST API, GraphQL, SQL API

---

Architecture

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│  PostgreSQL     │────▶│   Cube.dev     │────▶│  Consumers       │
│  (Gold Layer)   │     │   Semantic     │     │  • REST API      │
│                 │     │   Layer        │     │  • SQL API       │
│  dim_*, fact_*  │     │                │     │  • MCP Server    │
│  sem_*          │     │  Cubes/Views   │     │  • BI Tools      │
└─────────────────┘     └─────────────────┘     └─────────────────┘

---

Available Cubes

Dimension Cubes

Cube	Source	Description
Studies	dim_study	Clinical study master data
Subjects	dim_subject	Study subject demographics
Sites	dim_site	Trial site information
Patients	dim_patient	Patient records
Dates	dim_date	Date dimension

Fact Cubes

Cube	Source	Description
Enrollments	fact_enrollment	Enrollment events
AdverseEvents	fact_adverse_event	Adverse event records
Visits	fact_visit	Study visit data
VitalSigns	fact_vital_sign	Vital sign measurements
LabTests	fact_lab_result	Laboratory test results
Medications	fact_medication	Concomitant medications

CDISC Cubes

Cube	Source	Description
CdiscDm	cdisc_dm	Demographics domain
CdiscAe	cdisc_ae	Adverse Events domain

---

Measures & Dimensions

Key Measures

Cube	Measure	Description
Studies	count	Total studies
Subjects	count	Total subjects
Enrollments	count, enrollmentRate	Enrollment metrics
AdverseEvents	count, seriousCount	Safety metrics
Visits	count, completionRate	Visit metrics

Key Dimensions

Cube	Dimension	Type
Studies	studyName, phase, status	String
Subjects	sex, ageGroup, enrollmentStatus	String
AdverseEvents	severity, causality, isSerious	String/Boolean

---

Sample Queries

Natural Language → Cube Query

Natural Language	Cube Query
"How many studies do we have?"	Studies.count
"Total enrolled subjects"	Subjects.count with filter isEnrolled = true
"Adverse events by severity"	AdverseEvents.count by AdverseEvents.severity
"Enrollment by study and month"	Enrollments.count by Studies.studyName, Dates.month
"Sites with most subjects"	Subjects.count by Sites.siteName ordered DESC

REST API Example

curl -X POST http://localhost:4000/cubejs-api/v1/load \
  -H "Content-Type: application/json" \
  -d '{
    "query": {
      "measures": ["Enrollments.count"],
      "dimensions": ["Studies.studyName"],
      "limit": 10
    }
  }'

SQL API Example

-- Connect via psql to port 15432
SELECT 
  study_name,
  COUNT(*) as enrollments
FROM Enrollments
GROUP BY study_name
ORDER BY enrollments DESC;

---

Deployment

The Cube.js Docker image is self-contained — it bakes in both the cube.js config and the model/ directory at build time so no cross-repo volume mounts are needed at runtime.

Aspect	Detail
Source repo	ctms-data-pipeline-ai-analytics/ctms-cube/
Docker image	zynomi/ctms-cube (Docker Hub)
Base image	cubejs/cube:v0.35.81
CI workflow	docker-build-ctms-cube.yml — builds on push to ctms-cube/**
Profile	analytics
Port	4000 (host and container)

The image extends the official Cube image and copies in:

• cube.js — connection config (reads CUBEJS_* env vars at runtime)
• model/ — all .yml cube definitions (Studies, Subjects, Enrollments, etc.)

Cross-Repo Dependency

Cube models live in ctms-data-pipeline-ai-analytics, not in ctms.devops. When models change, push to that repo and the CI workflow rebuilds the image automatically.

---

API Endpoints

Endpoint	Port	Description
Playground	4000	Visual query builder
REST API	4000/cubejs-api/v1	JSON API
SQL API	15432	PostgreSQL-compatible
Health	4000/readyz	Health check