Business Analytics Platform

Atulaya runs on four disconnected systems: SAP Business One / HANA (finance, inventory, reagent stock), BSL (front-desk billing and orders across every diagnostic centre), LIS (lab results), and a third-party radiology system. None of them talk to each other. This platform wires all four into one real-time analytics layer — sales, lab ops, and radiology dashboards that stay in sync with the source systems every five minutes, replacing a set of legacy Tableau reports that had quietly drifted out of sync with SAP.

Why this is hard: none of these four systems were built to talk to each other, so there is no shared key, no shared schema, and no shared notion of "when did this record last change." Every join is a reverse-engineered bridge between systems on different database engines, and every sync job has to detect its own staleness — the failure mode isn't a crash, it's a dashboard that looks correct while quietly showing stale or undercounted numbers.

SAP Business One ships a standard Service Layer REST API for exactly this kind of integration — this platform doesn't use it. Instead it connects directly to the HANA 2.0 database engine that backs B1 and reads its tables with plain SQL, no calculation views or business-logic layer in between.

The Service Layer is a session-based REST wrapper built for transactional, record-at-a-time operations — not bulk analytical reads across millions of invoice lines. Going straight to HANA means no REST/session overhead and no B1 business-logic layer sitting between the query and the data — necessary at this data volume.

SELECT T0."DocEntry", T0."DocDate", T1."Quantity", T3."TestCode"
FROM "<schema>"."OINV" T0
JOIN "<schema>"."INV1" T1 ON T1."DocEntry" = T0."DocEntry"
JOIN "<schema>"."@TEST_MASTER" T3 ON T3."Code" = T1."ItemCode"

The connection runs through unixODBC + SAP's HDBODBC client via PDO, against a dedicated read-only HANA user — granted SELECT only, no EXECUTE, IMPORT, or DDL. A server-side HANA workload class hard-caps the account further — bounded statement time, memory, and thread count — enforced by SAP itself, not just client config. Writing to a B1 table directly voids SAP support, so read-only is enforced at the database grant level, not by developer convention.

Each business entity lives as its own schema inside one shared HANA tenant — over a dozen schemas total, tagged live/archive/shared across overlapping fiscal years, so a naive UNION across them would double-count revenue if the windows aren't handled correctly.

There is no database-level link between these systems — no foreign data wrappers, no shared schema. Every connection is an application-level sync job with an explicit bridge key:

• —BSL ↔ LIS — VisitingCode (BSL) matches LedgerTransactionNo (LIS) — string join across two unrelated databases on different engines.
• —BSL ↔ LIS masters — InvestigationId (BSL) matches Investigation_ID (LIS) to reconcile the test catalog.
• —SAP HANA → ClickHouse — Invoice/purchase/reagent lines synced nightly from HANA schemas into dimension and fact tables.
• —BSL → ClickHouse — Orders, invoices, cancellations, and refunds synced every 5 minutes into a MergeTree fact table, monthly partitioned.
• —Radiology (Stradus API) — Studies and TAT data pulled via HTTP, joined against BSL investigation records.

Every sync job tracks its own watermark in a cursor table (last ModifiedOn / ApprovedDate / AddedOn seen) — because different BSL stored procedures update different tables' timestamps independently. A shared single cursor would silently miss changes; each sync gets its own.

A single Laravel task scheduler drives everything — one cron entry (schedule:run every minute), timezone-aware (IST), dispatching dozens of independent, cursor-based sync jobs on their own cadence:

• —Every 5 minutes — BSL invoices, visit changes, cancellations, refunds, credit notes; LIS results and sample rejections; a ClickHouse re-feed of the current month, applied via an atomic partition swap so there's no visibility gap.
• —Every 15 minutes — a stall watchdog that alerts (log + Teams webhook) if fully-approved LIS tests sit unsynced past a staleness threshold.
• —Business hours only — radiology sync runs every 5 minutes from 07:00–22:00 IST, then drops to hourly overnight — sized to measured volume (~545 orders/day, almost none overnight) instead of hammering a flaky third-party API for nothing.
• —Nightly, staggered — dimension tables (centres, doctors, employees) sync from ~01:15, followed by SAP HANA pulls staggered roughly every 5 minutes from ~01:48–02:15 so multiple jobs never hit HANA's shared workload class limits simultaneously.
• —Daily / weekly sweeps — separate reconciliation jobs re-check LIS approvals and observations, because an LIS 'unapproval' doesn't update the timestamp column a pure incremental sync watches — it's invisible unless something re-checks the window.

An early cursor implementation paginated HANA results by DocDate alone. When a single day's result set exceeded the batch size, rows past the batch boundary were silently dropped — one test's monthly quantity came out at 644 instead of 840, an ~24% undercount with no error thrown anywhere. Fixed with a compound (DocDate, DocEntry) cursor — worth calling out because HANA's ODBC driver doesn't support row-value tuple comparisons, so the fix had to be implemented as expanded OR clauses instead of the obvious (a,b) > (?,?) syntax.

A custom Vue 3 + Inertia.js single-page app (not Metabase or Superset), backed by ClickHouse for the analytical queries. Covers an executive scorecard, sales explorer, centre/doctor drill-downs, patient-source attribution, lab-ops dashboards down to machine and pathologist level, and a full radiology ops view (TAT targets, worklists, network rollups).

Location and employee names anonymized — this is live production data. Click a screenshot to enlarge.

Replaced a set of legacy Tableau reports that queried HANA directly with drifted, broken grants — some reports had silently lost access to entire live company schemas. In their place: a managed, auditable, read-only HANA connection, unified with BSL and LIS data, refreshing every five minutes across sales, lab ops, and radiology — one dashboard instead of four disconnected systems.

Laravel 12 · Vue 3 / Inertia.js · SAP HANA (ODBC/HDBODBC) · SQL Server (BSL) · MySQL (LIS) · ClickHouse · Redis / Horizon

• •The compound-cursor bug (§06) shipped because HANA's ODBC driver limitation wasn't known up front — next time, I'd write the pagination contract as a documented invariant before implementation, not discover the gap via a silent 24% undercount in production.
• •I'd build the reconciliation sweeps (§05) alongside the incremental sync from day one — the LIS "unapproval" blind spot was a production discovery, not something caught at design time.
• •I'd generalize per-source cursor tracking (§03) as a first-class pattern immediately, instead of retrofitting it once BSL's per-procedure timestamp behavior made a single shared cursor unsafe.