How to Integrate SAP Business One ERP with Power BI using AWS Aurora

Introduction

SAP Business One (S1B) is an extremely popular ERP for fast-growing SMEs. However, generating Business Intelligence reports directly from your production database (whether SQL Server or SAP HANA) can degrade performance and stall invoicing.

In this article, we will see how to architect a modern and secure solution that synchronizes data from local SAP B1 to a read replica in AWS Aurora (PostgreSQL), serving as an optimized source for dynamic dashboards in Power BI.

---

The Technical Challenge

Typically, Power BI runs complex and heavy queries. If the main ERP database is accessed directly for every dashboard refresh, the performance of daily transactions (invoicing, stock releases) drops drastically.

The proposed architectural solution is based on: 1. Source (Local/On-Premise): SAP Business One server. 2. Synchronization Layer (CDC/Batch Process): Integration script that extracts only key financial and invoicing tables (OINV, INV1, OOCR, OCR1). 3. Destination (AWS Cloud): Aurora Serverless relational database. 4. Visualization: Power BI Gateway connecting to AWS for aggregated reports without overloading the ERP.

+------------+        +---------------+        +---------------+        +------------+
|  SAP B1    |  --->  | Integration   |  --->  |  AWS Aurora   |  --->  |  Power BI  |
|  (On-Prem) |        | Script (TS)   |        |  (PostgreSQL) |        |  Dashboard |
+------------+        +---------------+        +---------------+        +------------+

---

Implementation Steps

1. Modeling SAP B1 Key Tables

For financial dashboards, we focus on AR invoice tables: OINV: AR Invoice Header (Contains Posting Date, Document Total, Customer Code). INV1: AR Invoice Rows (Contains Item Code, Quantity, Unit Price, CFOP).

2. The Synchronization Script (Node.js & TypeScript)

Below is an example of a Node.js routine running in a scheduled task (Cron or AWS ECS Task) that queries the data modified in the last day and inserts it into PostgreSQL on AWS.

import { Client } from 'pg';
import sql from 'mssql';
// Connection Configuration
const sqlConfig = {
  user: process.env.SAP_DB_USER,
  password: process.env.SAP_DB_PASSWORD,
  server: process.env.SAP_DB_SERVER,
  database: 'SBO_PROD',
  options: { encrypt: true, trustServerCertificate: true }
};
const pgConfig = {
  connectionString: process.env.AWS_AURORA_URL,
};
async function syncSAPToAWS() {
  const mssqlPool = await sql.connect(sqlConfig);
  const pgClient = new Client(pgConfig);
  await pgClient.connect();
console.log("Iniciando sincronização SAP -> AWS Aurora...");
// 1. Incremental extraction (last 24 hours)
  const query = 
    SELECT DocEntry, DocNum, DocDate, DocTotal, CardCode 
    FROM OINV 
    WHERE UpdateDate >= DATEADD(day, -1, GETDATE())
  ;
  const result = await mssqlPool.request().query(query);
// 2. Load (Upsert) in Postgres AWS Aurora
  for (const row of result.recordset) {
    const upsertQuery = 
      INSERT INTO aws_sales_stage (doc_entry, doc_num, doc_date, doc_total, card_code)
      VALUES ($1, $2, $3, $4, $5)
      ON CONFLICT (doc_entry) DO UPDATE 
      SET doc_total = EXCLUDED.doc_total, doc_date = EXCLUDED.doc_date;
    ;
    await pgClient.query(upsertQuery, [
      row.DocEntry,
      row.DocNum,
      row.DocDate,
      row.DocTotal,
      row.CardCode
    ]);
  }
console.log(Sucesso: ${result.recordset.length} registros sincronizados.);
  await mssqlPool.close();
  await pgClient.end();
}
syncSAPToAWS().catch(console.error);

---

FinOps and Performance Optimizations

Table Partitioning: On AWS, partition the aws_sales_stage table by year or month to speed up date filters in Power BI. Sizing and Costs: Use Aurora Serverless v2 to scale capacity on demand, reducing it to zero on weekends and late nights when no one is reading reports.

---

What leaves SAP B1 and what Aurora answers

Power BI does not read production OINV. The job copies only open invoices into Aurora, and the report queries that replica.

SELECT "CardCode", "DocEntry", "DocTotal", "DocDate"
FROM "OINV"
WHERE "DocStatus" = 'O';

aws rds describe-db-clusters \
  --db-cluster-identifier sap-b1-aurora \
  --query "DBClusters[0].Endpoint" \
  --output text

That endpoint goes into the Power BI gateway connection string. If describe-db-clusters returns no host, the refresh fails before any DAX runs.

---

Conclusion

By decoupling the reporting database from the transactional database using AWS, we ensure security, scalability for Power BI, and high availability for the company's physical billing. This robust architecture cleanly resolves the performance bottleneck.

Want to know more about ERP integration and Cloud Architecture? Leave your comment on my LinkedIn!