A method and apparatus for dimension construction compatible with both coarse and fine granularity

Abstract

This invention discloses a method and apparatus for constructing dimensions that is compatible with both coarse and fine granularity, relating to the field of big data processing technology. The method includes: Step 1: Reconstructing the dimension table structure, adding surrogate keys for each granularity, and aligning the granularity of each dimension attribute with that of each surrogate key; Step 2: Expanding the dimension table metamodel, adding granularity level description information, and marking the granularity level of all surrogate keys and attributes; Step 3: When establishing the association between the fact table and the dimension table, selecting the surrogate key of the corresponding granularity in the dimension for association based on the actual granularity of the business facts; Step 4: First, deduplicating the coarse-grained surrogate keys in the dimension table, then associating the fact table with the deduplicated dataset to achieve accurate cross-granularity statistical analysis and clearly indicating changes in the business meaning of indicators. This invention overcomes the limitation that facts and dimensions must have consistent granularity, allowing for granularity differences between the fact table and the dimension table in dimensional modeling, and solving the need to group and statistically analyze facts using finer-grained dimension attributes.

Description

Technical Field

[0001] This invention discloses a method and apparatus for constructing dimensions that is compatible with both coarse and fine granularity, relating to the field of big data processing technology. Background Technology

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

[0003] Existing dimensional model construction methods place particular emphasis on granularity alignment, meaning that fact granularity and dimension granularity must be consistent. However, in actual dimensional model construction, there are various situations requiring cross-dimensional granularity fact table construction. For example, in government big data systems, service object identity categories are used as context for analyzing related business processes. In this case, a business fact table and a service object identity dimension table should be constructed. In practice, some service objects may have multiple identity categories, meaning one person may have multiple records in the service object identity dimension table. This leads to a mismatch between the granularity of the business facts and the primary surrogate key of the service object identity dimension. Existing technologies generally adopt solutions that satisfy granularity alignment, but they have some drawbacks: for example, they cannot achieve cross-granularity association from low-granularity facts to high-granularity dimensions, and they cannot clarify various derivative effects, such as changes in granularity leading to changes in analytical indicator values, resulting in statistical errors and user misunderstandings. Summary of the Invention

[0004] This invention addresses the problems of existing technologies by providing a method and apparatus for constructing and applying dimensions that are compatible with both coarse and fine granularity. It overcomes the limitation of existing dimensional modeling methods that require facts and dimensions to have consistent granularity: it supports the construction of dimensions that are compatible with multiple granularities simultaneously; it supports the association of a dimension compatible with multiple granularities with facts of different granularities; it supports the summary analysis of an indicator based on dimensional attributes of different granularities; it avoids errors in indicator calculation due to differences in granularity between facts and dimensions; and it provides a business interpretation of changes in the actual meaning of statistical indicators caused by selecting dimensional attributes of different granularities for statistical summary.

[0005] The specific solution proposed in this invention is as follows:

[0006] This invention provides a method for constructing dimensions that is compatible with both coarse and fine granularity, comprising:

[0007] Step 1: Restructure the dimension table structure, add surrogate keys at each granularity, and align the granularity of each dimension attribute with that of each surrogate key:

[0008] Step 11: Based on business needs, plan the number of granularities for the current dimension, and create different proxy keys according to the number of granularities. Each proxy key corresponds to a granularity.

[0009] Step 12: Analyze the granularity of each attribute in the dimension table and align it with each surrogate key. If no surrogate key of the same granularity exists, align it with the surrogate key of a finer granularity.

[0010] Step 2: Expand the dimension table meta-model, add granular hierarchical description information, and mark the granular hierarchy of all surrogate keys and attributes;

[0011] Step 3: When establishing the association between the fact table and the dimension table, select the surrogate key of the corresponding granularity in the dimension for association based on the actual granularity of the business facts;

[0012] Step 4: First, deduplicate the coarse-grained surrogate keys in the dimension tables. Then, associate the fact table with the deduplicated dataset to achieve cross-granularity statistical analysis and clearly indicate changes in the business meaning of the indicators.

[0013] Step 41: When performing statistical analysis, first obtain the surrogate key that associates the facts with the dimension table through the model, and use SELECT DISTINCT to obtain the dataset containing the surrogate key and the selected attribute. Then associate the fact table with the dataset of the selected attribute for statistical analysis to ensure that the analysis results are accurate and meet business requirements.

[0014] Step 42: Based on the attributes and surrogate key granularity levels marked in Step 2, if the selected dimension attribute is finer than the associated surrogate key granularity, provide a clear prompt explaining the change in the business meaning of the indicator.

[0015] Furthermore, in step 11 of the method for constructing and applying dimensions that is compatible with both coarse and fine granularity, different surrogate keys are created according to the number of granularities, including: setting the finest granularity surrogate key as the primary surrogate key, corresponding to the first granularity; setting the coarser granularity surrogate key as the secondary surrogate key, corresponding to the second granularity, and so on.

[0016] In step 12, the dimension attribute is aligned with the surrogate key at a finer granularity. If there is no sibling surrogate key, it is aligned with a surrogate key at a finer granularity.

[0017] Furthermore, in step 2 of the method for constructing application with both coarse and fine granularity, when marking the granularity level of all proxy keys and attributes, the finest granularity level of the primary proxy key is marked as 1, and each level is incremented as 2, 3...n according to the order of increasing granularity.

[0018] Furthermore, in step 3 of the aforementioned method for constructing and applying dimensions that is compatible with both coarse and fine granularity, the association between facts and dimensions is established in the metamodel, supporting the association of facts with surrogate keys corresponding to the granularity of dimensions based on their own granularity.

[0019] Furthermore, in step 41 of the aforementioned method for constructing and applying dimensions that is compatible with both coarse and fine granularity, a certain dimension attribute is selected as the summarization context, and the surrogate key of the dimension associated with the fact is obtained based on the meta-model.

[0020] Use SELECT DISTINCT to remove duplicate records introduced by low-granularity surrogate keys before joining them with the fact table to avoid statistical errors.

[0021] The granularity level of the associated dimension surrogate key is compared with that of the selected dimension attribute. If the granularity level of the dimension attribute is less than that of the granularity level of the dimension surrogate key, 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 change in granularity.

[0022] This invention also provides a dimension building application device compatible with both coarse and fine granularity, including a reconstruction module, an extension module, a correlation module, and an analysis module.

[0023] The refactoring module refactors the dimension table structure, adds surrogate keys at various granularities, and aligns the granularity of each dimension attribute with that of each surrogate key.

[0024] Step 11: Based on business needs, plan the number of granularities required for the current dimension, and create different surrogate keys according to the number of granularities the dimension needs to include. Each surrogate key corresponds to a granularity.

[0025] Step 12: Analyze the granularity of each attribute in the dimension table and align it with the granularity of each surrogate key. If there is no surrogate key with the same granularity, align it with the surrogate key with a finer granularity. Alignment means setting the same granularity level.

[0026] The extension module expands the dimension table meta-model, adding granular hierarchical description information and marking the granular hierarchy of all surrogate keys and attributes;

[0027] When the association module establishes an association between the fact table and the dimension table, it selects the surrogate key of the corresponding granularity in the dimension for association based on the actual granularity of the business facts.

[0028] The analysis module first deduplicates the coarse-grained surrogate keys in the dimension tables, then associates the fact table with the deduplicated dataset to achieve cross-granularity statistical analysis and clearly indicate changes in the business meaning of the indicators:

[0029] Step 41: When performing statistical analysis, first obtain the surrogate key that associates the facts with the dimension table through the model, and use SELECT DISTINCT to obtain the dataset containing the surrogate key and the selected attribute. Then associate the fact table with the dataset of the selected attribute for statistical analysis to ensure that the analysis results are accurate and meet business requirements.

[0030] Step 42: By analyzing the attributes and surrogate key granularity levels marked by the extended module, if the selected dimension attribute is more granular than the associated surrogate key, provide the user with a clear prompt explaining the change in the meaning of the indicator.

[0031] Furthermore, in the reconstruction module of the aforementioned coarse-grained dimensional construction application device, step 11 involves creating different surrogate keys based on the number of granularities. This includes: setting the finest-grained surrogate key in the dimension as the primary surrogate key, corresponding to the first granularity; setting the coarser-grained surrogate key as the secondary surrogate key, corresponding to the second granularity, and so on.

[0032] Step 12 aligns the dimension attributes with the surrogate key at a finer granularity. If no sibling surrogate key exists, it aligns with a surrogate key at a finer granularity.

[0033] Furthermore, when the extended module of the dimensional construction application device compatible with coarse and fine granularity marks the granularity level of all proxy keys and attributes, the finest granularity level of the primary proxy key is marked as 1, and each level is incremented as 2, 3...n according to the order of increasing granularity.

[0034] Furthermore, the association module of the dimension construction application device that is compatible with both coarse and fine granularity establishes the association between facts and dimensions in the metamodel, and supports the association of facts with surrogate keys of the corresponding granularity of dimensions based on their own granularity.

[0035] Furthermore, in step 41 of the analysis module of the application device for constructing dimensions compatible with both coarse and fine granularity, a certain dimension attribute is selected as the summary context, and the surrogate key of the dimension associated with the fact is obtained based on the meta-model.

[0036] Use SELECT DISTINCT to remove duplicate records introduced by low-granularity surrogate keys before joining them with the fact table to avoid statistical errors.

[0037] The granularity level of the associated dimension surrogate key is compared with that of the selected dimension attribute. If the granularity level of the dimension attribute is less than that of the granularity level of the dimension surrogate key, 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 change in granularity.

[0038] The advantages of this invention are:

[0039] This method breaks through the limitation of existing dimensional modeling methods that require facts and dimensions to have consistent granularity. It allows for granularity differences between fact tables and dimension tables in dimensional modeling, and solves the need in some business scenarios to use finer-grained dimensional attributes to group and count facts.

[0040] This method can reuse key dimensions to the greatest extent and avoid the problem of not being able to analyze multiple indicators based on the same dimension due to the expansion of new dimensions with different granularities.

[0041] This method reduces the number of dimension tables, enabling the merging of multiple dimension tables at different granularities into one, thus reducing the workload of ETL implementation. Attached Figure Description

[0042] Figure 1 This is a schematic diagram of the method flow of the present invention. Detailed Implementation

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

[0044] Example 1: This invention provides a dimension construction application method compatible with both coarse and fine granularity, comprising:

[0045] Step 1: Restructure the dimension table structure and add surrogate keys at various granularities:

[0046] Step 11: Based on the number of granularities required for the dimension, construct different surrogate keys for the dimension. Each surrogate key corresponds to a granularity. The finest granularity surrogate key in the dimension is set as the primary surrogate key, corresponding to the first granularity; the coarser granularity surrogate key is set as the secondary 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 card surrogate key. These two surrogate keys can be used to associate with fact tables of different granularities that contain specific identity attributes or individual attributes of the service object.

[0047] Step 12: Analyze the granularity of each attribute in the dimension table and align the granularity of each proxy key; specifically, align the service object identity category attribute with the main proxy key, and align the service object ID card attribute with the ID card proxy key.

[0048] Step 2: Expand the dimension table meta-model by adding granularity level description information and marking the granularity level of all surrogate keys and attributes. When marking the granularity level of all surrogate keys and attributes, the finest granularity primary surrogate key is marked as 1, and each level is incremented by 2, 3...n according to the order of increasing granularity. Taking the service object identity dimension as an example, the granularity level corresponding to the primary surrogate key and the service object identity category attribute is marked as 1, while the granularity level corresponding to the ID card surrogate key, other attributes such as gender and age is marked as 2.

[0049] When expanding the dimension metamodel, add the GRANULARITY_LEVEL field to mark the granularity level of each surrogate key and attribute. The following is an example of a dimension metamodel for reference:

[0050] create table BI_TABLE_INFO (

[0052] COLUMN_SK String comment 'primary key',

[0053] TABLE_NAME Nullable(String) comment 'table name',

[0054] TABLE_TYPE_CODE Nullable(String) comment 'Table type encoding',

[0055] TABLE_TYPE_NAME Nullable(String) comment 'Table type name',

[0056] COLUMN_NAME Nullable(String) comment 'Field Name',

[0057] COLUMN_TYPE_CODE Nullable(String) comment 'Field type encoding',

[0058] COLUMN_TYPE_NAME Nullable(String) comment 'Field type name',

[0059] DELETED Nullable(String) comment 'deletion flag',

[0060] CTIME Nullable(DateTime) comment 'creation time',

[0061] UTIME Nullable(DateTime) comment 'Update Time',

[0062] PARENT_FIELD Nullable(String) comment 'Parent field name, used for sorting and deduplication',

[0063] ORDER_SEQ Nullable(Int32) comment 'Attribute display sorting',

[0064] ORDER_FIELD_FLAG Nullable(Int8) comment 'sort field identifier',

[0065] ORDER_MODE Nullable(Int8) comment 'sorting mode',

[0066] PARENT_LEVEL_FIELD Nullable(String) comment 'Parent level field name (used for level dimension settings)',

[0067] SORT_VALUE_REVERSE_QUERY_FIELD Nullable(String) comment 'Reverse lookup field name for sort value',

[0068] ORGAN_RESTRAIN_FIELDNullable(Int8) comment 'Organization constraint field flag',

[0069] ROLE_RESTRAIN_FIELD Nullable(Int8) comment 'Role constraint field',

[0070] GRANULARITY_LEVEL Nullable(Int8) comment 'Granularity level', )

[0072] engine = MergeTreeORDER BY COLUMN_SK

[0073] Comment 'Analyze database table information'

[0074] Step 3: When establishing the association between the fact table and the dimension table, select the corresponding surrogate key in the dimension for association based on the actual granularity of the business facts. Taking the service object identity dimension as an example, some business facts cannot be resolved to the granularity of the dimension's primary surrogate key, but can be associated to the granularity of the ID card surrogate key. Therefore, the attributes of this fact table can be associated with the ID card surrogate key of the service object identity dimension. See Table 1 for reference. The following is a design example of the metamodel association table BI_DIM_MAP, which supports defining the association between fact tables and dimension tables:

[0075] create table BI_DIM_MAP (

[0077] MAP_SK String comment 'primary key',

[0078] MAP_TYPE_CODE Nullable(String) comment 'Mapping type encoding',

[0079] MAP_TYPE_NAME Nullable(String) comment 'Mapping type name',

[0080] SOURCE_TABLE_TYPE_CODE Nullable(String) comment 'Source table type code',

[0081] SOURCE_TABLE_TYPE_NAME Nullable(String) comment 'Source table type name',

[0082] SOURCE_TABLE_NAME Nullable(String) comment 'Source table name',

[0083] SOURCE_COLUMN_NAME Nullable(String) comment 'Source field name',

[0084] GOAL_TABLE_NAME Nullable(String) comment 'target table name',

[0085] GOAL_COLUMN_NAME Nullable(String) comment 'Mapped target encoded field name',

[0086] GOAL_VALUE_COLUMN_NAME Nullable(String) comment 'Name of the target field to be mapped',

[0087] DIC_CODE Nullable(String) comment 'dictionary code value',

[0088] DELETED Nullable(String) comment 'deletion flag',

[0089] CTIME Nullable(DateTime) comment 'creation time',

[0090] UTIME Nullable(DateTime) comment 'Update Time' )

[0092] engine = MergeTreeORDER BY MAP_SK

[0093] Comment 'Dimensional Fact Map'.

[0094] Step 4: Perform statistical analysis based on the association between the corresponding surrogate key and various business facts. Since the coarse-grained surrogate key is not a primary key, when the fact table is associated with the coarser-grained dimension table surrogate key, if the facts are directly associated using a select statement without the DISTINCT keyword through the coarse-grained surrogate key of the dimension table, the number of records in the returned dataset will exceed the number of records in the fact table, resulting in incorrect statistical results. The root cause of the problem is that the coarse-grained surrogate key is not a primary key.

[0095] To solve the above problem, try using the SELECT DISTINCT keyword to filter the dimension table data. See below for reference:

[0096] 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 granularity of the fact may differ from the granularity of the associated surrogate key, resulting in differences in the datasets returned by the following JOIN statements:

[0097] SELECT *

[0098] FROM fact_table AS f

[0099] LEFT JOIN (

[0100] SELECT DISTINCT dim_attribute_name1, dim_sk1

[0101] FROM dim_table

[0102] ) AS d ON f.attribute_fk1 = d.dim_sk1;

[0103] Case 1: When the selected dimension table attributes are equal to or coarser than the fact granularity, the JOIN returns a dataset with a granularity equal to that of the fact table.

[0104] Scenario 2: When the selected dimension table attributes are finer-grained compared to the fact granularity, the dataset returned by JOIN is finer-grained than the fact table. This can be understood as the facts being split based on finer-grained dimension attributes.

[0105] Using practical examples, the business implications of the indicators in scenario two are explained:

[0106] When a business fact is associated with the ID card proxy key of the service object's identity dimension (which is the same as the fact dimension), and then summarized at a finer granularity based on the service object's identity category attribute compared to the fact dimension, the example statement is as follows:

[0107] SELECT *

[0108] FROM fact_table_1 ASf

[0109] LEFT JOIN (

[0110] SELECT DISTINCTdim_client_identity.id_sk

[0111] ) as d on f.attribute_fk1 = d.id_sk

[0112] When a service recipient has n identities, the original single business record becomes n records after JOIN. This can be understood as each row of the dataset representing the business transactions for each service recipient's identity. Because different authorities focus on the business transactions for different service recipient identities, the counts or other summary indicators for each identity's business transactions obtained from the attribute aggregation statistics in the above dataset are accurate. However, the total number of business transactions for all identities is greater than the original total number of facts.

[0113] If SELECT DISTINCT is not used to select dimension table data before LEFT JOIN, and aggregation is performed using the same or coarser-grained attributes, the JOIN dataset will still generate attribute splitting and expansion based on finer grained attributes. This is meaningless to the business. For example, if aggregation is based on gender, the total number obtained will be greater than the original fact because the original fact is based on the ID card of the service object, which is at the same granularity as gender. The resulting statistical deviation has no business significance, which is an error. Therefore, SELECT DISTINCT should be used to select dimension table data in all scenarios.

[0114] Step 42: When constructing the above multi-granularity dimensions and associating various facts with keys of different granularities for statistical analysis, not all business intelligence system users can clearly recognize the granularity differences between facts and dimensions, or understand the changes in the meaning of statistical indicators caused by different granularities of selected attributes during aggregation. Therefore, it is necessary to provide users with clear prompts. 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 meta-model. 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 (which indicates that the attribute granularity is finer than the granularity of the associated surrogate key), the customer needs to be prompted about the difference in results caused by the change in granularity. Example: "Because the granularity of the selected attribute 'Service Object Identity Category' is finer than the granularity of the business facts, some facts will be split according to 'Service Object Identity Category,' resulting in a total number of results exceeding the original total number of facts. Please be aware."

[0115] Example 2: The present invention also provides a dimension construction application device compatible with both coarse and fine granularity, including a reconstruction module, an extension module, a correlation module, and an analysis module.

[0116] The refactoring module refactors the dimension table structure, adds surrogate keys at various granularities, and aligns the granularity of each dimension attribute with that of each surrogate key.

[0117] Step 11: Based on business needs, plan the number of granularities required for the current dimension, and create different surrogate keys according to the number of granularities the dimension needs to include. Each surrogate key corresponds to a granularity.

[0118] Step 12: Analyze the granularity of each attribute in the dimension table and align it with the granularity of each surrogate key. If there is no surrogate key with the same granularity, align it with the surrogate key with a finer granularity. Alignment means setting the same granularity level.

[0119] The extension module expands the dimension table meta-model, adding granular hierarchical description information and marking the granular hierarchy of all surrogate keys and attributes;

[0120] When the association module establishes an association between the fact table and the dimension table, it selects the surrogate key of the corresponding granularity in the dimension for association based on the actual granularity of the business facts.

[0121] The analysis module first deduplicates the coarse-grained surrogate keys in the dimension tables, then associates the fact table with the deduplicated dataset to achieve cross-granularity statistical analysis and clearly indicate changes in the business meaning of the indicators:

[0122] Step 41: When performing statistical analysis, first obtain the surrogate key that associates the facts with the dimension table through the model, and use SELECT DISTINCT to obtain the dataset containing the surrogate key and the selected attribute. Then associate the fact table with the dataset of the selected attribute for statistical analysis to ensure that the analysis results are accurate and meet business requirements.

[0123] Step 42: By analyzing the attributes and surrogate key granularity levels marked by the extended module, if the selected dimension attribute is more granular than the associated surrogate key, provide the user with a clear prompt explaining the change in the meaning of the indicator.

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

[0125] Similarly, the advantages of the device of the present invention are:

[0126] It breaks through the limitation of existing dimensional modeling methods that require facts and dimensions to have consistent granularity, allowing for differences in granularity between fact tables and dimension tables in dimensional modeling, and solving the need in some business scenarios to use finer-grained dimensional attributes to group and count facts;

[0127] It can reuse key dimensions to the greatest extent and avoid the problem of not being able to analyze multiple indicators based on the same dimension due to the expansion of new dimensions with different granularities;

[0128] The number of dimension tables is reduced, allowing multiple dimension tables at different granularities to be combined into one, thus reducing the workload of ETL implementation.

[0129] Table 1:

[0130]

[0131] It should be noted that not all steps and modules in the above processes and device structures are mandatory; some steps or modules can be omitted as needed. The execution order of each step is not fixed and can be adjusted as required. The system structure described in the above 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 they may be jointly implemented by certain components in multiple independent devices.

[0132] 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 dimension construction application method compatible with both coarse and fine granularity, characterized by: include: Step 1: Restructure the dimension table structure, add surrogate keys at each granularity, and align the granularity of each dimension attribute with that of each surrogate key: Step 11: Based on business needs, plan the number of granularities for the current dimension, and create different proxy keys according to the number of granularities. Each proxy key corresponds to a granularity. Step 12: Analyze the granularity of each attribute in the dimension table and align it with each surrogate key. If no surrogate key of the same granularity exists, align it with the surrogate key of a finer granularity. Step 2: Expand the dimension table meta-model, add granular hierarchical description information, and mark the granular hierarchy of all surrogate keys and attributes; Step 3: When establishing the association between the fact table and the dimension table, select the surrogate key of the corresponding granularity in the dimension for association based on the actual granularity of the business facts; Step 4: First, deduplicate the coarse-grained surrogate keys in the dimension tables. Then, associate the fact table with the deduplicated dataset to achieve 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 facts with the dimension table through the model, and use SELECTDISTINCT to obtain the dataset containing the surrogate key and the selected attribute. Then associate the fact table with the dataset of the selected attribute 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, if the selected dimension attribute is finer than the associated surrogate key granularity, provide a clear prompt explaining the change in the business meaning of the indicator.

2. The dimension construction application method compatible with both coarse and fine granularity as described in claim 1, characterized in that: Step 11 creates different surrogate keys based on the number of granularities, including: setting the finest-grained surrogate key as the primary surrogate key, corresponding to the first granularity; setting the coarser-grained surrogate key as the next-level surrogate key, corresponding to the second granularity, and so on. In step 12, the dimension attribute is aligned with the surrogate key at a finer granularity. If there is no sibling surrogate key, it is aligned with a surrogate key at a finer granularity.

3. The dimension construction application method compatible with both coarse and fine granularity as described in claim 1, characterized in that: In step 2, when marking the granularity level of all proxy keys and attributes, the finest granularity level of the primary proxy key is marked as 1, and each level is marked as 2, 3...n in order of increasing granularity.

4. The dimension construction application method compatible with coarse and fine granularity as described in claim 1 is characterized in that, in step 3, the association between facts and dimensions is established in the metamodel, and facts are associated with surrogate keys of the corresponding granularity of dimensions based on their own granularity.

5. The dimension construction and application method compatible with both coarse and fine granularity as described in claim 1, characterized in that in step 41, a certain dimension attribute is selected as the summarization context, and the surrogate key of the fact association dimension 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. The granularity level of the associated dimension surrogate key is compared with that of the selected dimension attribute. If the granularity level of the dimension attribute is less than that of the granularity level of the dimension surrogate key, 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 change in granularity.

6. A dimension-building application device compatible with both coarse and fine granularity, characterized in that: It includes a reconstruction module, an extension module, a correlation module, and an analysis module. The refactoring module refactors the dimension table structure, adds surrogate keys at various granularities, and aligns the granularity of each dimension attribute with that of each surrogate key. Step 11: Based on business needs, plan the number of granularities required for the current dimension, and create different surrogate keys according to the number of granularities the dimension needs to include. Each surrogate key corresponds to a granularity. Step 12: Analyze the granularity of each attribute in the dimension table and align it with the granularity of each surrogate key. If there is no surrogate key with the same granularity, align it with the surrogate key with a finer granularity. Alignment means setting the same granularity level. The extension module expands the dimension table meta-model, adding granular hierarchical description information and marking the granular hierarchy of all surrogate keys and attributes; When the association module establishes an association between the fact table and the dimension table, it selects the surrogate key of the corresponding granularity in the dimension for association based on the actual granularity of the business facts. The analysis module first deduplicates the coarse-grained surrogate keys in the dimension tables, then joins the fact table with the deduplicated dataset to achieve 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 facts with the dimension table through the model, and use SELECTDISTINCT to obtain the dataset containing the surrogate key and the selected attribute. Then associate the fact table with the dataset of the selected attribute for statistical analysis to ensure that the analysis results are accurate and meet business requirements. Step 42: By analyzing the attributes and surrogate key granularity levels marked by the extended module, if the selected dimension attribute is more granular than the associated surrogate key, provide the user with a clear prompt explaining the change in the meaning of the indicator.

7. The dimension construction application device compatible with both coarse and fine granularity according to claim 6, characterized in that... The refactoring module executes step 11, which creates different surrogate keys based on the number of granularities. This includes: setting the finest-grained surrogate key as the primary surrogate key, corresponding to the first granularity; setting the coarser-grained surrogate key as the lower-level surrogate key, corresponding to the second granularity, and so on. Step 12 aligns the dimension attributes with the surrogate key at a finer granularity. If no sibling surrogate key exists, it aligns with a surrogate key at a finer granularity.

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

9. The dimension construction application device compatible with both coarse and fine granularity according to claim 6, characterized in that... The association module establishes the association between facts and dimensions in the metamodel, and supports the association of facts with surrogate keys of the corresponding granularity of dimensions based on their own granularity.

10. The dimension construction application device compatible with both coarse and fine granularity according to claim 6, characterized in that... In step 41 of the analysis module, a specific dimension attribute is selected as the summary context, and the surrogate key of the dimension associated with the facts 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. The granularity level of the associated dimension surrogate key is compared with that of the selected dimension attribute. If the granularity level of the dimension attribute is less than that of the granularity level of the dimension surrogate key, 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 change in granularity.

Images