Internet of Things data storage method and system based on data warehouse modeling

Abstract

The invention discloses an Internet of Things data storage method and system based on data warehouse modeling, and belongs to the field of Internet of Things data storage systems, and the method comprises the steps: obtaining a business data source table, extracting multi-valued attribute data and slow change dimension data, and constructing a first dimension table and a second dimension table; storing the multi-valued attribute data into one attribute field of the first dimension table, storing the multi-valued attribute data into a plurality of attribute fields of the first dimension table, or storing each dimension value in the multi-valued attribute data into a single row of the first dimension table; and writing the slow change dimension data into the second dimension table in a rewriting manner, writing the slow change dimension data into the second dimension table in a manner of adding new rows, writing the slow change dimension data into the second dimension table in a manner of adding new attribute fields, or writing the slow change dimension data into a micro dimension table associated with the second dimension table. According to the method, multiple processing methods of the data warehouse model on the multi-valued attribute data and the slowly-changing dimension data are supported, and flexible processing of the data is achieved in multiple storage modes.

Description

Technical Field

[0001] This application relates to the field of Internet of Things (IoT) data storage systems, and particularly to an IoT data storage method and system based on data warehouse modeling. Background Art

[0002] With the rapid development of Internet of Things (IoT) technology, more and more devices and sensors are connected to the Internet, generating a large amount of data. This data is not only huge in quantity but also diverse in type, including structured data, semi-structured data, and unstructured data. How to efficiently store, process, and analyze this data has become a major challenge in the current technical field.

[0003] Currently, the data reported by devices has poor integrity. Different data sources and formats lead to problems such as data incompatibility and difficulty in unified analysis; complex data analysis usually requires multi-dimensional slicing and drilling of data. Without the modeling support of a data warehouse, it will be relatively difficult to achieve these requirements; at the same time, unmodeled data may be repeatedly stored in multiple systems, resulting in data redundancy, which in turn increases storage costs and maintenance difficulties; a non-modeled data architecture is difficult to cope with future business expansion. When more devices are connected or new analysis requirements need to be supported, there will be a risk of data architecture reconstruction.

[0004] The IoT data storage system is an important part of modern technology, which is responsible for storing and processing data from various IoT devices. The IoT data storage system realizes the classification, integration, cleaning, and standardization of the massive data from connected devices by constructing a data warehouse model for efficient data storage and query. This method supports advanced analyses such as dimensional analysis, time series analysis, and association rule mining of data, helping the product line to deeply understand the device operation status and user behavior patterns, and then realizing device monitoring, fault prediction, performance optimization, and resource allocation optimization, etc.

[0005] IoT devices may have multiple attributes, and each attribute may have multiple values. The storage system needs to be able to flexibly store and manage these multi-valued attributes for easy data query and analysis. However, the current storage system does not support the dynamic addition and modification of attribute values, cannot adapt to the changing IoT environment, cannot quickly locate and retrieve multiple values of a specific attribute, and cannot meet the needs of device attribute diversity.

[0006] In addition, the data of some dimension tables is not static but changes slowly over time (here, "slowly" is relative to the fact table, and the data in the fact table changes faster than that in the dimension table). Such dimensions that change over time are called Slowly Changing Dimensions (SCD). For example, according to the user dimension, the proportion of consumption amounts for different birth years (post-80s, post-90s, post-00s) is statistically analyzed. During this period, the user may modify the user data. For example, the date of birth is changed to 1992. At this time, the user dimension table has changed. Of course, this change is slower compared to the change in the fact table. This change in the user dimension table is the slowly changing dimension. However, the current storage system cannot effectively process these slowly changing data dimensions and cannot ensure the coherence and accuracy of historical data. Summary of the Invention

[0007] The present application provides an Internet of Things data storage method and system based on data warehouse modeling, which can solve the technical problem that the data warehouse model in the prior art cannot achieve the expected processing for multi-valued attribute data and slowly changing dimension data.

[0008] In a first aspect, an embodiment of the present application provides an Internet of Things data storage method based on data warehouse modeling. The method includes:

[0009] Obtain a business data source table, extract data from the business data source table to obtain multi-valued attribute data and slowly changing dimension data, and construct a first dimension table and a second dimension table based on the business data source table;

[0010] Store the multi-valued attribute data in one attribute field of the first dimension table, store the multi-valued attribute data in multiple attribute fields of the first dimension table, or store each dimension value in the multi-valued attribute data in a single row of the first dimension table;

[0011] Write the slowly changing dimension data into the second dimension table in a rewritten manner, write it into the second dimension table by adding a new row, write it into the second dimension table by adding a new attribute field, or write it into a micro dimension table associated with the second dimension table. The micro dimension table includes an association field for associating with the second dimension table and a changing dimension field in the slowly changing dimension data.

[0012] In combination with the first aspect, in an implementation, the method further includes:

[0013] Obtain business requirement information and granularity setting information;

[0014] Determine the table association relationship between each dimension table according to the business requirement information;

[0015] Construct a data warehouse model based on the granularity setting information, the first dimension table, the second dimension table, the micro dimension table, and the table association relationship.

[0016] Combined with the first aspect, in one implementation, the method further includes:

[0017] After uniformly converting the business data uploaded by the device to UTC time, store it in the ODS data area;

[0018] In the DWD data detail area, adopt the implementation method of a wide table, and expand multiple time zone dimension columns on the same data detail;

[0019] In the DWS data area and the ADS data area, summarize the data according to the time zone to obtain multiple business data source tables, and isolate the tables according to different time zones between the business data source tables.

[0020] Combined with the first aspect, in one implementation, the attribute fields in the multi-valued attribute data include a time zone field;

[0021] The attribute fields in the slowly changing dimension data include a time zone field.

[0022] Combined with the first aspect, in one implementation, the method further includes:

[0023] When writing the slowly changing dimension data into the second dimension table or the micro dimension table, add version information and a timestamp.

[0024] In the second aspect, an embodiment of the present application provides an Internet of Things data storage system based on data warehouse modeling, and the system includes:

[0025] A data acquisition module, which is used to acquire business data source tables;

[0026] A data preprocessing module, which is used to extract data from the business data source tables to obtain multi-valued attribute data and slowly changing dimension data, and construct a first dimension table and a second dimension table based on the business data source tables;

[0027] A data warehouse modeling module, which is used to store the multi-valued attribute data into an attribute field of the first dimension table, store the multi-valued attribute data into multiple attribute fields of the first dimension table, or store each dimension value in the multi-valued attribute data into a single row of the first dimension table; write the slowly changing dimension data into the second dimension table in a rewrite manner, write it into the second dimension table by adding a new row, write it into the second dimension table by adding a new attribute field, or write it into a micro dimension table associated with the second dimension table, and the micro dimension table includes an association field for associating with the second dimension table and a changing dimension field in the slowly changing dimension data.

[0028] In combination with the second aspect, in one implementation, the data acquisition module is further configured to acquire service requirement information and granularity setting information;

[0029] The data warehouse modeling module is further configured to determine the table association relationships between each dimension table according to the service requirement information; construct a data warehouse model according to the granularity setting information, the first dimension table, the second dimension table, the micro dimension table, and the table association relationships.

[0030] In combination with the second aspect, in one implementation, the data acquisition module is further configured to uniformly convert the service data uploaded by the device to UTC time and then store it in the ODS data area; in the DWD data detail area, adopt the implementation method of a wide table to expand multiple time zone dimension columns on the same data detail; perform data aggregation according to time zones in the DWS data area and the ADS data area to obtain multiple service data source tables, and isolate the tables between each service data source table according to different time zones.

[0031] In combination with the second aspect, in one implementation, the attribute fields in the multi-value attribute data include a time zone field;

[0032] The attribute fields in the slowly changing dimension data include a time zone field.

[0033] In combination with the second aspect, in one implementation, the data warehouse modeling module is further configured to add version information and timestamps when writing the slowly changing dimension data into the second dimension table or the micro dimension table.

[0034] The beneficial effects brought by the technical solutions provided in the embodiments of the present application include:

[0035] This data warehouse modeling method supports the management method of multi-value attributes, can meet the multi-value requirements in different scenarios, and has good scalability.

[0036] Supports the way of policy selection to meet various management methods of slowly changing dimensions such as rewriting, adding new rows, adding new attributes, and micro dimensions. Description of the Drawings

[0037] Figure 1 It is a schematic flowchart of an embodiment of the Internet of Things data storage method based on data warehouse modeling in the present application;

[0038] Figure 2 It is a schematic diagram of the functional modules of an embodiment of the Internet of Things data storage system based on data warehouse modeling in the present application. Detailed Embodiments

[0039] To enable those skilled in the art to better understand the solution of this application, the following will clearly and completely describe the technical solution in the embodiments of this application in conjunction with the accompanying drawings in the embodiments of this application. Obviously, the described embodiments are only a part of the embodiments of this application, rather than all of the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments in this application without creative efforts belong to the scope of protection of this application.

[0040] First, some technical terms in this application are explained to facilitate the understanding of this application by those skilled in the art.

[0041] Dimension Table: A table that stores descriptive data, such as a time dimension table, a product dimension table, etc.

[0042] Attribute: A column in the dimension table that describes the characteristics of an entity (from a business perspective), such as year, product category.

[0043] Field: The physical column name in the dimension table that stores the attribute (from a technical perspective), such as year, prod_category.

[0044] Dimension Value: The actual value of an attribute in a specific record. For example, the dimension values of the "year" attribute can be 2023, 2024, and the dimension values of the "product category" may be electronic products, household items.

[0045] Multi-valued Attribute: An IoT device may have multiple attributes, and each attribute may have multiple values. The storage system needs to be able to flexibly store and manage these multi-valued attributes for data query and analysis.

[0046] Slowly Changing Dimension: IoT data changes over time, but at a relatively slow rate. The storage system needs to be able to handle these slowly changing data dimensions to ensure the coherence and accuracy of historical data.

[0047] Multi-time Zone Management: Since IoT devices may be distributed in different regions around the world, the storage system needs to be able to handle multi-time zone data to ensure the accuracy and consistency of time data.

[0048] To make the purpose, technical solution, and advantages of this application clearer, the following will further describe the embodiments of this application in detail in conjunction with the accompanying drawings.

[0049] In a first aspect, an embodiment of this application provides an IoT data storage method based on data warehouse modeling.

[0050] In one embodiment, refer to Figure 1 , Figure 1This is a schematic flow chart of an embodiment of the Internet of Things data storage method based on data warehouse modeling. As Figure 1 shown, the Internet of Things data storage method based on data warehouse modeling includes:

[0051] Step S1: Obtain the business data source table, extract data from the business data source table to obtain multi-valued attribute data and slowly changing dimension data, and construct a first dimension table and a second dimension table based on the business data source table.

[0052] Step S2: Store the multi-valued attribute data in one attribute field of the first dimension table, store the multi-valued attribute data in multiple attribute fields of the first dimension table, or store each dimension value in the multi-valued attribute data in a single row of the first dimension table.

[0053] Step S3: Write the slowly changing dimension data into the second dimension table in a rewrite manner, write it into the second dimension table in a new row addition manner, write it into the second dimension table in a new attribute field addition manner, or write it into a micro dimension table associated with the second dimension table. The above micro dimension table contains an association field for associating with the second dimension table and a changing dimension field in the slowly changing dimension data.

[0054] In this embodiment, for multi-valued attribute data, it supports both placing multi-valued attributes in one attribute field of the dimension, placing multi-valued attributes in multiple attribute fields of the dimension, and storing multiple records for one dimension value. By constructing a flexible data warehouse model, it allows multiple values to be stored for each device attribute. For example, the system defines the structure and type of attributes through metadata, supporting the dynamic addition and modification of attribute values. This design enables the system to adapt to the ever-changing Internet of Things environment and meet the requirements of device attribute diversity. On this basis, the system can provide an efficient query and indexing mechanism, which can quickly locate and retrieve multiple values of specific attributes, facilitating data analysis and decision support.

[0055] For slowly changing dimension data, it also supports writing into the dimension table in multiple ways. Among them, the rewrite method means that when the data changes, the old data record is completely replaced with the new data, which is applicable to scenarios where the data change has little impact and historical changes do not need to be retained. The new row addition method means that when the data changes, a new record is added to the data table to represent the new state of the data, while retaining the old record for historical analysis. The new attribute addition method means adding a new column to a certain dimension in the existing data table to represent the new attribute or index of the data, which is applicable to the expansion of the data structure. The micro dimension method means that the slowly changing attributes are stored in another small dimension table, which can be associated with the main dimension table through simple key-value pairs, facilitating the maintenance and query of the changing data.

[0056] In a specific embodiment, when performing data modeling, a dimension table is first created. A dimension table is a structure in a data warehouse used to store descriptive information. When creating a dimension table, special consideration needs to be given to support for multi-timezone dimensions, multi-value attribute dimensions, and slowly changing dimensions. A multi-timezone dimension can ensure that the system can process data from different regions globally, thus meeting the business needs of multinational companies. A multi-value attribute dimension allows a dimension to have multiple attribute values, providing possibilities for richer data analysis. When constructing a dimension table, a reasonable key-value system needs to be designed to ensure the consistency and traceability of dimension data. At the same time, the scalability and maintainability of the dimension table need to be considered.

[0057] Then a fact table is created. The fact table is mainly used to store measurable business data, such as sales volume, usage, etc. In an Internet of Things storage system, the fact table should be linked to the dimension table through primary keys and foreign keys to form a star or snowflake model. The fact table should include business dates, measurement values, and foreign keys pointing to the dimension table to ensure data correlation. When designing the fact table, the growth trend of data and query performance need to be considered, and data partitioning and indexing should be reasonably allocated to improve the overall performance of the system.

[0058] Secondly, dimension relationships are defined. Defining dimension relationships is a key step to ensure the clarity and logical consistency of the data warehouse model. In an Internet of Things system, dimension relationships should clearly indicate the connections between different dimension tables, such as the relationship between the time dimension and the device dimension. Such relationship definitions should include hierarchical relationships, association relationships, and dependency relationships between dimension tables. By reasonably defining dimension relationships, the efficiency of data query can be improved and the analysis process can be simplified.

[0059] Finally, slowly changing dimensions are defined. Slowly changing dimensions are a mechanism in a data warehouse for handling data changes over time. In an Internet of Things system, supporting slowly changing dimensions means that the system can process data such as device status and configuration information that change over time. Slowly changing dimensions can support change patterns such as adding new rows, adding new attributes, and mini-dimensions. For example, when the device configuration changes, the system can record the change history by adding new rows while retaining the old data records for historical analysis.

[0060] In another embodiment, when placing multi-value attributes in an attribute field of a dimension, refer to the second dimension table instance shown in Table 1:

[0061] Table 1 Second dimension table instance with multi-value attributes placed in an attribute field of a dimension

[0062]

[0063] When placing multi-value attributes in multiple attribute fields of a dimension, refer to the second dimension table instance shown in Table 2:

[0064] Table 2 Second - dimension table instance with multi - valued attributes placed in multiple attribute fields of a dimension

[0065]

[0066] When multiple records are stored for one dimension value, refer to the second - dimension table instance shown in Table 3:

[0067] Table 3 Second - dimension table instance where multiple records are stored for one dimension value

[0068]

[0069]

[0070] Storing multi - valued attribute data in one attribute field of the first - dimension table has good scalability but is troublesome to use. Storing multi - valued attribute data in multiple attribute fields of the first - dimension table is simple to use but has poor scalability. Storing each dimension value in the multi - valued attribute data in a single row of the first - dimension table has good scalability and is convenient to use, but has poor performance. Generally speaking, this data warehouse modeling method supports the management method of multi - valued attributes, can meet the multi - valued requirements in different scenarios, and has better scalability.

[0071] For the specific instance of writing slowly changing dimension data into the second - dimension table in the rewrite mode, refer to Table 4 and Table 5 below. Table 4 is the original row of the device dimension in the rewrite mode, and Table 5 is the updated row of the device dimension in the rewrite mode:

[0072] Table 4 Original row of the device dimension in the rewrite mode

[0073] Device Key Device Unique Key Device MAC Tenant Belonged 123 9C287922 00-16-EA-AE-3C-40 A

[0074] Table 5 Updated row of the device dimension in the rewrite mode

[0075] Device Key Device Unique Key Device MAC Tenant Belonged 123 9C287922 00-16-EA-AE-3C-40 B

[0076] For the specific instance of writing slowly changing dimension data into the second - dimension table in the way of adding new rows, refer to Table 6 and Table 7 below. Table 6 is the original row of the device dimension in the way of adding new rows, and Table 7 is the updated row of the device dimension in the way of adding new rows:

[0077] Table 6 Original row of the device dimension in the way of adding new rows

[0078]

[0079] Table 7 Updated row of the device dimension in the way of adding new rows

[0080]

[0081]

[0082] For a specific example of writing slowly changing dimension data into the second dimension table by adding new attribute fields, refer to Table 8 and Table 9 below. Table 8 is the original row of the device dimension in the way of adding new attribute fields, and Table 9 is the updated row of the device dimension in the way of adding new attribute fields:

[0083] Table 8 Original Row of Device Dimension in the Way of Adding New Attribute Fields

[0084] Device Key Device Unique Key Device MAC Tenant Belonged 123 9C287922 00-16-EA-AE-3C-40 A

[0085] Table 9 Updated Row of Device Dimension in the Way of Adding New Attribute Fields

[0086] Device Key Device Unique Key Device MAC Tenant Belonged Historical Tenant 123 9C287922 00-16-EA-AE-3C-40 B A

[0087] For a specific example of writing slowly changing dimension data into the micro dimension table associated with the second dimension table, refer to Table 10 and Table 11 below. Table 10 is the micro dimension table for writing categorical data, and Table 11 is the micro dimension table for writing linear data:

[0088] Table 10 Micro Dimension Table for Writing Categorical Data

[0089] Micro Dimension Key Device Status Device Model 1 Online A 2 Online B 3 Offline A 4 Offline B

[0090] Table 11 Micro Dimension Table for Writing Linear Data

[0091]

[0092]

[0093] Further, in one embodiment, the above method further includes:

[0094] Obtain business requirement information and granularity setting information.

[0095] Determine the table association relationship between each dimension table according to the business requirement information.

[0096] Construct a data warehouse model according to the above granularity setting information, the first dimension table, the second dimension table, the micro dimension table, and the table association relationship.

[0097] In this embodiment, in the data Internet of Things system, selecting the granularity is the first step in data modeling, which determines the detail and accuracy of the data. The selection of the granularity should be determined according to the business requirements and analysis objectives. For example, if the system needs to track the real-time status of devices, the granularity may be set to the second level. If it is mainly used for monthly reports, the granularity may be the daily level. Selecting an appropriate granularity can optimize the storage efficiency, reduce data redundancy, and at the same time ensure the accuracy and practicality of the analysis. In addition, the selection of the granularity should also consider the data aggregation and decomposition processes to ensure that data analysis can be flexibly carried out under different dimensions.

[0098] Further, in one embodiment, the above method further includes:

[0099] After uniformly converting the service data uploaded by the device into UTC time, store it in the ODS data area.

[0100] In the DWD data detail area, adopt the implementation method of a wide table, and expand multiple time zone dimension columns on the same data detail.

[0101] In the DWS data area and the ADS data area, summarize data according to time zones to obtain multiple service data source tables, and isolate the tables according to different time zones between the service data source tables.

[0102] The attribute fields in the above multi-valued attribute data include time zone fields. The attribute fields in the above slowly changing dimension data include time zone fields.

[0103] In this embodiment, when configuring the model, different time zone dimensions are configured in the common dimension data area. For daylight saving time and winter time, set offsets in the model to implement. Configure optional time zone dimensions for different tenants. Configure optional time zone dimensions in the application layer.

[0104] After the device data is reported to the IoT, it is uniformly converted into standard UTC time and stored in the ODS data area. Start the data calculation task of the service end, create different tasks for different time zone dimensions, and the tasks do not interfere with each other. In the DWD data detail area, adopt the implementation method of a wide table, and dynamically expand multiple time zone dimension columns on the same data detail. The DWS data area and the ADS data area need to summarize data according to time zones, and the summarized data is isolated by tables according to different time zones. The application side can select different time zones to query data.

[0105] Based on the multi-time zone dimension configuration, the same data can be reused and repeatedly calculated by multiple time zones. All time zone configurations and calculations are directly implemented through configuration without the need for development. Only one copy of the data details needs to be stored, without storing multiple copies, saving storage space. Support multi-time zone business scenarios, uniformly process data, and meet the data usage requirements of users in different time zones.

[0106] Further, in one embodiment, the above method further includes:

[0107] When writing slowly changing dimension data into the second dimension table or the micro dimension table, add version information and timestamps.

[0108] In this embodiment, in view of the slow change characteristics of Internet of Things data, the storage system introduces a processing mechanism for slowly changing dimensions (SCD). The system can track and record the minute changes of data dimensions over time, and retain historical version data to ensure data coherence and traceability. Through version control and timestamp technology, the SCD management mechanism allows users to query and analyze the status of data at different time points, and at the same time supports data snapshot and rollback operations to meet the needs of data analysis and business decision-making.

[0109] In a second aspect, an embodiment of the present application further provides an Internet of Things data storage system based on data warehouse modeling.

[0110] In one embodiment, referring to Figure 2 , Figure 2 is a schematic diagram of the functional modules of an embodiment of the Internet of Things data storage system based on data warehouse modeling of the present application. As Figure 2 shown, the Internet of Things data storage system based on data warehouse modeling includes:

[0111] A data acquisition module 1, which is used to acquire a business data source table.

[0112] A data preprocessing module 2, which is used to extract data from the business data source table to obtain multi-valued attribute data and slowly changing dimension data, and construct a first dimension table and a second dimension table based on the business data source table.

[0113] A data warehouse modeling module 3, which is used to store multi-valued attribute data into an attribute field of the first dimension table, store multi-valued attribute data into multiple attribute fields of the first dimension table, or store each dimension value in the multi-valued attribute data into a single row of the first dimension table. Write the slowly changing dimension data into the second dimension table in a rewritten manner, write it into the second dimension table by adding a new row, write it into the second dimension table by adding a new attribute field, or write it into a micro dimension table associated with the second dimension table, where the micro dimension table contains an association field for associating with the second dimension table and a changing dimension field in the slowly changing dimension data.

[0114] In this embodiment, the data acquisition module 1 obtains information such as the business data source table through business research. Business research is a process of deeply understanding the enterprise's business processes and requirements. The main goal of this stage is to identify user requirements, analyze business processes, and determine key business indicators and performance indicators. During the research process, it is necessary to communicate with various departments within the enterprise to collect detailed information. This information will be used to guide subsequent data warehouse modeling and model implementation. Through business research, it can be ensured that the direction and goals of data warehouse construction match the actual needs of the enterprise.

[0115] Data warehouse modeling is the process of transforming the business requirements obtained in the research phase into a data model. This phase involves data classification, integration, and structured design. The design of the data warehouse model needs to consider multiple aspects such as data sources, storage, processing, and querying. Good model design can improve data accessibility and usability, while ensuring data consistency and accuracy. During the model design process, ER (Entity-Relationship) diagrams or dimensional models are usually used to express the logical relationships between data.

[0116] During model implementation, check the accuracy, integrity, and consistency of the data to ensure that the data meets the business requirements and model specifications. Test the performance and stability of the data model by simulating different business scenarios to ensure that the data system can correctly process and analyze the data. Deploy the tested data model to the production environment for business use. Continuously monitor the running status of the data model, promptly discover and solve possible problems, and ensure the stable operation of the data system.

[0117] Furthermore, in one embodiment, the above-mentioned data acquisition module 1 is also used to obtain business requirement information and granularity setting information.

[0118] The above-mentioned data warehouse modeling module 3 is also used to determine the table association relationships between each dimension table according to the business requirement information. Build a data warehouse model based on the above-mentioned granularity setting information, the first dimension table, the second dimension table, the micro dimension table, and the table association relationships.

[0119] Furthermore, in one embodiment, the above-mentioned data acquisition module 1 is also used to uniformly convert the business data uploaded by the device to UTC time and then store it in the ODS data area. In the DWD data detail area, adopt the implementation method of wide tables, and expand multiple time zone dimension columns on the same data detail. In the DWS data area and the ADS data area, summarize the data according to the time zone to obtain multiple business data source tables, and isolate the tables between each business data source table according to different time zones.

[0120] Furthermore, in one embodiment, the attribute fields in the above-mentioned multi-valued attribute data include time zone fields.

[0121] The attribute fields in the above-mentioned slowly changing dimension data include time zone fields.

[0122] Furthermore, in one embodiment, the above-mentioned data warehouse modeling module 3 is also used to add version information and timestamps when writing slowly changing dimension data into the second dimension table or the micro dimension table.

[0123] Among them, the functional implementation of each module in the above-mentioned Internet of Things data storage system based on data warehouse modeling corresponds to each step in the above-mentioned embodiment of the Internet of Things data storage method based on data warehouse modeling, and its functions and implementation processes will not be elaborated here one by one.

[0124] It should be noted that the serial numbers of the embodiments of the present application above are only for description and do not represent the superiority or inferiority of the embodiments.

[0125] The terms "comprising" and "having" and any variations thereof in the specification, claims and drawings of the present application are intended to cover non-exclusive inclusion. For example, a process, method, system, product or device that includes a series of steps or units is not limited to the listed steps or units, but optionally further includes steps or units not listed, or optionally further includes other steps or units inherent to these processes, methods, products or devices. The descriptions such as "first", "second" and "third" are used to distinguish different objects, etc., and do not represent a sequence, nor do they limit that "first", "second" and "third" are of different types.

[0126] In the description of the embodiments of the present application, "exemplary", "for example" or "for instance" are used to represent examples, illustrations or explanations. Any embodiment or design solution described as "exemplary", "for example" or "for instance" in the embodiments of the present application should not be construed as being more preferred or having more advantages than other embodiments or design solutions. Rather, the use of words such as "exemplary", "for example" or "for instance" is intended to present the relevant concepts in a specific manner.

[0127] In the description of the embodiments of the present application, unless otherwise specified, " / " means "or". For example, A / B may represent A or B; "and / or" in the text is only a description of the association relationship between associated objects, indicating that there can be three relationships. For example, A and / or B may represent: A exists alone, A and B exist simultaneously, and B exists alone. In addition, in the description of the embodiments of the present application, "a plurality of" means two or more than two.

[0128] In some processes described in the embodiments of the present application, there are multiple operations or steps that appear in a specific order. However, it should be understood that these operations or steps may not be executed in the order in which they appear in the embodiments of the present application or may be executed in parallel. The serial numbers of the operations are only used to distinguish different operations, and the serial numbers themselves do not represent any execution order. In addition, these processes may include more or fewer operations, and these operations or steps may be executed in order or in parallel, and these operations or steps may be combined.

[0129] Through the description of the above embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus a necessary general hardware platform. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application, in essence, or the part that contributes to the prior art can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above and includes several instructions for causing a terminal device to execute the methods described in various embodiments of the present application.

[0130] The above are only the preferred embodiments of the present application, and do not limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made by using the content of the specification and drawings of the present application, or directly or indirectly applied in other related technical fields, shall be equally included in the patent protection scope of the present application.

Claims

1. An Internet of Things data storage method based on data warehouse modeling, characterized in that, The method includes: Obtaining a business data source table, extracting data from the business data source table to obtain multi-valued attribute data and slowly changing dimension data, and constructing a first dimension table and a second dimension table based on the business data source table; Storing the multi-valued attribute data into one attribute field of the first dimension table, storing the multi-valued attribute data into multiple attribute fields of the first dimension table, or storing each dimension value in the multi-valued attribute data into a single row of the first dimension table; Writing the slowly changing dimension data into the second dimension table in a rewrite manner, writing it into the second dimension table by adding a new row, writing it into the second dimension table by adding a new attribute field, or writing it into a micro dimension table associated with the second dimension table, where the micro dimension table contains an association field for associating with the second dimension table and a changing dimension field in the slowly changing dimension data.

2. The Internet of Things data storage method based on data warehouse modeling according to claim 1, wherein, The method further includes: Obtaining business requirement information and granularity setting information; Determining the table association relationships between the respective dimension tables according to the business requirement information; Constructing a data warehouse model according to the granularity setting information, the first dimension table, the second dimension table, the micro dimension table, and the table association relationships.

3. The Internet of Things data storage method based on data warehouse modeling according to claim 1, wherein The method further includes: After uniformly converting the business data uploaded by the device to UTC time, storing it in the ODS data area; In the DWD data detail area, adopting a wide table implementation method to expand multiple time zone dimension columns on the same data detail; In the DWS data area and the ADS data area, performing data aggregation according to the time zone to obtain multiple business data source tables, and isolating the tables according to different time zones between the respective business data source tables.

4. The Internet of Things data storage method based on data warehouse modeling according to claim 3, wherein, The attribute fields in the multi-valued attribute data include time zone fields; The attribute fields in the slowly changing dimension data include time zone fields.

5. The method for storing Internet of Things data based on data warehouse modeling according to claim 1, wherein, The method further includes: When writing the slowly changing dimension data into the second dimension table or the micro dimension table, adding version information and timestamps.

6. An Internet of Things data storage system based on data warehouse modeling, characterized in that, The system includes: A data acquisition module for obtaining a business data source table; A data preprocessing module for extracting data from the business data source table to obtain multi-valued attribute data and slowly changing dimension data, and constructing a first dimension table and a second dimension table based on the business data source table; A data warehouse modeling module for storing the multi-valued attribute data into one attribute field of the first dimension table, storing the multi-valued attribute data into multiple attribute fields of the first dimension table, or storing each dimension value in the multi-valued attribute data into a single row of the first dimension table; writing the slowly changing dimension data into the second dimension table in a rewrite manner, writing it into the second dimension table by adding a new row, writing it into the second dimension table by adding a new attribute field, or writing it into a micro dimension table associated with the second dimension table, where the micro dimension table contains an association field for associating with the second dimension table and a changing dimension field in the slowly changing dimension data.

7. The Internet of Things data storage system based on data warehouse modeling according to claim 6, characterized in that The data acquisition module is further used for obtaining business requirement information and granularity setting information; The data warehouse modeling module is further used for determining the table association relationships between the respective dimension tables according to the business requirement information; constructing a data warehouse model according to the granularity setting information, the first dimension table, the second dimension table, the micro dimension table, and the table association relationships.

8. The Internet of Things data storage system based on data warehouse modeling according to claim 6, wherein The said data acquisition module is also used to uniformly convert the business data uploaded by the device into UTC time and then store it in the ODS data area; in the DWD data detail area, a wide table implementation method is adopted to expand multiple time zone dimension columns on the same data detail; in the DWS data area and the ADS data area, data is aggregated according to time zones to obtain multiple business data source tables, and the various business data source tables are isolated according to different time zones.

9. The Internet of Things data storage system based on data warehouse modeling according to claim 8, characterized in that, The attribute fields in the said multi-valued attribute data include time zone fields; The attribute fields in the said slowly changing dimension data include time zone fields.

10. The Internet of Things data storage system based on data warehouse modeling according to claim 6, wherein The said data warehouse modeling module is also used to add version information and timestamps when writing slowly changing dimension data into the second dimension table or the micro dimension table.

Images