Method for forming multi-dimensional data in the information financial and economic system at the enterprise of state space corporation “Roscosmos”

One of the main requirements for financial and economic systems is the ability to quickly obtain relevant analytical information needed to make operational management decisions and the generation of comprehensive detailed reporting. This task is especially urgent for the enterprises of the state corporation Roskosmos, which operate under the state defense order and are obliged to keep separate accounting of costs for various projects in their economic activities. Information support for accounting and management accounting is based on the rules for storing and processing data from primary source documents registered in accounting systems, their connections and the processes of forming new information accounting objects. Data in different areas of accounting is nothing more than derivatives of primary documents or a set of primary information. In the process of redistribution and consumption of data in certain areas of accounting, information links between various derivatives from one base may be lost. Information objects begin to "live their own lives" and generate new derivatives. With information gaps, the possibility of differentiating the current derivative to the primary source of data (foundation) and analyzing data related to it within the framework of technological and economic processes is lost. The mathematical solution of such problems, leading to a logically built chain of operations and the possibility of accounting automation, is the subject of research by many scientists and practitioners working on the creation of information support systems for financial and economic tasks in enterprise management.

Research Analysis

The leading place in solving this class of problems is occupied by OLAP technologies [1] (OLAP -online analytical processing, online analytical processing). Systems that implement OLAP must comply with the so-called FASMI principles [2]: Fast (fast system response), Analysis (full-featured analysis), Shared (multi-user access to data), Multidimensional (multidimensional conceptual representation of data), Information (obtaining information in the right volume where needed). As you can see, based on these principles, the requirements for a reporting system, which can really be called an OLAP system, are very strict. The reports should be built on millions of records in seconds, just as quickly disclosed by the analysts of interest to the user, filtered and regrouped [3].

The most common approach in the implementation of OLAP systems is the separation of data between two databases: transactional (OLTP - online transaction processing) and analytical (OLAP). At the same time, the OLTP database is optimized for fast data entry, and OLAP for the fast construction of complex reporting. In fact, an OLAP database is usually built according to a special architecture and contains pre-calculated aggregate data, which ensures high query execution speed. The price for this is the need to synchronize OLTP and OLAP. Since such a process, as a rule, is periodic, there is a delay between the appearance of data in the operational database and analytical data. This approach is implemented in almost all modern BI systems (business intelligence - collection and analysis of business information): Microsoft Analysis Services, Oracle OLAP, IBM Informix, Arbor Essbase, etc.

Another approach to solving problems of online analytical data processing is to embed OLAP tools directly into the OLTP storage. This is achieved both by using special data elements (for example, operational accounting registers in the 1C: Enterprise platform [4]) and optimal (from the point of view of optimizing the execution of OLAP queries) data schema structuring. This approach formed the basis of the financial and economic task management system developed at the enterprise (FES ACS) [5] as an effective alternative to complex and expensive BI systems.

Approach description

The structures and algorithms underlying the ACS FES allow you to effectively combine the flexibility and speed of a transactional database (DB), on the one hand, and the ability to build complex reporting in a reasonable time, on the other. Further, the article provides an overview of the technical implementation of the data warehouse using the example of the "Purchases" subsystem, which is responsible for accounting for goods, works and services that an enterprise acquires to create its own sales objects.

The core of the "Purchases" module consists of 5 tables built in accordance with the rules of normal forms [6; 7], and is shown in fig. one:

– POK_PRIH – receipt documents for inventory items, works, services;

– POK_OSN – basis documents for VAT accounting (incoming invoices);

– PO_SOGL – documents-grounds for payment (acceptance-consent of responsible employees to pay for inventory items, works, services);

– POK_OPL – payment documents;

– POK_SW – link table, contains links in the form of foreign keys to the primary keys of records from the tables listed above (ID_PR – to the POK_PRIH table, ID_OSN – to the POK_OSN table, ID_TS – to the POK_SOGL table, ID_OPL – to the POK_OPL table).

POK_PRIH								POK_OSN
PK	ID_PRIH	NUMERIC(10;2)						PK	ID_OSN	NUMERIC(10;2)
	WID_DOC NUM_DOC DATE_DOC REG_NUM_DOC REG_DATE_DOC COD_PRED COD_VAL SUM_DOC DOC_COD_PRED DOC_COD_VAL DOC_SUM_DOC DOC_PROC_NDS DOC_SUM_NDS RUB_SUM_DOC RUB_SUM_NDS COR_NUM_DOC COR_DATE_DOC PATCH_NUM_DOC PATCH_DATE_DOC BUH_PERIOD IN_BOOK DATE_OTGR USR DTR	CHAR(10) CHAR(30) DATETIME INTEGER DATETIME INTEGER INTEGER NUMERIC(10;2) INTEGER INTEGER NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) CHAR(30) DATETIME CHAR(30) DATETIME INTEGER INTEGER DATETIME CHAR(10) DATETIME							WID_DOC NUM_DOC DATE_DOC REG_NUM_DOC REG_DATE_DOC COD_PRED COD_VAL SUM_DOC DOC_COD_PRED DOC_COD_VAL DOC_SUM_DOC DOC_PROC_NDS DOC_SUM_NDS RUB_SUM_DOC RUB_SUM_NDS COR_NUM_DOC COR_DATE_DOC PATCH_NUM_DOC PATCH_DATE_DOC BUH_PERIOD IN_BOOK DATE_OTGR USR DTR	CHAR(10) CHAR(30) DATETIME INTEGER DATETIME INTEGER INTEGER NUMERIC(10;2) INTEGER INTEGER NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) CHAR(30) DATETIME CHAR(30) DATETIME INTEGER INTEGER DATETIME CHAR(10) DATETIME
			◄----	POK_SW			-----►
				PK	ID_SW	INTEGER
				FK1 FK2 FK4 FK3	ID_PR ID_OSN ID_TS ID_OPL SUM_DOC COD_VAL REF1 REF0 REF_STORNO COD_PRED ID_DOG ID_ZATR NUM1 NUM2 NUM3 NUM4 ID1_PR ID1_OSN ID1_TS ID1_OPL PR_BUH USR DTR	NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) INTEGER NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) INTEGER INTEGER INTEGER INTEGER INTEGER INTEGER INTEGER NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) CHAR(30) DATETIME
POK_SOGL			◄----				-----►	POK_OPL
PK	ID_SOGL	NUMERIC(10;2)						PK	ID_OPL	NUMERIC(10;2)
	WID_DOC NUM_DOC DATE_DOC REG_NUM_DOC REG_DATE_DOC COD_PRED COD_VAL SUM_DOC DOC_COD_PRED DOC_COD_VAL DOC_SUM_DOC DOC_PROC_NDS DOC_SUM_NDS RUB_SUM_DOC RUB_SUM_NDS COR_NUM_DOC COR_DATE_DOC PATCH_NUM_DOC PATCH_DATE_DOC BUH_PERIOD IN_BOOK DATE_OTGR USR DTR	CHAR(10) CHAR(30) DATETIME INTEGER DATETIME INTEGER INTEGER NUMERIC(10;2) INTEGER INTEGER NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) CHAR(30) DATETIME CHAR(30) DATETIME INTEGER INTEGER DATETIME CHAR(10) DATETIME							WID_DOC NUM_DOC DATE_DOC REG_NUM_DOC REG_DATE_DOC COD_PRED COD_VAL SUM_DOC DOC_COD_PRED DOC_COD_VAL DOC_SUM_DOC DOC_PROC_NDS DOC_SUM_NDS RUB_SUM_DOC RUB_SUM_NDS COR_NUM_DOC COR_DATE_DOC PATCH_NUM_DOC PATCH_DATE_DOC BUH_PERIOD IN_BOOK DATE_OTGR USR DTR	CHAR(10) CHAR(30) DATETIME INTEGER DATETIME INTEGER INTEGER NUMERIC(10;2) INTEGER INTEGER NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) NUMERIC(10;2) CHAR(30) DATETIME CHAR(30) DATETIME INTEGER INTEGER DATETIME CHAR(10) DATETIME

• Fig. 1.    Module “Purchase” data scheme

Рис. 1. Схема данных модуля «Покупки»

The proposed approach to data structuring allows you to perform a wide range of analytical processing tasks without preliminary data preparation or the use of intermediate representations. For a visual description, let us formalize them [8],then we will consider several real examples of such tasks solved within the framework of the "Purchases" subsystem. So, let the database scheme ℜ = {R1, R2, ..., Rk} be specified, obtained as a result of the normalization of relations [6; 7]. The relations Ri are defined on the set of attributes U = {A1, A2,..., An}. Let [Ri] be a relation scheme, a set of attributes on which the relation Ri is defined. Suppose that the scheme ℜ is reduced [7], i.e. there are no two relations such that [Ri] ⊆ [Rj], in ℜ is i ≠ j.. Tuple t[X] is a set of attribute values Aj ∈ X ⊆ [Ri] Aj specified in tuple t ∈ Ri. The NULL value of attribute t: t[Aj] = NULL is not equal to any other value, including another null value.

Многомерное представление будем задавать в виде совокупности размерностей { D 1 , D 2 , …, D d }, где D l – множество расширенных имен атрибутов: R i A j , A ϕ ∈ [ R i ]; M – множество мер, также заданных в виде расширенных имен атрибутов. Значения D l являются значениями координат гиперкуба, значения M будут располагаться в рабочей области гиперкуба. Для каждой размерности задается ограничение в виде логической формулы F l .

We will define a multidimensional representation as a set of dimensions { D 1 , D 2 ,…, D d , where D l is the set of extended attribute names: R i A j , A ϕ ∈ [ R i ]; M is a set of measures, also specified as extended attribute names. The D l values are the hypercube coordinate values, the M values will be located in the hypercube workspace. For each dimension, a constraint is specified in the form of a logical formula F l .

Example 1. It is necessary to find payment data (any transfer of funds) to an enterprise with code 12345 under project 111 and supply contracts 222, 223, where funds were paid from 01/01/2021 to 02/28/2021 according to budget planning items (BPS) 333, 334 and received goods/works/services in the period from 01/01/2021 to 01/31/2021.

In order to simplify the formulation and implementation, in this and subsequent examples, attributes not used in the selection are omitted. Thus, within the framework of the described database schema, we have the following subset of attributes: A1 - connection identifier, A2 - record identifier on receipt of goods/works/services, A3 – payment record identifier, A4 – company code, A5 – project identifier, A6 – supply contract identifier, A7 – SBP, A8 – receipt date, A9– payment date, A10– payment amount. The following functional dependencies exist here: DEP = {A1 → A2A3A4A5A6A7, A2 → A8, A3 → A9A10}. Based on this subset, we obtain a simplified relationship scheme: Document links = R1(A1, A2, A3, A4, A5, A6, A7), Receipts of goods/works/services= R2(A2, A8) , Payment documents = R3(A3, A9, A10), where key relationship attributes are highlighted in bold. One of the possible representations of the hypercube is given in table 1.

Table 1

Payment data

Enterprise code	12345
Project	111
Supply contract	222		223
BPS	333	334	333	334
Date	Sum	Sum	Sum	Sum
21.01.2021	75000	250000	357500	65500
26.01.2021	31000	310000	785000	96000
28.01.2021	70000	870080	6500	55000

Dimension attributes are in bold, fact attributes are in italics, and attribute values are in regular font. Diagram of the hypercube in Table. 1 can be represented as follows:

{ R 3 . A 9 } × { R 1 . A 4 { R 1 . A 5 { R 1 . A 6 { R 1 . A 7 ( R 3 . A 10 )}}}}, where D 1 = { R 3 . A 9 } и D 2 = { R 1 . A 4 , R 1 . A 5 , R 1 . A 6 , R 1 . A 7 } – measurements, M = { R 3 . A 10 } – facts. Logical constraint: F = ( R 1 . A 2 ≠ NULL ∧ R 1 . A 3 ≠ NULL ∧ R 1 . A 4 = 12345 ∧ R 1 . A 5 = 111 ∧ ( R 1 . A 6 = 222 ∨ R 1 . A 6

= 223) ∧ ( R 1 . A 7 = 333 ∨ R 1 . A 7 = 334) ∧ R 3 . A 11 >= 01.01.2021 ∧ R 3 . A 11 <= 28.02.2021 ∧ R 2 . A 8 >= 01.01.2020 ∧ R 8 . A 8 <= 31.01.2020).

The corresponding SQL query is shown in fig. 2.

SELECT pok.cod_pred AS pok_cod_pred, -- enterprise pok.id_zatr AS pok_id_zatr, -- project pok.id_dog AS pok_id_dog, -- contract pok.num4 AS pok_num4, -- budget plan item opl.date_doc AS opl_date_doc, -- payment document date opl.sum_doc AS opl_sum_doc -- payment document amount

FROM pok_sw pok

LEFT JOIN pok_opl opl ON pok.id_opl = opl.id_opl

LEFT JOIN pok_prih pr ON pok.id_pr = pr.id_prih

WHERE pok.id_opl IS NOT NULL -- money was paid

AND pok.id_pr IS NOT NULL -- goods/works/services were received

AND pok.cod_pred = 12345 -- data for the enterprise with the code

AND pok.id_zatr = 111 -- for project with code 111

AND pok.id_dog = 222 -- under contract with code 222

AND pok.num4 = 333 -- the payment was by SBP with code 333

• Fig. 2.    SQL query of payments data to the enterprise with the specified analytics

Рис. 2. SQL запрос данных оплат предприятию с указанной аналитикой

Using the described database scheme DB, the request includes any other measurements and facts (data and analytics of payments, invoices, acceptances, receipts etc.) in a similar way, allowing you to track all the movement (documents, operations) on a given or other logical constrain

Example 2. It is necessary to find the receivables (the amount of debt) to the enterprise with code 12345 for delivered goods/works/services under projects 111 and 112.

The minimum subset of attributes is specified: A 1 – communication identifier, A 2 – record identifier for the receipt of goods/works/services, A 3 – payment record identifier, A 4 – enterprise code, A 5 – project identifier, A 6 – supply contract identifier, A 7 – receipt document number, A 8 is the amount of the receipt document, A 9 is the reporting period. On this set of attributes, there are the following functional dependencies: DEP = { A 1 → A 2 A 3 A 4 A 5 A 6 , A 2 → A 7 A 8 A 9 }. We get the relationship scheme: Document links = R 1 ( A 1 , A 2 , A 3 , A 4 , A 5 , A 6 ) , Receipts of goods/works/services = R 2 ( A 2 , A 7 , A 8 , A 9 ). One of the possible representations of the hypercube is given in Table. 2.

Diagram of the hypercube in Table. 2 can be represented as follows:

{R2.A7} × {R1.A4{R1.A5{R1.A6{R2.A9(R2.A8)}}}}, where D1 = {R2.A7} и D2 = {R1.A4, R1.A5, R1.A6, R2.A9} – measurements, M = {R2.A8} – acts. Logical constraint: F = (R1.A2 ≠ NULL ∧ R1.A3 = NULL ∧ R1.A4 = 12345).

Table 2

Enterprise code	12345
Project	111				112
Supply contract	222		223		224		225
Reporting period	333		334		333		334
№ doc.	I/2020	II/2020	III/2020	IV/2020	I/2020	II/2020	III/2020	IV/2020
11	75000	21500	250000	784200	357500	650800	65500	48000
12	31000	79200	310000	58100	785000	130000	96000	51700
13	70000	623300	870080	3700	6500	210000	55000	3580

Debit debt

An example of the corresponding SQL query is shown in fig. 3.

SELECT pok.cod_pred AS pok_cod_pred, -- enterprise pok.id_zatr AS pok_id_zatr, -- project pok.id_dog AS pok_id_dog, -- contract prih.num_doc AS prih_num_doc, -- receipt document number prih.sum_doc AS prih_sum_doc, -- receipt document amount prih.cod_val AS prih_cod_val, -- currency code of the receipt docu ment prih.buh_period AS prih_buh_period -- accounting period FROM pok_sw pok

LEFT JOIN pok_prih prih ON pok.id_pr = prih.id_prih

WHERE pok.id_pr IS NOT NULL -- there was a receipt of goods (the presence of a receipt document)

AND pok.id_opl IS NULL -- there was no payment

AND pok.cod_pred = 12345 -- data for the enterprise with the code

• Fig. 3.    SQL data debit debt query on accounts for the specified company

Рис. 3. SQL запрос данных о дебиторской задолженности указанному предприятию

The result will be all lines for the enterprise under study, where primary receipt documents are registered, for all projects, supply contracts with possible payments in any currency. Here you can analyze which delivery was paid for by what, which acceptances, advances or payments were agreed, under which contracts, for whom, which invoices for tax accounting and when they should get into the books of purchases and sales, etc. An example of calculating the amount of payments to an enterprise is shown in fig. 4.

SELECT SUM(DECODE(NVL(pok.id_opl,  0),  0,  0, pok.sum_doc))  AS  sum_opl, pok.cod_val

FROM                                pok_sw                                pok

WHERE pok.id_pr IS NOT NULL -- there was an arrival of goods (the presence of                     a                     receipt                     order)

AND pok.cod_pred  =  12345     -- enterprise data with code 12345

GROUP BY pok.cod_val

• Fig. 4.    SQL debt amount query to the specified company

Рис. 4. SQL запрос суммы долга указанному предприятию

Example 3. Getting information about what goods/works/services were paid for by this document and on the basis of which agreement (acceptance) document it was done.

The minimum subset of attributes is specified: A 1 – connection identifier, , A 2 – record identifier for the receipt of goods/works/services, A 3 – agreement (acceptance) document record identifier, A 4 – payment record identifier, A 5 – enterprise code, A 6 – project identifier, A 7 – delivery contract identifier, A 8 – receipt document type , A 9 – receipt document number, A 10 – receipt amount, A 11 – acceptance number, A 12 – payment document number, A 13 – payment document date. There are the following dependencies: DEP = { A i ^ A 2 A 3 A 4 A 5 A 6 A 7 , A 2 ^ A 8 A 9 ( A 10 ), A 3 ^ A 11 , A 4 ^ A 12 A 13 } (three functional and one multivalued).

Rejection of this dependency will allow using meaningful (not key) attributes in dimensions and having several values (list) of an attribute in one hypercube cell R i A j e M .. Lists of values are used in data analysis when parameter values do not need to be correlated with objects.

We get the following relationship scheme: Document links = R 1 ( A 1 , A 2 , A 3 , A 4 , A 5 , A 6 , A 7 ),Receipts of goods/works/services = R 2 ( A 2 , A 8 , A 9 , A 10 ), Payment approval documents = R 3 ( A 3 , A 11 ), Payment documents = R 4 ( A 4 , A 12 , A 13 ).. One of the possible representations of the hypercube is given in table 3.

Таблица 3

Payment document data

Enterprise code	12345
Project	111
Supply contract	222				223
Receipt document image	Receipt note		Receipt order		Certificate of completed work		Receipt note
№ acceptance	Sum	№ Receipt document	Sum	№ Receipt document	Sum	№ Receipt document	Sum	№ Receipt document
11	75000	21	357500	24	250000	27	65500	30
12	31000	22	785000	25	310000	28	96000	31
13	70000	23	6500	26	870080	29	55000	32

Diagram of the hypercube in Table. 3 can be represented as follows:

{R3.A11} × {R1.A5{R1.A6{R1.A7{R2.A8(R2.A9)}(R2.A10)}}}, where D1 = {R3.A11} и D2 = {R1.A5, R1.A6, R1.A7, R2.A8} – measurements, M = {R3.A9, R3.A10} – facts. Logical constrain: F = (R4 .A12 = 987 л R4. A13 = 21.01.2021).

An example of the corresponding SQL query is shown in fig. 5.

The ACS module of the FEP "Sales" and other auxiliary modules are implemented in a similar way. Together, these modules form the core of the system.

The database schema was originally designed in accordance with the rules of normal forms [6; 7] and has the property of a lossless connection (LBI) in accordance with Theorem 5.8 [6]:

Let о be a decomposition of the relation Rformed by relation schemes in third normal form, and let also X be the key of R. Then т = о и {X} is a decomposition of R such that all its constituent relation schemes are in third normal form. This decomposition preserves dependencies and preserves the lossless connection property.

SELECT pok.cod_pred AS pok_cod_pred, -- enterprise pok.id_zatr AS pok_id_zatr, -- project pok.id_dog AS pok_id_dog, -- contract prih.wid_doc AS prih_wid_doc, -- receipt document type prih.num_doc AS prih_num_doc, -- receipt document number prih.sum_doc AS prih_num_doc, -- receipt amount sogl.num doc AS osn num doc - acceptance number

FROM pok_sw pok

LEFT JOIN pok_prih prih ON pok.id_pr = prih.id_prih

LEFT JOIN pok_sogl sogl ON pok.id_osn = sogl.id_osn

LEFT JOIN pok_opl opl ON pok.id_opl = opl.id_opl

• Fig. 5.    SQL query for payment data

Рис. 5. SQL запрос данных платежа

The resulting data cubes correspond to the formal rules considered in [14], in accordance with the “composite table” data model, which,in turn, is a generalization of the “semantic transformation” model [12] for the case of a list of values in one cell. This algorithm generates hierarchies in the dimensions of a hypercube using functional, multivalued dependencies of the source database and attribute hierarchies specified by the user.

The described method of storing and processing data allows you to efficiently perform both OLTP and OLAP operations without creating additional data structures and performing unnecessary transformations. This, in turn, leads to a significant reduction in overhead both in terms of the complexity of compiling queries and in terms of their execution time compared to the classic OLAP toolkit, contributes to a more optimal use of data warehouse resources, and avoids the need to store redundant data. The application of the described algorithm in OLAP systems allows to reduce the time for generating a schema of a new multidimensional data model, as well as to make the presentation of the model the most convenient for the user.

Conclusion

The proposed option for the formation and storage of data of primary documents on the economic activities of the enterprise provides a detailed separate accounting of financial and economic transactions of purchases and sales, and allows you to effectively solve the problems of building accounting, tax and management accounting. The use of the above schemes, the logic of storing and processing data for working with their various derivatives makes it possible to differentiate the elements of the chain of events (states) in accounting to primary sources linked in the link table in accordance with the logic of business transactions, which allows you to analyze data for any set of interest analytics. Thus, at any stage and section of accounting there are always integral information links. This provides the ability to build complex OLAP data structures in combination with the speed of a transactional DBMS. The efficiency of the FEZ ACS and its underlying algorithms is confirmed by the experience of many years of commercial operation at one of the leading enterprises of the state corporation Ros-cosmos JSC ISS without the involvement of external commercial solutions.