A method and system for implementing a role-playing dimension
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- INSPUR SOFTWARE TECH CO LTD
- Filing Date
- 2025-04-22
- Publication Date
- 2026-07-03
Smart Images
Figure CN120407542B_ABST
Abstract
Description
Technical Field
[0001] This invention discloses a method and system for implementing role-playing dimensions, relating to the field of data processing technology. Background Technology
[0002] Dimensional modeling is a data modeling method used in data warehouse construction. It involves building the data warehouse based on fact tables and dimension tables. Dimensional modeling is widely used in the field of business intelligence.
[0003] Role-playing dimensions refer to a single physical dimension that is referenced once or multiple times by a fact table, with each reference connecting to 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] In the fields of data warehousing and business intelligence, the meta-model of a dimensional model refers to the model that defines the structure and rules of the dimensional model. The meta-model is typically used to describe and standardize the data structure in a data warehouse, ensuring data consistency and maintainability.
[0005] When analyzing data metrics using the consistency dimension, the existence of a role-playing dimension leads to uncertainty in the business meaning:
[0006] For example, when summarizing and analyzing atomic metrics based on a certain physical dimension, if the selected dimension has one or more role-playing dimensions in the metric-related fact table, a series of problems will arise: the original physical dimension name cannot express the business meaning, and it is impossible to distinguish between multiple dimensions.
[0007] For example, a composite indicator is an indicator calculated from multiple atomic indicators. When a certain consistency dimension is used to summarize the composite indicator, if the selected dimension has multiple role-playing dimensions in the fact table where the atomic indicators that make up the composite indicator are located, there will be semantic ambiguity, and thus it will be impossible to calculate.
[0008] Alternatively, when multiple indicators are aggregated and analyzed based on a common and consistent dimension, the selected dimension may be one of the indicators, which may be an atomic indicator or a composite indicator. If there are multiple role-playing dimensions in the relevant fact table, there will be semantic ambiguity, making it impossible to calculate. Summary of the Invention
[0009] This invention addresses the problems of existing technologies by providing a method and system for implementing a role-playing dimension. It describes the data structure, algorithms, and interactive display content of the role-playing dimension, solving the problem of using the role-playing dimension in various analysis scenarios.
[0010] The specific solution proposed in this invention is as follows:
[0011] This invention also provides a method for implementing the role-playing dimension, including:
[0012] Create a metamodel of the dimensional model related to role-playing dimensions. The metamodel includes five types of data tables: fact table, fact attribute definition table, association definition table, dimension definition table, and dimension attribute definition table.
[0013] Choose a role-playing dimension name from its source based on requirements. If the role-playing dimension name differs from the physical dimension table name, then the dimension corresponding to the role-playing dimension name is considered the role-playing dimension.
[0014] When displaying the related dimensions of an indicator, role-playing dimension names are used instead of physical dimension names to demonstrate the actual business meaning of the dimension. When summarizing the indicator using selected dimensions, dynamic SQL is constructed using role-playing dimension names: field aliases are created using role-playing dimension names to distinguish fields from different role dimensions, simplifying subsequent data processing.
[0015] When analyzing composite indicators, if the facts underlying the multi-atomic indicators that constitute the composite indicator are all related to the same physical dimension, then the composite indicator is summarized using that physical dimension. The list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names.
[0016] When performing parallel analysis on multiple indicators, if the facts of each indicator involved in the analysis are all related to the same physical dimension, then the physical dimension is used to summarize the multiple indicators, and the list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names.
[0017] Furthermore, in the aforementioned method for implementing a role-playing dimension, the business meaning and category of the fact table are recorded through a fact table, the attribute name and category of each field in the fact table are recorded through a fact attribute definition table, the association between fact table attributes and dimension table surrogate keys are recorded through an association definition table, the business meaning and category of each dimension table are recorded through a dimension definition table, and the attribute name and category of each field in the dimension table are recorded through a dimension attribute definition table.
[0018] Furthermore, in one method for implementing the role-playing dimension, a certain physical dimension is selected to summarize and analyze the composite indicators. If the physical dimension has a role-playing dimension in the atomic indicators that constitute the composite indicators, then a suitable role-playing dimension is selected, and dynamic SQL is constructed to realize the summary based on different role-playing dimensions.
[0019] Furthermore, in the method for implementing the role-playing dimension, when selecting a physical dimension to summarize and analyze multiple indicators, if the physical dimension has role-playing dimensions for some indicators, then the appropriate role-playing dimension is selected. If one of the indicators is a composite indicator, then the appropriate role-playing dimension among the atomic indicators constituting the composite indicator is specified at the same time, and dynamic SQL is constructed to summarize the indicators based on different role-playing dimensions.
[0020] This invention also provides a system for implementing a role-playing dimension, including a model management module, a judgment module, and an indicator management module.
[0021] The model management module creates a metamodel of the dimensional model related to the role-playing dimension. The metamodel includes five types of data tables: fact table, fact attribute definition table, association definition table, dimension definition table, and dimension attribute definition table.
[0022] The decision module selects a role-playing dimension name from its source based on requirements. If the role-playing dimension name differs from the physical dimension table name, then the dimension corresponding to the role-playing dimension name is considered a role-playing dimension.
[0023] When displaying the relevant dimensions of an indicator, the indicator management module uses role-playing dimension names instead of physical dimension names to demonstrate the actual business meaning of the dimension. When summarizing the indicator by selecting dimensions, dynamic SQL is constructed using role-playing dimension names: field aliases are created through role-playing dimension names to distinguish fields from different role dimensions, simplifying subsequent data processing.
[0024] When the indicator management module analyzes composite indicators, if the facts underlying the multi-atomic indicators that make up the composite indicator are all related to the same physical dimension, then the composite indicator is summarized using that physical dimension. The list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names.
[0025] When the indicator management module performs parallel analysis on multiple indicators, if the facts of each indicator involved in the analysis are all related to the same physical dimension, the physical dimension is used to summarize the multiple indicators, and the list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names.
[0026] Furthermore, 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 attributes 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, in the indicator management module of the role-playing dimension implementation system, a certain physical dimension is selected to summarize and analyze the composite indicators. If the physical dimension has a role-playing dimension in the atomic indicators that constitute the composite indicators, a suitable role-playing dimension is selected, and dynamic SQL is constructed to realize the summary based on different role-playing dimensions.
[0028] Furthermore, when the indicator management module of the role-playing dimension implementation system selects a physical dimension to summarize and analyze multiple indicators, if the physical dimension has role-playing dimensions for some indicators, then the appropriate role-playing dimension is selected. If one of the indicators is a composite indicator, then the appropriate role-playing dimension among the atomic indicators constituting the composite indicator is specified at the same time, and dynamic SQL is constructed to summarize the indicators based on different role-playing dimensions.
[0029] The advantages of this invention are:
[0030] This invention addresses the challenges of using role-playing dimensions in various analytical scenarios by providing data structure descriptions, algorithms, and interactive display design. Specifically, it employs a meta-model to quickly define role-playing dimension names and uses dynamic SQL to perform summary analysis based on these dimensions, avoiding the maintenance of numerous fixed views. When displaying a list of available dimensions for atomic indicators, it differentiates and displays role-playing dimensions sharing the same physical dimension, and uses dynamic SQL to perform summary analysis based on multiple role-playing dimensions. Similarly, when displaying a list of available dimensions for composite indicators, it differentiates and displays role-playing dimensions sharing the same physical dimension. Furthermore, it guides users to select appropriate role-playing dimensions for summary analysis and uses dynamic SQL to perform summary analysis based on multiple role-playing dimensions. Finally, it distinguishes and displays role-playing dimensions sharing the same physical dimension when displaying a list of available dimensions for parallel analysis of multiple indicators. Attached Figure Description
[0031] Figure 1 This is a schematic diagram of the method flow of the present invention.
[0032] Figure 2 This is a schematic diagram of an example of the dimensional model of role-playing dimension.
[0033] Figure 3 This is a schematic diagram of the interface for summarizing composite indicators.
[0034] Figure 4 This is a schematic diagram of the role-playing dimension selection interface when summarizing multiple indicators.
[0035] Figure 5 This is a schematic diagram of the role-playing dimension selection interface when summarizing multiple indicators including composite indicators.
[0036] Figure 6 This is a schematic diagram of the role-playing dimension description box interface when performing parallel analysis of multiple indicators.
[0037] Figure 7 This is a schematic diagram of the role-playing dimension description box interface during composite index analysis. Detailed Implementation
[0038] The present invention will be further described below with reference to the accompanying drawings and specific embodiments, so that those skilled in the art can better understand and implement the present invention. However, the embodiments described are not intended to limit the present invention.
[0039] Example 1
[0040] This invention also provides a method for implementing the role-playing dimension, including:
[0041] Step 1: Create a metamodel for the dimensional models related to the role-playing dimension. The metamodel includes five types of data tables: fact tables, fact attribute definition tables, association definition tables, dimension definition tables, and dimension attribute definition tables. The fact tables record the business meaning and categories of the facts; the fact attribute definition tables record the attribute names and categories of each field in the fact tables; the association definition tables record the relationships between fact table attributes and surrogate keys in the dimension tables; the dimension definition tables record the business meaning and categories of each dimension table; and the dimension attribute definition tables record the attribute names and categories of each field in the dimension tables.
[0042] For example, Figure 1 In the example dimensional model, the fact table contains two date role-playing dimensions. In the metamodel, the fact attribute definition table and association definition table are set up for the role-playing dimensions as follows:
[0043] The fact attribute definition table contains the following two attribute definition records, as shown in 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] There are two related records in the association definition table, as shown in 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 between the definitions of the role-playing dimension and the physical dimension.
[0050] The role-playing dimension name is selected from the source of the name based on requirements. If the role-playing dimension name differs from the physical dimension table name, the dimension corresponding to that name is considered a role-playing dimension. The source of the role-playing dimension name can be chosen, such as using attribute names defined in the "Fact Attribute Table"; or using the comment information of the role-playing attribute field in the physical fact table metadata, such as in the ClickHouse database, where the comment information can be obtained by querying the `system.columns` table. When the role-playing dimension name differs from the physical dimension table name, that dimension is considered a role-playing dimension. The list of dimensions associated with the metric can be obtained by querying the "Associated Definition Table" through the specified fact table in the metric definition, and a role-playing dimension can be determined accordingly.
[0051] Step 2: When displaying the relevant dimensions of an indicator, use role-playing dimension names instead of physical dimension names to show the actual business meaning of the dimension. When selecting dimensions to summarize the indicator, use role-playing dimension names to construct dynamic SQL: construct field aliases through role-playing dimension names to distinguish 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) AS fact_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 statements, dim_alias1 and dim_alias2 are role-playing dimension names. Constructing dynamic SQL using these names resolves the issue of SQL errors caused by joining different fields with the same dimension table. Using role-playing dimension names to create field aliases makes it easier to distinguish fields from different role dimensions, simplifying subsequent data processing. Dynamic SQL avoids the need to build numerous static role-playing dimension views, simplifying implementation and reducing the probability of errors.
[0061] Step 3: When analyzing composite indicators, if the facts underlying the multi-atomic indicators constituting the composite indicator are all related to the same physical dimension, then the composite indicator is summarized using that physical dimension. The list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names. Specifically, when selecting a physical dimension for summarizing and analyzing composite indicators, if the physical dimension has a role-playing dimension among the atomic indicators constituting the composite indicator, then a suitable role-playing dimension is selected, and dynamic SQL is constructed 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 involved in the analysis are all related to the same physical dimension, then the physical dimension is used to summarize the multiple indicators, and the list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names.
[0063] When performing summary analysis on multiple indicators by selecting a certain physical dimension, if the physical dimension has role-playing dimensions for some indicators, then the appropriate role-playing dimension is selected. If one of the indicators is a composite indicator, then the appropriate role-playing dimension among the atomic indicators that constitute the composite indicator is specified at the same time, and dynamic SQL is constructed to summarize the indicators based on different role-playing dimensions.
[0064] Example 2
[0065] This invention also provides a system for implementing a role-playing dimension, including a model management module, a judgment module, and an indicator management module.
[0066] The model management module creates a metamodel of the dimensional model related to the role-playing dimension. The metamodel includes five types of data tables: fact table, fact attribute definition table, association definition table, dimension definition table, and dimension attribute definition table.
[0067] The decision module selects a role-playing dimension name from its source based on requirements. If the role-playing dimension name differs from the physical dimension table name, then the dimension corresponding to the role-playing dimension name is considered a role-playing dimension.
[0068] When displaying the relevant dimensions of an indicator, the indicator management module uses role-playing dimension names instead of physical dimension names to demonstrate the actual business meaning of the dimension. When summarizing the indicator by selecting dimensions, dynamic SQL is constructed using role-playing dimension names: field aliases are created through role-playing dimension names to distinguish fields from different role dimensions, simplifying subsequent data processing.
[0069] When the indicator management module analyzes composite indicators, if the facts underlying the multi-atomic indicators that make up the composite indicator are all related to the same physical dimension, then the composite indicator is summarized using that physical dimension. The list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names.
[0070] When the indicator management module performs parallel analysis on multiple indicators, if the facts of each indicator involved in the analysis are all related to the same physical dimension, the physical dimension is used to summarize the multiple indicators, and the list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names.
[0071] The above-described embodiments are merely preferred embodiments provided to fully illustrate the present invention, and the scope of protection of the present invention is not limited thereto. Equivalent substitutions or modifications made by those skilled in the art based on the present invention are all within the scope of protection of the present invention. The scope of protection of the present invention is defined by the claims.
Claims
1. A method for implementing a role-playing dimension, characterized by: include: Create a metamodel of the dimensional model related to role-playing dimensions. The metamodel includes five types of data tables: fact table, fact attribute definition table, association definition table, dimension definition table, and dimension attribute definition table. Choose a role-playing dimension name from its source based on requirements. If the role-playing dimension name differs from the physical dimension table name, then the dimension corresponding to the role-playing dimension name is considered the role-playing dimension. When displaying the related dimensions of an indicator, role-playing dimension names are used instead of physical dimension names to demonstrate the actual business meaning of the dimension. When summarizing the indicator using selected dimensions, dynamic SQL is constructed using role-playing dimension names: field aliases are created using role-playing dimension names to distinguish fields from different role dimensions, simplifying subsequent data processing. When analyzing composite indicators, if the facts underlying the multi-atomic indicators constituting the composite indicator are all related to the same physical dimension, then the composite indicator is summarized using that physical dimension. The list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names. Specifically, when selecting a physical dimension for summary analysis of composite indicators, if the physical dimension has a role-playing dimension among the atomic indicators constituting the composite indicator, then the appropriate role-playing dimension is selected, and dynamic SQL is constructed to achieve summarization based on different role-playing dimensions. When performing parallel analysis on multiple indicators, if the facts of all the indicators involved in the analysis are related to the same physical dimension, the physical dimension is used to summarize the multiple indicators. The list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names. When selecting a physical dimension to summarize and analyze multiple indicators, if the physical dimension has role-playing dimensions for some indicators, the appropriate role-playing dimension is selected. If one of the indicators is a composite indicator, the appropriate role-playing dimension among the atomic indicators that constitute the composite indicator is specified at the same time, and dynamic SQL is constructed to summarize the indicators based on different role-playing dimensions.
2. The method for implementing the role-playing dimension according to claim 1, characterized in that: The fact table records the business meaning and category of the fact table; the fact attribute definition table records the attribute name and category of each field in the fact table; the association definition table records the association between the fact table attributes and the surrogate key of the dimension table; the dimension definition table records the business meaning and category of each dimension table; and the dimension attribute definition table records the attribute name and category of each field in the dimension table.
3. A system for implementing a role-playing dimension, characterized by: It includes a model management module, a judgment module, and an indicator management module. The model management module creates a metamodel of the dimensional model related to the role-playing dimension. The metamodel includes five types of data tables: fact table, fact attribute definition table, association definition table, dimension definition table, and dimension attribute definition table. The decision module selects a role-playing dimension name from its source based on requirements. If the role-playing dimension name differs from the physical dimension table name, then the dimension corresponding to the role-playing dimension name is considered a role-playing dimension. When displaying the relevant dimensions of an indicator, the indicator management module uses role-playing dimension names instead of physical dimension names to demonstrate the actual business meaning of the dimension. When summarizing the indicator by selecting dimensions, dynamic SQL is constructed using role-playing dimension names: field aliases are created through role-playing dimension names to distinguish fields from different role dimensions, simplifying subsequent data processing. When the indicator management module analyzes composite indicators, if the facts underlying the multi-atomic indicators constituting the composite indicator are all related to the same physical dimension, then the composite indicator is summarized using that physical dimension. The list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names. Specifically, if the indicator management module selects a physical dimension for summary analysis of composite indicators, and if the physical dimension has role-playing dimensions among the atomic indicators constituting the composite indicator, then the appropriate role-playing dimension is selected, and dynamic SQL is constructed to achieve summarization based on different role-playing dimensions. When the indicator management module performs parallel analysis on multiple indicators, if the facts of all the indicators involved in the analysis are related to the same physical dimension, the physical dimension is used to summarize the multiple indicators. The list of related dimensions is displayed using the physical dimension name to avoid confusion caused by different role names. When the indicator management module selects a physical dimension to summarize and analyze multiple indicators, if the physical dimension has role-playing dimensions for some indicators, the appropriate role-playing dimension is selected. If one of the indicators is a composite indicator, the appropriate role-playing dimension among the atomic indicators that constitute the composite indicator is specified at the same time, and dynamic SQL is constructed to summarize the indicators based on different role-playing dimensions.
4. The system for implementing the role-playing dimension according to claim 3, characterized in that: The model management module records the business meaning and category of fact tables through fact tables, the attribute name and category of each field in fact tables through fact attribute definition tables, the association between fact table attributes and dimension table surrogate keys through association definition tables, the business meaning and category of each dimension table through dimension definition tables, and the attribute name and category of each field in dimension tables through dimension attribute definition tables.