Offline BI in asapBI: Docker and ClickHouse

Offline BI Development in asapBI: Local Stack and Visual Views

asapBI offers full offline support for BI development. The system uses a local Docker stack with PostgreSQL and ClickHouse, visual SQL Views stored as JSON files, and Git versioning. This enables building queries, experimenting with data, and documenting logic—all without relying on remote servers, eliminating downtime caused by unstable internet connections.

The local environment ensures data isolation, secure experimentation, and version control. Developers have complete control over container volumes, CPU, and memory allocation.

Advantages of the Local Stack

Local development solves key challenges in team collaboration:

• Data Isolation: Docker volumes are protected from external changes. No risk of losing test tables due to migrations or cleanup operations.
• Safe Experiments: Heavy JOINs or complex Views impact only the local container, not the shared cluster.
• Git Versioning: Visual Views are saved as JSON files, enabling diffs, code reviews, and rollbacks.
• Digital Twin of the Database: Schema changes, data types, and relationships can be tested without affecting production.

Setting Up the Offline Environment

Setup involves three steps.

1. Clone the Project from Git

Download the project into a local folder. The project structure follows business domains—finance, sales, etc. View files and dbcopy-json files are stored in the repository.

2. Launch the BI Stack via Docker Compose

Use docker-compose.yml to run PostgreSQL and ClickHouse. Example configuration:

services:
  asapbi-postgres:
    image: postgres:15-alpine
    container_name: asapbi-postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: admin
      POSTGRES_DB: demo
      PGDATA: /var/lib/postgresql/data/pgdata
    ports:
      - "15432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d demo"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 10s
    networks:
      - asapbi-net

  asapbi-clickhouse:
    image: clickhouse/clickhouse-server:24.8-alpine
    container_name: asapbi-clickhouse
    restart: unless-stopped
    environment:
      CLICKHOUSE_USER: admin
      CLICKHOUSE_PASSWORD: admin
      CLICKHOUSE_DB: analytics
    ports:
      - "18123:8123"   # HTTP
      - "19000:9000"   # Native
    volumes:
      - clickhouse-data:/var/lib/clickhouse
      - clickhouse-logs:/var/log/clickhouse-server
    healthcheck:
      test: ["CMD-SHELL", "wget --no-verbose --tries=1 --spider http://localhost:8123/ping || exit 1"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s
    networks:
      - asapbi-net

volumes:
  postgres-data:
    driver: local
  clickhouse-data:
    driver: local
  clickhouse-logs:
    driver: local

networks:
  asapbi-net:
    driver: bridge

Launch via the asapBI interface or CLI. Docker will automatically pull the required images.

3. Populate the Database with Test Data

Use *.dbcopy-json files to copy test datasets. Example:

{
  "description": "Copying clients with preparation and cleanup",
  "beforeSQL": [
    "BEGIN;",
    "SET session_replication_role = replica;",
    "CREATE TEMP TABLE IF NOT EXISTS _copy_log (ts timestamptz, msg text);"
  ],
  "afterSQL": [
    "SET session_replication_role = DEFAULT;",
    "INSERT INTO _copy_log VALUES (now(), 'Copy completed');",
    "COMMIT;"
  ],
  "tables": [
    {
      "schema": "reference",
      "table": "clients",
      "where": "is_active = true AND created_at >= '2024-01-01'",
      "beforeSQL": [
        "DROP INDEX IF EXISTS idx_clients_region_id",
        "DELETE FROM reference.clients WHERE created_at >= '2024-01-01'"
      ],
      "afterSQL": [
        "CREATE INDEX IF NOT EXISTS idx_clients_region_id ON reference.clients (region_id)",
        "ANALYZE reference.clients"
      ]
    },
    {
      "schema": "sales",
      "table": "orders",
      "where": "order_date >= '2024-01-01'",
      "beforeSQL": "ALTER TABLE sales.orders DISABLE TRIGGER ALL",
      "afterSQL": "ALTER TABLE sales.orders ENABLE TRIGGER ALL"
    },
    {
      "schema": "analytics",
      "table": "user_sessions",
      "where": "",
      "beforeSQL": null,
      "afterSQL": null
    }
  ]
}

Select source and target databases in the interface. Use only test data from dev servers.

Visual SQL Views

Connect to the local ClickHouse instance and create visual Views. These are JSON files with version control and support for code review. Views are decoupled from dashboards, ensuring a single source of truth for calculations.

Benefits:

• Centralized storage of calculation logic.
• Reusable across multiple dashboards.
• Change tracking via Git diff.

Documentation Integration

Attach documentation files (MD, draw.io) directly in the same folder as the View. Documentation updates automatically when the model changes. Text files are ideal for AI processing and automated doc generation.

Key Takeaways

• A local Docker stack with persistent volumes ensures data isolation and reliability.
• Visual Views as JSON files enable Git versioning and peer review for analytical work.
• *.dbcopy-json automates safe, repeatable copying of test data.
• Embedding documentation (MD, draw.io) in the project folder keeps it up-to-date.
• Full sandbox environment for experimentation without risking production stability.

— Editorial Team