Konsol Architecture

Overview

Konsol is an open-source Enterprise Performance Management stack for D365 Finance. It provides Excel-native budgeting, consolidation, and reporting at a fraction of the cost of commercial EPM tools.

Data Flow (Target Architecture)

┌──────────────────────────────────────────────────────────┐
│  Users                                                    │
│  ┌──────────┐  ┌──────────────┐  ┌────────────────────┐  │
│  │ Excel    │  │ Excel Online │  │ Frappe Desk        │  │
│  │ Desktop  │  │ / iPad       │  │ (Web UI)           │  │
│  │ (ODBC)   │  │ (Add-in)     │  │                    │  │
│  └────┬─────┘  └──────┬───────┘  └───────┬────────────┘  │
└───────┼───────────────┼──────────────────┼────────────────┘
        │               │                  │
        │       HTTPS + Frappe session     │
        ▼               ▼                  ▼
┌──────────────────────────────────────────────────────────┐
│  Frappe (Application Layer)                               │
│                                                           │
│  DocTypes: Budget Entry, Scenario, Consolidation Group,   │
│            IC Elimination Rule, Allocation Rule           │
│                                                           │
│  Built-in: Auth, RBAC, Workflows, Audit, REST API         │
│  DB: MariaDB (metadata + config only)                     │
└──────────────────┬───────────────────────────────────────┘
                   │ clickhouse-connect
                   ▼
┌──────────────────────────────────────────────────────────┐
│  ClickHouse (Azure / AWS — managed or self-hosted)        │
│                                                           │
│  epm_bronze  ← Airbyte (D365 OData)                      │
│  epm_silver  ← dbt (standardized)                        │
│  epm_gold    ← dbt (consolidated TB, IC elim, CTA)       │
│  epm_staging ← Frappe writes budget/config here           │
│                                                           │
│  Cube SQL API (port 15432) → Excel ODBC / Add-in         │
└──────────────────────────────────────────────────────────┘

Current Data Flow (v1 — FastAPI)

D365 F&O (OData) → Airbyte → ClickHouse → dbt → Cube → Excel
                                                    ↑
                                              FastAPI (write-back)

Medallion Architecture

Layer	Database	Purpose
Bronze	epm_bronze	Raw data from Airbyte, explicit type casting
Silver	epm_silver	Standardized, cleaned, D365 quirks handled
Gold	epm_gold	Business-ready: TB, P&L, BS, consolidation
Staging	epm_staging	Write-back from API (budget input)

Component Stack

Component	Technology	Purpose
Extraction	Airbyte (abctl)	D365 OData → ClickHouse
Warehouse	ClickHouse	Columnar analytics, fast aggregation
Transformation	dbt Core	Medallion layer models + tests
Semantic Layer	Cube Core	SQL API (Postgres wire) for Excel
Orchestration	Dagster	Airbyte + dbt asset graph, schedules
Write-back	FastAPI (v1) → Frappe (v2)	Budget/forecast input from Excel
Admin UI	Streamlit (v1) → Frappe Desk (v2)	Pipeline monitoring, rule editing, config management
User UI	Excel (Desktop + Online)	PivotTables via ODBC; =EPM() functions via Add-in
Auth & RBAC	— (v1) → Frappe (v2)	Users, roles (session login; SSO roadmap), 2FA, audit trail
Workflow	— (v1) → Frappe (v2)	Budget approval: Draft → Submitted → Approved

Key Design Decisions

ADR-001: ClickHouse over PostgreSQL

ClickHouse provides 10-100x faster aggregation queries for TB/consolidation workloads. MergeTree family engines (SummingMergeTree for TB) match EPM access patterns perfectly.

ADR-002: Airbyte via abctl, not Docker Compose

Airbyte is resource-heavy and has its own orchestration. Running it via abctl (local K8s) keeps Docker Compose lean. Dagster talks to Airbyte API on localhost:8000.

ADR-003: Cube SQL API for Excel

Excel connects via PostgreSQL ODBC driver to Cube's SQL API (port 15432). This avoids REST API complexity and gives Excel users native PivotTable support. Cube handles caching and query optimization.

ADR-004: Write-back via Staging (D365 stays read-only)

Budget/forecast input goes to ClickHouse staging tables, not back to D365. dbt unions staging data with actuals in the next run. This keeps D365 as the read-only source of truth.

ADR-005: Seed-driven Allocations (v1) → Frappe DocTypes (v2)

v1: Allocation rules and drivers are CSV seeds editable in Streamlit. v2: Rules move to Frappe DocTypes — web-editable, versioned, audited, role-protected. Frappe syncs config to ClickHouse on save, triggering a dbt rebuild.

ADR-006: Frappe over FastAPI for Application Layer

FastAPI is minimal and fast but requires building auth, RBAC, audit, workflows, and web UI from scratch. Frappe provides all of these out of the box. Trade-off: heavier deployment (MariaDB + Redis + workers), but docker compose handles it. Frappe's DB (MariaDB) stores only metadata and config — all analytical data stays in ClickHouse.

ADR-007: Excel Custom Functions Add-in for Online/Desktop/iPad

=EPM(entity, year, period, account) replaces the Hyperion HsGetValue() pattern. Built as an Office Add-in (TypeScript + Office.js), it authenticates via Frappe login (session cookie sent with credentials: "include") and calls Frappe API endpoints. Works in Excel Online, Desktop, and iPad — unlike SmartView which is desktop-only.

The add-in registers six custom functions: EPM, EPM_BUDGET, EPM_VARIANCE, EPM_DEBIT, EPM_CREDIT (read) and EPMSAVE (budget write-back).

Security

See docs/security-architecture.md for the full security design.

Summary:

• Auth (shipped): Frappe username/password login via POST /api/method/login; the session cookie is reused on every API call. RBAC enforced on every DocType and API endpoint.
• Roles: Reader (view) → Planner (view + submit budgets) → Controller (+ edit config) → Admin (+ user management)
• Transport: HTTPS via Caddy reverse proxy with auto-TLS; CORS for *.officeapps.live.com
• Audit: Frappe built-in — every change logged with user, timestamp, field-level diff
• ClickHouse: Never exposed to internet — private network only, accessed via Frappe or Cube
• D365 OData: Azure AD app registration (client credentials flow)
• Identity (roadmap): Microsoft Entra ID SSO (same tenant as D365) is a target, not yet shipped — current auth is Frappe session login.

Excel Online: Custom Functions

The Excel Add-in provides HSGETVALUE-equivalent cell formulas:

Formula	Purpose
=EPM(entity, year, period, account, [measure], [scenario], [cost_center], [dept])	Single cell value (actuals by default)
=EPM_BUDGET(entity, year, period, account, [cost_center], [dept])	Budget amount
=EPM_VARIANCE(entity, year, period, account)	Actual vs budget variance
=EPM_DEBIT(entity, year, period, account)	Period debit
=EPM_CREDIT(entity, year, period, account)	Period credit
=EPMSAVE(amount, entity, year, period, account, scenario_id, layer)	Write budget data back (planner role only)

See docs/security-architecture.md for full add-in design, auth flow, and comparison to Hyperion SmartView.