Translated by
2010/10/03 18:35:19

Data warehouse

From the conceptual point of view the data warehouse is, first of all, a uniform transparent source of data on business of the company for its management.

The directory of BI solutions and projects is available on


In the stated above determination all parts[1] are equally important]:

  • The source should be uniform. Any modern company has a set of IT-systems. In case of banks it can be the core banking system, processing, separate CRM and ERP systems. Each of the listed systems is optimized under work with continuous data therefore the historical data which are saved up by these systems often lie a dead load. That the IT-system could hereinafter be referred to as the data warehouse — it should integrate in itself all necessary data on different aspects and the temporary periods of activity of the company.

  • The source should be focused on business of the company. Such systems as CRM ERP, the core banking system or processing, have own data structures having an impact on business processes of the company. At the same time in development of the company of the version and the CRM, ERP brand and processing can change. Therefore data structures of storage should reflect business of the company, but not specifics of the separate used systems. The storage should contain all necessary data on basic business entities: "account", "ATM" or "client". At the same time the data storage format should be independent of a type and the version of a system data source.

  • The source is intended for company management (average and senior management). While the systems of processing are focused on the maximum accuracy of the most elementary data (transactions, etc.), the data warehouse should provide the data aggregated conveniently for the informative analysis. The data warehouse is not used for the solution of a problem of search of separate transactions. It is intended, for example, for the analysis of profitability of separate banking products throughout history and operational change tracking in dynamics of sales on all branches.

  • The source should be transparent and clear for users. As it was mentioned earlier, all data which can be placed in storage are usually already available in other IT-systems of the company. However the cumulative analysis of these data in their initial representation requires considerable technical knowledge of means and techniques of work with data. The data warehouse should be a simple and clear source of information for creation of the different integrated reporting, KPI (key performance indicators) and application of means of Data mining.

Short result of the stated above provisions: the data warehouse is a method to turn the various data obtained and received via IT-systems of the company into the powerful and effective tool of the operational analysis and business management.

Application of data warehouses

How exactly the data warehouse can be applied to business management optimization?

There are three basic approaches supplementing each other:

  • Static and dynamic reporting. Existence of uniform source of data on business of the company allows as with the minimum costs to build static reports, and to freely create the new report types providing the new point of view on business of the company. In the market there is a set of solutions for creation of the reporting from the largest world IT manufacturing whose full application is possible only at existence of the full-fledged data warehouse. Microsoft SQL Servers belong to similar solutions, for example: Reporting Services, Crystal Reports, Oracle Reports.

  • Intelligent data analysis Data Mining Business Intelligence (and). Existence in storage of all data set about the history of business of the company allows analysts to use modern means of intelligent data analysis for search of the hidden patterns in behavior of clients/competitors. At the market of these means there is a set of the companies, from leaders of the IT market with universal solutions, such as Oracle BI or Microsoft SQL Server: Analysis Services, to the small companies with specialized industry solutions.

  • Real-time calculation of key performance indicators (KPI). In modern business the situation in the market can change very quickly. Timely tracking of similar changes and adequate response to them is a basis for survival and growth of the companies. The commonly accepted means of change tracking available to owners of data warehouses, creation of set of the key performance indicators (KPI) considered in real time is. For example, the amount of the funds withdrawn from ATMs of some area for the last day can be classical KPI for banking sector. The sharp jump of a similar indicator will allow to trace timely panic among investors. Existence of the full-fledged data warehouse allows the company to implement and support the most various KPI with the minimum labor and time expenditure.

Creation of data warehouses

The specifics of the data warehouse require presence at the IT specialists who undertook its implementation, a number of special skills and qualifications.

The list of the potential risks arising during creation of storage is listed below and the skills necessary for minimization of these risks are specified.

Possible risks during creation of storage

  • Orientation to the available technologies, but not on business. The data warehouse to be the successful tool, should reflect, first of all, business processes of the customer. It means that in development specialists in business of customer company should be the most directly involved. The attempt to implement storage on the basis of expansion of the available IT-system or third-party developments can lead to emergence of one more of a set of IT-systems which is not solving problems of storage. For minimization of this risk the contractor should have experience of the analysis of business processes, knowledge of data domain and ability to communicate with specialists from other areas.

  • Orientation to data, but not on their use. The data warehouse should not only contain information, but also do convenient its analysis / use. For achievement of this purpose at design of storage it is necessary to understand to what groups of tasks it will be applied. For minimization of this risk the contractor should have rich experience of creation of the reporting and technical data analysis. Only in that case it will be able correctly to predict requirements to the data warehouse proceeding from wishes of its future users.

  • Ignoring of complexity of ETL of processes. The data warehouse should be filled with data from the various external sources which often have the different nature. A part of data of the customer can a part — in Excel or XML files is stored in relational DB. Data of the different systems can duplicate each other or contradict each other. At the same time filling of storage to data is not single process, and should be executed regularly, for replenishment of storage by new data. This complex task can be adequate and is reliably solved only due to use of modern industrial ETL solutions, such as Microsoft SQL Server: Integration Services or Oracle Data Integrator.

  • Lack of understanding of the used technology of data storage. The data warehouse should not just store data, it should manage to be updated for the set periods of time and also to satisfy with requirements of the customer for generation rate of reports and accomplishment of requests. For compliance to this requirement the developer should have rich experience of implementation of considerable databases on this specific technology, whether it be Microsoft SQL Sever 2008, Oracle 11g or Teradata 12. The same data structure implemented on technologies of data storage from Oracle and Teradata, can have cardinally different parameters of speed of a response and level of availability.

Online data warehouse

Online storages allow to receive different types the statistician — management of work of the organization, receipt of funds and their turnover, types of interaction of the company, etc. Demand for solutions in the field of data warehouses in which the online functionality is provided grows. Such solutions give the chance on the basis of transaction data and information from different sources to create the broad and real picture of business in regime of Real-time for quality improvement and speed of the made decisions.

There are different methods of extraction of the data which changed from the moment of the last loading online: logical, on the basis of the analysis of the DB magazines, etc. Now in many Russian financial companies as data source a large number of the same systems is used.

The solution which works in real time should be always available. It becomes critical for the customer. Therefore it is important that the architecture of the online data warehouse included the special software allowing to shift automatically in case of failure processes on in parallel the standing server. Existence of this option depends on the customer's tasks.[1]


  1. 1,0 1,1 [ the Concept of data warehouses