SAP Business One and Power BI: a readable reporting database in one day

Why not connect Power BI to SAP directly

• Tables and fields are named with codes: OINV, DocTotal, CardCode — unreadable without the SDK reference.
• Heavy report queries hit the live database — SAP slows down for sales and the warehouse.
• Data from other systems (BILLZ, Excel, a branch on 1C) cannot be combined with SAP in one report.

How ETL Sync does it

1. Connect the SAP B1 company database — on MS SQL or SAP HANA.
2. The program recognises SAP B1 by itself: ~90 standard tables get readable names in Russian, Uzbek or English, and fields and tables your consultant added (U_…, @…) get their descriptions from the database.
3. Tick the tables you need — mappings are created in one click.
4. From then on only changes are transferred on schedule, and Power BI or web reports work with the copy.

Only changed documents

SAP B1 records the date and time a row was changed (UpdateDate, UpdateTS). ETL Sync takes only documents and master data changed since the last run, and document lines together with their changed header. Every hour a reconciliation removes what was deleted in SAP and re-reads open documents — so invoice payments and partial deliveries reach the report too.

A read-only user

For MS SQL (company database SBO_COMPANY):

CREATE LOGIN etl_reader WITH PASSWORD = 'strong password';
USE SBO_COMPANY;
CREATE USER etl_reader FOR LOGIN etl_reader;
ALTER ROLE db_datareader ADD MEMBER etl_reader;

For SAP HANA (company schema SBO_COMPANY):

CREATE USER ETL_READER PASSWORD "Strong1Password" NO FORCE_FIRST_PASSWORD_CHANGE;
GRANT SELECT ON SCHEMA SBO_COMPANY TO ETL_READER;

What SAP HANA needs

• SAP HANA Client is installed on the ETL Sync computer — it contains the HDBODBC ODBC driver (from your SAP partner or the SAP portal).
• In the connection: the HANA server, the SQL port of the company database (usually 30015 or 30041 — ask your SAP administrator) and the company schema in the «Database» field.
• SAP HANA support is in beta: we help with the first connection for free.

Where to start

1. AR invoices and their lines (OINV, INV1), customers (OCRD), items (OITM).
2. Payments (ORCT, RCT2) for receivables, journal entries (OJDT, JDT1) for financial reporting.
3. We can build ready web reports on sales, receivables and stock turnkey.