Data model construction methods, data query methods, devices and storage media

Abstract

This application discloses a method for constructing a data model, a method for data querying, an apparatus, and a storage medium, relating to the field of data processing technology. The method includes: obtaining field information from a dimensional model and at least two dimensions of a dimension table, where the field information indicates a mapping relationship between at least two dimensions; constructing a full dimensional relationship graph based on the field information, where the field information also indicates the association relationship between each of the at least two dimensions and a fact table; and attaching the fact table to at least one dimension in the full dimensional relationship graph based on the field information, generating a directed acyclic connected graph centered on the fact table as a data model, used to determine the path from the query metric to the fact table based on the query metric and its dimensions. This method can construct a data model based on existing data warehouse construction achievements, thereby determining the path of the query metric in the data warehouse through the data model.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a method for constructing a data model, a method for querying data, an apparatus, and a storage medium. Background Technology

[0002] A data warehouse is a strategic collection that provides all types of data to support decision-making processes at all levels of an enterprise.

[0003] To meet business needs such as operations, business analytics, and data products, data users need to extract required data from the data warehouse. However, typically, the data users' knowledge of data warehousing is insufficient to directly extract the required data. Therefore, data users often submit numerous fragmented data retrieval requests to data developers, who then provide the necessary data. Furthermore, to facilitate data users' own data extraction from the data warehouse, data developers construct additional thematic wide tables based on existing data tables according to business requirements.

[0004] However, the additionally constructed wide tables for specific topics cannot reuse the existing data warehouse infrastructure, resulting in a waste of resources. Summary of the Invention

[0005] This application provides a method for constructing a data model, a method for data querying, an apparatus, and a storage medium. It can construct a directed acyclic connected graph as a data model based on existing data warehouse construction achievements, thereby determining the path of query indicators within the data warehouse and fully utilizing the existing data warehouse construction achievements. The technical solution is as follows:

[0006] According to one aspect of this application, a method for constructing a data model is provided, the method comprising:

[0007] Obtain model information of a dimensional model, which is constructed based on a dimension table and a fact table. The model information includes field information and at least two dimensions of the dimension table, and the field information indicates the mapping relationship between the at least two dimensions.

[0008] A full dimension relationship graph is constructed based on the field information, wherein the field information also indicates the association relationship between each of the at least two dimensions and the fact table;

[0009] Based on the field information, the fact table is mounted onto at least one dimension of the full dimension relationship graph, generating a directed acyclic connected graph centered on the fact table as a data model. The data model is used to determine the path from the query indicator to the fact table based on the query indicator and the dimension of the query indicator.

[0010] According to another aspect of this application, a data query method is provided, applied to a data system including a data model constructed by the method described in the preceding aspect, the method comprising:

[0011] Receive a data query request, the data query request including query indicators;

[0012] Based on the query metric and the dimensions of the query metric, the data model is invoked to calculate the path to the fact table corresponding to the query metric;

[0013] Generate a query statement for the query metrics based on the path;

[0014] Execute the query statement to obtain the value of the query indicator from the data system.

[0015] According to another aspect of this application, a data model construction apparatus is provided, the apparatus comprising:

[0016] The first acquisition module is used to acquire model information of a dimensional model, which is constructed based on a dimension table and a fact table. The model information includes field information and at least two dimensions of the dimension table, and the field information indicates the mapping relationship between the at least two dimensions.

[0017] The construction module is used to construct a full dimension relationship graph between the at least two dimensions based on the field information, wherein the field information also indicates the association relationship between each of the at least two dimensions and the fact table;

[0018] The first generation module is used to mount the fact table to at least one dimension of the full-dimensional relationship graph based on the field information, and generate a directed acyclic connected graph centered on the fact table as a data model. The data model is used to determine the path from the query indicator to the fact table based on the query indicator and the dimension of the query indicator.

[0019] According to another aspect of this application, a data query apparatus is provided, which applies a data model constructed using the method described in the preceding aspect, the apparatus comprising:

[0020] A receiving module is used to receive data query requests, wherein the data query requests include query indicators;

[0021] The calculation module is used to calculate the path to the fact table corresponding to the query indicator by calling the data model based on the query indicator and the dimension of the query indicator;

[0022] The second generation module is used to generate a query statement for the query indicator based on the path;

[0023] The second acquisition module is used to execute the query statement and obtain the value of the query indicator from the data system.

[0024] According to another aspect of this application, a server is provided, the server comprising:

[0025] Memory, and the processor connected to the memory;

[0026] The processor is configured to load and execute executable instructions stored in memory to implement the data model construction method as described in the foregoing aspect and its alternative embodiments, or the data query method as described in the other aspect and its alternative embodiments.

[0027] According to another aspect of this application, a computer-readable storage medium is provided, which stores at least one instruction, at least one program, code set, or instruction set, wherein the at least one instruction, at least one program, code set, or instruction set is loaded and executed by a processor to implement a data model construction method as described in one aspect and its optional embodiments above, or a data query method as described in another aspect and its optional embodiments above.

[0028] According to another aspect of this application, a computer program product is provided, comprising computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform a method for constructing a data model as described in one aspect and its optional embodiments, or a method for data querying as described in the other aspect and its optional embodiments.

[0029] The beneficial effects of the technical solutions provided in this application include at least the following:

[0030] This method obtains model information of the dimensional model from the data system, constructs a directed acyclic connected graph (DAG) corresponding to the dimension table and the fact table based on the model information, and connects dimensions with dimensions and dimensions with the fact table. Using the DAG as the data model, when performing indicator queries, the path from the queried indicator to the fact table can be determined through the data model, and the value of the queried indicator can be extracted from the data warehouse according to the above path. This fully utilizes the existing construction results of the data warehouse, providing users with a more convenient and faster indicator data extraction environment, and avoiding the resource waste caused by the inability to reuse the existing construction results of the data warehouse due to the additional construction of topic wide tables. Secondly, this method can also realize cross-topic data queries, avoiding the problem that additionally constructed topic wide tables can only realize data queries for a specific topic. Furthermore, because this method does not require the construction of topic wide tables, it frees up a lot of human resources that would otherwise be spent on the construction of topic wide tables. Attached Figure Description

[0031] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0032] Figure 1 This is a schematic diagram of the structure of a data system provided in an exemplary embodiment of this application;

[0033] Figure 2 This is a flowchart of a method for constructing a data model provided in an exemplary embodiment of this application;

[0034] Figure 3 This is a schematic diagram of the structure of a data model provided in an exemplary embodiment of this application;

[0035] Figure 4 This is a schematic diagram illustrating the construction process of a data model provided in an exemplary embodiment of this application;

[0036] Figure 5 This is a flowchart of a data query method provided in an exemplary embodiment of this application;

[0037] Figure 6 This is a schematic diagram illustrating a data query process provided in an exemplary embodiment of this application;

[0038] Figure 7 This is a block diagram of a data model construction apparatus provided in an exemplary embodiment of this application;

[0039] Figure 8 This is a block diagram of a data query apparatus provided in an exemplary embodiment of this application;

[0040] Figure 9 This is a schematic diagram of the structure of a server provided in an exemplary embodiment of this application. Detailed Implementation

[0041] To make the objectives, technical solutions, and advantages of this application clearer, the embodiments of this application will be described in further detail below with reference to the accompanying drawings.

[0042] The following is an explanation of the terms used in this application:

[0043] Metadata, also known as intermediary data or relay data, is data that describes data. It mainly describes information about data properties and is used to support functions such as indicating storage location, historical data, data retrieval, and file records. Metadata is a kind of electronic catalog. In order to achieve the purpose of cataloging, it is necessary to describe and collect the content or characteristics of the data, thereby assisting in data retrieval.

[0044] Indicators are units and methods for measuring the degree of development of a matter. They are usually obtained through aggregation statistics such as summation and averaging, and are under certain conditions. For example, positive review rate and growth rate are indicators.

[0045] A dimension is a certain characteristic of a phenomenon, such as gender, region, and time.

[0046] Dimension tables, also known as dimension tables, contain the characteristics of fact records in a fact data table. Some characteristics provide descriptive information, while others specify how to summarize the data in the fact data table to provide useful information for analysts. Dimension tables contain a hierarchical structure of characteristics that help summarize the data.

[0047] Fact tables, also known as fact data tables, are characterized by containing numerical data (facts), and this numerical information is summarized to provide historical data about the relevant units. Each fact data table contains a multi-part index that includes the primary key of the related table as a foreign key, while the dimension tables contain the characteristics of the fact records.

[0048] A wide table is a data table that links together metrics, dimensions, and attributes.

[0049] Atomic metrics are indivisible sets of concepts that express the atomic quantifiable attributes of a business entity, such as order volume, user volume, page views (PV), and unique visitors (UV).

[0050] Calculated metrics refer to a set of metrics built upon atomic metrics and formed through certain calculation rules, such as average user transaction volume and debt-to-equity ratio.

[0051] Derived metrics are metrics generated by combining atomic or calculated metrics with dimension members, statistical attributes, management attributes, etc., such as the completed value, planned value, cumulative value, year-on-year, month-on-month, and percentage of transaction amount.

[0052] Figure 1 The diagram shows a structural schematic of a data system 100 according to an embodiment of this application. The data system 100 includes an application layer 101, a service layer 102, middleware 103, and a storage layer 104.

[0053] Application layer 101 provides five functions: data extraction requirement management, task execution, data security control, operation and maintenance management, and application access. Among them, data extraction requirement management manages data extraction requirements, task execution manages the execution of tasks set in application layer 101, data security control manages data security in data system 100, operation and maintenance management manages the operation of data system 100, and application access manages the access of application programs to data system 100.

[0054] The service layer 102 includes three modules: metadata processing 1021, data query processing 1022, and auxiliary module 1023. Metadata processing 1021 includes three functions: metadata synchronization, metadata construction, and metadata storage. Metadata processing 1021 can implement the data model construction method provided in this embodiment. Metadata synchronization includes the synchronization of physical tables, metrics, dimensions, mapping relationships, and resource security levels. Metadata construction includes two aspects: expanding metrics and expanding dimensions. Expanding metrics includes the expansion of atomic metrics, derived metrics, and calculated metrics. Expanding dimensions includes the expansion of wide table models, star schema models, and snowflake schema models. Metadata storage includes the storage of dimension path graphs (including directed acyclic connected graphs) and logical wide tables.

[0055] Data query processing 1022 includes protocol parsing, logical construction, model optimization, and physical construction. Protocol parsing provides functions for statistical indicators, filtering conditions, summary dimensions, and query attributes, providing indicators and dimensions for logical construction. Logical construction provides functions for calculating available indicators and dimensions, filtering wide table models, and marking user permissions, thereby constructing candidate logical wide tables for model optimization. Model optimization includes two aspects: wide table filtering and dimension table filtering. Wide table filtering conditions include fewer dimension fields, higher hierarchy, and fewer fact tables. Dimension table filtering conditions include fewer levels of related fact tables and higher related coverage. Logical wide tables that meet the above filtering conditions are selected from the candidate logical wide tables. Physical construction is based on logical wide tables to implement mappings from wide tables to fact tables, indicators to fields, dimensions to dimension tables, dimensions to fields, and fact tables to dimension tables, as well as fact table merging, ultimately generating query statements. The query statements are used to extract the values ​​of query indicators from the data warehouse.

[0056] The auxiliary module 1023 provides auxiliary services such as task management, operation and maintenance management, permission management, task scheduling and query rate limiting to assist in the processing of metadata and data queries.

[0057] Middleware 103 includes an offline query engine, a task scheduling system, and an access control service. The task scheduling system schedules tasks, such as data query tasks. The access control service determines whether data query permissions are available. The offline query engine executes the query tasks scheduled by the task scheduling system to perform data queries.

[0058] Storage layer 104 includes the data warehouse tool Hive, the relational database management system MySQL, and cloud services; Hive is used for data extraction, transformation, and loading, and is a mechanism for storing, querying, and analyzing large-scale data; MySQL is used to store data in different tables and supports fast data querying; cloud services are used to store data on cloud servers and support data storage and querying.

[0059] For data governance of data system 100, data governance application support 200 can be used. Data governance application support 200 includes model management, access control, process control, and interface services for data system 100. Among them, model management includes logical model, physical model, and relational mapping. Data governance application support 200 can realize the governance of metadata in data system 100, such as updating metadata in data system 100.

[0060] For example, the data system 100 described above is hosted by a server cluster, which may include at least one of a single server, multiple servers, a cloud computing platform, and a virtualization center. Those skilled in the art will understand that the server cluster may consist of only one server, or dozens, hundreds, or more servers; this application embodiment does not limit the number or type of server clusters.

[0061] Figure 2 A flowchart illustrating a method for constructing a data model provided in an exemplary embodiment of this application is shown, the method being applied to Figure 1 In the server of the data system shown, the method includes:

[0062] Step 301: Obtain the model information of the dimensional model, which includes field information and at least two dimensions of the dimension table.

[0063] The server retrieves the model information of the aforementioned dimensional model from the data system. This dimensional model is constructed based on dimension tables and fact tables. The aforementioned field information indicates a mapping relationship between at least two dimensions, and also indicates an association relationship between each of the at least two dimensions and the fact table. For example, the aforementioned field information includes at least one of the field information from the dimension table and the field information from the fact table.

[0064] For example, the above model information includes n fields, among which at least one field is used to uniquely identify a dimension; for example, in the five fields, field 1 uniquely identifies dimension 1, and fields 2, 3, 4 and 5 are all used to uniquely identify dimension 2.

[0065] For example, the server begins executing step 301 to construct the data model during data system initialization; or, the server begins executing step 301 to rebuild the data model in response to metadata updates in the data system.

[0066] Step 302: Construct a full dimensional relationship graph between at least two dimensions based on the field information.

[0067] The server constructs a full dimension relationship graph between at least two dimensions according to the mapping relationship indicated by the field information, wherein there are directed connections between dimensions in the full dimension relationship graph.

[0068] In the full dimension relationship graph, dimensions are divided into dimension keys and dimension members. Optionally, each dimension key uniquely identifies a subject, and dimension members that have a direct or indirect mapping relationship with the dimension key are used to jointly describe the subject identified by the dimension key; for example, the dimension key "Merchant Tier 3 City" uniquely identifies the subject "Merchant City", and the dimension members of this dimension key include "Merchant Tier 1 City" and "Merchant Tier 2 City". Both "Merchant Tier 1 City" and "Merchant Tier 2 City" are dimension members that expand around the subject "Merchant City".

[0069] A dimension key is relative to a dimension member. A dimension member of one subject can also be used as a dimension key of another subject. For example, the dimension member "Merchant Cellular" of the dimension key "Daily Merchant" can be used as a dimension key of another subject. There is a directed connection between "Merchant Cellular" and its two dimension members "Merchant Tier 3 City" and "Merchant Cellular Type".

[0070] For example, directed connections in a full dimension graph include directed connections from a dimension primary key to a dimension member; directed connections may also include directed connections from one dimension member to another dimension member.

[0071] Optionally, for the construction of the full dimension relationship graph, the server determines the dimension primary key from at least two dimensions; based on the field information and the dimension members of the dimension primary key determined by the at least two dimensions, a mapping relationship between the dimension primary key and the dimension members is constructed to form the full dimension relationship graph mentioned above; wherein, the at least two dimensions with the mapping relationship are dimensions that describe the same subject.

[0072] Optionally, the dimension primary key and dimension members can be directly mapped or indirectly mapped. For example, dimension members include a first dimension member and a second dimension member. To construct the mapping relationship between dimensions, the server constructs a first mapping relationship between the dimension primary key and the first dimension member, and a second mapping relationship between the first dimension member and the second dimension member. The first and second mapping relationships together describe the indirect mapping relationship between the second dimension member and the dimension primary key.

[0073] Optionally, for dimension expansion, the server iterates through the dimension model, expanding each of at least two dimensions to obtain the expanded dimensions; and determines the dimension members from the at least two dimensions and the expanded dimensions based on field information. For example, the server can iterate through the dimension model and expand dimensions across dimension tables based on the relationships between dimensions.

[0074] Optionally, for determining the dimension primary key, the server can determine the dimension that the dimension table is associated with in the fact table from at least two dimensions as the dimension primary key. For example, a fact table is associated with at least one dimension table, and the dimension table is associated with the fact table through a dimension. The server determines this dimension as the dimension primary key during the data model construction process.

[0075] Step 303: Based on the field information, mount the fact table to at least one dimension in the full dimension relationship graph, and generate a directed acyclic connected graph centered on the fact table as a data model. The data model is used to determine the path from the query metric to the fact table based on the query metric and the dimension of the query metric.

[0076] Based on the mapping relationship between the fact table and dimensions indicated by the field information, the server mounts the fact table onto at least one dimension in the full dimension relationship graph, generating a directed acyclic connected graph centered on the fact table, thus obtaining the data model.

[0077] Optionally, the server mounts the fact table to at least one dimension primary key in the full-dimensional relational graph based on field information, generating a fact-centric directed acyclic connected graph as a data model.

[0078] For example, such as Figure 3The image above is a schematic diagram of the structure of the full-dimensional relationship graph, which includes dimension primary keys and dimension members; the image below is a schematic diagram of the structure of the constructed directed acyclic connected graph, with fact table 11 attached to the four dimension primary keys: "Daily Merchants", "Merchant Cellular", "Daily", and "User Tier 3 City".

[0079] Optionally, after the server mounts the fact table to at least one dimension in the full dimension relationship graph based on field information, it deletes dimensions in the directed acyclic connected graph that have no mapping relationship with the fact table, generating a directed acyclic connected graph centered on the fact table as the data model. For example, such as... Figure 3 In the diagram above, the dimension primary key and dimension members circled by the dashed box have no mapping relationship with the fact table, so they can be deleted. The diagram below shows a directed acyclic connected graph with the dimension circled by the dashed box deleted.

[0080] For example, after generating a directed acyclic connected graph, the server can store at least one of the following in memory: the metrics supported by the data model, the calculation method of the metrics, at least two dimensions, and the mapping method between dimensions, along with the data model, in the form of a wide table. This ultimately forms a wide table that corresponds one-to-one with the fact table. For example, such as... Figure 4 The diagram illustrates the formation of the wide table. The data system contains structured metadata, as shown in metadata structured storage 21. The server synchronizes the metadata structured storage to snowflake model storage 22. Field f1 is mapped to dimensions, resulting in the dimensions "Date" and "Merchant"; field f1 is also mapped to metrics, resulting in metrics "Number of Orders" and "Transaction Amount". After completing the field-to-dimension mapping, a dimension-to-dimensional table mapping is performed, resulting in dimension tables d1 and d2; and so on, continuing the field-to-dimension, field-to-metric, and dimension-to-dimensional table mappings. After completing the mapping, metadata is constructed. Dimensions and metrics are expanded and then concatenated to generate wide table 23. Logical wide table 23 includes the mapping relationships between dimensions and the mapping relationships between dimensions and the fact table.

[0081] During the construction of the data model, the server iterates through the dimensional model to expand the metrics. For example, after iterating through the dimensional model, the server expands the metrics based on at least one of atomic metrics and calculated metrics to generate extended metrics. These extended metrics can include at least one of derived metrics and calculated metrics. That is, some atomic metrics can be transformed into calculated metrics through certain operational rules, and / or some atomic or calculated metrics can be combined with dimension members, statistical attributes, management attributes, etc., to form derived metrics. Therefore, after iterating through the dimensional model, the server expands the calculated and derived metrics, associating the extended metrics with the dimensions corresponding to those extended metrics. These extended metrics can be used to generate the wide table mentioned above.

[0082] In summary, the data model construction method provided in this embodiment obtains model information of the dimensional model from the data system, constructs a directed acyclic connected graph corresponding to the dimension table and the fact table based on the model information, connects dimensions with dimensions and dimensions with the fact table, and uses the directed acyclic connected graph as the data model. When performing indicator queries, the data model can be used to determine the path from the query indicator to the fact table, and then the value of the query indicator can be extracted from the data warehouse according to the above path. This fully utilizes the existing construction results of the data warehouse, providing users with a more convenient and faster indicator data extraction environment, and avoiding the resource waste caused by the inability to reuse the existing construction results of the data warehouse due to the additional construction of thematic wide tables. Secondly, this method can also realize cross-thematic data queries, avoiding the problem that the additionally constructed thematic wide tables can only realize data queries for a specific theme. Furthermore, because this method does not require the construction of thematic wide tables, it releases a lot of human resources that would have been spent on the construction of thematic wide tables.

[0083] Figure 5 A flowchart illustrating a data query method provided in an exemplary embodiment of this application is shown, the method being applied to Figure 1 The data system server shown includes, as described above, the data system server. Figure 2 The data model constructed by the method provided in the illustrated embodiment includes:

[0084] Step 401: Receive a data query request, which includes query metrics.

[0085] The server receives a data query request sent by the terminal. The data query request carries query metrics. The server extracts the query metrics from the data query request and determines the dimensions of the query metrics; or, the data query request carries both query metrics and the dimensions of the query metrics. The server extracts both the query metrics and the dimensions of the query metrics from the data query request.

[0086] Step 402: Based on the query metric and its dimensions, call the data model to calculate the path to the fact table corresponding to the query metric.

[0087] For example, the server parses the data query request, obtains the query metrics and their dimensions, and then filters the wide table combination that stores both the query metrics and their dimensions. The data model is then called to calculate the path to the fact table with the fewest associated dimensions based on the wide table combination.

[0088] Optionally, for the selection of wide table combinations, the server can select at least two candidate wide table combinations based on the query metrics and the dimensions of the query metrics; and determine the wide table combinations that meet the optimization conditions from the at least two candidate wide table combinations. The optimization conditions are used to select wide table combinations that can quickly achieve the performance of metric query.

[0089] Optionally, the above optimization conditions include at least one of the following:

[0090] The information in the wide table combination occupies the fewest number of rows, or the information in the wide table occupies less than the row count threshold;

[0091] Wide tables are used most frequently, or the frequency of use of wide table combinations exceeds the frequency threshold; for example, when querying metrics, the data system records the frequency of use of wide table combinations.

[0092] The combination of wide tables corresponds to the combination of dimension tables with the fewest associations with the fact table.

[0093] Step 403: Generate query statements for query metrics based on the path.

[0094] For example, such as Figure 6 After obtaining the query metrics, the server performs logical construction to obtain wide table combination 1 and wide table combination 2. Then, it optimizes the logical model by filtering the wide table combinations using optimization criteria to determine wide table combination 1, which has fewer rows and higher usage frequency. Next, it optimizes the physical model by calculating the path with the fewest associated dimensions (or associated fact tables) in the mapping path corresponding to the wide table combination, resulting in the path from the fact table to the merchant, to d2, and to the cell type. Finally, it performs physical construction to generate the query statement corresponding to the above path. For example, a wide table combination refers to a combination of at least two wide tables; for instance, wide table 1 and wide table 2 combined become wide table combination 12.

[0095] For example, the query statement above may include a Structured Query Language (SQL) statement. For example, the final SQL statement is generated by using a left outer join between the fact table and the dimension table, and a union all between the fact tables, in the above path.

[0096] Step 404: Execute the query statement to obtain the value of the query indicator from the data system.

[0097] For example, the server executes an SQL statement to extract the value of a query metric from the data system.

[0098] In summary, the data query method provided in this embodiment applies... Figure 2The data model constructed by the method provided in the illustrated embodiment includes mapping relationships between dimensions and between dimensions and the fact table. When performing indicator queries, the data model can determine the path from the query indicator to the fact table, and then extract the value of the query indicator from the data warehouse according to the above path. This fully utilizes the existing construction results of the data warehouse, providing users with a more convenient and faster indicator data extraction environment, and avoiding the resource waste caused by the inability to reuse the existing construction results of the data warehouse due to the additional construction of thematic wide tables. Secondly, this method can also realize cross-thematic data queries, avoiding the problem that the additionally constructed thematic wide tables can only realize data queries for a specific theme. Furthermore, because this method does not require the construction of thematic wide tables, it frees up a lot of human resources that would have been spent on the construction of thematic wide tables.

[0099] Figure 7 The diagram illustrates a block diagram of a data model construction apparatus provided in an exemplary embodiment of this application. This apparatus is applied in a server and is implemented as part or all of the server through software, hardware, or a combination of both. The apparatus includes:

[0100] The first acquisition module 512 is used to acquire model information of the dimension model. The dimension model is constructed based on the dimension table and the fact table. The model information includes field information and at least two dimensions of the dimension table. The field information indicates that there is a mapping relationship between at least two dimensions.

[0101] Module 514 is used to construct a full dimension relationship graph between at least two dimensions based on field information, which also indicates the association between each of the at least two dimensions and the fact table.

[0102] The first generation module 516 is used to mount the fact table to at least one dimension of the full dimension relationship graph based on field information, and generate a directed acyclic connected graph centered on the fact table as a data model. The data model is used to determine the path from the query metric to the fact table based on the query metric and the dimension of the query metric.

[0103] In some embodiments, the construction module 514 is configured to:

[0104] Determine the primary key of a dimension from at least two dimensions;

[0105] Based on field information and at least two dimensions, determine the dimension members of the dimension primary key, construct the mapping relationship between the dimension primary key and the dimension members, and form a full dimension relationship graph;

[0106] Among them, at least two dimensions that have a mapping relationship are dimensions that describe the same subject.

[0107] In some embodiments, the construction module 514 is configured to:

[0108] Dimension expansion is performed on each of at least two dimensions to obtain the expanded dimensions;

[0109] Dimension members are determined from at least two dimensions and extended dimensions based on field information.

[0110] In some embodiments, dimension members include first dimension members and second dimension members; construction module 514 is used for:

[0111] Construct the first mapping relationship between the dimension primary key and the first dimension member, and the second mapping relationship between the first dimension member and the second dimension member. The first mapping relationship and the second mapping relationship together describe the indirect mapping relationship between the second dimension member and the dimension primary key.

[0112] In some embodiments, the construction module 514 is configured to:

[0113] Determine the dimension primary key as the dimension table that is associated with the fact table in at least two dimensions.

[0114] In some embodiments, the device further includes a storage module 518;

[0115] Storage module 518 is used to store at least one of the following in memory: the indicators supported by the data model, the calculation method of the indicators, at least two dimensions, and the mapping method between dimensions, together with the data model, in the form of a wide table.

[0116] In some embodiments, the first generation module 516 is configured to:

[0117] Before generating a directed acyclic connected graph centered on the fact table as the data model, remove dimensions in the directed acyclic connected graph that have no mapping relationship with the fact table.

[0118] In summary, the data model construction device provided in this embodiment obtains model information of the dimensional model from the data system, constructs a directed acyclic connected graph corresponding to the dimension table and the fact table based on the model information, and connects dimensions with dimensions and dimensions with the fact table. Using the directed acyclic connected graph as the data model, when performing indicator queries, the path from the queried indicator to the fact table can be determined through the data model, and then the value of the queried indicator can be extracted from the data warehouse according to the above path. This fully utilizes the existing construction results of the data warehouse, providing users with a more convenient and faster indicator data extraction environment, and avoiding the resource waste caused by the inability to reuse the existing construction results of the data warehouse due to the additionally constructed topic wide table. Secondly, this device can also realize cross-topic data queries, avoiding the problem that the additionally constructed topic wide table can only realize data queries for a specific topic. Furthermore, because this device does not require the construction of topic wide tables, it frees up a large amount of human resources originally spent on the construction of topic wide tables.

[0119] Figure 8 The diagram illustrates a block diagram of a data query apparatus provided in an exemplary embodiment of this application. This apparatus is applied in a server and is implemented as part or all of the server through software, hardware, or a combination of both. The apparatus is applied as follows: Figure 2 The data model constructed by the method of the illustrated embodiment includes:

[0120] The receiving module 522 is used to receive data query requests, which include query indicators.

[0121] The calculation module 524 is used to call the data model to calculate the path to the fact table corresponding to the query metric based on the query metric and the dimension of the query metric.

[0122] The second generation module 526 is used to generate query statements for query indicators based on the path;

[0123] The second acquisition module 528 is used to execute query statements and obtain the values ​​of query indicators from the data system.

[0124] In some embodiments, the calculation module 524 is configured to:

[0125] Filter the wide table combination that stores both the query metrics and the dimensions of the query metrics;

[0126] The data model is invoked to calculate the path to the fact table with the fewest associated dimensions based on the wide table combination.

[0127] In some embodiments, the calculation module 524 is configured to:

[0128] Filter for at least two candidate wide table combinations based on query metrics and the dimensions of the query metrics;

[0129] The optimization criteria are used to select wide table combinations that meet the optimization conditions from at least two candidate wide table combinations. These optimization criteria are used to filter wide table combinations that can quickly and accurately achieve the performance of querying metrics.

[0130] In some embodiments, the optimization conditions include at least one of the following:

[0131] The information in the wide table combination occupies the fewest number of rows, or the information in the wide table combination occupies less than the row count threshold;

[0132] Wide table combinations are used most frequently, or the frequency of use of wide table combinations exceeds the frequency threshold.

[0133] The combination of wide tables corresponds to the combination of dimension tables with the fewest associations with the fact table.

[0134] In summary, the data query device provided in this embodiment utilizes... Figure 2The data model constructed by the method provided in the illustrated embodiment includes mapping relationships between dimensions and between dimensions and the fact table. When performing indicator queries, the data model can determine the path from the query indicator to the fact table, and then extract the value of the query indicator from the data warehouse according to the above path. This fully utilizes the existing construction results of the data warehouse, providing users with a more convenient and faster indicator data extraction environment, and avoiding the resource waste caused by the inability to reuse the existing construction results of the data warehouse due to the additional construction of thematic wide tables. Secondly, the device can also realize cross-thematic data queries, avoiding the problem that the additionally constructed thematic wide tables can only realize data queries for a specific theme. Furthermore, since the device does not require the construction of thematic wide tables, it frees up a lot of human resources that would have been spent on the construction of thematic wide tables.

[0135] Figure 9 A schematic diagram of a server structure according to an embodiment of this application is shown. This server is used to implement the data model construction method or the data query method provided in the above embodiments. Specifically:

[0136] The server 600 includes a CPU (Central Processing Unit) 601, a system memory 604 including RAM (Random Access Memory) 602 and ROM (Read-Only Memory) 603, and a system bus 605 connecting the system memory 604 and the central processing unit 601. The server 600 also includes a basic I / O (Input / Output) system 606 that facilitates information transfer between various devices within the computer, and a large-capacity storage device 607 for storing the operating system 613, application programs 614, and other program modules 615.

[0137] The basic input / output system 606 includes a display 608 for displaying information and an input device 609 for user input, such as a mouse or keyboard. Both the display 608 and the input device 609 are connected to the central processing unit 601 via an input / output controller 610 connected to the system bus 605. The basic input / output system 606 may also include the input / output controller 610 for receiving and processing input from multiple other devices such as a keyboard, mouse, or electronic stylus. Similarly, the input / output controller 610 also provides output to a display screen, printer, or other types of output devices.

[0138] The mass storage device 607 is connected to the central processing unit 601 via a mass storage controller (not shown) connected to the system bus 605. The mass storage device 607 and its associated computer-readable media provide non-volatile storage for the server 600. That is, the mass storage device 607 may include computer-readable media (not shown) such as a hard disk or a CD-ROM (Compact Disc Read-Only Memory) drive.

[0139] Without loss of generality, the computer-readable medium may include computer storage media and communication media. Computer storage media include volatile and non-volatile, removable and non-removable media implemented using any method or technology for storing information such as computer-readable instructions, data structures, program modules, or other data. Computer storage media include RAM, ROM, EPROM (Erasable Programmable Read-Only Memory), EEPROM (Electrically Erasable Programmable Read-Only Memory), flash memory or other solid-state storage technologies, CD-ROM, DVD (Digital Versatile Disc) or other optical storage, magnetic tape cassettes, magnetic tape, disk storage, or other magnetic storage devices. Of course, those skilled in the art will recognize that the computer storage media are not limited to the above-mentioned types. The system memory 604 and mass storage device 607 described above can be collectively referred to as memory.

[0140] According to various embodiments of this application, the server 600 can also be connected to a remote computer on a network, such as the Internet. That is, the server 600 can be connected to a network 612 via a network interface unit 611 connected to the system bus 605, or the network interface unit 611 can be used to connect to other types of networks or remote computer systems (not shown).

[0141] The sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0142] Those skilled in the art will understand that all or part of the steps of the above embodiments can be implemented by hardware or by a program instructing related hardware. The program can be stored in a computer-readable storage medium, such as a read-only memory, a disk, or an optical disk.

[0143] The above description is merely an optional embodiment of this application and is not intended to limit this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.

Claims

1. A method of constructing a data model, characterized by, The method includes: Obtain model information of a dimensional model, which is constructed based on a dimension table and a fact table. The model information includes field information and at least two dimensions of the dimension table, and the field information indicates the mapping relationship between the at least two dimensions. A full dimension relationship graph is constructed based on the field information, wherein the field information also indicates the association relationship between each of the at least two dimensions and the fact table; Based on the field information, the fact table is mounted onto at least one dimension of the full dimension relationship graph, generating a directed acyclic connected graph centered on the fact table as a data model. The data model is used to determine the path from the query indicator to the fact table based on the query indicator and the dimension of the query indicator when a query request is received. The construction of a full-dimensional relationship graph between at least two dimensions based on the field information includes: Determine the dimensional primary key from the at least two dimensions; Based on the field information and the at least two dimensions, determine the dimension members of the dimension primary key, construct the mapping relationship between the dimension primary key and the dimension members, and form the full dimension relationship graph; Among them, at least two dimensions that have the mapping relationship are dimensions that describe the same subject.

2. The method of claim 1, wherein, The step of determining the dimension members of the primary key based on the field information and the at least two dimensions includes: For each of the at least two dimensions, perform dimension expansion to obtain the expanded dimension; The dimension members are determined from the at least two dimensions and the extended dimension based on the field information.

3. The method of claim 1, wherein, The dimension members include first dimension members and second dimension members; The process of constructing the mapping relationship between the dimension primary key and the dimension members includes: Construct a first mapping relationship between the primary key of the dimension and the first member of the dimension, and a second mapping relationship between the first member of the dimension and the second member of the dimension. The first mapping relationship and the second mapping relationship together describe the indirect mapping relationship between the second member of the dimension and the primary key of the dimension.

4. The method of claim 1, wherein, Determining the dimensional primary key from the at least two dimensions includes: The dimension that is associated with the fact table in the at least two dimensions is determined as the dimension primary key.

5. The method according to any one of claims 1 to 2, characterized in that, After generating the directed acyclic connected graph centered on the fact table as the data model, the process includes: The data model supports at least one of the following: the metrics, the calculation method of the metrics, at least two dimensions, and the mapping method between dimensions. These are stored in memory in the form of a wide table.

6. The method according to any one of claims 1 to 2, characterized in that, Before generating the directed acyclic connected graph centered on the fact table as the data model, the following steps are included: Delete the dimensions in the directed acyclic connected graph that have no mapping relationship with the fact table.

7. A method for data querying, characterized in that, Applied to a data system, the data system including a data model constructed by the method as described in claim 1, the method comprising: Receive a data query request, the data query request including query indicators; Based on the query metric and the dimensions of the query metric, the data model is invoked to calculate the path to the fact table corresponding to the query metric; Generate a query statement for the query metrics based on the path; Execute the query statement to obtain the value of the query indicator from the data system.

8. The method according to claim 7, characterized in that, The step of calculating the path to the fact table corresponding to the query metric based on the query metric and the dimensions of the query metric, using the data model, includes: Filter the wide table combination that stores both the query metric and the dimensions of the query metric; The data model is invoked to calculate the path that reaches the fact table with the fewest associated dimensions based on the wide table combination.

9. The method according to claim 8, characterized in that, The filtering method involves storing the query metrics and their dimensions in a wide table, including: Based on the query metrics and the dimensions of the query metrics, at least two candidate wide table combinations are selected; The wide table combination that meets the optimization criteria is determined from the at least two candidate wide table combinations. The optimization criteria are used to filter wide table combinations that can quickly and accurately achieve the performance of index query.

10. The method according to claim 9, characterized in that, The optimization conditions include at least one of the following: The information in the wide table combination occupies the fewest number of rows, or the information in the wide table combination occupies less than the row count threshold. The wide table combination is used most frequently, or the wide table combination is used more frequently than the usage frequency threshold. The combination of wide tables corresponds to the combination of dimension tables with the fewest associations with the fact table.

11. A data model construction apparatus, characterized in that, The device includes: The first acquisition module is used to acquire model information of a dimensional model, which is constructed based on a dimension table and a fact table. The model information includes field information and at least two dimensions of the dimension table, and the field information indicates the mapping relationship between the at least two dimensions. The construction module is used to construct a full dimension relationship graph between the at least two dimensions based on the field information, wherein the field information also indicates the association relationship between each of the at least two dimensions and the fact table; The first generation module is used to mount the fact table to at least one dimension of the full dimension relationship graph based on the field information, and generate a directed acyclic connected graph centered on the fact table as a data model. The data model is used to determine the path from the query indicator to the fact table based on the query indicator and the dimension of the query indicator when a query request is received. The construction of a full-dimensional relationship graph between at least two dimensions based on the field information includes: Determine the dimensional primary key from the at least two dimensions; Based on the field information and the at least two dimensions, determine the dimension members of the dimension primary key, construct the mapping relationship between the dimension primary key and the dimension members, and form the full dimension relationship graph; Among them, at least two dimensions that have the mapping relationship are dimensions that describe the same subject.

12. A data query apparatus, characterized in that, The apparatus for constructing a data model using the method described in claim 1 includes: A receiving module is used to receive data query requests, wherein the data query requests include query indicators; The calculation module is used to calculate the path to the fact table corresponding to the query indicator by calling the data model based on the query indicator and the dimension of the query indicator; The second generation module is used to generate a query statement for the query indicator based on the path; The second acquisition module is used to execute the query statement and obtain the value of the query indicator from the data system.

13. A server, characterized in that, The server includes: Memory, and processor connected to the memory; The processor is configured to load and execute executable instructions stored in the memory to implement the method for constructing a data model as described in any one of claims 1 to 6, or the method for querying data as described in any one of claims 7 to 10.

14. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores at least one instruction, at least one program, code set, or instruction set; the at least one instruction, the at least one program, the code set, or the instruction set is loaded and executed by a processor to implement the data model construction method as described in any one of claims 1 to 6, or the data query method as described in any one of claims 7 to 10.

Images