Data Warehouse and Information System for Alfa-Bank
The system is designed to automate generation of analytical and prudential reports, and analytical accounting of incomes and expenses, as well as facilitate management decisions. The purpose of the system is to raise the efficiency of the bank's divisions that are engaged in accounting, minimize manual operations, and eliminate duplication of operations and mistakes.
The reporting and accounting processes include receiving of data from internal and external information sources, data aggregation and calculation, data categorization and accumulation, quick search and filtering, generation of regular and ad hoc reports in different views, and online analytical processing (OLAP).
Reporting was not sufficiently formalized and automated. A single information source was missing. Different results were obtained when building the same report.
The joint IBA–customer team performed the following:
1. The data warehouse was logically divided into data marts (topic areas). Currently, the following marts are singled out:
- Balance statements of the banks of Belarus
- Balance statement of Alfa-Bank
2. For each mart, dimensions were defined (reference information). The basic dimensions are:
- Balance (non–balance) accounts
- Personal accounts
- Banks of Belarus
- Time scale
- Balance hierarchies (resident/non–resident, by property type, by currency)
- Location of Alfa-Bank branches
- Alfa-Bank branches (central and field offices)
- Contract types (products)
- Credit contracts
- Liability types
- Contract types of the credit department
- The basic fact data of the data warehouse are:
— Debit balance (assets) in foreign currencies and rubles
— Credit balance (liabilities) in foreign currencies and rubles
— Debit turnover in foreign currencies and rubles
— Credit turnover in foreign currencies and rubles
— Account balance (assets plus liabilities) in foreign currencies and rubles
— Debit rate for personal accounts
— Credit rate for personal accounts.
3. A procedure to load data from data warehouse sources was defined.
4. A data warehouse was developed based on Oracle 10G and processes of data loading to the warehouse (ETL) were implemented. A data analysis system based on IBM Cognos BI 8.4 was implemented.
The project resulted in the creation of a central data warehouse that serves as a single information center for planning, accounting, and budget control and analysis of the bank. Using the new application, it is possible to generate regular and ad hoc reports, i.e. build reports using data warehouse objects described in business terms. The application supports visual presentation tools such as drawings, graphs, diagrams, bar charts, spreadsheets, and accounting units, as well as full–fledged printed reports.