Skip to main content

Setup Guide

Complete deployment of Konsolidat: ClickHouse, Airbyte, dbt, Frappe/Konsol, and Excel VBA.

Prerequisites​

ComponentVersionPurpose
Docker Desktop24+ClickHouse container
Python3.10+dbt Core, Frappe Bench
Node.js18+Frappe assets
Excel2016+VBA reporting
D365 F&OAnySource ERP (optional for demo)

1. Clone the Repository​

git clone https://github.com/your-org/konsolidat.git
cd konsolidat
cp .env.example .env

Edit .env to set your ClickHouse password:

CLICKHOUSE_PASSWORD=your_secure_password

2. Start ClickHouse​

Tip: ./deploy.sh is the primary one-click entrypoint — it provisions ClickHouse and the rest of the stack in one step. The manual steps below are useful when you want to bring up services individually.

docker compose up -d

This starts a single ClickHouse container with:

  • HTTP API on port 8123
  • Native protocol on port 9000
  • PostgreSQL wire protocol on port 15432
  • Init SQL creates databases: epm_bronze, epm_staging, epm_silver, epm_gold

Verify:

curl "http://localhost:8123/?query=SHOW+DATABASES"

3. Set Up Airbyte (D365 Extraction)​

Skip this section if using seed data only (demo mode).

3.1 Install Airbyte​

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

Airbyte UI will be available at http://localhost:8000.

3.2 Register a D365 App in Azure AD​

  1. Go to Azure Portal → App Registrations
  2. New registration → name: Konsolidat Airbyte
  3. Under API permissions → Add: Dynamics 365 Finance and Operations → Ax.FullAccess
  4. Under Certificates & secrets → New client secret → copy the value
  5. Note: Tenant ID, Client ID, Client Secret, D365 Environment URL

3.3 Configure Airbyte Connection​

Source: Dynamics 365 Finance & Operations (OData)

  • Tenant ID, Client ID, Client Secret, Environment URL from step 3.2
  • Enable cross_company=true on all streams

Destination: ClickHouse

  • Host: host.docker.internal (Docker-to-host), Port: 8123
  • Database: epm_bronze, User: default, Password: from .env

Streams (15 entities):

OData EntityBronze Table
GeneralJournalAccountEntriesbronze_general_journal_account_entries
GeneralJournalEntriesbronze_general_journal_entries
MainAccountsbronze_main_accounts
MainAccountCategoriesbronze_main_account_categories
LegalEntitiesbronze_legal_entities
FiscalCalendarsbronze_fiscal_calendars
FiscalCalendarYearsbronze_fiscal_calendar_years
FinancialDimensionsbronze_financial_dimensions
FinancialDimensionValuesbronze_financial_dimension_values
ExchangeRateCurrencyPairsbronze_exchange_rate_currency_pairs
ExchangeRateTypesbronze_exchange_rate_types
BudgetRegisterEntriesbronze_budget_register_entries
BudgetTransactionLinesbronze_budget_transaction_lines
ConsolidationAccountGroupsbronze_consolidation_account_groups
LedgerTrialBalanceFiscalYearSnapshotDataEntitybronze_trial_balance_snapshot

3.4 Run Initial Sync​

Trigger a full sync from the Airbyte UI or CLI. This populates epm_bronze tables.

4. Install and Run dbt​

cd dbt_project
pip install dbt-core dbt-clickhouse

Create ~/.dbt/profiles.yml (if not present):

open_epm:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: localhost
      port: 8123
      user: default
      password: "your_password"
      schema: epm
      secure: false

Build:

dbt deps       # Install dbt packages
dbt seed       # Load 13 CSV seeds into epm_gold schema
dbt build      # Build all models + run 150+ tests

Seed Files (13)​

SeedPurpose
allocation_rules.csvMulti-step allocation rules
allocation_drivers_headcount.csvHeadcount driver values
allocation_drivers_sqm.csvSquare meter driver values
allocation_drivers_revenue.csvRevenue driver values
budget_annual_input.csvAnnual budget line items
spread_profiles.csvMonthly spread weights (EVEN, SEASONAL_RETAIL)
consolidation_groups.csvEntity → group mapping with ownership %
consolidation_adjustments.csvTop-side journal entries
ic_elimination_rules.csvIntercompany elimination rules
scenario_definitions.csvScenario metadata (actuals, budget, forecast)
entity_fiscal_calendars.csvEntity → fiscal calendar mapping
dimension_mappings.csvCanonical dimension value harmonization (macro-consumed)
test_consolidation_hierarchy.csvFixture for the consolidation hierarchy tests

5. Set Up Frappe / Konsol​

5.1 Install Frappe Bench​

pip install frappe-bench
bench init frappe-bench --frappe-branch version-15
cd frappe-bench

5.2 Install Konsol App​

bench get-app /path/to/konsol    # Or git URL
bench new-site konsolidat.local --db-type mariadb --admin-password admin
bench --site konsolidat.local install-app konsol
bench start

Frappe is now running at http://localhost:8069.

5.3 Configure EPM Settings​

  1. Log in to Frappe Desk (http://localhost:8069)
  2. Navigate to Setup → EPM Settings
  3. Fill in:
FieldValue
ClickHouse Hostlocalhost
ClickHouse Port8123
ClickHouse Userdefault
ClickHouse PasswordYour .env password
Airbyte API URLhttp://localhost:8000
Airbyte Connection IDFrom Airbyte UI
dbt Project Path/path/to/konsolidat/dbt_project
  1. Save

6. Set Up Excel VBA​

6.1 Import the VBA Module​

  1. Open Excel → Alt+F11 (VBA Editor)
  2. File → Import File → excel/OpenEPM.bas
  3. Close VBA Editor
  4. Save workbook as .xlsm

6.2 Add References (Required)​

In the VBA Editor: Tools → References → check:

  • Microsoft Scripting Runtime (for Scripting.Dictionary)
  • Microsoft XML, v6.0 (for MSXML2.XMLHTTP60)

6.3 Configure Server URL​

Run EPM_SetServer macro (Alt+F8) and enter your Frappe URL (e.g., http://localhost:8069).

This saves the URL as a Custom Document Property in the workbook, so it persists across sessions.

6.4 Log In and Test​

  1. Run EPM_Login → enter Frappe credentials
  2. In any cell: =EPM("USMF", 2024, 5, "401100")
  3. Press Ctrl+Shift+R to refresh

7. Optional: Excel Task Pane Add-in​

The Office.js task pane provides pipeline orchestration (trigger sync + dbt from Excel).

Sideload for Development​

  1. Copy excel-addin/manifest.xml
  2. In Excel: Insert → My Add-ins → Upload My Add-in → select the manifest
  3. The task pane appears on the Home tab as "Konsolidat"

The task pane connects to Frappe at http://localhost:8069 and allows:

  • Login with Frappe credentials
  • Viewing latest pipeline run status
  • Triggering new pipeline runs (Airbyte sync + dbt build)

Verification Checklist​

  • docker ps shows konsolidat_clickhouse healthy
  • curl http://localhost:8123/?query=SHOW+DATABASES returns 4 databases
  • dbt build completes with 0 errors
  • Frappe Desk accessible at http://localhost:8069
  • EPM Settings saved with ClickHouse connection
  • =EPM("USMF", 2024, 5, "401100") returns a numeric value after Ctrl+Shift+R

Next Steps​