Business Analytics Platform
Internal, Atulaya
- Role
- Software Engineer
- Type
- Internal · Production
- Systems
- SAP HANA · BSL · LIS · RIS
- Scale
- ~20M lab tests indexed
- Sync
- Every 5 minutes
Objective
Atulaya runs on four disconnected systems: SAP Business One / HANA (finance, inventory, reagent stock), BSL (front-desk billing and orders), 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.
Decisions
The SAP HANA decision
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.
Read-only, enforced at the grant level
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.
How the systems link together
There is no database-level link between these systems. Every connection is an application-level sync job with an explicit bridge key — BSL's VisitingCode matches LIS's LedgerTransactionNo, SAP HANA feeds ClickHouse nightly, BSL feeds it every 5 minutes, and radiology data arrives via the Stradus HTTP API. Every sync job tracks its own watermark in a cursor table, because different BSL stored procedures update different tables' timestamps independently — a shared single cursor would silently miss changes.
Architecture
Sync architecture
SAP HANA / BSL / LIS / RIS → Sync jobs → ClickHouse → Dashboard
- Four source systems, none talking to each other natively.
- Independent, cursor-based Laravel jobs on their own cadence — 5 minutes for BSL/LIS, nightly staggered for HANA.
- A MergeTree analytical warehouse, fed via atomic partition swaps so there's never a visibility gap during a re-feed.
- A custom Vue 3 + Inertia SPA reading straight from ClickHouse, refreshed every five minutes.
Screens
Location and employee names anonymized — this is live production data.
Result
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.
By the numbers
Stack
Laravel, Vue/Inertia, SAP HANA (ODBC), SQL Server, MySQL, ClickHouse
Lessons learned
- An early cursor implementation paginated HANA results by DocDate alone — when a single day's result set exceeded the batch size, rows past the boundary were silently dropped, undercounting one test's monthly quantity by ~24% with no error thrown anywhere. Fixed with a compound (DocDate, DocEntry) cursor, implemented as expanded OR clauses since HANA's ODBC driver doesn't support row-value tuple comparisons.
- I'd build the reconciliation sweeps alongside the incremental sync from day one — the LIS "unapproval" blind spot (a status change that doesn't update the timestamp column a pure incremental sync watches) was a production discovery, not something caught at design time.
- I'd generalize per-source cursor tracking as a first-class pattern immediately, instead of retrofitting it once BSL's per-procedure timestamp behavior made a single shared cursor unsafe.
Click to zoom · Esc to close