Construction and application method of organization dimension with data gathering characteristic
By building an intermediate table and adding proxy keys to generate a new institutional dimension table, the problem of internal department data convergence in the business intelligence system is solved, and data consistency and efficient statistical results are achieved.
Patent Information
- Application Number
- CN202510543231.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-04-28
- Publication Date
- 2025-08-15
AI Technical Summary
The existing business intelligence system cannot effectively converge internal department data in statistical needs, resulting in inconsistent statistical results and cannot meet the data needs when drilling through administrative agencies layer by layer.
By building an intermediate table, add dimension proxy keys, converge proxy keys and map proxy keys, generate a new institutional dimension table, and use the intermediate table to achieve convergence of internal department data, supporting data updates of slowly changing institutional dimensions.
It realizes data consistency when administrative agencies drill down layer by layer, reduces ETL resources and time consumption, and improves the efficiency and accuracy of data convergence.
Abstract
Description
Technical Field
[0001] The present invention relates to the field of big data processing in administration and management, and in particular to a method for constructing and applying an organizational dimension with data aggregation characteristics. Background Art
[0002] Dimensional modeling is 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] During the construction of a certain business intelligence system, the organizational dictionary table of the upstream business system includes administrative agencies at all levels of the ministry and internal departments of each level of agencies (such as various departments within provincial departments). Different businesses are associated with various types of units at different levels based on actual business handling conditions.
[0004] Based on the dimension table, it is converted into an organizational hierarchy dimension table; using this dimension table, after constructing facts based on the original business records and dimensions, the indicators are summarized and calculated using the organizational dimension. When drilling down from the high-level administrative agency, not only the lower-level administrative agencies will be displayed, but also the internal departments. However, in the statistical needs of the business intelligence system, the business handling status of internal departments such as business offices is usually not the focus, but the business handling status of administrative agencies at all levels. Summary of the Invention
[0005] To solve the above technical problems, the present invention provides a method for constructing and applying an organizational dimension with data aggregation characteristics. The method supports the design of an organizational dimension that aggregates internal departments, meeting the need to aggregate internal department data when drilling down through administrative agencies layer by layer.
[0006] The technical solution of the present invention is:
[0007] A method for constructing and applying an organizational dimension with data aggregation characteristics includes the design of a dimension table for aggregating virtual administrative agency records, and the establishment of records for aggregating internal department data for administrative agencies at all levels - "aggregation departments", which are generally named the administrative agency level.
[0008] A method for rapidly generating a dimension table containing records of aggregated virtual administrative agencies with the aid of an intermediate table is characterized in that an intermediate table is established, which contains newly added "aggregated department" data records, making it easy to extract data therefrom to generate a new agency dimension table containing the "aggregated department".
[0009] A method for quickly establishing an association between facts and a new organizational dimension table with the help of an intermediate table is characterized in that the intermediate table contains an association mapping relationship between "aggregated departments" and "internal departments". When constructing an association between a fact table and a new organizational dimension table, the association mapping relationship between "aggregated departments" and "internal departments" in the intermediate table can be used to map the original business facts associated with "internal departments" in the fact table to the "aggregated departments" in the new dimension table.
[0010] Furthermore, a slowly changing organizational dimension that supports data aggregation is implemented. By utilizing the above-mentioned intermediate table and combining it with the record validity period management of the slowly changing dimension, the automatic aggregation and association of business records in multiple time periods can be achieved.
[0011] The extension is applicable to other similar new dimension tables generated by grouping or diverging the original dictionary, and quickly building the association between facts and dimension tables, adding new records for grouping in the dimension table, deleting the grouped records, and maintaining the association between the grouped records and the grouped records in the form of an intermediate table, which facilitates subsequent data implementation operations such as building new dimension tables and associating fact tables.
[0012] Further,
[0013] Step 1) specifically includes
[0014] 1.1) The intermediate table structure adds three new fields to the original organization dictionary table: dimension surrogate key, aggregation surrogate key, and mapping surrogate key;
[0015] 1.2) Load all original organization dictionary table data into the intermediate table.
[0016] Step 2) specifically includes
[0017] 2.1) In the intermediate table, add a record of a lower-level virtual organization with the name of the original organization + the current level for each level of administrative organization. Based on the different types of administrative organizations, set the newly added virtual organization to a specific category at the same level. These organizations and departments are collectively referred to as the centralized department.
[0018] 2.2) Based on the dimension table surrogate key rules, dimension surrogate key fields are generated for all 1x0 and 1x9 series institution records in the intermediate table. The aggregate surrogate key field values of administrative agencies at all levels, categorized as 1x0, are set as the dimension surrogate keys of their subordinate 1x9 series records. Dimension surrogate keys do not need to be generated for institutions originally categorized as 2xx series.
[0019] 2.3) In the intermediate table, generate mapping surrogate key field data for all records, including:
[0020] For organizations with the original field category of the 1xx series, the mapping surrogate key is set to the dimension surrogate key of the organization itself;
[0021] For organizations originally classified as the 2xx series, the parent organization information is obtained through the parent organization number field. The mapping surrogate key of the 2xx series organization is set to the aggregation surrogate key of its parent administrative organization.
[0022] Step 3) specifically includes
[0023] Extract data records of administrative agency categories and aggregated departments from the intermediate table, convert them according to the dimension table structure, and load them into the dimension table. Use the dimension surrogate key field of the intermediate table as the dimension table primary key.
[0024] At this point, the new dimension table is constructed. The original internal departments in the organization dictionary table are filtered out in the new organization dimension table, and a virtual organization at this level is added to group all internal department businesses.
[0025] Step 4) specifically includes
[0026] Because the intermediate table contains the primary key from the original organization dictionary table, when constructing the fact table, you can use the organization foreign key from the original business table to directly associate it with the intermediate table, obtain its mapping surrogate key, and write its mapping surrogate key as the new organization attribute in the fact table.
[0027] If some analysis scenarios require the use of the original organizational dimension, you can retain both organizational dimension attributes in the fact table and flexibly select one based on application requirements.
[0028] Step 5) requires the following processing:
[0029] 5.1) Expand the start and end validity period fields for the new organization dimension table and intermediate table;
[0030] 5.2) Add a data change mark field and a data change date field to the intermediate table as incremental processing marks;
[0031] 5.3) Generate a consolidated department for all newly added 1xx series administrative agencies, and generate dimension surrogate keys and consolidated surrogate keys for the newly added administrative agencies and consolidated departments, setting their record validity period from the change date to 9999-12-31;
[0032] 5.4) For abolished 1xx series administrative agencies and their related consolidated departments, their records will be updated with the effective expiration date to the date of the change;
[0033] 5.5) For all newly added 2xx series departments, follow steps 5.2)-5.3) and set their mapping surrogate key to the aggregated department surrogate key, and set their record validity period from the change date to 9999-12-31;
[0034] 5.6) For all revoked 2xx series departments, update their record effective expiration date to the change date;
[0035] 5.7) Based on the occurrence date of the fact in the fact table, the organization records within the validity period in the associated dimension table are mapped and associated.
[0036] The beneficial effects of the present invention are
[0037] By using this method to construct a new organization dimension table, business statistical results can be aggregated to a virtual organization at this level, and the statistical data at each level can be kept consistent.
[0038] Based on the extended intermediate table, new dimension table data can be efficiently generated, and batch association of various facts and new dimensions can be accurately achieved, reducing ETL resource and time consumption. DETAILED DESCRIPTION
[0039] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below. Obviously, the described embodiments are part of the embodiments of the present invention, not all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.
[0040] The original dimension table contains records of internal departments of the organization, and some facts are also directly related to the internal departments of the organization. If the internal department types are simply filtered out in the statistical results, the sum of the statistical values of the subordinate units will not be equal to the statistical values of the superior units. Therefore, the essence of the problem lies in aggregating the internal department data and incorporating it into administrative agencies at all levels.
[0041] 1. Generate an intermediate table based on the original organization dictionary table:
[0042] 1) The intermediate table structure adds three new fields to the original organization dictionary table: dimension surrogate key, aggregation surrogate key, and mapping surrogate key.
[0043] 2) Load all original organization dictionary table data into the intermediate table.
[0044] 2. Generate relevant data in the intermediate table to facilitate the generation of new dimension tables and the association between facts and dimension tables
[0045] 1) In the middle table, add a subordinate virtual organization record named "Original Organization Name + Current Level" for administrative agencies at all levels (category: 1x0). For example, add "Civil Affairs Bureau of a certain city at this level" under "Civil Affairs Bureau of a certain city". And according to the different categories of administrative agencies (such as 100, 120, 140, etc.), set the new virtual organization to a specific category at the same level, such as 109, 129, 149, etc. These organizations and departments are collectively referred to as "aggregated departments".
[0046] 2) Based on the dimension table surrogate key rules, generate a "Dimension Surrogate Key" field for all 1x0 and 1x9 series organization records in the intermediate table. Set the "Collective Surrogate Key" field value of each administrative organization (category 1x0) to the "Dimension Surrogate Key" of its subordinate 1x9 series records. No "Dimension Surrogate Key" is required for organizations originally categorized as 2xx series.
[0047] 3) In the intermediate table, generate "mapping surrogate key" field data for all records:
[0048] i. For organizations with a 1xx field type, the "Mapping Surrogate Key" is set to the organization's own "Dimension Surrogate Key."
[0049] ii. For organizations originally classified as the 2xx series, the "Superior Organization Number" field is used to obtain information about the superior organization. If the superior organization category is administrative organizations at all levels (1x0 series), the "Mapping Proxy Key" of the 2xx series organization is set to the "Collecting Proxy Key" of its superior administrative organization. If the organization's superior organization category is still in the 2xx series, continue to search upward until the category is the 1x0 series, and use its "Collecting Proxy Key" to write the "Mapping Proxy Key."
[0050] 3. Select appropriate records from the intermediate table and load them into the new dimension table.
[0051] 1) Extract data records of administrative agency category (1x0 series) and "aggregation department" (1x9 series) from the intermediate table, convert them according to the dimension table structure, and load them into the dimension table. Use the "dimension surrogate key" field of the intermediate table as the dimension table primary key.
[0052] At this point, the new dimension table is constructed. The original internal departments in the organization dictionary table are filtered out in the new organization dimension table, and a virtual organization at this level is added to group all internal department businesses.
[0053] 4. Create an association between the business fact table and the new organization dimension table:
[0054] 1) Because the intermediate table contains the primary key in the original organization dictionary table, when constructing the fact table, the organization foreign key of the original business table can be used to directly associate with the intermediate table, obtain its "mapping surrogate key", and write its "mapping surrogate key" as the new organization attribute of the fact table.
[0055] 2) If some analysis scenarios require the use of the original organizational dimension, both organizational dimension attributes can be retained in the fact table at the same time, allowing for flexible selection based on application requirements.
[0056] 5. To implement a slowly changing organizational dimension (SCD), when the original organizational dictionary table changes, obtain the updated record based on the incremental update marker (timestamp or other) and load it into the intermediate table. Repeat steps 2, 3, and 4 for the updated record. Note the following special handling:
[0057] 1) Expand the start and end validity period fields for the new organization dimension table and intermediate table.
[0058] 2) Add a data change mark field and a data change date field to the intermediate table as incremental processing marks.
[0059] 3) Generate "aggregated departments" for all newly added 1xx series administrative agencies, and generate "dimension surrogate keys" and "aggregated surrogate keys" for the newly added administrative agencies and "aggregated departments". Set the record validity period from the change date to 9999-12-31.
[0060] 4) For the abolished 1xx series administrative agencies and their related "collective departments", the effective deadline of their records will be updated to the date of change.
[0061] 5) For all newly added 2xx series departments, set their "mapping surrogate key" to the "aggregated department" surrogate key according to method 2-(3)-ii, and set their record validity period to the change date to 9999-12-31.
[0062] 6) For all revoked 2xx series departments, update their record effective date to the change date.
[0063] 7) According to the occurrence date of the fact in the fact table, the organization records within the validity period in the associated dimension table are mapped and associated.
[0064] The above description is only a preferred embodiment of the present invention and is only used to illustrate the technical solution of the present invention, and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention are included in the scope of protection of the present invention.
Claims
1. A method for constructing and applying an organizational dimension with data aggregation characteristics, characterized in that: include: 1) Generate an intermediate table based on the original organization dictionary table; 2) Generate relevant data in the intermediate table to facilitate the generation of new dimension tables and the association of facts with dimension tables; 3) Select appropriate records from the intermediate table and load them into the new dimension table; 4) Establish an association between the business fact table and the new organization dimension table; 5) If you want to implement a slowly changing organization dimension SCD, when the original organization dictionary table changes, get the updated record according to the incremental update mark and load it into the intermediate table, and repeat the above steps 2), 3), and 4) for the updated record.
2. The method according to claim 1, characterized in that Establish records for administrative agencies at all levels to aggregate internal department data and name them as administrative agency levels; Create an intermediate table containing newly added department data records, so that data can be extracted from it to generate a new organizational dimension table containing the newly added departments. The intermediate table contains the association mapping relationship between the aggregated departments and the internal departments. When building the association between the fact table and the new organization dimension table, the association mapping relationship between the aggregated departments and the internal departments in the intermediate table is used to map the original business facts associated with the internal departments in the fact table to the aggregated departments in the new dimension table.
3. The method according to claim 2, characterized in that Add new records for grouping in the dimension table, delete the grouped records, and maintain the association between the grouped records and the grouped records in the form of an intermediate table to facilitate the construction of new dimension tables and the association of fact tables with subsequent data operations.
4. The method according to claim 1, wherein Step 1) specifically includes 1.1) The intermediate table structure adds three new fields to the original organization dictionary table: dimension surrogate key, aggregation surrogate key, and mapping surrogate key; 1.2) Load all original organization dictionary table data into the intermediate table.
5. The method according to claim 4, characterized in that Step 2) specifically includes 2.1) In the intermediate table, add a record of a lower-level virtual organization with the name of the original organization + the current level for each level of administrative organization. Based on the different types of administrative organizations, set the newly added virtual organization to a specific category at the same level. These organizations and departments are collectively referred to as the centralized department. 2.2) Based on the dimension table surrogate key rules, generate dimension surrogate key fields for all 1x0 and 1x9 series organization records in the intermediate table. Set the aggregate surrogate key field value of the 1x0 records for administrative organizations at all levels as the dimension surrogate key for their subordinate 1x9 series records. No dimension surrogate key is required for organizations originally classified as 2xx series. 2.3) In the intermediate table, generate mapping surrogate key field data for all records.
6. The method according to claim 5, characterized in that The field data in step 2.3) includes: For organizations with the original field category of the 1xx series, the mapping surrogate key is set to the dimension surrogate key of the organization itself; For organizations originally classified as the 2xx series, the parent organization information is obtained through the parent organization number field. The mapping surrogate key of the 2xx series organization is set to the aggregation surrogate key of its parent administrative organization.
7. The method according to claim 6, characterized in that Step 3) specifically includes Extract data records of administrative agency categories and aggregated departments from the intermediate table, convert them according to the dimension table structure, and load them into the dimension table. Use the dimension surrogate key field of the intermediate table as the dimension table primary key. At this point, the new dimension table is constructed. The original internal departments in the organization dictionary table are filtered out in the new organization dimension table, and a virtual organization at this level is added to group all internal department businesses.
8. The method according to claim 1, characterized in that Step 4) specifically includes Because the intermediate table contains the primary key from the original organization dictionary table, when constructing the fact table, you can use the organization foreign key from the original business table to directly associate it with the intermediate table, obtain its mapping surrogate key, and write its mapping surrogate key as the new organization attribute in the fact table. If some analysis scenarios require the use of the original organizational dimension, you can retain both organizational dimension attributes in the fact table and flexibly select one based on application requirements.
9. The method according to claim 1, characterized in that Step 5) requires the following processing: 5.1) Expand the start and end validity period fields for the new organization dimension table and intermediate table; 5.2) Add a data change mark field and a data change date field to the intermediate table as incremental processing marks; 5.3) Generate aggregated departments for all newly added 1xx series administrative agencies. Generate dimension surrogate keys and aggregated surrogate keys for the newly added administrative agencies and aggregated departments, and set their record validity period from the change date to December 31, 9999. 5.4) For abolished 1xx series administrative agencies and their related consolidated departments, their records will be updated with the effective expiration date to the date of the change; 5.5) For all newly added 2xx series departments, follow steps 5.2)-5.3) and set their mapping surrogate key to the aggregated department surrogate key, and set their record validity period from the change date to 9999-12-31; 5.6) For all revoked 2xx series departments, update their record effective expiration date to the change date; 5.7) Based on the occurrence date of the fact in the fact table, the organization records within the validity period in the associated dimension table are mapped and associated.