Says Henry Walton, MIS eExecutive at African Bank: "With our primary AS/400 system running 400m transaction lines a month to service our client base, the batteries of reports that we requested from the system on a daily basis were slowing it down.

In addition, it required significant skill to get the right information out of the core system and our ability to create customised reports timeously was limited. We needed a solution that would remove the strain from our operational systems and enable simpler, faster reporting."

The bank approached BI Planning Services (BIPS), a BI solution provider, to help to provide some answers. "We needed the assistance of a company that would understand the urgency and criticality of our situation, and could meet our requirements," says Walton. "As we already knew that what we needed would not be a textbook solution, we wanted a solution provider that would understand our operational system and had significant experience in manipulating and managing data."

Says Jannie Strydom, director at BI Planning Services: "We have considerable experience in the design and development of data warehouses and business intelligence solutions across multiple industry sectors using different technology solutions. African Bank needed a system that would cater to fast-changing reporting requirements as well as its extraordinary growth. The solution that we assisted African Bank to put in place is an evolutionary one, which is tailored for ongoing growth and development."

For African Bank the first order of business was to create a separate datastore that made use of the data collected by the AS/400 transactional system, rather than rely on this system directly to produce reports.

"The AS/400 is a difficult system to work with," explains Walton. "To write a query the IT professional must understand the system and the software. For example, to obtain a single figure to satisfy a seemingly simple query such as 'has Joe Bloggs repaid his loan this month', at least nine different totals must be aggregated. The datastore we designed thus had to aggregate the data from the AS/400 system to provide useful totals that could easily be used in the creation of standard and ad hoc reports."

BIPS built a data warehouse for African Bank based on Microsoft SQL Server. Microsoft's Data Transformation Services (DTS) was used to extract data from the AS/400 system, then transform and load it into the data warehouse. Queries for standard reports were written in SQL, with African Bank developing an in-house Management Information System (MIS) team with data warehousing and SQL skills to maintain and develop the solution as well as cater to the bank's daily reporting needs. "Most departments now have a dedicated MIS professional that caters to their requirements and will ensure that daily reports are extracted from the data warehouse," says Walton.