A partition data processing method and usage method of a slowly changing dimension table

Abstract

The present invention relates to a partitioned data processing method and a use method of a slowly changing dimension table, and belongs to the field of ETL data processing technology in a data warehouse. First, a partitioned database table is established in a database according to the time range of a natural month, and the start timestamp of the slowly changing dimension table is designated as a partition key; when there is new data to be stored in the data warehouse and the processing time node is the beginning of the month, a partitioned slowly changing dimension table algorithm is used to perform closed-chain processing on the full amount of open-chain data in the slowly changing dimension table before access, and then calculate the new warehouse data, and perform open-chain processing on the newly accessed data; when the partitioned slowly changing dimension table processed by the above method is used, the start timestamp is placed between the beginning of the month and the target date where the query target date is located, and the data to be queried is locked in the same partition at this time, which greatly improves the query efficiency of the slowly changing dimension table. While retaining the characteristics of the slowly changing dimension table, the present invention can also improve query efficiency and query stability.

Description

Technical Field

[0001] The invention relates to a partition data processing method and a use method of a slowly changing dimension table, belonging to the field of data processing, in particular to the field of ETL algorithms in data warehouses. Background Art

[0002] Data warehouse technology was first proposed by Bill Inmon in the mid-1980s. Data warehouse is not a ready-made product, but a dynamic process of data application.

[0003] The ETL process of data is the first step in building a data warehouse, the basic work of the data warehouse, and the preparatory work for subsequent operations of the data warehouse. ETL is the abbreviation of the three steps of Extract, Transform, and Load during data scheduling, which is the abbreviation of extraction, transformation, and loading, that is, the process of extracting, transforming, and loading business data scattered in various systems into the data warehouse in sequence.

[0004] Slowly changing dimension tables and snapshot tables are the two most commonly used storage methods in data warehouses, and are also two algorithms for the transformation process in ETL work. Among them, Slowly Changing Dimensions (SCD) is one of the commonly used algorithms in data warehouses. It is mainly used to store historical information about changes in a certain type of object. From the beginning of the object to the current time point, the changes of the object in any period can be obtained through data zipper table queries. The idea of ​​this algorithm is to use two timestamps to record the duration of the attribute state of the same object to store historical data. The advantage of this algorithm is that it can save storage space to the greatest extent. The limitation of this algorithm is that not all tables can be stored using this algorithm. Secondly, because two timestamp attributes are required to retrieve data, there are disadvantages of low query efficiency and inconvenient calculation in actual use.

[0005] Nowadays, slowly changing dimension tables have been maturely used in data warehouses of various enterprises. For example, in commercial bank data warehouses, the storage of historical data and the loading strategy of model data can be implemented using slowly changing dimension tables. However, as various data needs are constantly moved "online", the query efficiency of conventional slowly changing dimension tables can no longer meet the ever-increasing needs of various end users. Therefore, considering the needs or characteristics of commercial banks' monthly settlement and data assessment, we can consider combining time range partitioning technology with slowly changing dimension table algorithms to improve the query efficiency of slowly changing dimension tables. Summary of the invention

[0006] The technical problem to be solved by the present invention is the defect of slow query efficiency of conventional slowly changing dimension tables. A slowly changing dimension table processing method and a use method with a partition storage function are provided. The method can improve the query efficiency of the zipper table, especially when the target table itself has a large amount of data. The query efficiency is improved particularly significantly. The method can also ensure that the efficiency of each query is basically stable and will not increase with the increase of data volume.

[0007] The technical solution adopted by the present invention is:

[0008] Steps of the partition data processing method for slowly changing dimension tables:

[0009] 1) Create a database table partitioned by natural month time range and specify the start timestamp of the slowly changing dimension table as the partition key;

[0010] 2) Prepare two database tables with similar table structures to the target slowly changing dimension table for temporary data storage in the partitioning algorithm;

[0011] 3) Use the slowly changing dimension partitioning algorithm to implement partitioning when data is stored in the warehouse;

[0012] 4) Regularly perform partition maintenance on partitions of slowly changing dimension tables with partitions.

[0013] Furthermore, the step 1) includes the following work:

[0014] Before storing data in the slowly changing dimension table, create a database table with time range partitions, including: the partition primary key is the preset start timestamp field in the slowly changing dimension table; the time lower limit of each partition in the table is the beginning of each natural month, and the time upper limit of the partition is the end of each natural month.

[0015] Furthermore, the step 2) includes the following work:

[0016] Prepare two database tables with similar table structures to the target slowly changing dimension table for temporary data storage in the partitioning algorithm, including:

[0017] Temporary table 1, denoted as TEMP1, is used to store source data from the upstream database in the data warehouse. It contains a data date field to store the date of data collection. The rest of the table structure is all the fields in the target table except the start timestamp and end timestamp.

[0018] Temporary table 2, denoted as TEMP2, has the same table structure as temporary table 1 and is used to store comparison result data in the partitioning algorithm.

[0019] Furthermore, the step 3) includes the following work:

[0020] The partitioning algorithm of the slowly changing dimension is used to selectively store the data collected from upstream into the slowly changing dimension table. The main steps of the algorithm can be described as follows:

[0021] Step 1: When the data processing date is the beginning of the month, all the original open-chain data in the target slowly changing dimension table (hereinafter referred to as the target table) are closed-chained;

[0022] Step 2: Store the raw data collected on the upstream processing day into the temporary table TEMP1;

[0023] Step 3: Use the minus method to positively compare the open-link data of the target table in step 1 with the attributes of the same object in TEMP1, and store the data of the newly added objects or objects with changed attributes in the temporary table TEMP2;

[0024] Step 4: Use the left join method to reversely compare the open chain data of the target table in step 1 with the objects in TEMP1, and store the objects that do not exist in the target table (that is, the deleted objects) in TEMP2;

[0025] Step 5: Close the chain of objects in TEMP2 in step 1;

[0026] Step 6: Insert the object data in TEMP2 into the target table and open the link for these objects.

[0027] Furthermore, the step 4) includes the following work:

[0028] Regularly perform partition maintenance on slowly changing dimension tables with partitions, that is, add partitions with natural months as the time range.

[0029] A method for using partitioned data of a slowly changing dimension table. When querying a slowly changing dimension table with partitions obtained by using the partitioned data processing method of the slowly changing dimension table, the start timestamp is placed between the beginning of the month where the query target date is located and the target date.

[0030] When querying a partitioned slowly changing dimension table, place the target query date between the beginning of the month where the start timestamp is located and the start timestamp, and the end timestamp is greater than the target query date, so that the attributes corresponding to the object at the target query time can be obtained.

[0031] The beneficial effect of the present invention is that: while retaining the characteristic of the slowly changing dimension table of retaining historical data, the present invention utilizes the partitioning technology to specify the principle of physical storage location to partition and store the slowly changing dimension table data. Practice has proved that this method can improve the query efficiency of the zipper table, especially when the target table itself has a large amount of data, the query efficiency is improved particularly significantly. This method can also ensure that the efficiency of each query is basically stable and will not increase with the increase of data volume. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Figure 1 It is a structural schematic diagram of the present invention;

[0033] Figure 2 It is a flow chart of the slowly changing dimension partitioning algorithm of the present invention;

[0034] Figure 3 This is a diagram illustrating the principle of the partitioned slowly changing dimension query method of the present invention. DETAILED DESCRIPTION

[0035] The present invention is further described below in conjunction with the accompanying drawings and specific embodiments.

[0036] Example 1: Figure 1-3 As shown in the figure, the steps of the partition storage processing method for the slowly changing dimension table data named goal_table_hs in the data warehouse are as follows:

[0037] 1) Create a database table partitioned by natural month time range and specify the start timestamp of the slowly changing dimension table as the partition key;

[0038] 2) Prepare two database tables with similar table structures to the target slowly changing dimension table for temporary data storage in the partitioning algorithm;

[0039] 3) Use the slowly changing dimension partitioning algorithm to implement partitioning when data is stored in the warehouse;

[0040] 4) Regularly perform partition maintenance on partitions of slowly changing dimension tables with partitions.

[0041] Furthermore, the step 1) includes the following work:

[0042] Create a database table goal_table_hs with time range partitioning and execute the following data definition language DDL in the database:

[0043] CREATE TABLE goal_table_hs(

[0044] begn_dt DATE,

[0045] over_dt DATE,

[0046] pk VARCHAR2(30),

[0047] col_1 VARCHAR2(30),

[0048] col_2 VARCHAR2(30) )

[0050] PARTITION BY RANGE(begn_dt)(

[0051] PARTITION p202101 VALUES LESS THAN(DATE('2021-02-01'))TABLESPACEPART01,

[0052] PARTITION p202102 VALUES LESS THAN(DATE('2021-03-01'))TABLESPACEPART02);

[0053] The primary key of the partition is the preset start timestamp field in the slowly changing dimension table, that is, the begn_dt field. The lower limit of each partition in the table is the beginning of each natural month, and the upper limit of the partition is the end of each natural month. The number of partitions to be added is equal to the number of natural months contained in the target table.

[0054] Furthermore, the step 2) includes the following work:

[0055] Prepare two database tables with similar table structures to the target slowly changing dimension table for temporary data storage in the partitioning algorithm, including:

[0056] Temporary table 1, named goal_table, is aliased as TEMP1 in the algorithm. It is used to store source data from the upstream database in the data warehouse. Its fields are: data date etl_dt, and the remaining fields are: pk, col_1, col_2;

[0057] Temporary table 2, named tmp_goal_table, is aliased as TEMP2 in the algorithm. Its structure is the same as that of temporary table 1 and is used to store comparison result data in the partitioning algorithm.

[0058] Furthermore, the step 3) includes the following work:

[0059] Use the partitioning algorithm of the slowly changing dimension to selectively store the data collected from upstream into the slowly changing dimension table. Before data processing, first define the parameters required in the algorithm as follows: TX_DATE: parameter type date, input date parameter, that is, data processing date;

[0060] MAX_DATE: parameter type date, maximum date, i.e. 9999 / 12 / 31;

[0061] MIN_DATE: parameter type date, minimum date, i.e. 1900 / 01 / 01;

[0062] The second step is the preparation work before data processing. Clear the data in the temporary table for this use:

[0063] TRUNCATE TABLE goal_table;

[0064] TRUNCATE TABLE tmp_goal_table;

[0065] The following starts the slowly changing dimension partitioning algorithm

[0066] Step 1: When the data processing date is the beginning of the month, all the original open-chain data in the target slowly changing dimension table goal_table_hs are closed-chained. The key SQL statements are as follows:

[0067] UPDATE goal_table_hs SET over_dt=TX_DATE

[0068] WHERE over_dt=MAX_DATE AND SUBSTR(TX_DATE),9,2)='01';

[0069] Step 2: Store the raw data collected on the upstream processing day into the temporary table TEMP1. The key SQL statements are as follows (where f_goal_table is the upstream database table of the data warehouse):

[0070] INSERT INTO goal_table(pk,col1,col2,etl_dt)

[0071] SELECT pk,col1,col2,TX_DATE as etl_dt FORM f_goal_table;

[0072] Step 3: Use the minus method to positively compare the open-link data of the target table in step 1 with the attributes of the same object in TEMP1, and store the data of newly added objects or objects with changed attributes in the temporary table TEMP2. The key SQL statements are as follows:

[0073] INSERT INTO tmp_goal_table(pk,col1,col2,etl_dt)

[0074] SELECT pk,col1,col2,TX_DATE as etl_dt

[0075] FROM goal_table AS temp1

[0076] MINUS

[0077] SELECT pk,col1,col2,TX_DATE as etl_dt

[0078] FROM goal_table_hs

[0079] WHERE over_dt = MAX_DATE;

[0080] Step 4: Use the left join method to reversely compare the open chain data of the target table in step 1 with the objects in TEMP1, and store the objects that do not exist in the target table (that is, the deleted objects) in TEMP2. The key SQL statements are as follows:

[0081] INSERT INTO tmp_goal_table(pk,col1,col2,etl_dt)

[0082] SELECT pk,col1,col2,MIN_DATE as etl_dt

[0083] FROM goal_table_hs goal

[0084] LEFT JOIN goal_table temp1ON goal.pk=temp1.pk

[0085] WHERE goal.over_dt=MAX_DATE AND temp1.pk IS NULL;

[0086] Step 5: Close the object in TEMP2 in step 1. The key SQL statement is as follows:

[0087] UPDATE goal_table_hs goal SET goal.over_dt=TX_DATE

[0088] WHERE goal.over_dt=MAX_DATE

[0089] AND EXISTS(SELECT 1FROM tmp_goal_table temp2 WHERE goal.pk=temp2.pk);

[0090] Step 6: Insert the object data in TEMP2 into the target table and perform open-link processing on these objects. The key SQL statements are as follows:

[0091] INSERT INTO goal_table_hs(begn_dt,over_dt,pk,col1,col2)

[0092] SELECT TX_DATE,MAX_DATE,pk,col1,col2

[0093] FROM tmp_goal_table

[0094] WHERE etl_dt = TX_DATE;

[0095] The algorithm ends here and the task is submitted.

[0096] Furthermore, the step 4) includes the following work:

[0097] Regularly perform partition maintenance on slowly changing dimension tables with partitions, that is, add partitions with natural months as the time range.

[0098] A method for using partitioned data of a slowly changing dimension table. When querying a slowly changing dimension table with partitions obtained by using the partitioned data processing method of the slowly changing dimension table, the start timestamp is placed between the beginning of the month where the query target date is located and the target date.

[0099] When querying a partitioned slowly changing dimension table, place the target query date between the beginning of the month where the start timestamp is located and the start timestamp, and the end timestamp is greater than the target query date, so that the attributes corresponding to the object at the target query time can be obtained.

[0100] For example, if the parameter v_date is defined as the target query date, the target data can be obtained by executing the following SQL statement in the target slowly changing dimension table goal_table_hs:

[0101] SELECT * FROM goal_table_hs

[0102] WHERE v_date BETWEEN TRUNC(begn_dt,'month')and begn_dt

[0103] AND over_dt>v_date;

[0104] The following is combined with Figure 3 The principle of the present invention is described in detail:

[0105] According to the characteristic of slowly changing dimension tables to retain historical data, which is to record attribute changes through time changes, we consider optimizing conventional partitioning algorithms by partitioning according to time ranges. For example, the condition of each block is set from the 1st to the end of each month, and the timestamp begn_dt of the zipper table is set as the partition key. However, if begn_dt is simply partitioned in this way, the problem of low query efficiency cannot be solved. This is because when filtering open chain data, it is necessary to meet the following requirements at the same time:

[0106] ①begn_dt<=v_date;

[0107] ②over_dt>v_date;

[0108] If we simply store the data of begn_dt in the same month in one partition, it cannot be guaranteed that the scan of begn_dt <= v_date will be in the same partition, so begn_dt still needs to be scanned in the entire table, and it is a cross-partition scan. Therefore, this query method cannot use the characteristics of partitioning technology to lock the scan of partition keys in the same partition, and it is no different from a slowly changing dimension table without partitions.

[0109] To improve the query efficiency of the zipper table through partitioned storage, the query condition should be executed in only one partition. If this goal can be achieved, it is equivalent to specifying the physical storage location of the data for this query, which can greatly improve the query efficiency. In order to achieve the goal that the open chain data queried each time must be in the same partition, you can use the query method of specifying the partition, that is, transform the two query conditions mentioned above into:

[0110] ①begn_dt between trunc(v_date,'month')and v_date;

[0111] ②over_dt>v_date,

[0112] At this time, the scan range of begn_dt is locked in the same natural month. According to the principle of partitioning technology, the scan range of this query is locked in the table space of the corresponding month. In addition, the physical location of the data file needs to be specified when defining the table space. In this way, this query can be regarded as specifying the physical location of the storage, which can greatly improve the query efficiency. In order to achieve the effect that begn_dt is in the same natural month every time it is queried, it is necessary to close and open the chain of the full amount of data at the beginning of each month when processing data in the slowly changing dimension table, so as to ensure the consistency of the two timestamps of the slowly changing dimension table. This is also the basic idea of ​​the slowly changing dimension partitioning algorithm. In practice, it can also be confirmed that this method can effectively improve the query efficiency of data, especially when the target table has a large amount of data. In addition, this method can also ensure the stability of each query of the same target table.

[0113] The specific embodiments of the present invention are described in detail above in conjunction with the accompanying drawings, but the present invention is not limited to the above embodiments. Various changes can be made within the knowledge scope of ordinary technicians in this field without departing from the purpose of the present invention.

Claims

1. A partition data processing method for a slowly changing dimension table, characterized by: The steps include: 1) Create a database table partitioned by natural month time range and specify the start timestamp of the slowly changing dimension table as the partition key; 2) Prepare two database tables with similar table structures to the target slowly changing dimension table for temporary data storage in the partitioning algorithm; 3) Use the slowly changing dimension partitioning algorithm to implement partitioning when data is stored in the warehouse; 4) Regularly perform partition maintenance on partitions of slowly changing dimension tables with partitions; Step 1): Before storing data in the slowly changing dimension table, create a database table with time range partitions, specifically including: the partition primary key is the preset start timestamp field in the slowly changing dimension table; the time lower limit of each partition in the table is the beginning of each natural month, and the time upper limit of the partition is the end of each natural month; Step 2): Prepare two database tables with similar table structures to the target slowly changing dimension table for temporary data storage in the partitioning algorithm, including: Temporary table 1, denoted as TEMP1, is used to store source data from the upstream database in the data warehouse. It contains a data date field to store the date of data collection. The rest of the table structure is all the fields in the target table except the start timestamp and end timestamp. Temporary table 2, denoted as TEMP2, has the same table structure as temporary table 1 and is used to store the comparison result data in the partitioning algorithm; Step 3): Use the slowly changing dimension partitioning algorithm to selectively store the data collected from upstream into the slowly changing dimension table. The steps of the algorithm are: Step 1: When the data processing date is the beginning of the month, all the original open-chain data in the target slowly changing dimension table are closed-chained. The target slowly changing dimension table is referred to as the target table. Step 2: Store the raw data collected on the upstream processing day into the temporary table TEMP1; Step 3: Use the minus method to positively compare the open-link data of the target table in step 1 with the attributes of the same object in TEMP1, and store the data of the newly added objects or objects with changed attributes in the temporary table TEMP2; Step 4: Use the left join method to reversely compare the open chain data of the target table in step 1 with the objects in TEMP1, and store the objects that do not exist in the target table into TEMP2; Step 5: Close the chain of objects in TEMP2 in step 1; Step 6: Insert the object data in TEMP2 into the target table and open the link for these objects.

2. The partition data processing method of a slowly changing dimension table according to claim 1, characterized in that: The changes of the attributes of the same object in the slowly changing dimension table are marked by two timestamps, namely the start time and the end time, and the slowly changing dimension table has a business primary key that can identify a unique object.

3. The partition data processing method of a slowly changing dimension table according to claim 1, characterized in that: Step 4): Regularly perform partition maintenance on slowly changing dimension tables with partitions, that is, add partitions with natural months as the time range.

4. A method for using partitioned data of a slowly changing dimension table, characterized by: For a slowly changing dimension table with partitions obtained by using the partition data processing method for a slowly changing dimension table according to any one of claims 1 to 3, when querying, the start timestamp is placed between the beginning of the month where the query target date is located and the target date.

5. The method for using partitioned data of a slowly changing dimension table according to claim 4, characterized in that: When querying a partitioned slowly changing dimension table, place the target query date between the beginning of the month where the start timestamp is located and the start timestamp, and the end timestamp is greater than the target query date, so that the attributes corresponding to the object at the target query time can be obtained.

Images