Sberbank of the Russian Federation (Microsoft Business Intelligence)

Situation

The Sberbank of Russia has an extensive branch network through the whole country. For control of activity of bank structure of such scale, the system of collecting and the analysis of statistical reporting was developed. Indicators of statistical reporting gather according to the 'The sheet of forms of statistical reporting' approved by Board from all divisions of bank with the set frequency. Collected statistical data are controlled by means of the economic and financial performance calculated on the basis of accounting records.

During collecting statistical data, each separate branch of Sberbank solves several problems at once. First of all is a data collection from subordinate divisions (according to hierarchy of Sberbank) and import of data from own systems. Further control of collected information and sending results to higher division is carried out.

"For the purpose of management of all structure of Sberbank, it is very important that the analysis of the reporting and providing management information it was performed quickly and simply", - Yury Savinov, the deputy chief of IT of division of Sberbank of the Russian Federation notes.

Techniques of aggregation of balance sheet items, the profit and loss statement and also calculation on the basis of data retrieveds of economic indicators, are developed and affirm financial management of bank. Earlier algorithms of aggregation of balance sheet items and the profit and loss statement were implemented in the form of Microsoft Excel macroes which generated SQL database requests.

As macroes were stored directly in report files, to accompany such system very difficult. In bank there are more than 5000 reports for control of the collected statistical data and in case of change of calculation procedures, an old system did not allow to make changes quickly.

An old system had a number of problems which affected efficiency of calculations, so and flexibility of management of bank structure. First of all, data analysis involved thousands of documents, at the same time amount of data requiring processing, made several hundred gigabytes and required a set of hours on loading and processing.

"An old system had no sufficient flexibility in order that it was possible to arrange quickly it under difficult business processes of exchange and data validation. Besides, a system obviously lacked performance, and for calculation of one aggregated indicator aggregation of 50 million remaining balance on balance accounts of divisions of Sberbank could be required", - Yury Savinov says

In an analysis result of the existing problems before department of automation of Sberbank there was a task to organize high-quality and fast calculation of aggregated data on the basis of accounting records. At the same time the flexibility of a system in relation to changes and its high performance was an important condition of project implementation.

Solution

Implementing solution was carried out with assistance of Microsoft Consulting Services. A reference platform for the new system of formation of the assets/liabilities aggregated by article, an income/expenses it was selected by the Microsoft SQL Server of 2005. For automation of development process, a statement and replication of a technique of aggregation the balance sheet item and OPU created a separate subsystem: the analysis of assets/liabilities on the basis of the Microsoft SQL Server of 2005 Analysis Services.

"Employees of Financial management received the tool automating process of formation of the description of a technique of aggregation of data of accounting records", - Yury Savinov says.

Thanks to opportunities of the Microsoft SQL Server of 2005 Analysis Services the OLAP cube was constructed and appropriately configured and also the procedure of its filling with data is provided. At the same time the data coming to a cube are taken by a system from the uniform aggregated base.

As a result of implementing solution Financial management can receive the aggregated report of the set type on the basis of more than 100 million indicators within 3 minutes. Speed of calculation of the aggregated indicators will allow to implement calculation of the aggregated groupings on the basis of a srednekhronologichesky remaining balance, - Yury Savinov estimates the speed of work of a new system

A system is used by the responsible person of Financial management of Sberbank of Russia for calculation of data by preparation of the project of the Technique "About the aggregated groupings of balance sheet items, the Profit and loss statement and a method of calculation of economic indicators" on the basis of the existing chart of accounts. For users of Financial management whom a system is expected it is important to work at the description of a technique of aggregation in terms of balance accounts, groupings of balance accounts, but not in terms of dimensions and values. Therefore by the Microsoft SQL Server it was most hidden. Within system implementation processes of creation, storage and change of groupings which gave to a system flexibility unavailable earlier in change of rules of calculation of groupings were formalized.

The designer of groupings existing earlier was rewritten through Microsoft Visual Studio Tools for Office. As a result of work of the designer the description of grouping in the XML format is created.

Unlike an old system when forming articles of assets/liabilities and income/expenses where all process of creation of grouping was a whole, a new system means strict separation into stages. The first selected stage is preserving of the created grouping in a XML-format. The next stage is the generator receiving the description of grouping on an input and making on it a set of requests to an OLAP cube of the Microsoft SQL Server 2005. Based on accomplishment of requests the closing statement in the Microsoft Excel file forms.

"The implemented system should solve the specific objectives connected with receiving groupings of balance accounts. It was necessary to simplify as much as possible a user interface that work with a system could normal business - users. Actually, the user works with Microsoft Excel and in it receives the answer", - Yury Savinov comments.

The created system of providing the management reporting completely answers the tasks set for implementing solution. The data obtained by means of the system of collecting of the reporting and providing management information are used by the most different divisions of Sberbank for control of the reporting of other type and acceptance of important management decisions by central office.

Advantages

The implemented solution allowed to increase significantly the speed of obtaining important integrated characteristics of reports. Time necessary for data acquisition was reduced several times.

"Existence in a new solution of a flexible system of settings and high speed of calculation of data allowed employees of Financial management to describe in optimum short terms a new technique of aggregation by the balance sheet item, taking into account the new accounting rules introduced by the Bank of Russia since January, 2008 and in a short time to receive the necessary management reporting", - Yury Savinov sums up the results

The implemented solution allows Financial management and other divisions of Sberbank to expand a circle of problems which will be solved by means of the system of collecting of statistical reporting. In particular, the implemented analysis system of financial statements is also a method of verification of collected data.

"The bank is money therefore it is always necessary to have a method of check of digit in the report to guarantee its correctness. Such forms (in which control with balance is required) very much and, moreover, they need to be controlled by the techniques approved by bank. We can change method of calculation of indicators as often as it is necessary for us – all changes happen on SQL Server 2005 and, unlike the system based on macroes do not affect in any way files with which the user works. We directly connect the user to a cube, and the same data which are cubed, it can look and group in the Microsoft Excel interface", - Yury Savinov specifies.

Besides, use of products of Microsoft allowed to create a system with which not prepared user can work even.

"Huge plus of the Microsoft SQL Server of 2005 is the absolute transparency of its work for users. Users work with well familiar Microsoft Excel and use its opportunities for creation of analytical reports. The high performance of the Microsoft SQL Server and the flexible system of setup of an algorithm of calculations of the aggregated indicators implemented in a new system allow analysts of bank independently (without employee involvement of IT of divisions) to model algorithms of aggregation of accounting records for data acquisition, management decisions, necessary for acceptance", - Yury Savinov comments.

In general the solution completely corresponds shown at a stage of its formation of the requirement. According to plans of Financial management for further development of functionality of the IT division system plans use of new opportunities of products of Microsoft.