Credit Europe Bank (Microsoft Business Intelligence)

The centralized system of the business analysis became for bank a source of operational and reliable information. Using 250 ready reports users of a system are analysts, managers, heads of branches and departments — study data for any period and in the most different cuts that allows them to understand more deeply an essence of new market trends, to reveal potential violations (fraud), to manage activity of branches and divisions, to plan the development strategy.

The uniform and complete logical data model allows to record accurately value of each indicator that helps different divisions to interact better among themselves.

Daily through a corporate portal about 200 users address reports.

Situation

Development of the direction of bank retail requires deep and comprehensive study of the customer base and transactions made by clients. For understanding of the happening processes, detection of patterns and creation of forecasts are necessary effective tools of the business analysis. Originally employees of the bank were content with opportunities of Microsoft application of Excel, and Microsoft Access was applied to loading and preprocessing of the data arriving from the transaction systems. Users had access only to data for the last month or to pivot tables for the last year, they could not study parts of separate transactions and collect the unrolled statistics. These restrictions did not allow to reveal long-term trends and to build the exact forecasts necessary for portfolio management of banking services. Besides growth of number of clients and active development of the new business direction in service of plastic cards led to significant increase in information volumes — the record count began to be measured by millions. Former tools — normal office applications — any more did not cope with the increased computing loadings and did not allow to be engaged in data analysis effectively. The management of bank charged to the IT specialists to develop the full analytical solution. The Microsoft SQL Server platform became a basis for a BI system.

Solution

Originally possibilities of the Microsoft SQL Server of 2005 were used only for acceleration of data processing in Access. In 2008 the bank put into operation the first version of a BI system on the SQL Server platform which allowed to create a set of reports on the retail direction for unloading in Excel and mailings to the employees needing this information. Daily reports on the issued credits, on transactions in departments and offices of bank, on issue of credit cards, on deposits and accounts and also the monthly report on quality of the loan portfolio were the most demanded (for defaultness assessment in different cuts). As IT specialists of bank improved skills of work with SQL Server, all of them involved advantages of the platform more fully. Gradually user-friendly interface and functionality of Reporting Services allowed to refuse almost completely spreadsheets for benefit of the SQL Server environment.

Implementation of OLAP tools, allowed to carry out complex multidimensional data analysis became the next step in development of the BI solution. Every day the data arriving from the transaction systems are loaded into SQL Server storage and on their basis an OLAP cubes forms. As data sources the different banking systems working both at the SQL Server platform and with assistance of DBMS of other producers are used: a processing system, the application for formation of financial statements for the Bank of Russia, a system for internal accounting on the Diasoft platform and others. All data for the last ten years up to information for the last day are available to users to the analysis.

Most of users study data by means of ready reports — the internal portal where they are published, is implemented by means of Reporting Services. Group of analysts (about 40 people, on 1-2 employees from each division) work directly with an OLAP cubes, at the level of summary or detailed information, depending on the access provided to them. These users act as "conductors" of BI in the divisions and at the same time are customers on development of new cubes and reports. On regularly organized corporate trainings they get acquainted with new systems capabilities and state wishes on its development.

Development of cubes and reports is executed in the environment of Business Intelligence Development Studio, and tasks of loading and preprocessing of data are automated by means of Integration Services. Access control to data is exercised on the basis of roles which are configured and appropriated to users using the Active Directory integrated with SQL Server.

After end in 2010 migrations on the version of the Microsoft SQL Server 2008 R2 Enterprise Edition platform became available new functionality, including PowerPivot — the analysts working in different business divisions combine data from an OLAP cubes and local sources with its help.

Today the BI solution is unrolled on two virtual machines running Microsoft Windows Server 2008 R2 with Hyper-V role. On one of them (occupies 12 Gbytes of RAM and 1.5 Tbyte of the hard drive) SQL Server is used as the data warehouse and executes operations on loading and preprocessing of information. On the second (48 Gbytes of RAM and 200 Gbytes of the hard drive) works on the business analysis using services Reporting Services and Analysis Services are performed.

"The system of the business analysis created for service of the retail direction was very demanded by both employees, and the management. Quickly created clear and evident reports containing the freshest data and besides covering a bigger historical period, almost completely saved from need to carry out calculations to Excel. But the main thing, a system became a source of 'the uniform version of the truth': it provides observance of uniform methodology when calculating key indicators and helps to provide data in shape, clear to business and not allowing discrepancies. The management of bank estimated advantages which so qualitative and convenient analytics gives, and made the decision on replication of a system on other business directions — for service of corporate clients, a segment of SMB and plastic cards" — Mikhail Kasradze-Panasyan comments on project results.

Advantages

The centralized system of the business analysis became for "the CREDIT EUROPE of BANK" a source of operational and reliable information. Specialists of analysis department support the exhaustive and relevant description of all OLAP cubes, died them also measurements. It allowed to create a complete logical data model which is updated and extends depending on business needs. Thanks to such model users of a BI system had an opportunity to analyze data for any period and in the most different cuts: from the region and the city to the specific user who issued/approved the credit or made transaction. The uniform data model allows to understand correctly value of each indicator that helps them to avoid many errors in the analysis of the current situation and drawing up forecasts.

Developers created about 250 ready reports: about issue and repayment of the credits by clients of bank (physical persons and legal entities), sale of different types of banking services, about productivity of work of employees, financial and operational indicators on branches or the directions of business. Being based on the data provided to them, the management of bank controls efficiency of the made decisions, monitors market reaction on new banking products, gains an evident impression about development of the separate directions of business.

"Daily through a corporate portal about 200 users — analysts, managers, heads of branches and departments address reports. Now they can understand more deeply an essence of new market trends, reveal problem cases and potential violations (fraud), perform calculations with the organizations for the issued credits, manage activity of divisions, plan development, and many other things. Based on performance indicators of work of divisions and individual employees the system of motivation and bonuses is under construction, business objectives the next month are formulated" — Alexey Lichenko, the head of the department of analytics and strategic planning of CREDIT EVROPA BANK Ltd tells

Prospects

The bank intends to continue development of a system of the business analysis: successfully to manage all directions of banking business, scaling of the solution is going to perform management and to implement it in other divisions. In the next half a year by request of marketing department on the basis of SQL Server the specialized storage of the not personified data arriving from the processing system of card business will be created. Their analysis will help to study a customer behavior, to profile the customer base, to develop marketing campaigns and to develop cross-sellings.

Within a year it is supposed to perform migration on the new version of Microsoft SQL Server 2012 that will allow to master means of dynamic data visualization. In particular, the evident interactive reports created using PowerPivot will facilitate acceptance of crucial management decisions. For convenience of conducting joint work it is going to unroll on the SharePoint platform the portal where the graphic panels displaying a current status of key indicators of business in the form of indicators will be placed. There will be means for formation of the preconfigured reports and mailing to different user groups. New tools for quality improvement (data quality) and deep data analysis (data mining) are planned to implementation. Besides, the fault tolerance of the solution due to use of AlwaysOn technology will be improved.

Software

Microsoft SQL Server 2008 R2

Microsoft Windows Server 2008 R2 Enterprise SP1