Back to Case work

Extensibility & Scalability of Data Warehousing


In order to minimize bad debt volumes, this Tier 1 banking client was renegotiating mortgage credit agreements with borrowers. In a move to control these rearrangements, the Regulator has defined various acceptable renegotiation terms designed to get the  economy moving and lower the risk  of future bank failure. The challenge for the client was that it did not have a sufficiently robust reporting infrastructure in place in order to meet the Forbearance requirements within the given timeframe.


ACS were engaged to determine the business requirements, gain a clear understanding of the detail, it’s criticality and impact. The team designed an appropriate solution that was simple for the business to understand, robust, scalable and fully automated. The solution is based on different technologies/applications including OFSAA, Oracle, OBIEE and PL/SQL. Whilst the requirement for Forbearance reporting was initiated by the Group, the design was approached in such a way that the solution was extensible to Divisions for Divisional reporting purposes.


The different components of the solution are outlined below:

  • Divisions upload Forbearance data in the form of predefined excel templates via OFSAA into the system. As users upload the data, they are then forced to verify the quality of data by executing validation components built into the OFSAA framework (validation components perform defined sets of validations on the including Reference Check, List of Values checks etc.)
  • Once the data is validated the records are submitted for approval. Where data quality issues arise, errors are reported to the user, users will then correct the error and the data is then re-uploaded accordingly.
  • Workflow then helps the Authoriser to identify pending Authorisations. Data uploads are then authorized as appropriate and the data is then available to the system for onward processing.

Relevant Design Flexibility

  • The system has been designed so that that new data fields can be added to upload files and new validations can be easily applied to the process as required.

ETL Flow

  • The validated data is then auto loaded and processed in the strategic regulatory platform Relational Layer. During the processing, core data warehousing concepts are followed to maintained data integrity. In the Staging Layer, OFSAA authorized data is staged. In the Relational Layer, staged data is transformed and moved to the Data Warehouse. Subsequently in the Presentation Layer, based on the Reporting requirements, data is further transformed and moved to the relevant Data Mart.

Relevant Design Flexibility

  • Business transformations specific to Forbearance reporting are applied in the strategic regulatory platform Presentation Layer in such a way that if any new requirement is introduced, we can directly implement the relevant logic into the presentation layer without affecting other CORE (Relational) layers.


  • OBIEE uses the presentation layer to derive the required reports. Dimensional modelling was implemented throughout the Relational, Presentation and OBIEE layer so that new requests could be easily accommodated within the design.


  • A regulatory compliant and industry standard solution.
  • Produced timely reporting to the Regulator.
  • Improved the business confidence in the solution (due to ease of understanding the solution).
  • Appropriate MI helped management take quick and decisive decisions, lowering the overall business cost.
  • Reduced implementation costs.
  • Optimum design allowed the solution to be extended to multiple stakeholders.
  • Offered the ability to enhance the solution with minimum cost and within shorter timeframes.

For more information about how we can help you please contact us

Vijay Jadhav

Vijay Jadhav

Managing Consultant
BI Architecture




Recent Case Studies

Delivery that counts


Our track record for end to end delivery speaks for itself. In our short history, we have delivered some of the most complex business and IT change across some of the worlds largest organisations. Our people are happy and so are our clients. If you would like to know more about a career in ACS please click here.


Leave a comment

Leave a Reply

Thank you for posting blog.