Social insurance report construction system and method based on multi-dimensional data cube

Abstract

The invention discloses a social insurance report construction system and method based on a multi-dimensional data cube. The system comprises a data acquisition module, a multi-dimensional index description module, a multi-dimensional data cube calculation module, a report construction module and a report generation module. The multi-dimensional data cube calculation module is used for calculating and generating each cell of a data cube through a data classification and clustering algorithm, and constructing a social insurance multi-dimensional data cube by using a plurality of cube cell sets with the same dimension; the report generation module is used for generating and exporting social insurance reports meeting different requirements, and the social insurance reports comprise two types of reports including traditional standard reports and custom custom reports; the method is suitable for deeply analyzing the social insurance data from multiple angles, meets diversified requirements of different users to generate two types of reports, achieves refined data management in the field of social insurance, and has wide application prospects and remarkable economic benefits.

Description

Technical Field

[0001] The present invention relates to a social insurance report construction system and method, and in particular to a social insurance report construction system and method based on a multi-dimensional data cube. Background Art

[0002] The social insurance system involves the recording and analysis of a large amount of detailed data, including basic information of insured persons (such as name, gender, nature of unit, etc.), information of insured units (nature of unit, economic type, etc.), as well as years, industry indicators, etc. At present, traditional data processing and analysis methods are widely used. In particular, in the process of building standardized reports, due to the diversity of data sources and the cumbersomeness of data processing procedures, it is often necessary to associate and match data from multiple data sources in order to obtain complete and accurate data information. Under the existing model, repeated data collection, repeated processing, repeated calculation and data redundancy often occur.

[0003] In the existing technology, the processing and analysis of social insurance data mainly rely on relational databases and simple statistical tools, lacking support for multi-dimensional data. Although some systems attempt to integrate data through data warehouse technology, due to the lack of effective multi-dimensional data cube technology, it is difficult to achieve cross-dimensional data aggregation and efficient query. In addition, existing report generation tools usually require manual configuration of data dimensions and indicators, and cannot achieve automated and intelligent report generation, resulting in low report generation efficiency and poor flexibility.

[0004] Traditional report generation methods often require manual extraction and organization of information from massive amounts of data, which is not only time-consuming and labor-intensive, but also difficult to display multi-dimensional relationships between data, reducing the efficiency of data analysis and management. Through multi-dimensional data cube technology, insurance companies can quickly generate data reports that meet different needs, and through querying and analyzing data cubes, they can explore the laws and trends behind the data to assist managers in making decisions. Summary of the invention

[0005] Purpose of the invention: The purpose of the invention is to provide a social insurance standard report construction system based on a multi-dimensional data cube to improve the efficiency and accuracy of data processing by constructing social insurance reports. On the other hand, a social insurance report construction method based on a multi-dimensional data cube is provided.

[0006] Technical solution: A social insurance report construction system based on multi-dimensional data cube, including: Data collection module, used to collect social insurance data from data sources and clean and integrate the data; The multi-dimensional indicator characterization module is used to define the multi-dimensional indicator system according to the needs, determine the value range of each dimension, data format and the relationship between dimensions; A multi-dimensional data cube calculation module is used to map the social insurance data to the multi-dimensional indicator system, calculate and generate each cell of the data cube through data classification and clustering algorithm, and construct a multi-dimensional data cube by combining multiple cube cells of the same dimension. The multi-dimensional data cube includes business field characteristics, unit characteristics, time characteristics, and indicator description characteristics; A report construction module, used to configure a custom report template by dragging and dropping through a graphical interface, and to retrieve data from the multi-dimensional data cube to construct a social insurance report that meets the needs; A report generation module is used to generate and export social insurance reports that meet different needs. The social insurance reports include traditional standard reports and custom customized reports; the traditional standard reports are generated by configuring dimensions, main columns and auxiliary columns, and exported according to the report template; the custom customized reports are generated by configuring dimensions, indicators and query conditions, and exported by modifying the dimension range.

[0007] Preferably, it also includes an intelligent analysis module for analyzing and comparing historical social insurance data based on social insurance reports and predicting data change trends.

[0008] Preferably, it also includes an alarm module, which is used to trigger an alarm when the data in the report exceeds a preset threshold, and the report data will be marked in red.

[0009] Preferably, the social insurance data is collected from a data source through a database query script or a data extraction tool, and the data extraction tool includes an open source task scheduling execution center dophonsculer, a data extraction sqoop, and a data extraction Datax component.

[0010] Preferably, by configuring data cleaning scripts and data comparison scripts in the task scheduling execution center dophonsculer, tasks are arranged based on the data cleaning scripts, and data volume queries are executed in the data source library and the big data warehouse based on the data comparison scripts, the data volumes are compared, and missing values, duplicate values ​​and abnormal values ​​of the data are processed to complete data cleaning and integration.

[0011] Preferably, the multi-dimensional indicator system includes the dimensions of name, gender, nature of unit, unit name, unit ID, unit registration ID, economic type, affiliation, industry category, district, city and county ID, statistical year and month, fund source, and risk industry category under the total number of employees participating in pension insurance.

[0012] Preferably, the clustering algorithm process is: configure data extraction tasks, extract social insurance data from different data sources into the big data base Hadoop through data extraction tools for storage, configure execution task scripts in the task scheduling execution center Dophonsculer, obtain target data through calculations of the big data computing engine Spark and the big data computing and storage Hive, and generate each cell of the data cube.

[0013] The method for constructing a social insurance report based on a multi-dimensional data cube according to the present invention comprises the following steps: S1. Based on the multi-dimensional indicator characterization module, define the multi-dimensional indicator system according to the needs, determine the value range, data format and relationship between dimensions of each dimension; S2, collect social insurance data from data sources through the data collection module, and clean and integrate the data; S3, based on the multi-dimensional data cube calculation module, mapping the social insurance data to the multi-dimensional indicator system, calculating and generating each cell of the data cube through data classification and clustering algorithm, and constructing a multi-dimensional data cube by combining multiple cube cells of the same dimension; S4. Drag and drop the report building module to configure a custom report template, and retrieve data from the multi-dimensional data cube to build a social insurance report that meets the needs; S5. Generate and export social insurance reports that meet different needs based on the report generation module.

[0014] Beneficial effects: Compared with the prior art, the present invention has the following significant advantages: 1. By defining a multi-dimensional indicator system, it is possible to conduct in-depth analysis of social insurance data from multiple angles to meet the diverse needs of different users. The data cube supports cross-dimensional data aggregation and efficient query, making complex data analysis tasks simple and efficient, and improving the efficiency and accuracy of data processing; 2. By introducing a multi-dimensional data cube, it is possible to efficiently integrate social insurance data from multiple data sources, avoiding the problems of repeated collection, repeated processing and repeated calculation in traditional methods; 3. In the process of data cleaning and integration, by configuring data comparison scripts, the data consistency in the source library and the big data warehouse is ensured, data redundancy and errors are avoided, and the construction of the data cube is based on a unified dimensional standard to ensure the accuracy and consistency of the data. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Figure 1 This is the system architecture intention of the present invention; Figure 2 It is a schematic diagram of the process of the present invention. DETAILED DESCRIPTION

[0016] The technical solution of the present invention is described in detail below in conjunction with the accompanying drawings.

[0017] like Figure 1 As shown, this embodiment is based on a multi-dimensional data cube social insurance report construction system, including: Data collection module, used to collect social insurance data from data sources and clean and integrate the data; The multi-dimensional indicator characterization module is used to define the multi-dimensional indicator system according to the needs, determine the value range of each dimension, data format and the relationship between dimensions; The multi-dimensional data cube calculation module is used to map social insurance data to a multi-dimensional indicator system, calculate and generate each cell of the data cube through data classification and clustering algorithms, and construct a multi-dimensional data cube by combining multiple cube cells of the same dimension. The multi-dimensional data cube includes business field characteristics, unit characteristics, time characteristics, and indicator description characteristics; The report building module is used to configure custom report templates by dragging and dropping through a graphical interface, and to retrieve data from a multi-dimensional data cube to build social insurance reports that meet the needs; The report generation module is used to generate and export social insurance reports that meet different needs. Social insurance reports include traditional standard reports and customized reports. Traditional standard reports are generated by configuring dimensions, main columns and secondary columns, and exported according to report templates. Customized reports are generated by configuring dimensions, indicators and query conditions, and exported by modifying dimension ranges. Intelligent analysis module, used to analyze and compare historical social insurance data based on social insurance standard reports and predict data change trends; The alarm module is used to trigger an alarm when the data in the report exceeds the preset threshold, and the report data will be marked in red.

[0018] like Figure 2 As shown, the method for constructing a social insurance report based on a multi-dimensional data cube includes the following steps: S1. Based on the multi-dimensional indicator characterization module, according to daily statistical needs, users may define a multi-dimensional indicator system according to statistical needs in the future, determine the value range of each dimension, data format and the relationship between dimensions; Dimension generally refers to a certain characteristic of a phenomenon, a measurement statistic from a certain perspective, and the perspective is the dimension; the multi-dimensional indicator system includes the name, gender, nature of the unit, unit name, unit ID, unit registration ID, economic type, affiliation, industry category, district, city and county ID, statistical year and month, fund source, and risk industry category dimensions under the total number of employees participating in pension insurance.

[0019] S2, collect social insurance data from data sources through the data collection module, and clean and integrate the data; The specific steps for data cleaning and integration are as follows: configure the data cleaning script in the task scheduling execution center dophonsculer and schedule the script; configure the data comparison script after the cleaning task is completed and execute it in the source library and the big data warehouse respectively; check the data volume through data volume query, process missing values, duplicate values ​​and abnormal values, complete data cleaning and integration, and determine the integrity and consistency of the data.

[0020] Take the situation of insured persons in the pension sector of Nanjing government agencies as an example: Nanjing currently has 1,148 data cleaning tasks configured on site (including 301 for employment and entrepreneurship, 625 for social insurance, 68 for talent and personnel, 70 for labor relations, and 84 for public services) and 545 data fusion computing tasks.

[0021] S3. Based on the multi-dimensional data cube calculation module, the social insurance data is mapped to the multi-dimensional indicator system and stored in the big data base Hadoop. The dimensions are extracted according to the business accumulation, such as unit nature, economic type, district, city, industry category, risk industry category, affiliation, etc., to ensure that the characteristics of each indicator have the same scale; Configure the execution task script and execute it in the task scheduling execution center dophonsculer. After clustering calculations of Spark (big data computing engine) and hive (big data computing and storage), the cells of the data cube are generated. Multiple cube cells of the same dimension are combined to form a multi-dimensional data cube. The multi-dimensional data cube includes business field characteristics, unit characteristics, time characteristics, and indicator description characteristics. The multi-dimensional data cube tries to ensure that the cubes under the same topic belong to the same business field. Currently, a topic can contain multiple cubes, but a cube can only belong to one topic.

[0022] The multi-dimensional data cubes in the field of human resources and social security business include: Unit characteristics: the unit of the indicator, such as person, 10,000 people, unit, yuan, 10,000 yuan, ratio, etc.; Business field characteristics: describe the business field in which the indicator is located; Source characteristics: indicates the calculation source of the indicator, which can be calculated based on the business table, calculated based on the ledger, or manually entered (entry frequency, reporting level); Comparison feature: The current period indicators need to be compared with the historical period cube, and can be compared with the previous period, the period before that, the same period last year, and the end of last year; Dimensional characteristics: that is, the angle from which the analysis is conducted. From the current analysis, the dimensions include: year and month, district and county, nature of unit, economic type, industry category, etc.

[0023] The specific calculation is as follows: Indicator: Number of employees participating in insurance at the end of the agency's pension period; (1) Initialize the basic information table of government pension units every month, query the basic information of the units (fields include: statistical year and month, unit registration ID, unit ID, unit name, district and county ID, unit nature, economic type, affiliation, fund source, industry category, risk industry category, etc.), and limit the handling agency ID to belong to the government and participate in the government pension insurance (insurance type mark 102).

[0024] According to the basic information table of the agency pension unit, the monthly statistical year and month, and the statistical end date, the unit registration form and the agency personnel registration form are queried. The restricted unit insurance type flag is 102, the unit termination year and month are not empty, the insured unit status is normal, and the insurance category of the insured personnel under the unit includes agency pension, the employee category belongs to "in-service personnel, interrupted personnel", and the starting year and month of the personnel registration form is less than or equal to the statistical year and month, and the in-service reduction time is greater than or equal to the statistical end date. Under the restricted conditions, it is determined that the corresponding unit belongs to the agency insured unit.

[0025] The basic information table of the government pension unit is stored in the corresponding ledger table according to the "Government Pension Theme" dimension: statistical year and month, unit nature, fund source, affiliation, district and city, and the grouped values ​​are obtained according to the above dimensions.

[0026] When querying a report, select information items based on the dimensions configured in the report main column, and directly query the ledger table to obtain the corresponding numerical information.

[0027] S4. Configure custom report templates by dragging and dropping through the graphical interface, and retrieve data from the social insurance multi-dimensional data cube to build social insurance reports that meet your needs.

[0028] S5. Generate and export social insurance reports that meet different needs according to the report generation module. The social insurance reports include traditional standard reports and custom customized reports.

[0029] Traditional standard reports are generated by configuring dimensions, main columns and secondary columns, and exported according to the report template; custom customized reports are generated by configuring dimensions, indicators and query conditions, and exported by modifying the dimension range.

[0030] The details are as follows: The main column of the report is the description of each row of each report, and each row in the traditional standard report can be controlled by one or more dimensions. For example, the three dimensions of unit nature, economic type, and enterprise agency logo can be used to control the row data; the object column is a column or an indicator, and a row in the main column is realized by limiting the dimension of the indicator.

[0031] The system displays all dimensions under the configured business domain theme, and users select the dimensions they want to use according to their reporting requirements.

[0032] Manually configure the value of the main column in the report, and select the dimension for each main column cell. Manually configure the value of the secondary column in the report, and select the corresponding cube (indicator) for each secondary column. Through the configuration of the main column and secondary column of the report, each cell in the report is covered with cubes and dimensions. The system automatically obtains these cubes and dimensions to automatically calculate the results of the report.

[0033] (a) To generate a traditional standard report on the status of insured personnel in the field of institutional pension: The main columns of the report have five categories, namely "Total", "Basic pension insurance for government agencies and public institutions", "Government agencies", "Public institutions", and "Other units", which are classified according to the different nature of the units of the indicators.

[0034] The report includes the following indicators: number of employees participating in insurance at the end of the agency pension period, number of employees paying premiums at the end of the agency pension period, number of retirees at the end of the agency pension period, number of retirees at the end of the agency pension period - retired, number of retirees at the end of the agency pension period - retired (employed), number of retirees increased this year under agency pension - total, number of retirees increased this year under agency pension - retired due to illness, number of retirees increased this year under agency pension - special occupations, etc., a total of 17 indicators.

[0035] The data sources of the indicators in the report are all related values ​​obtained from the business system. The multi-dimensional data cube distinguishes the respective numerical information according to the nature of the unit, affiliation, fund source and other dimensions, so that the indicators in the report can be assembled and configured according to various dimensions, and finally a traditional standard report on the situation of insured persons in the field of agency pension is generated.

[0036] (b) To generate a custom report on the status of insured personnel in the field of institutional pension: Users can quickly generate intelligent customized reports by selecting the required dimensions (such as unit nature, economic type) through a graphical interface. Users can select the required indicators (such as the number of insured units at the end of the period, the number of paying units at the end of the period) and query conditions. By modifying the dimension range, personalized statistical needs can be quickly met, and customized reports on the situation of insured personnel in the field of government pensions can be generated.

[0037] The report supports year-on-year analysis of historical data, analysis of changing trends in the number of retirees over the years, and trend forecasting of data.

[0038] The system configures thresholds based on business needs. When the number of insured persons at the end of the period calculated by the system meets the warning conditions, an alarm is automatically triggered, such as "the increase in the number of insured persons at the end of this period compared with the number of insured persons at the end of the previous period is less than 3%, and the decrease is greater than 1%." The system supports exporting reports as traditional standard reports or customized reports in Excel format to meet user needs in different scenarios. When exporting reports, the values ​​of relevant indicators are marked in red to remind users.

Claims

1. A social insurance report construction system based on multi-dimensional data cube, characterized in that: include: Data collection module, used to collect social insurance data from data sources and clean and integrate the data; The multi-dimensional indicator characterization module is used to define the multi-dimensional indicator system according to the needs, determine the value range of each dimension, data format and the relationship between dimensions; A multi-dimensional data cube calculation module is used to map the social insurance data to the multi-dimensional indicator system, calculate and generate each cell of the data cube through data classification and clustering algorithm, and construct a multi-dimensional data cube by combining multiple cube cells of the same dimension. The multi-dimensional data cube includes business field characteristics, unit characteristics, time characteristics, and indicator description characteristics; A report construction module, used to configure a custom report template by dragging and dropping through a graphical interface, and to retrieve data from the multi-dimensional data cube to construct a social insurance report that meets the needs; A report generation module is used to generate and export social insurance reports that meet different needs. The social insurance reports include traditional standard reports and custom customized reports; the traditional standard reports are generated by configuring dimensions, main columns and auxiliary columns, and exported according to the report template; the custom customized reports are generated by configuring dimensions, indicators and query conditions, and exported by modifying the dimension range.

2. The social insurance report construction system according to claim 1, characterized in that: It also includes an intelligent analysis module, which is used to analyze and compare historical social insurance data based on the generated social insurance reports and predict data change trends.

3. The social insurance report construction system according to claim 1, characterized in that: It also includes an alarm module, which is used to trigger an alarm when the data in the report exceeds a preset threshold, and the report data will be marked in red.

4. The social insurance report construction system according to claim 1, characterized in that: The social insurance data is collected from the data source through a database query script or a data extraction tool, wherein the data extraction tool includes an open source task scheduling execution center dophonsculer, a data extraction sqoop and a data extraction Datax component.

5. The social insurance report construction system according to claim 1, characterized in that: By configuring data cleaning scripts and data comparison scripts in the task scheduling execution center dophonsculer, tasks are arranged based on the data cleaning scripts, and data volume queries are executed in the data source library and the big data warehouse based on the data comparison scripts, the data volume is compared, and missing values, duplicate values ​​and abnormal values ​​are processed to complete data cleaning and integration.

6. The social insurance report construction system according to claim 1, characterized in that: The multi-dimensional indicator system includes the dimensions of name, gender, nature of unit, unit name, unit ID, unit registration ID, economic type, affiliation, industry category, district, city and county ID, statistical year and month, fund source, and risk industry category under the total number of employees participating in pension insurance.

7. The social insurance report construction system according to claim 1, characterized in that: The clustering algorithm process is as follows: configure data extraction tasks, extract social insurance data from different data sources into the big data base Hadoop through data extraction tools, configure execution task scripts in the task scheduling execution center Dophonsculer, obtain target data through the calculation of the big data computing engine Spark and the big data computing and storage Hive, and generate each cell of the data cube.

8. A method for constructing social insurance reports based on multi-dimensional data cubes, characterized in that: The following steps are involved: S1. Based on the multi-dimensional indicator characterization module, define the multi-dimensional indicator system according to the needs, determine the value range, data format and relationship between dimensions of each dimension; S2, collect social insurance data from data sources through the data collection module, and clean and integrate the data; S3, based on the multi-dimensional data cube calculation module, mapping the social insurance data to the multi-dimensional indicator system, calculating and generating each cell of the data cube through data classification and clustering algorithm, and constructing a multi-dimensional data cube by combining multiple cube cells of the same dimension; S4. Drag and drop the report building module to configure a custom report template, and retrieve data from the multi-dimensional data cube to build a social insurance report that meets the needs; S5. Generate and export social insurance reports that meet different needs based on the report generation module.

9. A computer device, characterized in that: It includes one or more processors, a memory and one or more programs, wherein the one or more programs are stored in the memory and are configured to be executed by the one or more processors, and when the programs are executed by the processors, the steps of the method for constructing a social insurance report based on a multi-dimensional data cube as described in claim 8 are implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method for constructing a social insurance report based on a multi-dimensional data cube as described in claim 8 are implemented.

Images