Dimension construction application method and device compatible with coarse and fine granularities

Abstract

The invention discloses a dimension construction application method and device compatible with coarse and fine granularity, and relates to the technical field of big data processing. Comprising the following steps of: 1, reconstructing a dimension table structure, adding proxy keys of each granularity, and aligning each attribute of a dimension with each proxy key granularity; 2, expanding the dimension table element model, adding granularity level description information, and marking granularity levels of all proxy keys and attributes; 3, when the fact table and the dimension table are established to be associated, according to the actual granularity of the business facts, selecting the proxy key of the corresponding granularity in the dimensions to be associated; step 4, performing de-duplication on a dimension table coarse-grained proxy key, associating a fact table with a de-duplication data set, realizing accurate cross-granularity statistical analysis, and clearly prompting the change of the meaning of an index service; according to the method, the limitation that facts and dimensions must be consistent in granularity is broken through, granularity difference between a fact table and a dimension table in dimension modeling is allowed, and the requirement for grouping statistics of the facts by using dimension attributes with finer granularity is met.

Description

Technical Field

[0001] The present invention discloses a method and device for constructing a dimension application compatible with coarse and fine granularity, and relates to the technical field of big data processing. Background Art

[0002] Dimensional modeling: A data modeling approach used in data warehouse construction. Ralph Kimball first proposed the concept. In its simplest form, it constructs a data warehouse based on fact tables and dimension tables. Dimensional modeling is widely used in the business intelligence field.

[0003] Existing dimensional model construction methods place particular emphasis on granularity alignment, meaning that the granularity of facts and dimensions must be consistent. However, in actual dimensional model construction, there are various situations where fact tables need to be constructed across dimensional granularities. For example, in a government big data system, service object identity categories are used as context for analyzing related businesses. In this case, a business fact table and a service object identity dimension table should be constructed. However, in actual business operations, some service objects may have multiple identity categories, meaning that a single person has multiple records in the service object identity dimension table. This leads to a mismatch between the granularity of business facts and the primary and surrogate keys of the service object identity dimension. Existing technologies generally employ solutions that ensure granularity alignment, but these solutions suffer from several drawbacks: for example, they cannot achieve cross-granularity associations from low-granularity facts to high-granularity dimensions, and they cannot disentangle various derivative effects. For example, changes in granularity can lead to changes in analytical indicator values, leading to statistical errors and user misunderstandings. Summary of the Invention

[0004] In response to the problems of the prior art, the present invention provides an application method and device for dimension construction that is compatible with coarse and fine granularity, breaking through the limitation of the existing dimensional modeling method that facts and dimensions must have consistent granularity: supporting the construction of dimensions that are compatible with multiple granularities at the same time; supporting the association of a dimension that is compatible with multiple granularities with facts of multiple different granularities; supporting the summary analysis of an indicator based on dimension attributes of different granularities; avoiding indicator calculation errors caused by granularity differences between facts and dimensions; when selecting dimension attributes of different granularities for statistical summary, the actual meaning of the statistical indicator will be derived and explained in a business sense.

[0005] The specific scheme proposed by the present invention is: The present invention provides a method for constructing an application with dimensions compatible with coarse and fine granularity, comprising: Step 1: Restructure the dimension table, add surrogate keys at each granularity, and align dimension attributes with the surrogate key granularity: Step 11: Plan the number of current dimension granularities based on business needs, and create different surrogate keys based on the number of granularities, with each surrogate key corresponding to one granularity. Step 12: Sort out the granularity of each attribute in the dimension table and align it with the surrogate key. If a surrogate key of the same granularity does not exist, align it with the surrogate key of a finer granularity. Step 2: Expand the dimension table metamodel, add granularity level description information, and mark the granularity levels of all surrogate keys and attributes; Step 3: When establishing a relationship between the fact table and the dimension table, select the surrogate key of the corresponding granularity in the dimension based on the actual granularity of the business fact. Step 4: First, deduplicate the coarse-grained surrogate keys in the dimension tables, then join the fact table with the deduplicated dataset to implement cross-granularity statistical analysis and clearly indicate changes in the business meaning of the indicators: Step 41: When performing statistical analysis, first obtain the surrogate key that associates the fact table with the dimension table through the model. Then use SELECT DISTINCT to obtain a dataset containing the surrogate key and the selected attributes. Then, associate the fact table with the dataset containing the selected attributes for statistical analysis to ensure that the analysis results are accurate and meet business requirements. Step 42: Based on the attributes and surrogate key granularity levels marked in step 2, when it is determined that the selected dimension attribute is finer than the associated surrogate key granularity, a clear prompt is given to explain the change in the business meaning of the indicator.

[0006] Furthermore, in step 11 of the method for constructing an application compatible with coarse and fine granularity dimensions, different surrogate keys are created according to the number of granularities, including: setting the surrogate key with the finest granularity in the dimension as the primary surrogate key, corresponding to the first granularity; setting the surrogate key with the coarser granularity as the subordinate surrogate key, corresponding to the second granularity, and so on. In step 12, the dimension attributes are aligned with the surrogate key at the same level. If there is no surrogate key at the same level, they are aligned with a surrogate key of a finer granularity.

[0007] Furthermore, in step 2 of the method for constructing an application of dimensions compatible with coarse and fine granularity, when marking the granularity levels of all surrogate keys and attributes, the granularity level of the finest granularity primary surrogate key is marked as 1, and each level is incrementally marked as 2, 3...n according to the order in which the granularity becomes coarser.

[0008] Furthermore, in step 3 of the method for constructing an application compatible with coarse and fine granularity dimensions, an association between facts and dimensions is established in the meta-model, supporting the association of facts with surrogate keys of corresponding granularity of dimensions based on their own granularity.

[0009] Furthermore, in step 41 of the dimension construction application method compatible with coarse and fine granularity, a dimension attribute is selected as the summary context, and a dimension surrogate key associated with the fact is obtained based on the meta-model. Use SELECT DISTINCT to remove duplicate records introduced by low-granularity surrogate keys before joining them with the fact table to avoid statistical errors. Compare the granularity of the associated dimension surrogate key with the selected dimension attribute. If the dimension attribute granularity value is smaller than the dimension surrogate key granularity value, it means that the selected dimension table attribute is finer than the fact granularity. In this case, the user is prompted with the difference in the meaning of the indicator caused by the granularity change.

[0010] The present invention also provides a dimension construction application device compatible with coarse and fine granularity, including a reconstruction module, an expansion module, an association module, and an analysis module. The reconstruction module reconstructs the dimension table structure, adds surrogate keys at each granularity, and aligns dimension attributes with the surrogate key granularity: Step 11: Based on business needs, plan the number of granularities required for the current dimension and create different surrogate keys based on the number of granularities required for the dimension. Each surrogate key corresponds to one granularity. Step 12: Sort out the granularity of each attribute in the dimension table and align it with the surrogate keys. If a surrogate key of the same granularity does not exist, align it with a surrogate key of a finer granularity. Alignment means setting the same granularity level. The extension module extends the dimension table metamodel, adds granularity level description information, and marks the granularity level of all surrogate keys and attributes; When the association module establishes an association between a fact table and a dimension table, it selects the surrogate key of the corresponding granularity in the dimension for association based on the actual granularity of the business fact; The analysis module first deduplicates the coarse-grained surrogate keys in the dimension tables and then associates the fact table with the deduplicated dataset to implement cross-granularity statistical analysis and clearly indicate changes in the business meaning of indicators: Step 41: When performing statistical analysis, first obtain the surrogate key that associates the fact table with the dimension table through the model. Then use SELECT DISTINCT to obtain a dataset containing the surrogate key and the selected attributes. Then, associate the fact table with the dataset containing the selected attributes for statistical analysis to ensure that the analysis results are accurate and meet business requirements. Step 42: When it is determined that the selected dimension attribute is finer than the associated surrogate key granularity by extending the attribute and surrogate key granularity level of the module mark, a clear prompt is given to the user to explain the change in the meaning of the indicator.

[0011] Furthermore, the reconstruction module of the dimension construction application device compatible with coarse and fine granularity executes step 11 to create different surrogate keys according to the number of granularities, including: setting the surrogate key of the finest granularity in the dimension as the primary surrogate key, corresponding to the first granularity; setting the surrogate key of the coarser granularity as the subordinate surrogate key, corresponding to the second granularity, and so on. Execute step 12 to align the dimension attributes with the surrogate key. If no surrogate key exists at the same level, align with a surrogate key of a finer granularity.

[0012] Furthermore, when the extension module of the dimension construction application device compatible with coarse and fine granularity marks the granularity levels of all proxy keys and attributes, the finest granularity primary proxy key granularity level is marked as 1, and each level is incrementally marked as 2, 3...n according to the order of coarser granularity.

[0013] Furthermore, the association module of the dimension construction application device compatible with coarse and fine granularity establishes an association between facts and dimensions in the meta-model, and supports associating facts with surrogate keys of corresponding granularity of dimensions according to their own granularity.

[0014] Furthermore, the analysis module of the coarse-grained and fine-grained dimension construction application device executes step 41 to select a dimension attribute as a summary context, obtain a dimension surrogate key associated with the fact based on the meta-model, Use SELECT DISTINCT to remove duplicate records introduced by low-granularity surrogate keys before joining them with the fact table to avoid statistical errors. Compare the granularity of the associated dimension surrogate key with the selected dimension attribute. If the dimension attribute granularity value is smaller than the dimension surrogate key granularity value, it means that the selected dimension table attribute is finer than the fact granularity. In this case, the user is prompted with the difference in the meaning of the indicator caused by the granularity change.

[0015] The benefits of the present invention are: This method breaks through the limitation of existing dimensional modeling methods that facts and dimensions must have the same granularity. It allows for different granularities between fact tables and dimension tables in dimensional modeling, solving the need to use finer-grained dimension attributes to group and count facts in certain business scenarios. This method can reuse key dimensions to the greatest extent possible, avoiding the problem of being unable to analyze multiple indicators based on the same dimension due to the expansion of new dimensions due to different granularity; This method reduces the number of dimension tables, can merge multiple dimension tables of multiple granularities into one, and reduces the workload of ETL implementation. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] Figure 1 It is a schematic flow chart of the method of the present invention. DETAILED DESCRIPTION

[0017] The present invention will be further described below with reference to specific examples so that those skilled in the art can better understand the present invention and implement it, but the examples are not intended to limit the present invention.

[0018] Example 1: The present invention provides a method for constructing an application with dimensions compatible with coarse and fine granularity, comprising: Step 1: Restructure the dimension table structure and add surrogate keys at each granularity: Step 11: Construct different surrogate keys for the dimension based on the number of granularities that the dimension needs to contain. Each surrogate key corresponds to a granularity. The surrogate key with the finest granularity in the dimension is set as the primary surrogate key, corresponding to the first granularity; the surrogate key with a coarser granularity is set as the subordinate surrogate key, corresponding to the second granularity, and so on. Taking the service object identity dimension as an example, the primary surrogate key is the service object identity surrogate key, and the second surrogate key is the service object ID surrogate key. These two surrogate keys can be used to associate with fact tables of different granularities containing the specific identity attributes or individual attributes of the service object.

[0019] Step 12: Sort out the granularity of each attribute in the dimension table and align the granularity of each surrogate key; among them, align the service object identity category attribute with the primary surrogate key, and align the service object ID card attribute with the ID card surrogate key.

[0020] Step 2: Expand the dimension table metamodel, add granularity level description information, and mark the granularity levels of all surrogate keys and attributes. When marking the granularity levels of all surrogate keys and attributes, mark the finest primary surrogate key as 1, and mark each level as 2, 3, and so on, in order of increasing granularity. For example, in the service object identity dimension, the granularity level corresponding to the primary surrogate key and service object identity category attributes is marked as 1, and the granularity level corresponding to the ID card surrogate key and other attributes such as gender and age is marked as 2.

[0021] When extending the dimension table metamodel, add the GRANULARITY_LEVEL field to mark the granularity level of each surrogate key and attribute. The following is an example of the dimension table metamodel for reference: create table BI_TABLE_INFO ( COLUMN_SK String comment 'Primary key', TABLE_NAME Nullable(String) comment 'Data table name', TABLE_TYPE_CODE Nullable(String) comment 'Data table type code', TABLE_TYPE_NAME Nullable(String) comment 'Data table type name', COLUMN_NAME Nullable(String) comment 'Field name', COLUMN_TYPE_CODE Nullable(String) comment 'Field type code', COLUMN_TYPE_NAME Nullable(String) comment 'Field type name', DELETED Nullable(String) comment 'delete flag', CTIME Nullable(DateTime) comment 'creation time', UTIME Nullable(DateTime) comment 'Update time', PARENT_FIELD Nullable(String) comment 'Parent field name, used for sorting and calculation to remove duplicates', ORDER_SEQ Nullable(Int32) comment 'property display sort', ORDER_FIELD_FLAG Nullable(Int8) comment 'sort field identifier', ORDER_MODE Nullable(Int8) comment 'sort mode', PARENT_LEVEL_FIELD Nullable(String) comment 'Parent level field name (for level dimension setting)', SORT_VALUE_REVERSE_QUERY_FIELD Nullable(String) comment 'sort value reverse query field name', ORGAN_RESTRAIN_FIELDNullable(Int8) comment 'Organization constraint field tag', ROLE_RESTRAIN_FIELD Nullable(Int8) comment 'Role constraint field', GRANULARITY_LEVEL Nullable(Int8) comment 'Granularity level', ) engine = MergeTreeORDER BY COLUMN_SK comment 'Analyze library table information' Step 3: When establishing an association between the fact table and the dimension table, select the surrogate key of the corresponding granularity in the dimension based on the actual granularity of the business fact. Taking the service object identity dimension as an example, some business facts cannot be parsed to the dimension primary surrogate key granularity, but can be associated to the ID card surrogate key granularity. Therefore, the attributes of the fact table can be associated with the ID card surrogate key of the service object identity dimension. Please refer to Table 1. The following is an example of the design of the metamodel association table BI_DIM_MAP, which supports the definition of the association relationship between the fact table and the dimension table: create table BI_DIM_MAP ( MAP_SK String comment 'Primary key', MAP_TYPE_CODE Nullable(String) comment 'Mapping type code', MAP_TYPE_NAME Nullable(String) comment 'Mapping type name', SOURCE_TABLE_TYPE_CODE Nullable(String) comment 'Source table type code', SOURCE_TABLE_TYPE_NAME Nullable(String) comment 'Source table type name', SOURCE_TABLE_NAME Nullable(String) comment 'Source table name', SOURCE_COLUMN_NAME Nullable(String) comment 'Source field name', GOAL_TABLE_NAME Nullable(String) comment 'Target table name', GOAL_COLUMN_NAME Nullable(String) comment 'Mapping target encoding field name', GOAL_VALUE_COLUMN_NAME Nullable(String) comment 'Mapping target value field name', DIC_CODE Nullable(String) comment 'Dictionary code value', DELETED Nullable(String) comment 'delete flag', CTIME Nullable(DateTime) comment 'creation time', UTIME Nullable(DateTime) comment 'Update time' ) engine = MergeTreeORDER BY MAP_SK comment 'Dimension fact mapping table'.

[0022] Step 4: Perform statistical analysis based on the association between the corresponding granularity's surrogate key and various business facts. Because the coarse-grained surrogate key is not the primary key, when the fact table is associated with the coarser-grained dimension table's surrogate key, directly using the select statement without the DISTINCT keyword to associate the facts through the dimension table's coarse-grained surrogate key will result in the number of records in the returned result dataset exceeding the number of records in the fact table, resulting in incorrect statistical results. The essence of the problem is that the coarse-grained surrogate key is not the primary key.

[0023] To solve the above problem, try to use the SELECT DISTINCT keyword to filter the dimension table data, as shown below: Step 41: When performing statistical analysis based on any attribute in a multi-attribute dimension table, considering that dimension attributes have multiple granularities, the selected summary attribute and the fact granularity, which is equal to the granularity of the associated surrogate key, may differ. This results in differences in the data sets returned by the following JOIN statements: SELECT * FROM fact_table AS f LEFT JOIN ( SELECT DISTINCT dim_attribute_name1, dim_sk1 FROM dim_table ) AS d ON f.attribute_fk1 = d.dim_sk1; Case 1: When the selected dimension table attributes are equal to or coarser than the fact granularity, the granularity of the dataset returned by the JOIN is equal to the granularity of the fact table; In the second case, when the selected dimension table attributes are finer than the fact granularity, the dataset returned by the JOIN is finer than the fact table granularity. This can be understood as the fact being split based on the finer-grained dimension attributes.

[0024] Based on actual examples, the business significance of the indicators in Case 2 is explained: After a business fact is associated with the ID card surrogate key of the service object identity dimension (the same as the fact dimension), when summarizing based on the service object identity category attribute at a finer granularity than the fact dimension, the schematic statement is as follows: SELECT * FROM fact_table_1 ASf LEFT JOIN ( SELECT DISTINCTdim_client_identity.id_sk ) as d on f.attribute_fk1 = d.id_sk When a service recipient has n identities, the dataset becomes n records after the original business record is joined. This means each row represents the business transactions for each service recipient's identity. Because different regulatory authorities focus on the business transactions for different service recipient identities, the counts or other aggregated metrics for each identity's transactions, derived from the attribute aggregation statistics in the dataset, are accurate. However, the sum of all transactions for all identities is greater than the original total.

[0025] If SELECT DISTINCT is not used to select dimension table data before LEFT JOIN, aggregation using attributes of equal or coarser granularity will still result in splitting and expansion based on finer-grained attributes in the JOIN data set. This is meaningless to the business. For example, if aggregation is based on gender, the resulting statistical total will be greater than the original facts. This is because the original facts are based on the service recipient's ID card, which has the same granularity as gender. The resulting statistical deviation has no business significance and is an error. Therefore, SELECT DISTINCT should be used to select dimension table data in all scenarios.

[0026] Step 42: When constructing the aforementioned multi-granularity dimensions and associating them with various facts through keys of different granularities for statistical analysis, not all business intelligence system users clearly recognize the granularity differences between facts and dimensions, nor understand the changes in the meaning of statistical indicators caused by the different granularities of the attributes selected during aggregation. Therefore, it is necessary to provide clear prompts to users. The specific implementation method is as follows: In the business intelligence system, when a dimension attribute is selected for aggregation, the granularity level value of the dimension surrogate key associated with the fact and the granularity level value of the selected dimension attribute are obtained based on the metamodel. The two are compared. If the granularity level value of the dimension attribute is smaller than the granularity level value of the dimension surrogate key (indicating that the attribute granularity is finer than the granularity of the associated surrogate key), the user needs to be informed of the difference in results caused by the change in granularity. For example: "Because the granularity of the selected attribute "Service Object Identity Category" is finer than the granularity of the business fact, some facts will be split according to "Service Object Identity Category", resulting in the total number of results exceeding the original total number of facts. Please be aware of this." Example 2: The present invention also provides a dimension construction application device compatible with coarse and fine granularity, including a reconstruction module, an expansion module, an association module, and an analysis module. The reconstruction module reconstructs the dimension table structure, adds surrogate keys at each granularity, and aligns dimension attributes with the surrogate key granularity: Step 11: Based on business needs, plan the number of granularities required for the current dimension and create different surrogate keys based on the number of granularities required for the dimension. Each surrogate key corresponds to one granularity. Step 12: Sort out the granularity of each attribute in the dimension table and align it with the surrogate keys. If a surrogate key of the same granularity does not exist, align it with a surrogate key of a finer granularity. Alignment means setting the same granularity level. The extension module extends the dimension table metamodel, adds granularity level description information, and marks the granularity level of all surrogate keys and attributes; When the association module establishes an association between a fact table and a dimension table, it selects the surrogate key of the corresponding granularity in the dimension for association based on the actual granularity of the business fact; The analysis module first deduplicates the coarse-grained surrogate keys in the dimension tables and then associates the fact table with the deduplicated dataset to implement cross-granularity statistical analysis and clearly indicate changes in the business meaning of indicators: Step 41: When performing statistical analysis, first obtain the surrogate key that associates the fact table with the dimension table through the model. Then use SELECT DISTINCT to obtain a dataset containing the surrogate key and the selected attributes. Then, associate the fact table with the dataset containing the selected attributes for statistical analysis to ensure that the analysis results are accurate and meet business requirements. Step 42: When it is determined that the selected dimension attribute is finer than the associated surrogate key granularity by extending the attribute and surrogate key granularity level of the module mark, a clear prompt is given to the user to explain the change in the meaning of the indicator.

[0027] Since the information interaction and execution process between modules in the above-mentioned device are based on the same concept as the embodiment of the method of the present invention, the specific contents can be found in the description of the embodiment of the method of the present invention and will not be repeated here.

[0028] Likewise, the benefits of the device of the present invention are: This breaks through the limitation of existing dimensional modeling methods that facts and dimensions must have the same granularity. It allows for different granularities between fact tables and dimension tables in dimensional modeling, solving the need to use finer-grained dimension attributes to group and count facts in certain business scenarios. It can reuse key dimensions to the greatest extent possible, avoiding the problem of adding new dimensions due to different granularity, which would result in the inability to analyze multiple indicators based on the same dimension. The number of dimension tables is reduced, and multiple dimension tables of different granularities can be merged into one, reducing the workload of ETL implementation.

[0029] Table 1:

[0030] It should be noted that not all steps and modules in the above-mentioned processes and device structures are required, and certain steps or modules can be omitted according to actual needs. The execution order of each step is not fixed and can be adjusted as needed. The system structure described in the above-mentioned embodiments can be a physical structure or a logical structure, that is, some modules may be implemented by the same physical entity, or some modules may be implemented by multiple physical entities, or may be implemented by certain components in multiple independent devices.

[0031] The above embodiments are merely preferred embodiments for the purpose of fully illustrating 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 within the scope of protection of the present invention. The scope of protection of the present invention shall be subject to the claims.

Claims

1. A method for constructing an application with dimensions compatible with both coarse and fine granularity, characterized by: include: Step 1: Restructure the dimension table, add surrogate keys at each granularity, and align dimension attributes with the surrogate key granularity: Step 11: Plan the number of current dimension granularities based on business needs, and create different surrogate keys based on the number of granularities, with each surrogate key corresponding to one granularity. Step 12: Sort out the granularity of each attribute in the dimension table and align it with the surrogate key. If a surrogate key of the same granularity does not exist, align it with the surrogate key of a finer granularity. Step 2: Expand the dimension table metamodel, add granularity level description information, and mark the granularity levels of all surrogate keys and attributes; Step 3: When establishing a relationship between the fact table and the dimension table, select the surrogate key of the corresponding granularity in the dimension based on the actual granularity of the business fact. Step 4: First, deduplicate the coarse-grained surrogate keys in the dimension tables, then join the fact table with the deduplicated dataset to implement cross-granularity statistical analysis and clearly indicate changes in the business meaning of the indicators: Step 41: When performing statistical analysis, first obtain the surrogate key that associates the fact table with the dimension table through the model. Then use SELECT DISTINCT to obtain a dataset containing the surrogate key and the selected attributes. Then, associate the fact table with the dataset containing the selected attributes for statistical analysis to ensure that the analysis results are accurate and meet business requirements. Step 42: Based on the attributes and surrogate key granularity levels marked in step 2, when it is determined that the selected dimension attribute is finer than the associated surrogate key granularity, a clear prompt is given to explain the change in the business meaning of the indicator.

2. A method for constructing an application compatible with coarse and fine granularity dimensions according to claim 1, characterized in that In step 11, different surrogate keys are created according to the number of granularities, including: setting the surrogate key of the finest granularity in the dimension as the primary surrogate key, corresponding to the first granularity; setting the surrogate key of the coarser granularity as the subordinate surrogate key, corresponding to the second granularity, and so on. In step 12, the dimension attributes are aligned with the surrogate key at the same level. If there is no surrogate key at the same level, they are aligned with a surrogate key of a finer granularity.

3. The method for constructing an application compatible with coarse and fine granularity dimensions according to claim 1, characterized in that When marking the granularity levels of all surrogate keys and attributes in step 2, the finest granularity primary surrogate key granularity level is marked as 1, and each level is marked incrementally as 2, 3...n in the order of coarser granularity.

4. A dimension construction application method compatible with coarse and fine granularity according to claim 1, characterized in that in step 3, an association between facts and dimensions is established in the metamodel, supporting the association of facts based on their own granularity and the proxy key of the granularity corresponding to the dimension.

5. The method for constructing a dimension compatible with coarse and fine granularity according to claim 1, wherein in step 41, a dimension attribute is selected as the summary context, and a dimension surrogate key associated with the fact is obtained based on the metamodel. Use SELECT DISTINCT to remove duplicate records introduced by low-granularity surrogate keys before joining them with the fact table to avoid statistical errors. Compare the granularity of the associated dimension surrogate key with the selected dimension attribute. If the dimension attribute granularity value is smaller than the dimension surrogate key granularity value, it means that the selected dimension table attribute is finer than the fact granularity. In this case, the user is prompted with the difference in the meaning of the indicator caused by the granularity change.

6. A device for constructing a dimension that is compatible with both coarse and fine granularity, characterized by: Including reconstruction module, extension module, association module, analysis module, The reconstruction module reconstructs the dimension table structure, adds surrogate keys at each granularity, and aligns dimension attributes with the surrogate key granularity: Step 11: Based on business needs, plan the number of granularities required for the current dimension and create different surrogate keys based on the number of granularities required for the dimension. Each surrogate key corresponds to one granularity. Step 12: Sort out the granularity of each attribute in the dimension table and align it with the surrogate keys. If a surrogate key of the same granularity does not exist, align it with a surrogate key of a finer granularity. Alignment means setting the same granularity level. The extension module extends the dimension table metamodel, adds granularity level description information, and marks the granularity level of all surrogate keys and attributes; When the association module establishes an association between a fact table and a dimension table, it selects the surrogate key of the corresponding granularity in the dimension for association based on the actual granularity of the business fact; The analysis module first deduplicates the coarse-grained surrogate keys in the dimension tables and then associates the fact table with the deduplicated dataset to implement cross-granularity statistical analysis and clearly indicate changes in the business meaning of indicators: Step 41: When performing statistical analysis, first obtain the surrogate key that associates the fact table with the dimension table through the model. Then use SELECT DISTINCT to obtain a dataset containing the surrogate key and the selected attributes. Then, associate the fact table with the dataset containing the selected attributes for statistical analysis to ensure that the analysis results are accurate and meet business requirements. Step 42: When it is determined that the selected dimension attribute is finer than the associated surrogate key granularity by extending the attribute and surrogate key granularity level of the module mark, a clear prompt is given to the user to explain the change in the meaning of the indicator.

7. The device for constructing a dimension compatible with coarse and fine granularity according to claim 6, characterized in that The reconstruction module executes step 11 to create different surrogate keys according to the number of granularities, including: setting the surrogate key of the finest granularity in the dimension as the primary surrogate key, corresponding to the first granularity; the coarser granularity surrogate key as the subordinate surrogate key, corresponding to the second granularity, and so on. Execute step 12 to align the dimension attributes with the surrogate key. If no surrogate key exists at the same level, align with a surrogate key of a finer granularity.

8. A dimension construction application device compatible with coarse and fine granularity according to claim 6, characterized in that when the extension module marks the granularity levels of all proxy keys and attributes, the finest granularity primary proxy key granularity level is marked as 1, and each level is incrementally marked as 2, 3...n according to the order of coarser granularity.

9. The device for constructing a dimension compatible with coarse and fine granularity according to claim 6, characterized in that The association module establishes the association between facts and dimensions in the meta-model, and supports associating facts based on their own granularity with the surrogate key of the corresponding granularity of the dimension.

10. The device for constructing a dimension compatible with coarse and fine granularity according to claim 6, characterized in that The analysis module executes step 41 to select a dimension attribute as the summary context, and obtains the dimension surrogate key associated with the fact based on the meta-model. Use SELECT DISTINCT to remove duplicate records introduced by low-granularity surrogate keys before joining them with the fact table to avoid statistical errors. Compare the granularity of the associated dimension surrogate key with the selected dimension attribute. If the dimension attribute granularity value is smaller than the dimension surrogate key granularity value, it means that the selected dimension table attribute is finer than the fact granularity. In this case, the user is prompted with the difference in the meaning of the indicator caused by the granularity change.

Images