API Overview

Konsolidat exposes a REST API through the Frappe/Konsol web framework. The API is consumed primarily by the Excel VBA module but can be called from any HTTP client.

Base URL

http://localhost:8069/api/method/konsol.api.{endpoint}

In production, replace with your Frappe server URL (e.g., https://epm.yourcompany.com).

Authentication

The API uses Frappe session-based authentication (cookies).

Login

curl -X POST http://localhost:8069/api/method/login \
  -H "Content-Type: application/json" \
  -d '{"usr": "admin@example.com", "pwd": "your_password"}'

The response sets Set-Cookie headers. Include these cookies in subsequent requests.

Session Check

curl http://localhost:8069/api/method/frappe.auth.get_logged_user \
  -b "cookies.txt"

Guest Access

Only the health endpoint allows guest access (allow_guest=True). All other endpoints require authentication.

Request Flow

sequenceDiagram
    participant Client as Excel / HTTP Client
    participant Frappe as Frappe (Konsol)
    participant Cube as Cube.js (Semantic Layer)
    participant CH as ClickHouse

    Client->>Frappe: POST /api/method/konsol.api.epm_batch
    Frappe->>Frappe: Resolve fact + validate measure/dimensions per item
    Frappe->>Cube: Query metrics & dimensions
    Cube->>CH: Optimized SQL query
    CH-->>Cube: Result set
    Cube-->>Frappe: Structured response
    Frappe-->>Client: JSON {values: [...], errors: [...]}

Endpoints

Data Retrieval

Method	Endpoint	Auth	Description
GET	health	Guest	Health check
GET	epm_value	Session	Single value query
POST	epm_batch	Session	Batch query (up to 2000 items)

Budget Write-Back

Method	Endpoint	Auth	Description
POST	budget_save	Session	Save full budget line
POST	budget_cell_save	Session	Save single cell (EPMSAVE)
POST	budget_save_batch	Session	Save multiple budget lines

Consolidation

Method	Endpoint	Auth	Description
GET	get_hierarchy_tree	Session	Consolidation hierarchy as nested JSON
POST	approve_adjustment	Session	Approve topside journal
POST	reverse_adjustment	Session	Reverse approved journal

Allocation

Method	Endpoint	Auth	Description
POST	run_allocation	Session	Create and execute allocation run
POST	reverse_allocation	Session	Reverse active allocation run
GET	allocation_history	Session	List runs with filters

Error Handling

All errors return a JSON object with exc_type and message:

{
  "exc_type": "ValidationError",
  "_server_messages": "[\"Invalid scenario 'forecast'. Allowed: actuals, budget, variance\"]"
}

Validation is registry-driven (Fact Table, Measure, and Dimension doctypes). The allowed values listed in each message are computed at request time from the registry, so adding a fact, measure, or dimension changes the messages without a code change.

Validation Errors

Error	Cause
Invalid scenario '{x}'. Allowed: ...	No Fact Table has scenario_key = {x}; the allowed list is the distinct scenario_key values across all Fact Table records
Invalid fact '{x}'. Allowed: ...	A fact was supplied but no Fact Table with that fact_name exists; the allowed list is every registered fact_name
Invalid measure '{m}' for fact '{fact}'. Allowed: ...	Measure not in the fact's allowed measures intersected with the Published Measure registry
Invalid dimension '{d}' for fact '{fact}'. Allowed: ...	Dimension not in the resolved fact's allowed dimensions
Batch size {n} exceeds maximum of 2000	Too many items in a single batch request

ClickHouse Errors

Error	Cause
ClickHouse query timeout	Query took > 30 seconds
ClickHouse connection failed	ClickHouse is unreachable

Batch Size Limit

MAX_BATCH_SIZE = 2000

The batch endpoint rejects requests exceeding 2000 items. The VBA module sends all EPM formulas on a sheet in a single batch, so this limit applies per-sheet refresh.

Fact → Table Resolution

There is no fixed scenario→table map. Each request resolves a Fact Table record — by fact_name when fact is supplied, otherwise by scenario_key from the legacy scenario argument — and reads its clickhouse_table field. Adding or repointing a fact is a registry operation that requires no code change. See GET epm_value for details.

Query Optimization

The batch endpoint groups requests by (scenario, measure, period_tuple, has_cost_center, has_department) and issues one SQL query per group. This means 500 requests for the same scenario/measure/period combination become a single ClickHouse query with an IN clause.

Next Steps

Data Retrieval:

• GET epm_value — Single value endpoint
• POST epm_batch — Batch endpoint
• GET health — Health check

Budget Write-Back:

• POST budget_save — Save full budget line
• POST budget_cell_save — Save single cell (EPMSAVE)
• POST budget_save_batch — Save multiple lines

Consolidation:

• GET get_hierarchy_tree — Hierarchy as nested JSON
• POST approve_adjustment — Approve topside journal
• POST reverse_adjustment — Reverse approved journal

Allocation:

• POST run_allocation — Create and execute run
• POST reverse_allocation — Reverse active run
• GET allocation_history — Run history