Project Name
Delivered 100% Reconciled Daily Deposit Reporting on Informatica
The client is a bank with a dedicated reporting team. After each business day closes, the team needs deposit balances by branch, product, and currency. Customer profiles must keep their history, so older reports show the segment that applied at the time. Deposit reports were built by hand from separate core banking, customer, and reference extracts. The bank needed one repeatable daily batch that releases only complete, checked data.
- Inputs from different points in time: Balance and customer files could come from different days. One report could mix two business dates.
- Inconsistent customer data across feeds: Identifiers lost their leading zeros. Text values differed in case, spacing, and how nulls were handled.
- Segment changes rewrote history: Customer rows were overwritten. Older reports showed the new segment instead of the one that applied at the time.
- Joins multiplied or lost balances: Two balance rows for one account doubled its balance. A missing account row silently dropped it.
- Successful sessions could hide bad data: PowerCenter reported success even when business rules had failed. Bad totals could reach reports unnoticed.
- Partial loads after a failure: Earlier sessions could commit before a later one failed. Reports could read a half-loaded date, or a rerun could count balances twice.
Ksolves built a daily ETL pipeline on Informatica PowerCenter. It prepares each business date outside the reporting tables, checks it fully, and publishes it in one database transaction.
- One business date per batch: Every input must carry the same business date and control totals. Processing starts only when all inputs arrive. The inputs stay frozen in staging so reruns use the same data.
- Reusable cleansing mapplets: Every feed uses the same standardisation and null rules. Customer identifiers stay as text, and ambiguous ones become exceptions instead of guesses.
- SCD Type 2 customer history: A cached Lookup and a Router classify each customer as new, changed, or unchanged. A changed customer gets one expired version and one new version, and their dates never overlap.
- Uniqueness checks before the Joiner: The batch confirms there is one account row and one balance row per account and date. The joined count is then reconciled to the expected number of accounts.
- Release based on checks, not session status: Errors, exceptions, row counts, and currency totals are all checked before release. An invalid balance is never replaced with zero.
- Work tables and one publication transaction: Sessions write only to work tables. One procedure publishes customer changes, facts, and summaries together and commits once, or rolls everything back.
Technology Stack
| Category | Technology |
|---|---|
| Platform | Informatica PowerCenter |
| Database | Oracle reporting mart |
| Architecture | SCD Type 2 customer dimension |
| Processing | Mapplets, Joiner and Aggregator |
| Integration | Command task and publication procedure |
| Compliance | Batch control and exception audit |
- 1 transaction per business date: Each date reaches reports either complete or not at all.
- 100% of balances reconciled: Counts and balances match the control totals by branch, product and currency.
- 0 silently dropped rows: Every rejected record is kept with a reason code.
- Every customer version kept: Past reports keep the segment that applied on their date.
- 7 acceptance tests at sign-off: Each failure path is tested on purpose before release.
“We no longer build deposit reports by hand. Each morning we get one reconciled business date, and every past report still tells the same story.”
– Head of Regulatory and MIS Reporting, BFSI sector.
Deposit reports used to be assembled by hand from separate extracts. Duplicates, missing codes, and overwritten customer data could all distort them. Ksolves replaced this with one scheduled batch on Informatica PowerCenter. Each business date is validated, reconciled, and published in a single transaction. Customer history is kept by date, and every rejected row is explained. If a run fails, reports keep showing the previous date. Connecting another core banking system now only means mapping its snapshots to the same staging layout.
Still Building Deposit Reports by Hand? Get Reconciled Reports Every Morning.
