Skip to main content

Initial Setup Guide

After deployment, Konsolidat needs configuration before it can process financial data. This guide walks through each step — from connecting infrastructure to entering your first budget line.

Configuration Flow​

graph TD
    EPM[1. EPM Settings<br/>ClickHouse + Airbyte + dbt] --> DIM[2. Dimensional Model<br/>Periods, Dimensions, Measures]
    DIM --> SCEN[3. Scenarios<br/>Actual, Budget, Forecast]
    SCEN --> CONSOL[4. Consolidation<br/>Entity hierarchy + rules]
    SCEN --> ALLOC[5. Allocations<br/>Rules + drivers]
    SCEN --> BUDGET[6. Budgeting<br/>Spread profiles + input]
    CONSOL --> PIPE[7. Pipeline Run<br/>Sync + transform]
    ALLOC --> PIPE
    BUDGET --> PIPE
    PIPE --> EXCEL[8. Excel<br/>First =EPM formula]

    style EPM fill:#4051b5,color:#fff
    style PIPE fill:#4051b5,color:#fff

Steps 4–6 are independent — configure only what you need.

Step 1: EPM Settings​

Frappe Desk → Setup → EPM Settings

This is the foundation — all connections to infrastructure are configured here.

ClickHouse Connection​

FieldValueNotes
ClickHouse HostclickhouseDocker service name. Use localhost for bare-metal installs
ClickHouse Port8123HTTP API port
ClickHouse UserdefaultFrom .credentials file
ClickHouse Password(from deploy)From .credentials file

Verify the connection after saving:

curl -sf "http://localhost:8123/?query=SHOW+DATABASES" \
  -u "default:YOUR_PASSWORD"

You should see epm_staging, epm_bronze, epm_silver, epm_gold in the output.

Airbyte Connection (optional)​

Only needed if you're syncing live data from D365, SAP, or ERPNext. Skip this if you're using seed/demo data.

FieldValueNotes
Airbyte API URLhttp://localhost:8000Default Airbyte API
Connection ID(from Airbyte UI)UUID of your source → ClickHouse connection
Client ID(from Airbyte)OAuth credentials for API access
Client Secret(from Airbyte)OAuth credentials for API access

See Setting up Airbyte below for how to get these values.

dbt Configuration​

FieldValueNotes
dbt Project Path/home/frappe/dbt_projectDocker default. Adjust for bare-metal

Step 2: Dimensional Model​

Define the structure of your financial reporting. Do these in order — later items reference earlier ones.

Fiscal Periods​

Lists → EPM → Fiscal Period

Create 14 periods for a standard fiscal year:

PeriodLabelQuarterHalfPurpose
0OPNOPNOPNOpening balances
1P1Q1H1January
2P2Q1H1February
3P3Q1H1March
4P4Q2H1April
5P5Q2H1May
6P6Q2H1June
7P7Q3H2July
8P8Q3H2August
9P9Q3H2September
10P10Q4H2October
11P11Q4H2November
12P12Q4H2December
13CLSCLSCLSClosing adjustments

Dimensions​

Lists → EPM → Dimension

Dimensions define the axes of your financial cube. Create one for each reporting dimension you need:

Dimension NameSource ColumnLabelIn BudgetAllocation Role
dim_cost_centerCostCenterCost CenterYescost_center
dim_departmentDepartmentDepartmentYes—
dim_business_unitBusinessUnitBusiness UnitNo—
  • Source Column maps to the OData field name in your ERP
  • In Budget includes this dimension in budget entry forms
  • Allocation Role links the dimension to cost allocation drivers

Measures​

Lists → EPM → Measure

Measures define the aggregations available in Excel and the API:

Measure NameExpressionLabelCube Type
period_net_amountsum(accounting_currency_amount)Net Amountsum
period_debitsum(debit_amount)Debitsum
period_creditsum(credit_amount)Creditsum
ytd_net_amountsum(accounting_currency_amount)YTD Netsum
transaction_countcount(*)Transactionscount

These become available as the measure parameter in =EPM() formulas and the API.

Step 3: Scenarios​

Lists → EPM → Scenario Definition

Scenarios tag data by its purpose — actual vs. budget vs. forecast:

Scenario IDNameTypeActive
actual_2024Actual 2024actualYes
budget_2025Budget 2025budgetYes
forecast_2025Forecast 2025forecastYes

Scenario type determines how the data flows through the pipeline. Actuals come from ERP sync, budgets from manual input.

Step 4: Consolidation (multi-entity only)​

Skip this section if you have a single legal entity.

Entity Hierarchy​

Lists → Consolidation → Consolidation Group

Build a tree of legal entities with ownership and consolidation method:

HOLDING (is_group=true, ownership=100%)
├── USMF (data_area_id=USMF, ownership=100%, method=full)
├── EMEA (is_group=true)
│   ├── FR01 (data_area_id=FRAX, ownership=100%, method=full)
│   └── DE01 (data_area_id=DEAX, ownership=75%, method=full)
└── APAC (is_group=true)
    └── JP01 (data_area_id=JPAX, ownership=51%, method=full)
FieldDescription
data_area_idLegal entity code from your ERP
ownership_pctParent's ownership percentage (0–100)
reporting_currencyGroup reporting currency (e.g., USD)
consolidation_methodfull, proportional, or equity
goodwill_methodpartial (NCI at proportional NA) or full (NCI at fair value)

IC Elimination Rules​

Lists → Consolidation → IC Elimination Rule

Define rules to eliminate intercompany balances and unrealized profit:

Rule IDTypeDebit AccountCredit AccountNotes
IC-AR-AP-01balance210200 (AP)112000 (AR)Eliminates IC receivables/payables
IC-INV-01unrealized_profit312000114500Eliminates IC inventory margin (25%)

For unrealized profit rules, set margin_pct to the intercompany gross margin percentage.

Ownership Periods (step acquisitions)​

Lists → Consolidation → Ownership Period

Track ownership changes over time — needed for step acquisitions and disposals:

EntityEffective DateOwnershipMethodEvent
DE012024-01-0150%equityInitial stake
DE012024-07-0175%fullStep acquisition

See the Consolidation Guide for formulas, translation rules, and worked examples.

Step 5: Cost Allocation (optional)​

Allocation Rules​

Lists → Allocation → Allocation Rule

Define cost pools and how they're distributed. Rules execute in step_order:

StepRuleSourceDriverMethod
1IT Cost AllocationAccount 7100, CC: ITheadcountstep_down
2Facility AllocationAccount 7200, CC: FACILITYsqmstep_down
3Management FeesAccount 7300, CC: MGMTrevenuestep_down

Driver types: headcount, revenue, sqm, composite, conditional, tiered.

Allocation Drivers​

Lists → Allocation → Allocation Driver

Populate driver values for each entity, cost center, and period:

EntityCost CenterDriver TypeValueYearPeriod
USMFSALESheadcount5020245
USMFMARKETINGheadcount2520245
USMFSALESsqm200020245

See the Allocation Guide for cascade logic, reciprocal allocation, and composite drivers.

Step 6: Budgeting (optional)​

Spread Profiles​

Lists → EPM → Spread Profile

Define how annual amounts distribute across 12 months:

ProfilePatternExample
EVENEqual weights (1.0 each)$1.2M annual → $100K/month
SEASONAL_RETAILQ4-heavy (0.5–2.5)$600K annual → $23K low / $117K peak

Budget Input​

Lists → EPM → Budget Input

Enter budget lines with monthly amounts or spread profiles:

  1. Select Scenario (e.g., Budget 2025)
  2. Set Entity, Account, and Dimensions
  3. Either enter monthly amounts directly, or select a Spread Profile and enter an annual total
  4. Optionally set a Layer (base, challenge, management, board) for collaborative budgeting

See the Budgeting Guide and Budget Layers for details.

Step 7: Connect Your ERP via Airbyte​

Airbyte syncs live data from your ERP into ClickHouse. It runs as a separate service.

Install Airbyte​

curl -fsSL https://get.airbyte.com | bash
abctl local install

Open the Airbyte UI at http://localhost:8000.

Configure Source​

=== "D365 Finance & Operations"

1. **Sources → Add Source → Microsoft Dynamics 365 F&O (OData)**
2. Enter your Azure AD credentials:

| Field | Where to find it |
|-------|-----------------|
| Environment URL | `https://your-env.operations.dynamics.com` |
| Tenant ID | Azure Portal → Azure AD → Properties |
| Client ID | Azure Portal → App Registrations → your app |
| Client Secret | Azure Portal → App Registrations → Certificates & secrets |

3. Select streams: `GeneralJournalAccountEntries`, `MainAccounts`, `LedgerParameters`, `ExchangeRates`, `FiscalCalendars`

=== "SAP"

Use the SAP OData or SAP HANA connector. Map GL journal entries to the same staging schema.

=== "ERPNext"

Use the ERPNext API connector. Map GL Entry and Account doctypes.

Configure Destination​

  1. Destinations → Add Destination → ClickHouse
  2. Connection details:
FieldValue
Hostlocalhost
Port8123
Databaseepm_staging
Userfrom .credentials
Passwordfrom .credentials

Create Connection​

  1. Connections → New Connection
  2. Wire your source → ClickHouse destination
  3. Set sync mode to Incremental (Append)
  4. Run a test sync
  5. Copy the Connection ID from the URL bar (UUID format) and paste it into EPM Settings → Airbyte Connection ID

Step 8: Run the Pipeline​

Lists → Pipeline → Pipeline Run → New → Submit

The pipeline executes two phases:

graph LR
    SYNC[Phase 1: Airbyte Sync<br/>ERP → ClickHouse staging] --> DBT[Phase 2: dbt Build<br/>staging → bronze → silver → gold]
    DBT --> READY[Data ready in<br/>Cube.js + Excel]

Monitor progress in the Pipeline Run document — status updates automatically:

StatusWhat's happening
QueuedWaiting for background worker
ExtractingAirbyte is syncing ERP data to staging
Transformingdbt is building bronze → silver → gold models
CompletedData is ready to query
FailedCheck the error log field for details

After the first successful run, your data is available in:

  • Excel: =EPM("USMF", 2024, 5, "401100", "period_net_amount")
  • Cube.js: https://localhost:4443 (query playground)
  • API: GET /api/method/konsol.api.epm_value?entity=USMF&year=2024&period=5&account=401100&measure=period_net_amount

Quick Reference: Configuration Checklist​

#StepLocationRequiredTime
1EPM SettingsSetup → EPM SettingsYes2 min
2Fiscal PeriodsLists → EPM → Fiscal PeriodYes5 min
3DimensionsLists → EPM → DimensionYes5 min
4MeasuresLists → EPM → MeasureYes5 min
5ScenariosLists → EPM → Scenario DefinitionYes2 min
6Consolidation GroupsLists → Consolidation → Consolidation GroupMulti-entity only10 min
7IC Elimination RulesLists → Consolidation → IC Elimination RuleMulti-entity only5 min
8Allocation RulesLists → Allocation → Allocation RuleIf allocating costs10 min
9Allocation DriversLists → Allocation → Allocation DriverIf allocating costs10 min
10Spread ProfilesLists → EPM → Spread ProfileIf budgeting5 min
11Budget InputLists → EPM → Budget InputIf budgetingvaries
12Airbyte ConnectionAirbyte UI + EPM SettingsFor live ERP data15 min
13Pipeline RunLists → Pipeline → Pipeline RunYes5 min

Next Steps​