Method and system for realizing role playing dimension

Abstract

The invention discloses a role playing dimension implementation method and system, and relates to the technical field of data processing. Comprising the steps that a meta-model of a dimension model related to a role playing dimension is created, the meta-model comprises five types of data tables, a role playing dimension name is selected from a role playing dimension name source according to requirements, when the role playing dimension name is different from a physical dimension table name, it is considered that the dimension corresponding to the role playing dimension name is the role playing dimension, and the role playing dimension is selected from a physical dimension table name; when the related dimension of one index is displayed, the role playing dimension name is used for replacing the physical dimension name to display the actual business meaning of the dimension, when the dimension is selected to summarize the indexes, the role playing dimension name is used for constructing a dynamic SQL, and when the composite index is analyzed, the actual business meaning of the dimension is displayed. When multiple indexes are analyzed in parallel, if the facts where the polyatomic indexes forming the composite indexes are located are all associated with the same physical dimension, the composite indexes are summarized through the physical dimension, and when the multiple indexes are analyzed in parallel, if the facts where all the indexes participating in analysis are located are all associated with the same physical dimension, the multiple indexes are summarized through the physical dimension; and the associated dimension list is displayed by using the physical dimension name, so that confusion caused by different role names is avoided.

Description

Technical Field

[0001] The present invention discloses a method and system for implementing role-playing dimensions, which relates to the technical field of data processing. Background Art

[0002] Dimensional modeling is a data modeling method in data warehouse construction. It constructs a data warehouse according to fact tables and dimension tables. The dimensional modeling method is widely used in the field of business intelligence.

[0003] Role-Playing Dimensions refer to a single physical dimension being referenced by a fact table once or multiple times, and each reference is connected to a role with a different business meaning of the physical dimension. For example, a fact table can have multiple date attributes, such as order date, delivery date, etc., and each date attribute is connected to the date dimension by a foreign key.

[0004] The Meta Model of a dimensional model refers to a model that defines the structure and rules of the dimensional model in the fields of data warehouse and business intelligence. The Meta Model of the dimensional model is usually used to describe and standardize the data structure in the data warehouse to ensure data consistency and maintainability.

[0005] When analyzing data metrics using consistent dimensions, due to the existence of role-playing dimensions, there is uncertainty in business meaning:

[0006] For example, when performing a summary analysis of atomic metrics based on a certain physical dimension, if there are one or more role-playing dimensions of the selected dimension in the fact table related to the metric, there will be a series of problems: the original physical dimension name cannot express the business meaning and cannot be distinguished among multiple dimensions;

[0007] Another example: A composite metric is a metric calculated through multiple atomic metrics. When using a certain consistent dimension to summarize the composite metric, if there are multiple role-playing dimensions of the selected dimension in the fact table where the atomic metrics that make up the composite metric are located, there will be semantic ambiguity and thus cannot be calculated;

[0008] Or, when multiple metrics are summarized and analyzed based on a common consistent dimension, the selected dimension may have multiple role-playing dimensions in the fact table related to one of the metrics, which may be an atomic metric or a composite metric, resulting in semantic ambiguity and thus cannot be calculated. Summary of the Invention

[0009] In view of the problems of the prior art, the present invention provides a method and system for implementing role-playing dimensions, which perform data structure description, algorithms, and interactive display content design for role-playing dimensions to solve the problems of using role-playing dimensions in the following multiple analysis scenarios.

[0010] The specific solution proposed by the present invention is as follows:

[0011] The present invention also provides a method for implementing a role-playing dimension, including:

[0012] Create a meta-model of the dimension model related to the role-playing dimension. The meta-model includes five types of data tables, namely fact table, fact attribute definition table, association definition table, dimension definition table, and dimension attribute definition table.

[0013] Select the role-playing dimension name from the source of the role-playing dimension name according to the requirement. When the role-playing dimension name is different from the physical dimension table name, the dimension corresponding to the role-playing dimension name is considered as the role-playing dimension.

[0014] When displaying the related dimensions of an indicator, use the role-playing dimension name to replace the physical dimension name to show the actual business meaning of the dimension. When selecting dimensions to summarize the indicator, construct a dynamic SQL using the role-playing dimension name: construct field aliases through the role-playing dimension name to distinguish the fields of different role dimensions and simplify subsequent data processing.

[0015] When analyzing a composite indicator, if the facts where the multiple atomic indicators constituting the composite indicator are all associated with the same physical dimension, use this physical dimension to summarize the composite indicator and display the associated dimension list using the physical dimension name to avoid confusion caused by different role names.

[0016] When performing parallel analysis on multiple indicators, if the facts where each indicator participating in the analysis are all associated with the same physical dimension, use the physical dimension to summarize the multiple indicators and display the associated dimension list using the physical dimension name to avoid confusion caused by different role names.

[0017] Furthermore, in the method for implementing a role-playing dimension, record the business meaning and category of the fact table through the fact table, record the attribute name and category of each field of the fact table through the fact attribute definition table, record the association between the fact table attribute and the surrogate key of the dimension table through the association definition table, record the business meaning and category of each dimension table through the dimension definition table, and record the attribute name and category of each field of the dimension table through the dimension attribute definition table.

[0018] Furthermore, in the method for implementing a role-playing dimension, when selecting a certain physical dimension to summarize and analyze a composite indicator, if there is a role-playing dimension in the atomic indicators constituting the composite indicator, select a suitable role-playing dimension and construct a dynamic SQL to achieve summarization based on different role-playing dimensions.

[0019] Further, when selecting a certain physical dimension to perform summary analysis on multiple indicators in the implementation method of a role-playing dimension, if there is a role-playing dimension in some indicators of the physical dimension, a suitable role-playing dimension is selected. If a certain indicator is a composite indicator, the suitable role-playing dimensions in the atomic indicators that make up the composite indicator are also specified, and a dynamic SQL is constructed to perform indicator summarization based on different role-playing dimensions.

[0020] The present invention also provides a role-playing dimension implementation system, including a model management module, a judgment module, and an indicator management module.

[0021] The model management module creates a meta-model of a dimension model related to the role-playing dimension. The meta-model includes five types of data tables, namely a fact table, a fact attribute definition table, an association definition table, a dimension definition table, and a dimension attribute definition table.

[0022] The judgment module selects a role-playing dimension name from the source of the role-playing dimension name according to requirements. When there is a difference between the role-playing dimension name and the physical dimension table name, the dimension corresponding to the role-playing dimension name is considered as the role-playing dimension.

[0023] When the indicator management module displays the relevant dimensions of an indicator, it uses the role-playing dimension name to replace the physical dimension name to show the actual business meaning of the dimension. When selecting a dimension to summarize the indicator, a dynamic SQL is constructed using the role-playing dimension name: field aliases are constructed through the role-playing dimension name to distinguish the fields of different role dimensions and simplify subsequent data processing.

[0024] When the indicator management module analyzes a composite indicator, if the facts where the multiple atomic indicators that make up the composite indicator are all associated with the same physical dimension, the physical dimension is used to summarize the composite indicator, and the physical dimension name is used to display the associated dimension list to avoid confusion caused by different role names.

[0025] When the indicator management module performs parallel analysis on multiple indicators, if the facts where each indicator participating in the analysis are all associated with the same physical dimension, the physical dimension is used to summarize the multiple indicators, and the physical dimension name is used to display the associated dimension list to avoid confusion caused by different role names.

[0026] Further, the model management module of the role-playing dimension implementation system records the business meaning and category of the fact table through the fact table, records the attribute name and category of each field of the fact table through the fact attribute definition table, records the association between the fact table attribute and the surrogate key of the dimension table through the association definition table, records the business meaning and category of each dimension table through the dimension definition table, and records the attribute name and category of each field of the dimension table through the dimension attribute definition table.

[0027] Furthermore, the index management module of the implementation system for a role-playing dimension selects a certain physical dimension to conduct a summary analysis of composite indexes. If there is a role-playing dimension among the atomic indexes that make up the composite index in the physical dimension, it selects a suitable role-playing dimension and constructs a dynamic SQL to achieve the summary based on different role-playing dimensions.

[0028] Furthermore, when the index management module of the implementation system for a role-playing dimension selects a certain physical dimension to conduct a summary analysis of multiple indexes, if there is a role-playing dimension in some of the indexes in the physical dimension, it selects a suitable role-playing dimension. If one of the indexes is a composite index, it also specifies the suitable role-playing dimensions among the atomic indexes that make up the composite index, and constructs a dynamic SQL to conduct index summarization based on different role-playing dimensions.

[0029] The beneficial effects of the present invention are as follows:

[0030] The present invention conducts data structure description, algorithms, and interactive display content design for the role-playing dimension, and solves the problems of using the role-playing dimension in the following multiple analysis scenarios. Among them, it uses a meta-model to quickly define the role-playing dimension name, realizes summary analysis based on the role-playing dimension through dynamic SQL, avoids maintaining a large number of fixed views, distinguishes and displays the role-playing dimensions of the same physical dimension when showing the available dimension list of atomic indexes, and realizes summary based on multiple role-playing dimensions through dynamic SQL. When showing the available dimension list of composite indexes, it distinguishes and displays the role-playing dimensions of the same physical dimension; guides users to select suitable role-playing dimensions for summary analysis, realizes summary based on multiple role-playing dimensions through dynamic SQL, and distinguishes and displays the role-playing dimensions of the same physical dimension when showing the available dimension list for parallel analysis of multiple indexes; guides users to select suitable role-playing dimensions for summary analysis; realizes summary based on multiple role-playing dimensions through dynamic SQL. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] Figure 1 is a schematic diagram of the method flow of the present invention.

[0032] Figure 2 is an example schematic diagram of the dimension model of the role-playing dimension.

[0033] Figure 3 is a schematic diagram of the operation interface for composite index summarization.

[0034] Figure 4 is a schematic diagram of the role-playing dimension selection interface during multi-index summarization.

[0035] Figure 5 is a schematic diagram of the role-playing dimension selection interface during multi-index summarization including composite indexes.

[0036] Figure 6 It is a schematic diagram of the interface of the role-playing dimension description box during multi-index parallel analysis.

[0037] Figure 7 It is a schematic diagram of the interface of the role-playing dimension description box during composite index analysis. Specific implementation manners

[0038] The present invention will be further described below in conjunction with the accompanying drawings and specific embodiments, so that those skilled in the art can better understand the present invention and be able to implement it, but the exemplified embodiments do not limit the present invention.

[0039] Embodiment 1

[0040] The present invention also provides a method for implementing a role-playing dimension, including:

[0041] Step 1: Create a meta-model of a dimension model related to the role-playing dimension. The meta-model includes five types of data tables, namely a fact table, a fact attribute definition table, an association definition table, a dimension definition table, and a dimension attribute definition table. Among them, the business meaning and category of the fact table are recorded through the fact table, the attribute name and category of each field of the fact table are recorded through the fact attribute definition table, the association between the fact table attributes and the surrogate keys of the dimension tables is recorded through the association definition table, the business meaning and category of each dimension table are recorded through the dimension definition table, and the attribute name and category of each field of the dimension table are recorded through the dimension attribute definition table.

[0042] For example, Figure 1 In the dimension model exemplified in, the fact table contains two date role-playing dimensions. In the meta-model, the settings of the fact attribute definition table and the association definition table for the role-playing dimension are as follows:

[0043] In the fact attribute definition table, there are the following two attribute definition records, which can be referred to Table 1.

[0044] Table 1

[0045] Table Name Field Name Attribute Name Type …… order_detail order_date Order Date String …… order_detail delivery_date Delivery Date String ……

[0046] In the association definition table, there are two association records, which can be referred to Table 2.

[0047] Table 2

[0048] Source Table Source Field Target Table Target Field …… order_detail order_date dim_date date_sk …… order_detail delivery_date dim_date date_sk ……

[0049] In other definition tables, there is no difference in the definition of the role-playing dimension and the physical dimension.

[0050] Select the role-playing dimension name from the source of the role-playing dimension name according to the requirement. When the role-playing dimension name is different from the physical dimension table name, the dimension corresponding to the role-playing dimension name is considered as the role-playing dimension: The source of the role-playing dimension name can be selected. For example, the attribute name defined in the "fact attribute table" can be used; The role-playing attribute field comment (comment) of the physical fact table metadata can also be used. For example, in the clickhouse database, the comment information of the field can be obtained by querying the system.columns table. When the above role-playing dimension name is different from the physical dimension table name, this dimension is determined as the role-playing dimension. The dimension list associated with the indicator can be obtained by querying the "association definition table" through the specified fact table in the indicator definition, and it is determined whether there is a role-playing dimension according to this.

[0051] Step 2: When displaying the relevant dimensions of an indicator, use the role-playing dimension name to replace the physical dimension name to show the actual business meaning of this dimension. When selecting dimensions to summarize the indicator, use the role-playing dimension name to construct a dynamic SQL: Construct field aliases through the role-playing dimension name to distinguish the fields of different role dimensions and simplify subsequent data processing.

[0052] For example:

[0053] SELECT dim_alias1.field1 ASdim_alias1_field1,

[0054] dim_alias2.field1 ASdim_alias2_field1,

[0055] COUNT(fact.sk)ASfact_sk_count

[0056] FROM fact

[0057] LEFT JOIN dim AS dim_alias1 ON fact.role_field_1=dim_alias1.sk

[0058] LEFT JOIN dim AS dim_alias2 ON fact.role_field_2=dim_alias2.sk

[0059] GROUP BY dim_alias1.field1,dim_alias2.field1;

[0060] In the above statement, dim_alias1 and dim_alias2 are the names of the role-playing dimensions. By using the role-playing dimension names to construct dynamic SQL, the problem of SQL errors caused by associating different fields with the same dimension table is solved. By constructing field aliases using the role-playing dimension names, it is convenient to distinguish the fields of different role dimensions and simplify subsequent data processing. Using dynamic SQL can avoid constructing a large number of static role-playing dimension views, simplify the implementation work, and reduce the probability of errors.

[0061] Step 3: When analyzing composite indicators, if the facts of the multiple atomic indicators that make up the composite indicator are all associated with the same physical dimension, then use this physical dimension to summarize the composite indicator, and use the physical dimension name to display the associated dimension list to avoid confusion caused by different role names. When selecting a certain physical dimension to summarize and analyze the composite indicator, if there is a role-playing dimension in the atomic indicator that makes up the composite indicator, then select the appropriate role-playing dimension and construct dynamic SQL to achieve summarization based on different role-playing dimensions.

[0062] Step 4: When performing parallel analysis on multiple indicators, if the facts of each indicator participating in the analysis are all associated with the same physical dimension, then use the physical dimension to summarize the multiple indicators, and use the physical dimension name to display the associated dimension list to avoid confusion caused by different role names.

[0063] When selecting a certain physical dimension to summarize and analyze multiple indicators, if there is a role-playing dimension in some of the indicators for the physical dimension, then select the appropriate role-playing dimension. If one of the indicators is a composite indicator, then also specify the appropriate role-playing dimensions in the atomic indicators that make up the composite indicator, and construct dynamic SQL to perform indicator summarization based on different role-playing dimensions.

[0064] Embodiment 2

[0065] The present invention also provides a system for implementing role-playing dimensions, including a model management module, a judgment module, and an indicator management module.

[0066] The model management module creates a meta-model of the dimension model related to the role-playing dimension. The meta-model includes five types of data tables, namely, a fact table, a fact attribute definition table, an association definition table, a dimension definition table, and a dimension attribute definition table.

[0067] The judgment module selects the role-playing dimension name from the source of the role-playing dimension name according to the requirements. When there is a difference between the role-playing dimension name and the physical dimension table name, the dimension corresponding to the role-playing dimension name is considered as the role-playing dimension.

[0068] When the index management module displays the relevant dimensions of an index, it uses the role-playing dimension name to replace the physical dimension name to show the actual business meaning of the dimension. When selecting dimensions to summarize the index, it constructs a dynamic SQL using the role-playing dimension name: constructs field aliases through the role-playing dimension name to distinguish fields of different role dimensions and simplify subsequent data processing.

[0069] When the index management module analyzes a composite index, if the facts where multiple atomic indexes that make up the composite index are all associated with the same physical dimension, it uses this physical dimension to summarize the composite index and uses the physical dimension name to display the associated dimension list to avoid confusion caused by different role names.

[0070] When the index management module performs parallel analysis on multiple indexes, if the facts where each index participating in the analysis is all associated with the same physical dimension, it uses the physical dimension to summarize the multiple indexes and uses the physical dimension name to display the associated dimension list to avoid confusion caused by different role names.

[0071] The above-described embodiments are only preferred embodiments given to fully illustrate the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or transformations made by those skilled in the art on the basis of the present invention are all within the protection scope of the present invention. The protection scope of the present invention is subject to the claims.

Claims

1. A method for implementing a role-playing dimension, characterized in that Including: Create a meta-model for a dimension model related to the role-playing dimension. The meta-model includes five types of data tables, namely fact table, fact attribute definition table, association definition table, dimension definition table, and dimension attribute definition table. Select the role-playing dimension name from the source of the role-playing dimension name according to the requirement. When the role-playing dimension name is different from the physical dimension table name, the dimension corresponding to the role-playing dimension name is considered as the role-playing dimension. When displaying the related dimensions of an indicator, use the role-playing dimension name to replace the physical dimension name to show the actual business meaning of the dimension. When selecting dimensions to summarize the indicator, construct a dynamic SQL using the role-playing dimension name: construct field aliases using the role-playing dimension name to distinguish fields of different role dimensions and simplify subsequent data processing. When analyzing a composite indicator, if the facts of the multiple atomic indicators that make up the composite indicator are all associated with the same physical dimension, use this physical dimension to summarize the composite indicator, and use the physical dimension name to display the associated dimension list to avoid confusion caused by different role names. When performing parallel analysis on multiple indicators, if the facts of each indicator participating in the analysis are all associated with the same physical dimension, use the physical dimension to summarize the multiple indicators, and use the physical dimension name to display the associated dimension list to avoid confusion caused by different role names.

2. The implementation method of a role-playing dimension according to claim 1, characterized in that Record the business meaning and category of the fact table through the fact table, record the attribute name and category of each field of the fact table through the fact attribute definition table, record the association between the fact table attribute and the surrogate key of the dimension table through the association definition table, record the business meaning and category of each dimension table through the dimension definition table, and record the attribute name and category of each field of the dimension table through the dimension attribute definition table.

3. The implementation method of a role-playing dimension according to claim 1, characterized in that Select a certain physical dimension to perform summary analysis on a composite indicator. If there is a role-playing dimension in the atomic indicators that make up the composite indicator for the physical dimension, select a suitable role-playing dimension and construct a dynamic SQL to achieve summary based on different role-playing dimensions.

4. The method for realizing a role-playing dimension according to claim 1, characterized in that When selecting a certain physical dimension to perform summary analysis on multiple indicators, if there is a role-playing dimension in some of the indicators for the physical dimension, select a suitable role-playing dimension. If one of the indicators is a composite indicator, also specify the suitable role-playing dimensions in the atomic indicators that make up the composite indicator, and construct a dynamic SQL to perform indicator summary based on different role-playing dimensions.

5. An implementation system for the role-playing dimension, characterized in that Including a model management module, a judgment module, and an indicator management module. The model management module creates a meta-model for a dimension model related to the role-playing dimension. The meta-model includes five types of data tables, namely fact table, fact attribute definition table, association definition table, dimension definition table, and dimension attribute definition table. The judgment module selects the role-playing dimension name from the source of the role-playing dimension name according to the requirement. When the role-playing dimension name is different from the physical dimension table name, the dimension corresponding to the role-playing dimension name is considered as the role-playing dimension. When the index management module displays the relevant dimensions of an index, it uses the role-playing dimension name to replace the physical dimension name to show the actual business meaning of the dimension. When selecting dimensions to summarize the index, it constructs a dynamic SQL using the role-playing dimension name: constructs field aliases through the role-playing dimension name to distinguish fields of different role dimensions and simplifies subsequent data processing. When the index management module analyzes a composite index, if the facts where the multiple atomic indexes that make up the composite index are all associated with the same physical dimension, it uses this physical dimension to summarize the composite index and uses the physical dimension name to display the associated dimension list to avoid confusion caused by different role names. When the index management module performs parallel analysis on multiple indexes, if the facts where each index participating in the analysis are all associated with the same physical dimension, it uses the physical dimension to summarize the multiple indexes and uses the physical dimension name to display the associated dimension list to avoid confusion caused by different role names.

6. A system for realizing a role-playing dimension according to claim 5, characterized in that The model management module records the business meaning and category of the fact table through the fact table, records the attribute name and category of each field of the fact table through the fact attribute definition table, records the association between the fact table attribute and the surrogate key of the dimension table through the association definition table, records the business meaning and category of each dimension table through the dimension definition table, and records the attribute name and category of each field of the dimension table through the dimension attribute definition table.

7. The implementation system of a role-playing dimension according to claim 5, characterized in that the index When the management module selects a certain physical dimension to perform summary analysis on a composite index, if there is a role-playing dimension in the atomic indexes that make up the composite index for the physical dimension, it selects a suitable role-playing dimension and constructs a dynamic SQL to achieve summarization based on different role-playing dimensions.

8. An implementation system for a role-playing dimension according to claim 5, characterized in that the index When the management module selects a certain physical dimension to perform summary analysis on multiple indexes, if there is a role-playing dimension in some of the indexes for the physical dimension, it selects a suitable role-playing dimension. If one of the indexes is a composite index, it also specifies the suitable role-playing dimensions in the atomic indexes that make up the composite index and constructs a dynamic SQL to perform index summarization based on different role-playing dimensions.

Images