ENDEFRESNLTR
⚡ Instant 🏢 Multi-Entity Retail & Consumer Goods Holding 👥 14 Legal Entities · 9 Banks · 5 Currencies ⏱️ 4 Weeks (SFTP + Treasury Flows + Star-Schema Power BI)

Table Formatting

Automated daily cash-position and EBITDA cockpit pulling encrypted SWIFT MT940 (.sta) and ISO 20022 CAMT.053 XML bank statements over SSH Private Key SFTP, verifying opening/closing balances mathematically, normalizing 150+ currencies, and refreshing executive Power BI models before 08:00 AM.

07:35 AM
Daily Cash Cockpit Live
94%
Auto Counterparty Match
150+ FX
Live ISO Currencies
0
SFTP 504 Timeout Errors

⚠️ Legacy Challenge & Bottlenecks

Treasury analysts spent 3.5 hours every morning logging into 9 bank portals or SFTP folders, manually converting cryptic `:60F:`/`:61:`/`:86:` MT940 files into Excel, looking up daily FX rates, and fixing Power Automate SFTP 504 gateway timeouts on SSH PEM keys.

  • ✕Native Power Automate SFTP-SSH connector failing with 504 timeouts on bank PEM keys
  • ✕Zero native support in Power Automate or Power Query for SWIFT MT940 (:61:/:86:) tags
  • ✕3.5-hour daily delay before CFO & Board could see consolidated EUR/USD/TRY liquidity
  • ✕Unreconciled statement gaps discovered only at month-end close

✅ Power Platform + Zetaleap Solution

Configured Zetaleap SFTP Gateway (`/sftp`) with SSH PEM authentication to pull nightly bank statements reliably. Each statement passes through `POST /api/hub/bank-statement`, which parses MT940 and CAMT.053 XML into typed JSON + reconciled `.xlsx` workbooks (`balanceReconciled === true`). Counterparty names are matched to ERP vendor codes via Fuzzy Match (`G3`), converted into reporting USD/EUR/TRY via Zetaleap Currency API, and pushed into a governed Star-Schema Power BI semantic model with Row-Level Security (RLS).

  • ✓Consolidated cash position across 14 entities live by 07:35 AM daily (down from 11:30 AM)
  • ✓100% mathematical opening + movements = closing balance verification before ERP ledger import
  • ✓94% automatic counterparty-to-ERP vendor code matching on unstructured `:86:` bank narratives
  • ✓Automated morning CFO digest emailed to Outlook & Teams with embedded vector Donut/Bar SVG charts

⚡ 5-Stage End-to-End Execution Pipeline

01
Scheduled Bank SFTP Pull GET /api/sftp/* (SSH Private Key PEM)
Pulls nightly .sta (MT940) and .xml (CAMT.053) statements from 9 banking SFTP gateways without timeouts.
02
MT940 / CAMT.053 Parse & Audit POST /api/hub/bank-statement
Extracts IBAN, value dates, debit/credit lines, verifies balance math, and builds archive .xlsx.
03
Fuzzy ERP Counterparty Matching POST /api/edge/fuzzy-match (G3)
Matches noisy bank `:86:` sender strings against SAP/Logo/Netsis master vendor records.
04
Multi-Currency FX Normalization GET /api/currency + Power BI Dataflow
Applies hourly synced USD, EUR, GBP, CHF & TRY cross-rates into the Star-Schema FactCashflow table.
05
Power BI Refresh & Executive Digest Power BI REST API + POST /api/automation/chart-svg
Triggers Power BI semantic model refresh and emails crisp Donut/Bar SVG cash summary to leadership.

🛡️ Enterprise Security, DLP & ALM Governance

  • ◆Dynamic Power BI Row-Level Security (RLS) restricting entity controllers to authorized subsidiaries
  • ◆Encrypted SSH Private Key (PEM) vaulting with strict admin-only schedule governance
  • ◆Immediate Teams alert whenever `balanceReconciled === false` on any bank account
  • ◆Incremental Power BI dataset refresh preserving 5 years of historical FX-adjusted cashflows

🧩 Technology & Connector Stack

Engineered with governed Microsoft Cloud services and drop-in Zetaleap Swagger 2.0 Custom Connectors:

Power BI Star Schema & RLSDAX Multi-Currency ModelZetaleap SFTP SSH VaultZetaleap MT940 & CAMT.053 (E2)Zetaleap Fuzzy Match (G3)Zetaleap 150+ Currency FXZetaleap Executive Chart SVG
Swagger 2.0 Connectors Scope Similar Architecture →

🚀 Try the Live Zetaleap API Studios Featured in This Case Study

More Enterprise Case Studies

View All Case Studies (8) →