Data correlation query method and data correlation query device

Abstract

The invention provides a data correlation query device and a data correlation query method. The data correlation query device comprises a data acquisition unit, an SQL (structured query language) execution unit, a cache number-picking formula construction unit and a cache execution unit. The data acquisition unit is used for obtaining a fundamental field of a fact table and selecting a field of a dimension table based on the fundamental field to be used as an correlation field for correlation of the fact table and the dimension table; and the SQL execution unit is used for implementing query operations through SQL statements based on the fundamental field and a datasheet with the fundamental field, and obtaining a result set; the cache number-picking formula construction unit is used for obtaining a main key value corresponding to the correlation field from the result set, and constructing a cache number-picking formula in combination with a data path with the correlation field; and the cache execution unit is used for obtaining the value of the correlation field according to the cache number-picking formula and combining the value of the correlation field and the result set to obtain final query results. The fact table and the dimension table can be associated efficiently, results can be implemented efficiently, the fatal pressure on the system caused by correlation query of the fact table and the dimension table with a large number of data can be avoided, and the efficiency can be improved.

Description

Technical field [0001] The invention relates to the field of report analysis, in particular to a data association query method and a data association query device. Background technique [0002] In the field of report analysis, it is a very typical application to query the results after associating fact tables and dimension tables and display them in the report. Take ERP (Enterprise Resource Planning) software products and relational databases as examples. The document table can be regarded as a fact table, and the amount of data is usually large; the basic file table can be regarded as a dimension table, and the amount of data depends on the file type. There are big and small. The primary key of the basic file table is stored in the document table as a foreign key. Simple SQL can be used to find out the business information (such as document number, document date, quantity, amount, etc.) stored in the document table and the primary key of the relevant basic file. The report req...

AI Technical Summary

Problems solved by technology

[0009] The above schemes 1 and 3, when solving the aforementioned application problems, still need to use multi-table joint query SQL as the main implementation scheme, and it is difficult to solve the pressure brought by multiple dimension tables on the system operating efficiency in the process of breadth and depth association fields

Although the second solution takes into account the joint operation of SQL+cache formulas, the unit formula solution is difficult to meet the modeling requirements of the data layer, so it brings inconvenience and trouble to users in terms of data model reusability and operation interaction technology

In addition, the above schemes also have constraints on the free scalability of associated fields. Although some related schemes take into account the query of associated extended fields, they are ultimately limited to querying original data + extended data through SQL, and do not solve big data. Performance problems caused by joint query

Images