A data query method and system based on BI

Abstract

The embodiment of the present invention provides a data query method based on a BI system, comprising the following steps: a client obtains a first database and a second database from a server, and according to the first database and the second database, the client obtains a first data table from the first database and a second data table from the second database, and the first database and the second database have an associated relationship; according to the first data table and the second data table, a first target data field and a second target data field are obtained; the server generates a first sql statement and a second sql statement according to the associated condition field and query field; the first database is queried through the first sql statement, and the second database is queried through the second sql statement. The embodiment of the present invention reduces database pressure and improves external calculations, thereby improving the efficiency of database association queries, and further improving user experience.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and more specifically, to a BI-based data query method and system. Background Art

[0002] Due to the complexity of the business, the relationship between the dimension table and the fact table is very complicated. The use of database connection query has low data efficiency and is not conducive to user experience. The existing technology is completely dependent on the database supported by the IBM cognos system. Since the database is only a relational database, the data source for data query is relatively single, resulting in a query of complete data. Developers manually obtain data offline, which consumes a lot of manpower costs. Summary of the invention

[0003] In order to overcome the deficiencies of the prior art, the present invention provides a BI-based data query method for solving the problem that the relational database data source relying on the IBM Cognos system is relatively single and the query data efficiency is low.

[0004] The technical solution adopted by the present invention to solve the technical problem is: a data query method based on a BI system, comprising the following steps:

[0005] Step 1: The client obtains a first database and a second database from a server. According to the first database and the second database, the client obtains a first data table from the first database and a second data table from the second database, and the first database and the second database have an associated relationship.

[0006] Step 2: Acquire a first target data field and a second target data field according to the first data table and the second data table;

[0007] Step 3: The server generates a first SQL statement and a second SQL statement according to the associated condition field and query field;

[0008] Step 4: query the first database through the first SQL statement, and query the second database through the second SQL statement.

[0009] Preferably, the steps further include:

[0010] Based on the first target data field and the second target data field, the associated condition fields are marked and the fields to be queried are marked.

[0011] Preferably, the steps further include:

[0012] Determine whether the amount of data to be queried exceeds a threshold;

[0013] When the amount of data required for query exceeds a threshold, the first SQL statement and the second SQL statement are split separately according to certain rules.

[0014] Specifically, the steps include:

[0015] When the data volume does not exceed the threshold, the server queries the first database through the first SQL statement, and the server queries the second database through the second SQL statement.

[0016] Preferably, the steps further include:

[0017] According to the query of the condition field, when two records are identical, the two identical records are concatenated into one record.

[0018] A data query system based on a BI system, the system comprising:

[0019] A first acquisition unit is used for the client to acquire a first database and a second database from the server, and according to the first database and the second database, the client acquires a first data table from the first database and acquires a second data table from the second database, and the first database and the second database have an associated relationship;

[0020] A second acquisition unit, configured to acquire a first target data field and a second target data field according to the first data table and the second data table;

[0021] A generating unit, used for the server to generate a first SQL statement and a second SQL statement according to the associated condition field and the query field;

[0022] A query unit is used to query the first database through the first SQL statement and query the second database through the second SQL statement.

[0023] Preferably, the system further comprises:

[0024] A marking unit is used to mark the associated condition fields and the fields to be queried based on the first target data field and the second target data field.

[0025] Preferably, the system further comprises:

[0026] A judgment unit, used to judge whether the amount of data to be queried exceeds a threshold;

[0027] The splitting unit is used to split the first SQL statement and the second SQL statement respectively according to certain rules when the amount of data to be queried exceeds a threshold.

[0028] Specifically, the query unit includes:

[0029] A query subunit is used for, when the amount of data to be queried does not exceed a threshold, the server queries the first database through the first SQL statement, and the server queries the second database through the second SQL statement.

[0030] Preferably, the system further comprises:

[0031] The concatenation unit is used to concatenate the multiple records obtained by querying the condition field into one record when two records are identical.

[0032] The beneficial effects of the present invention are: by marking the associated fields in the associated data table in the associated database, extracting the database association logic, and realizing it through program automation, the database pressure is reduced, and external calculation is improved, thereby improving the efficiency of database association query and further improving the user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] Figure 1 It is a flowchart of the data query method based on BI.

[0034] Figure 2 It is a functional module diagram of the BI-based data query system.

[0035] Figure 3 This is another flowchart of the BI-based data query method. DETAILED DESCRIPTION

[0036] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.

[0037] The specific implementation of the present invention is described in detail below in conjunction with specific embodiments:

[0038] Embodiment 1:

[0039] Figure 1 as well as Figure 3 The implementation process of the BI-based data query method provided in the first embodiment of the present invention is shown. For the convenience of description, only the part related to the embodiment of the present invention is shown, which is described in detail as follows:

[0040] In step S101: the client obtains a first database and a second database from a server. According to the first database and the second database, the client obtains a first data table from the first database and a second data table from the second database, and the first database and the second database are associated with each other;

[0041] In the embodiment of the present application, the source information of the database for the associated query is saved in advance, and the system automatically pulls the interface for the user, and the user selects the database for the associated query through the interface. The associated first database and second database are obtained, the first data table is obtained from the first database, and the second data table is obtained from the second database. For example, when the user selects the oracle database above 100.15 and selects the instance ops, the system will automatically obtain a list of all databases in the ops system. When the user selects the hive database above 116.68 and selects the instance dw, the system will automatically obtain a list of all databases under dw.

[0042] In step S102: acquiring a first target data field and a second target data field according to the first data table and the second data table;

[0043] In the embodiment of the present application, the first data table and the second data table include field names, field type lengths, and other contents. If the user selects the offline transaction table (hpstjnl) under the Oracle database ops system on 100.15, the ops system automatically pulls all fields of all hpstjnl, such as: transaction flow, accounting date, transaction amount, bank card number, merchant number, etc. Similarly, the user selects the merchant information table (mer_info) on the hive database dw on 116.68, and selects fields such as merchant number, settlement card number, and merchant registration address.

[0044] In step S103: the server generates a first SQL statement and a second SQL statement according to the associated condition field and query field;

[0045] In the embodiment of the present application, which fields are query fields and which fields are condition fields are marked in advance. According to the selection field of step S102, the merchant number and the accounting date are marked as the condition fields of hpstjnl. The merchant number is marked in order to assemble the mer_info condition field query statement and perform an associated query on the data. The backend receives the information submitted by the relevant user and assembles the corresponding sql statements. For example, two sql statements are generated as follows:

[0046] sql1=select accounting date, transaction amount, bank card number, merchant number where merchant number = 'xxxx' and accounting date = 'xxxx'

[0047] sql2,=select merchant number, settlement card number, merchant registration address where merchant number='xxxx'

[0048] In step S104: the first database is queried using the first SQL statement, and the second database is queried using the second SQL statement.

[0049] In the embodiment of the present application, the corresponding sql statements are used to query different databases respectively to determine whether the amount of data to be queried exceeds the threshold. If the amount of data is very large, the sql1 statement and sql2 statement will be split into multiple sql statements according to certain rules and sent to the corresponding database for query. If the amount of data to be queried does not exceed the threshold and the amount of data is relatively small, there is no need to split the sql1 statement and sql2 statement, and the first sql statement and the second sql statement are directly queried in the specified database. The results of multiple sql queries are merged and calculated, and uniformly returned to the front-end page. The results calculated by multiple sql statements are recorded one by one through the query condition field and compared. If the two records are the same, they are spliced ​​into one record and stored for easy query by the front-end.

[0050] A person skilled in the art can understand that all or part of the steps in the above-mentioned embodiment method can be completed by instructing related hardware through a program, and the program can be stored in a computer-readable storage medium, such as ROM / RAM, disk, CD-ROM, etc.

[0051] Embodiment 2:

[0052] Figure 2 The structure of the BI-based data query system provided by the second embodiment of the present invention is shown. For the convenience of explanation, only the part related to the embodiment of the present invention is shown, which is described in detail as follows:

[0053] A first acquisition unit 201 is used for the client to acquire a first database and a second database from a server. According to the first database and the second database, the client acquires a first data table from the first database and acquires a second data table from the second database, and the first database and the second database have an associated relationship;

[0054] A second acquisition unit 202, configured to acquire a first target data field and a second target data field according to the first data table and the second data table;

[0055] A generating unit 203, configured for the server to generate a first SQL statement and a second SQL statement according to the associated condition field and the query field;

[0056] The query unit 204 is used to query the first database through the first SQL statement, and query the second database through the second SQL statement.

[0057] In an embodiment of the present invention, the client is used to obtain the first database and the second database from the server. According to the first database and the second database, the client obtains the first data table from the first database and the second data table from the second database, and the first database and the second database have an associated relationship; according to the first data table and the second data table, the first target data field and the second target data field are obtained; according to the associated condition field and the query field, the server generates the first SQL statement and the second SQL statement; the first SQL statement is used to query the first database, and the second SQL statement is used to query the second database, extract the database association logic, and use the program to automate the implementation, reduce the database pressure, improve external calculations, thereby improving the efficiency of database association queries, and then improve the user experience. The specific implementation methods of each unit can refer to the description of Example 1, which will not be repeated here.

[0058] Embodiment three:

[0059] Another structure of the BI-based data query system provided in the third embodiment of the present invention, for the sake of convenience of description, only the part related to the embodiment of the present invention is shown, including:

[0060] A first acquisition unit is used for the client to acquire a first database and a second database from the server, and according to the first database and the second database, the client acquires a first data table from the first database and acquires a second data table from the second database, and the first database and the second database have an associated relationship;

[0061] A second acquisition unit, configured to acquire a first target data field and a second target data field according to the first data table and the second data table;

[0062] a marking unit, configured to mark the associated condition fields and the fields to be queried based on the first target data field and the second target data field;

[0063] A generating unit, used for the server to generate a first SQL statement and a second SQL statement according to the associated condition field and the query field;

[0064] A judgment unit, used to judge whether the amount of data to be queried exceeds a threshold;

[0065] A splitting unit, used for splitting the first SQL statement and the second SQL statement according to certain rules respectively when the amount of data to be queried exceeds a threshold;

[0066] A query unit, configured to query the first database using the first SQL statement, and query the second database using the second SQL statement;

[0067] Specifically, the query subunit is used for, when the amount of data to be queried does not exceed a threshold, the server queries the first database through the first SQL statement, and the server queries the second database through the second SQL statement;

[0068] The concatenation unit is used to concatenate the multiple records obtained by querying the condition field into one record when two records are identical.

[0069] Embodiment 4:

[0070] Figure 3 Another flowchart of the BI-based data query method provided in the fourth embodiment of the present invention is shown. For the convenience of explanation, only the part related to the embodiment of the present invention is shown, which is described in detail as follows.

[0071] 1. The user selects the database to be associated with the query through the interface

[0072] The user selects the database to be associated and merged through the interface;

[0073] The system automatically pulls the database for the user, and the source information of the database has been saved in the database in advance.

[0074] 2. The system dynamically obtains the corresponding table

[0075] The system dynamically obtains all tables under the corresponding database according to the database selected by the user;

[0076] For example, if the user selects the Oracle database on 100.15 and selects the instance ops, the system will automatically obtain all database tables under ops;

[0077] The user selects the hive database on 116.68 and the instance dw. The system automatically obtains all database tables under dw.

[0078] 3. The system dynamically obtains the corresponding fields of the corresponding table

[0079] The system dynamically obtains the corresponding fields in the table based on the table selected by the user;

[0080] If the user selects hpstjnl (offline flow table) under Oracle ops on 100.15, the system may pull all fields of hpstjnl such as transaction water, accounting date, transaction amount, bank card number, merchant number, etc.;

[0081] Similarly, the user selects the merchant information table mer_info on the hive database dw above 116.68, and selects fields such as merchant number, settlement card number, merchant registration address, etc.

[0082] 4. Mark the associated fields

[0083] Mark which are query fields and which are condition fields;

[0084] According to the selection field in the second step, the merchant number and accounting date are marked as the conditional fields of hpstjnl. The merchant number is marked as a mark in order to assemble the mer_info conditional field query statement and perform an associated query on the data.

[0085] 5. Generate related single table sql

[0086] The backend receives the information submitted by relevant users and assembles the corresponding SQL statements;

[0087] For example, two SQL statements are generated as follows:

[0088] sql1=select accounting date, transaction amount, bank card number, merchant number where merchant number='xxxx'and accounting date='xxxx';

[0089] sql2,=select merchant number, settlement card number, merchant registration address where merchant number='xxxx'.

[0090] 6. Query related data concurrently through SQL segmentation

[0091] Use the corresponding SQL statements to query different databases and store the corresponding results;

[0092] Determine the data volume. If the data volume is very large, sql1 and sql2 will be split into multiple sql statements according to certain rules and sent to the corresponding database for execution;

[0093] If the data volume is small, there is no need to split it and directly use the sql1 and sql2 generated in the fifth step to query the execution results of the specified library.

[0094] 7. Pagination and merging results for front-end query

[0095] Combine and calculate the results of multiple SQL queries and return them to the front-end page;

[0096] The results calculated by multiple SQL statements are compared one by one through the query condition field records. If they are the same, they are spliced ​​into one record and stored for front-end query.

[0097] In an embodiment of the present invention, BI-based data query can be implemented by corresponding hardware or software units. Each unit can be an independent software or hardware unit or integrated into one software or hardware unit, which does not limit the present invention.

[0098] Those skilled in the art may appreciate that the units and algorithm steps of the various embodiments described in conjunction with the embodiments disclosed herein may be implemented by electronic hardware or a combination of computer software and electronic hardware.

[0099] Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution.

[0100] Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention. The above is only a specific implementation of the present invention, but the protection scope of the present invention is not limited thereto. Any person familiar with the technical field can easily think of changes or replacements within the technical scope disclosed by the present invention, which should be covered within the protection scope of the present invention. Therefore, the protection scope of the present invention shall be based on the protection scope of the claims.

Claims

1. A data query method based on a BI system, characterized in that: The following steps are involved: Step 1: The client obtains a first database and a second database from a server. According to the first database and the second database, the client obtains a first data table from the first database and a second data table from the second database, and the first database and the second database have an associated relationship. Step 2: Acquire a first target data field and a second target data field according to the first data table and the second data table; Step 3: The server generates a first SQL statement and a second SQL statement according to the associated condition field and query field; Step 4: query the first database through the first SQL statement, and query the second database through the second SQL statement.

2. A data query method based on a BI system according to claim 1, characterized in that: Before the server generates the first SQL statement and the second SQL statement according to the associated condition field and the query field, the step further includes: Based on the first target data field and the second target data field, the associated condition fields are marked and the fields to be queried are marked.

3. A data query method based on a BI system according to claim 2, characterized in that: Before querying the first database by using the first SQL statement and querying the second database by using the second SQL statement, the step further includes: Determine whether the amount of data to be queried exceeds a threshold; When the amount of data required for query exceeds a threshold, the first SQL statement and the second SQL statement are split separately according to certain rules.

4. A data query method based on a BI system according to claim 3, characterized in that: Before querying the first database by using the first SQL statement and querying the second database by using the second SQL statement, the step further includes: When the data volume does not exceed the threshold, the server queries the first database through the first SQL statement, and the server queries the second database through the second SQL statement.

5. A data query method based on a BI system according to claim 4, characterized in that: After querying the first database by using the first SQL statement and querying the second database by using the second SQL statement, the step further includes: According to the query of the condition field, when two records are identical, the two identical records are concatenated into one record.

6. A data query system based on a BI system, characterized in that: The system comprises: A first acquisition unit is used for the client to acquire a first database and a second database from the server, and according to the first database and the second database, the client acquires a first data table from the first database and acquires a second data table from the second database, and the first database and the second database have an associated relationship; A second acquisition unit, configured to acquire a first target data field and a second target data field according to the first data table and the second data table; A generating unit, used for the server to generate a first SQL statement and a second SQL statement according to the associated condition field and the query field; A query unit is used to query the first database through the first SQL statement and query the second database through the second SQL statement.

7. A data query system based on a BI system according to claim 6, characterized in that: The system further comprises: A marking unit is used to mark the associated condition fields and the fields to be queried based on the first target data field and the second target data field.

8. A data query system based on a BI system according to claim 7, characterized in that: The system further comprises: A judgment unit, used to judge whether the amount of data to be queried exceeds a threshold; The splitting unit is used to split the first SQL statement and the second SQL statement respectively according to certain rules when the amount of data to be queried exceeds a threshold.

9. A data query system based on a BI system according to claim 8, characterized in that: The query unit comprises: A query subunit is used for, when the amount of data to be queried does not exceed a threshold, the server queries the first database through the first SQL statement, and the server queries the second database through the second SQL statement.

10. A data query system based on a BI system according to claim 9, characterized in that: The system further comprises: The concatenation unit is used to concatenate the multiple records obtained by querying the condition field into one record when two records are identical.

Images