Alexander N. Podobry, Vladimir A. Maklaev

Ulyanovsk State Technical University, Russia

Approach to Uniform Integration of Corporate Data Warehouses for Designing of Computer-Aided Systems

An integrated information system of Company management shall have, as an information basis, a common distributed database where every user with the requisite access rights may promptly receive data from different information sources. The designing of effective and secure information infrastructures is to be based on applying of formal models for metadata structuring, functioning and isolation of access to information resources.

The integration of corporate data warehouses requires the designing of a common cloud to represent the information on implementation of a product-lifecycle stage, and the designing of an experience base for a Design Organization. For this reason, a federated architecture for data-storage is to be designed. This type of architecture will allow isolating data warehouses taking into account the needs and the purpose of computer-aided and information systems, and combining them due to a common structure of interconnections. To design this architecture, basic components of information and computer-aided systems are to be sorted out, that is, corporate data warehouses are to be uniformed. The uniform representation of corporate data warehouses and their descriptions in metadata will allow automating at maximum the design of services to perform sorting out and to process data sets which meet the defined conditions and constraints. The basis for the suggested integration of information resources of computer-aided systems forms the data sets, which are stored in independent corporate data warehouses.

The information system, per se, is a set of objects interacting with each other. On that premise, the following main object types can be sorted out:

- documents – an information, fixed in the information system;

- reference books – an object for storage of data with the same structure and in list-form;

- Registers – tables for on-line data accumulation and a summary acquisition;

- Constants – reserved values for variables.

Each object type includes the list of superclasses, i.e., components of the same type. A superclass includes components describing this object. These components may refer to a source object and represent the dependence between a subclass’s components and another component, which is the superclass one. On this basis, a diagram of dependencies between information objects is proposed (fig.1).

Fig. 1 Uniform Structure of Information Systems

A majority of information and computer-aided systems measures up to the represented description in terms of “document” and “reference book”.

Thus, in the given example of “1C 7.7” Corporate Information System, the document “Product Order” has the field “Product” which refers to the reference book “Products” where objects refer in turn to another reference book, etc.

There are three main tables for metadata storage at the database level that are suggested (fig.2):

- META_SYSTEM_PARAM – list of information systems

- META_OBJECT – list of objects of information systems and their interconnections

- META_DICT – reference book for interpretation of metadata-structure characteristics

Fig. 2 Structure of Metadata at Database Level

The reference book for lists of information systems includes the name, description and accounting data to link to a system’s data warehouse. The table of a list of information systems’ objects represents an undirected graph wich has, as a point, an object of the information system, and, as an edge, a linkage between objects.

On the basis of this metadata structure, a query to data sets may be formed taking into account the structure for writing queries in Transact SQL. The major components of this language are: Select, Join, and Where (fig. 3).

Fig. 3 Structure for Designing SQL-Query Based on Metadata

The component Select is formed on the basis of the fields, discribed in META_OBJECT table, and is interpreted in LINK_OBJ attribute. The linkage table Join is formed based on the reference fields LINK_DICT и LINK_DICT_COL. The parameter table Where includes the conditions applied to the fields’ sets to limit the output of data. After the major components of the query generator are populated, a SQL-function appears, allowing appplying functions to manupulate data sets. As a result of the described manupulations, we have the final Transact SQL query text referring to independent corporate data warehouses.

The full information on the final query is stored in the table of metadata and represents the fields’ sets and their linkage with other objects, both within a corporate data warehouse, and with another warehouse (fig.4).

Fig. 4 Storage of Information at Database Level

The remaining two components of the query design may be applied to the final query: the storing and grouping tables are implemented as in the Transact SQL language.

Thus, taking into account the peculiarities of a manufactuing enterprise, the result of integration may be shown as the data representation module which gives information both to design the experience base for the Design Organization that is implemented on the basis of a set of facilities, called WIQA (Working In Questions and Answers) [4, 5] which is to serve the workflows “Experience Interaction” at the stage of a concept design of a CAS (computer-aided system); and to track the product lifecycle. This module receives information from the main corporate data warehouses on the basis of metadata (fig. 5).

Fig. 5 Structure for Integration of Uniform Corporate Warehouses

The data representation module may be shown as a service allowing sorting out and processing of data according to the predefined conditions and constraints. Based on the module’s structure, implemented on the basis of MS SQL, data may be represented both in a form of data sets and as Web-pages.

These services represent an accumulated “knowledge base”, i.e., data library available for everybody’s use. That is, evey task under solution may represent a groundwork for other tasks. Thus, the approach given meets main requirements of SOAs (service-oriented architectures). The result of the service work may be represented by “data marts”, i.e., a set of the data required to resolve a particular task.

To this point, the corporate data warehouses have been uniformly integrated, at that, the warehouses’ objects have been included into the metadata structure. We have represented a diagram of data integration by example of a manufacturing enterprise. The data representation module implemented on the basis of MS SQL Server DBMS (database management system) has been described. The structure for data storage allows using OLAP data representation methods and including new independent data warehouses into this structure.

BIBLIOGRAPHY

1. ISO/IEC Std 2382-1

http://www.morepc.ru/informatisation/iso2381-1.html

2. Lisin, Nikolay. G. Patchwise Automation or How to Manage a “Zoo” of Software Programs //BYTE Publishers, 2009

http://www.bytemag.ru/articles/detail.php?ID=14862

3. Voroysky, Felix S. Informatics. New Methodical Reference Dictionary (Introduction to Informatics and Computer Science in Form of Terms). – 2^nd Revised and Enlarged Edition, – “Liberia” Publishers, 2001.

4. Maklaev, Vladimir A., Andrey A. Pertsev. Regulations Professional Maturity of a Design Organization. – Ulyanovsk: Ulyanovsk State Technical University, 2012 – 300 p.

5. Sosnin, Petr I. Question-and-Answer Programming of a Man-Machine Interaction. – Ulyanovsk: Ulyanovsk State Technical University, 2010 – 240 p.