Method and system for constructing ADM layer table structure of bank counting warehouse

Abstract

The invention relates to a method and a system for constructing an ADM layer table structure of a bank counting warehouse. The method comprises the following steps of: a, designing a standardized data model; b, integrating and processing data; c, carrying out personalized service design; d, data security management; and e, continuously optimizing and improving. The method has the advantages that through standardized data model design, data redundancy can be reduced, data consistency is improved, and data are easier to manage and maintain. Through standardized processing and ETL process design, the data storage structure can be optimized, the query efficiency is improved, and the query response time is shortened.

Description

Technical Field

[0001] The invention relates to a method for constructing an ADM layer table structure of a bank data warehouse, and belongs to the field of banking. Background Art

[0002] With the development of banking business, the rapid increase in data volume has put forward higher requirements for data storage and processing. Traditional data warehouse design can no longer meet the needs of efficient data query and analysis in current complex business scenarios. Therefore, it is particularly important to optimize the data warehouse architecture, especially the design of the ADM (Application Data Mart) layer.

[0003] At present, bank data warehouses generally adopt a layered architecture design, including ODS (Operational Data Store), DWD (Data Warehouse Detail), DWS (Data Warehouse Summary) and ADS (Application Data Service) layers. Among them, the ADM layer, as an important bridge connecting the DWS layer and the final application, bears the important responsibilities of data integration, processing and personalized services. However, the existing ADM layer table structure design often has problems such as data redundancy, low query efficiency, and difficulty in meeting personalized needs. Summary of the invention

[0004] The purpose of the present invention is to provide a bank data warehouse ADM layer table structure design scheme, aiming to solve the problems of data redundancy, low query efficiency and difficulty in meeting personalized needs in the prior art.

[0005] In order to overcome the defects of the prior art, the present invention provides a method for constructing the ADM layer table structure of a bank data warehouse. The technical solution of the present invention is:

[0006] A method for constructing an ADM layer table structure of a bank data warehouse includes the following steps: a. standardized data model design; b. data integration and processing; c. personalized service design; d. data security management; e. continuous optimization and improvement.

[0007] The step a is specifically:

[0008] Business subject classification: Use feature-based clustering algorithms to classify business data. Where J is the clustering cost function, k is the number of clusters, Si is the i-th cluster set, and μi is the center point of the i-th cluster;

[0009] Entity-relationship model building: Apply ER model building tools to define entities and relationships and generate ER diagrams;

[0010] Normalization: Use functional dependencies and multi-valued dependencies to determine the degree of normalization of the table and ensure that the data model meets at least the third normal form.

[0011] The step b is specifically as follows: ETL process design: designing ETL templates, using automated data extraction, conversion and loading;

[0012] Data cleaning: Introduce the statistical anomaly detection algorithm Grubbs'test to identify and process outliers. Where G is the Grubbs'test statistic, is the sample mean, is the population mean, s is the sample standard deviation;

[0013] Data transformation: Use data transformation algorithms to process time series data or perform feature extraction.

[0014] The step c is specifically as follows:

[0015] User demand analysis: Analyze user feedback through natural language processing technology to better understand user needs;

[0016] Dynamic data service construction: Use the rule engine to dynamically adjust data services based on user needs.

[0017] The step d is specifically as follows:

[0018] Access control strategy: implement attribute-based access control;

[0019] Data desensitization: Apply data desensitization algorithms to sensitive data to protect personal privacy;

[0020]

[0021] The step e is specifically as follows:

[0022] Performance monitoring and optimization: Use application performance management tools to monitor application performance and optimize database queries;

[0023] Feedback loop: Establish a feedback system based on machine learning to automatically adjust data services based on user feedback;

[0024] Reinforcement learning update = α·reward + β·penalty, where α and β are learning rates that adjust the speed at which the algorithm learns based on rewards and penalties.

[0025] A system based on the method for constructing the bank data warehouse ADM layer table structure, comprising:

[0026] Standardized data model design module, used to classify data according to business topics, and design a standardized data model containing entities, attributes and their relationships for each business topic; Data integration and processing module, used to obtain basic data from the DWS layer, and integrate and process it according to business needs;

[0027] Personalized service design module, used to design personalized data services according to the needs of different business departments and users;

[0028] Data security management module, used to strengthen the control of data access rights and ensure data security and privacy;

[0029] The continuous optimization and improvement module is used to regularly evaluate and optimize the performance of the ADM layer table structure.

[0030] The advantages of the present invention are:

[0031] 1. Through standardized data model design, data redundancy can be reduced, data consistency can be improved, and data can be easier to manage and maintain.

[0032] 2. Through normalization processing and ETL process design, the data storage structure can be optimized, query efficiency can be improved, and query response time can be reduced.

[0033] 3. The personalized service design module can provide customized data services according to the needs of different business departments and users to meet personalized needs.

[0034] 4. The data security management module strengthens data security and privacy through access control strategies and data desensitization technology to prevent data leakage and abuse.

[0035] 5. Introducing statistical algorithms such as Grubbs'test to deal with outliers can improve data quality and reduce the impact of erroneous data on analysis results.

[0036] 6. Dynamic data service construction can dynamically adjust data services according to user needs, improving service flexibility and responsiveness.

[0037] 7. Through performance monitoring and optimization and machine learning-based feedback systems, the performance and user experience of data services can be continuously improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] Figure 1 It is a schematic diagram of the structure of the system of the present invention. DETAILED DESCRIPTION

[0039] The present invention will be further described below in conjunction with specific embodiments, and the advantages and features of the present invention will become clearer as the description proceeds. However, these embodiments are exemplary only and do not constitute any limitation to the scope of the present invention. It should be understood by those skilled in the art that the details and forms of the technical solution of the present invention may be modified or replaced without departing from the spirit and scope of the present invention, but these modifications and replacements all fall within the scope of protection of the present invention.

[0040] The present invention relates to a method for constructing an ADM layer table structure of a bank data warehouse, comprising the following steps:

[0041] a. Standardized data model design; b. Data integration and processing; c. Personalized service design; d. Data security management; e. Continuous optimization and improvement.

[0042] The step a is specifically:

[0043] Business topic classification: Use feature-based clustering algorithms, such as K-means or DBSCAN, to classify business data. Where J is the clustering cost function, k is the number of clusters, Si is the i-th cluster set, and μi is the center point of the i-th cluster;

[0044] Entity-relationship model building: Apply ER model building tools, such as ER Studio, to define entities and relationships and generate ER diagrams;

[0045] Normalization: Use functional dependencies and multi-valued dependencies to determine the degree of normalization of the table and ensure that the data model meets at least the third normal form.

[0046] The step b is specifically:

[0047] ETL process design: Design ETL templates to automate data extraction, transformation, and loading using Apache Nifi or Talend.

[0048] Data cleaning: Introduce the statistical anomaly detection algorithm Grubbs'test to identify and process outliers. Where G is the Grubbs'test statistic, is the sample mean, is the population mean, s is the sample standard deviation;

[0049] Data transformation: Use data transformation algorithms, such as Fourier transform, to process time series data or perform feature extraction.

[0050] The step c is specifically as follows:

[0051] User needs analysis: Analyze user feedback through natural language processing techniques, such as sentiment analysis, to better understand user needs;

[0052] Dynamic data service construction: Use rule engines, such as Drools, to dynamically adjust data services based on user needs.

[0053] The step d is specifically as follows:

[0054] Access control policy: Implement attribute-based access control to provide more fine-grained access control.

[0055] Data desensitization: Apply data desensitization algorithms such as Pseudonymization to sensitive data.

[0056] The step e is specifically as follows:

[0057] Performance monitoring and optimization: Use application performance management tools, such as New Relic, to monitor application performance and optimize database queries;

[0058] Feedback loop: Establish a feedback system based on machine learning, such as using reinforcement learning algorithms to automatically adjust data services based on user feedback;

[0059] Reinforcement learning update = α·reward + β·penalty, where α and β are learning rates that adjust the speed at which the algorithm learns based on rewards and penalties.

[0060] In this step, the performance of the ADM layer table structure is regularly evaluated and optimized, such as adjusting indexes and optimizing query statements.

[0061] According to business development needs, the data model is continuously adjusted and improved, such as adding new business topics, expanding data attributes, etc.

[0062] Introduce new technologies and tools, such as big data processing technology, machine learning algorithms, etc., to improve the efficiency and accuracy of data processing and analysis.

[0063] 1. General ledger subject indicator design table structure

[0064] Top level categories:

[0065] General Ledger

[0066] Second level classification:

[0067] Subcategories under the general ledger include: amount category, quantity category, and organization.

[0068] Amount Class: This class may be used to store amount information related to the general ledger.

[0069] Quantity Class: This class may be used to store quantity information related to the general ledger.

[0070] Organization: This category may be used to store organization or department information related to the general ledger.

[0071] Internal ledger (as a parallel or related category to the general ledger, but also considered a section or sub-topic of the general ledger in this design):

[0072] The subcategories of the internal ledger also include: amount, quantity, and organization. These subcategories have similar uses to the subcategories of the general ledger, but may be used specifically for internal accounting or management purposes.

[0073] The third level of classification (subject level):

[0074] Under the three second-level categories of amount, quantity and organization, there is a common subcategory: subject.

[0075] Account: This category may be used to store specific accounting account information. Whether it is information under the amount, quantity or institution category, it needs to be subdivided and recorded through accounts.

[0076] Table structure diagram:

[0077] This table structure can be viewed as a hierarchical relationship table, where each level has a corresponding field to store relevant information. For example:

[0078] Top level (Ledger): May have a field to identify the ledger topic.

[0079] Second level (amount category, quantity category, organization): There may be a field to identify these subcategories.

[0080] The third level (account): There may be a field to identify the specific accounting account.

[0081] 2. Deposit theme classification design

[0082] (1) Summary of deposit themes

[0083] Table name: Deposit subject table

[0084] Fields:

[0085] Deposit ID (primary key): uniquely identifies each deposit type.

[0086] Deposit name: such as "personal demand deposit", "corporate time deposit", etc.

[0087] Deposit type: such as "individual" and "enterprise", used to distinguish the deposit owner. Deposit term: such as "current" and "fixed deposit", used to distinguish the term type of the deposit.

[0088] (2) Summary of amount table

[0089] Table name: Deposit summary amount table

[0090] Fields:

[0091] Summary ID (primary key): uniquely identifies each summary record.

[0092] Deposit ID (foreign key): Associated to the deposit ID in the deposit subject summary table, indicating which deposit type this summary record belongs to.

[0093] Total Amount: The total amount of this deposit type.

[0094] Summarization time: The time point at which the amount is summarized.

[0095] (3) Summary Quantity Table

[0096] Table name: Deposit summary quantity table

[0097] Fields:

[0098] Quantity ID (primary key): uniquely identifies each quantity summary record.

[0099] Deposit ID (foreign key): Associated to the deposit ID in the deposit subject summary table, indicating which deposit type this quantity summary record belongs to.

[0100] Total quantity: The total quantity of this deposit type (such as the number of accounts, the number of customers, etc., depending on business needs).

[0101] Summarization time: The time point at which the quantity is summarized.

[0102] (4) Analyze the data table

[0103] Table name: Deposit analysis data table

[0104] Fields:

[0105] Analysis ID (primary key): uniquely identifies each analysis record.

[0106] Deposit ID (foreign key): Associated to the deposit ID in the deposit subject table, indicating which type of deposit data this analysis record is based on.

[0107] Analytical indicators: such as "average deposit balance", "deposit growth rate", etc., are used to measure the development of deposit business.

[0108] Analysis time: The period or point in time during which the data analysis is performed.

[0109] Analysis results: Results calculated based on analysis indicators.

[0110] Logical relationship description The deposit subject summary table is the core table, which defines the basic information of all deposit types.

[0111] The Summary Amount Class Table and Summary Quantity Class Table are derived tables that summarize amounts and count quantities based on the deposit types in the Deposit Subject Summary Table. These two tables are associated with the Deposit Subject Summary Table through the deposit ID.

[0112] The analysis data table is also a derived table that performs analysis and calculation based on the data in the deposit subject table. This table is associated with the deposit subject table through the deposit ID and contains different analysis indicators and results based on business needs.

[0113] In addition, in actual business scenarios, other related tables may need to be designed to support the complete implementation of the deposit subject classification design. For example:

[0114] Account table: used to store customer account information, including account ID, customer ID, deposit ID (indicating which deposit type the account belongs to), account balance and other fields.

[0115] Customer table: used to store basic customer information, such as customer ID, name, contact information, etc. Institution table: used to store information about bank branches, such as institution ID, institution name, region, etc. These institutions may be associated with the deposit types in the deposit subject master table, indicating which deposit types are provided by which institutions.

[0116] Product table: If the bank regards deposits as a financial product, it may be necessary to design a product table to store detailed information about the product, such as product ID, product name, product type (such as deposits, loans, etc.), product description, etc. These products may be associated with the deposit types in the deposit subject master table, indicating which deposit types belong to which products.

[0117] 3. (1) Summary table of loan themes

[0118] Table name: Loan subject table

[0119] Fields:

[0120] Loan ID (primary key): uniquely identifies each loan type.

[0121] Loan name: such as "personal housing loan", "corporate working capital loan", etc. Loan type: such as "personal", "corporate", etc., used to distinguish the entity to which the loan belongs.

[0122] (2) Personal loan related tables

[0123] Customer table:

[0124] Customer ID (primary key): uniquely identifies each customer.

[0125] Customer information such as name, contact information, ID number, etc.

[0126] Calculation table:

[0127] Accounting ID (primary key): uniquely identifies each accounting record.

[0128] Loan ID (foreign key): Associated to the loan ID in the loan subject master table.

[0129] Accounting information such as accounting amount and accounting date.

[0130] Credit form:

[0131] Credit ID (primary key): uniquely identifies each credit record.

[0132] Loan ID (foreign key): Associated to the loan ID in the loan subject master table.

[0133] Credit information such as credit limit and credit validity period.

[0134] Application Form:

[0135] Application ID (primary key): uniquely identifies each application record.

[0136] Customer ID (foreign key): relates to the customer ID in the Customers table.

[0137] Loan ID (foreign key): Associated to the loan ID in the loan subject master table.

[0138] Application information such as application date and application amount.

[0139] Contract Form:

[0140] Contract ID (primary key): uniquely identifies each contract.

[0141] Application ID (foreign key): associated with the application ID in the application table.

[0142] Contract information such as contract terms and signing date.

[0143] Expenditure table:

[0144] Expenditures ID (primary key): uniquely identifies each expenditure record.

[0145] Contract ID (foreign key): Associated to the contract ID in the contract table.

[0146] Expenditure information such as expenditure amount and expenditure date.

[0147] Guarantee Form:

[0148] Guarantee ID (primary key): uniquely identifies each guarantee record.

[0149] Contract ID (foreign key): Associated to the contract ID in the contract table.

[0150] Guarantor information, guarantee amount and other guarantee information.

[0151] Management table:

[0152] Management ID (primary key): uniquely identifies each management record.

[0153] Loan ID (foreign key): associated with the loan ID in the loan subject master table. Management information such as management status and management date.

[0154] (3) Enterprise loan related tables

[0155] Amount table:

[0156] Amount ID (primary key): uniquely identifies each amount record.

[0157] Loan ID (foreign key): related to the loan ID in the loan subject master table. Loan amount, interest amount and other amount information.

[0158] Quantity table:

[0159] Quantity ID (primary key): uniquely identifies each quantity record.

[0160] Loan ID (foreign key): Associated to the loan ID in the loan subject master table.

[0161] Quantitative information such as number of loans and number of customers.

[0162] Analyze the data table:

[0163] Analysis ID (primary key): uniquely identifies each analysis record.

[0164] Loan ID (foreign key): Associated to the loan ID in the loan subject master table.

[0165] Analytical data such as analytical indicators and analytical results.

[0166] (4) Other related tables

[0167] Account table:

[0168] Account ID (primary key): uniquely identifies each account.

[0169] Customer ID (foreign key): relates to the customer ID in the Customers table.

[0170] Account information such as account balance and account status.

[0171] Organization table:

[0172] Institution ID (primary key): uniquely identifies each institution.

[0173] Institutional information such as institution name and institution type.

[0174] Product Table:

[0175] Product ID (primary key): uniquely identifies each product.

[0176] Product information such as product name and product type.

[0177] Logical relationship description The loan subject summary table is the core table, which defines the basic information of all loan types.

[0178] The personal loan related table and the enterprise loan related table are derived tables that further subdivide and record details based on the loan type in the loan subject master table. These tables are associated with the loan subject master table through the loan ID.

[0179] The customer table, account table, institution table, and product table are auxiliary tables that provide other information related to the loan business, such as customer information, account information, institution information, and product information. These tables are associated with other tables through fields such as customer ID, account ID, institution ID, and product ID.

[0180] In the entire loan subject classification design, each table is connected to each other through foreign key relationships, forming a complete and logically clear data structure. This design facilitates data storage, query and analysis, and improves the efficiency and accuracy of data management.

[0181] 4. Channel theme classification design

[0182] Table structure description

[0183] Channel Table

[0184] This is the core table of the entire channel theme classification design, which is used to record the bank's various channel information.

[0185] Table fields may include: channel ID (primary key), channel name, channel type, creation time, update time, etc.

[0186] Individual Customer Table (Individual_Customer)

[0187] Used to record individual customer information and associated with the channel table.

[0188] Table fields may include: customer ID (primary key), name, gender, age, contact information, channel ID (foreign key, pointing to the channel table), etc.

[0189] Enterprise Customer Table (Enterprise_Customer)

[0190] Used to record information about corporate customers and is also associated with the channel table.

[0191] Table fields may include: enterprise ID (primary key), enterprise name, registered address, legal representative, channel ID (foreign key, pointing to channel table), etc.

[0192] Amount Category Table (Amount_Category)

[0193] Used to record information categories related to amounts.

[0194] Table fields may include: Category ID (primary key), Category name, description, creation time, etc. Quantity Category Table (Quantity_Category)

[0195] As a subcategory of the amount table, it is used to record quantity-related information classification.

[0196] Table fields may include: quantity ID (primary key), quantity name, unit, description, etc. Analysis Data Table (Analysis_Data)

[0197] As another subcategory of the amount table, it is used to record data that needs to be analyzed.

[0198] Table fields may include: data ID (primary key), data name, data type, data source, creation time, etc.

[0199] Logical relationship description

[0200] The relationship between channels and individual and corporate customers

[0201] Each individual customer or corporate customer is associated with the channel table through the channel ID, indicating the channel through which these customers access the bank.

[0202] Relationship between amount and quantity and analytical data

[0203] There are two subcategories under the Amount table: Quantity and Analytical Data. There may be some association between these subcategory tables and the Amount table (such as through classification ID), but the specific relationship needs to be determined based on the actual business logic.

[0204] The quantity table may record quantity information related to the amount, such as the number of transactions, the number of customers, etc.

[0205] The analysis data table may record the raw data that needs to be analyzed, which may be associated with certain categories in the amount table.

[0206] In addition to the relationships explicitly described above, there may be other potential relationships. For example, the customer table (which can be a combination or abstraction of the personal customer table and the corporate customer table) may have an association relationship with the product table, organization table, etc., indicating the products purchased by the customer or the organization to which the customer belongs. These potential relationships need to be further defined and implemented based on actual business needs and database design principles.

[0207] 5. Classification design of intermediary business themes

[0208] Table structure description:

[0209] Summary of intermediary business topics

[0210] Table name: Intermediate business table

[0211] Fields:

[0212] Intermediary business ID (primary key): uniquely identifies each intermediary business.

[0213] Name of intermediary business: such as "foreign exchange trading", "domestic settlement", etc.

[0214] Foreign exchange trading related tables:

[0215] It may include multiple sub-tables to record specific business data of foreign exchange trading, such as transaction records, exchange rate information, etc.

[0216] The sub-table fields may include: transaction ID, transaction time, buy currency, sell currency, buy amount, sell amount, exchange rate, etc.

[0217] Domestic settlement related tables:

[0218] Used to record data on domestic settlement business, such as transfer records, settlement account information, etc.

[0219] The sub-table fields may include: settlement ID, settlement time, payer account, payee account, settlement amount, etc.

[0220] International settlement related tables:

[0221] Used to record data of international settlement business, such as cross-border remittance records, foreign exchange conversion information, etc. The subtable fields may include: international settlement ID, settlement time, payer information, payee information, currency, settlement amount, handling fee, etc.

[0222] Securities fund related tables:

[0223] Used to record securities fund business data, such as fund transaction records, fund net value information, etc. The subtable fields may include: fund transaction ID, transaction time, fund code, transaction amount, fund net value, etc.

[0224] Amount table:

[0225] Used to record information related to amounts, such as total income and total expenditure of intermediary business.

[0226] Fields may include: amount ID, business type (associated with the intermediate business table), amount value, time, etc.

[0227] Quantity table:

[0228] Used to record quantity-related information, such as the number of transactions, the number of customers, etc.

[0229] Fields may include: quantity ID, business type (associated with the intermediate business table), quantity value, time, etc.

[0230] Analyze the data table:

[0231] Used to store analytical data of intermediate business, such as business growth rate, customer retention rate, etc.

[0232] The fields may include: analysis data ID, analysis index, analysis time, analysis result, etc. In addition, there may be other auxiliary tables related to intermediate business, such as:

[0233] Account table: records customer account information.

[0234] Customer table: records the basic information of customers.

[0235] Institution table: records the information of banks or financial institutions.

[0236] Product table: records the product information provided by the bank, such as financial products, fund products, etc. Channel table: records the channels through which customers handle intermediary business.

[0237] The logical relationship description of the intermediate business subject summary table is the core table, and other related tables are developed around it.

[0238] The foreign exchange trading related tables, domestic settlement related tables, international settlement related tables and securities and fund related tables record different types of intermediary business data respectively, and are associated with the intermediary business subject master table through the business type field.

[0239] The amount table and quantity table record information related to amount and quantity respectively, and are associated with the intermediary business subject summary table or other related tables through the business type field.

[0240] The analysis data table performs business analysis based on data from other tables and stores the analysis results. The account table, customer table, organization table, product table, and channel table are auxiliary tables that provide other information related to intermediary business and are associated with other tables through fields such as customer ID, account ID, and organization ID.

[0241] Customer theme classification design

[0242] Table structure description

[0243] In this customer topic classification design, we can imagine the following tables to record different types of information:

[0244] Customer Summary

[0245] Table name: Customer table fields: Customer ID (primary key), Customer type (such as individual high-end, individual low-end, corporate customer, etc.), other customer information (such as name, contact information, address, etc.)

[0246] Amount table

[0247] Table name: Amount record table fields: Amount ID (primary key), customer ID (foreign key, associated customer table), transaction amount, transaction time, transaction type, etc.

[0248] Quantity Class Table

[0249] Table name: Quantity record table fields: Quantity ID (primary key), Customer ID (foreign key, associated with customer table), transaction quantity, transaction time, transaction type, etc.

[0250] Analyze the data table

[0251] Table name: Customer analysis data table fields: analysis data ID (primary key), customer ID (foreign key, associated customer table), analysis indicators (such as consumption frequency, average transaction amount, etc.), analysis time, etc.

[0252] Organization table (if there is independent organization information that needs to be recorded)

[0253] Table name: Institution information table fields: Institution ID (primary key), institution name, institution type, contact information, address and other logical relationship descriptions

[0254] The relationship between the customer summary table and other tables:

[0255] The customer summary table is the core table of the entire customer subject classification design, and other tables are associated with it through the customer ID field.

[0256] The amount table, quantity table and analytical data table all reference customers in the customer master table through the customer ID field to record transaction or behavior data of these customers in different aspects.

[0257] The relationship between the amount table and the quantity table:

[0258] These two tables may be logically independent of each other, recording the customer's transaction amount and transaction quantity respectively.

[0259] However, they may be associated in some cases, for example, the amount and quantity of the same transaction can be associated through a transaction ID.

[0260] Analyze the relationship between the data table and other tables:

[0261] The analysis data table conducts further analysis and statistics based on the data in the customer summary table, amount table and quantity table.

[0262] It is associated with specific customers through the customer ID field and generates various analytical indicators based on the transaction data of these customers.

[0263] Relationship of the organization table (if any):

[0264] If the organization table exists, it may have some association with the customer master table, for example, recording which customers belong to which organization.

[0265] However, the specific relationship between the organization table and other tables is not explicitly mentioned.

[0266] Bank card theme classification design

[0267] Table structure description

[0268] In this bank card theme classification design, we can imagine the following core tables to record different types of information:

[0269] Bank Card Summary

[0270] Table name: Bank card table Fields: Bank card ID (primary key), bank card type (such as debit card, credit card, wealth management card, etc.), card number, institution ID (foreign key, associated institution table), product ID (foreign key, associated product table), etc.

[0271] Amount table

[0272] Table name: Bank card amount record table Fields: Amount record ID (primary key), bank card ID (foreign key, associated bank card table), transaction amount, transaction time, transaction type, etc.

[0273] Quantity Class Table

[0274] Table name: Bank card quantity record table fields: quantity record ID (primary key), bank card ID (foreign key, associated bank card table), transaction quantity, transaction time, transaction type, etc.

[0275] Analyze the data table

[0276] Table name: Bank card analysis data table fields: analysis data ID (primary key), bank card ID (foreign key, associated bank card table), analysis indicators (such as transaction frequency, average transaction amount, etc.), analysis time, etc.

[0277] Organization table

[0278] Table name: Institution information table fields: Institution ID (primary key), institution name, institution type, contact information, etc.

[0279] Product Sheet

[0280] Table name: Product information table Fields: Product ID (primary key), product name, product type, product description, etc. Line table (if line information needs to be recorded separately)

[0281] Table name: Line information table

[0282] Fields: Line ID (primary key), Line name, Line description, etc. In addition, there may be other auxiliary tables related to bank card transactions, such as ATM transaction record table, POS transaction record table, merchant information table, etc. These tables can record more detailed transaction information and merchant information.

[0283] Logical relationship description

[0284] The relationship between the bank card summary table and other tables:

[0285] The bank card summary table is the core table of the entire bank card subject classification design, and other tables are associated with it through the bank card ID field.

[0286] The amount table, quantity table and analysis data table all reference the bank cards in the bank card master table through the bank card ID field to record the transaction or behavior data of these bank cards in different aspects. The relationship between the amount table and the quantity table is as follows:

[0287] These two tables may be logically independent of each other, recording the transaction amount and transaction quantity of bank cards respectively.

[0288] However, they may be associated in some cases, for example, the amount and quantity of the same transaction can be associated through a transaction ID.

[0289] Analyze the relationship between the data table and other tables:

[0290] The analysis data table conducts further analysis and statistics based on the data in the bank card summary table, amount table and quantity table.

[0291] It is associated with specific bank cards through the bank card ID field and generates various analysis indicators based on the transaction data of these bank cards.

[0292] The relationship between the organization table, product table and line table:

[0293] These tables are associated with the bank card master table and are used to record the institution, product, and line information to which the bank card belongs.

[0294] The institution ID, product ID and line ID (if any) are used as foreign keys to associate with corresponding records in the bank card master table.

[0295] Relationships with other auxiliary tables:

[0296] ATM transaction record sheets, POS transaction record sheets, etc. may record the transaction information of bank cards at ATM, POS and other channels.

[0297] The merchant information table may record merchant information related to bank card transactions, such as merchant name, merchant type, merchant address, etc.

[0298] These tables are linked to the amount table, quantity table or bank card summary table through fields such as transaction ID or merchant ID.

[0299] like Figure 1 As shown, the present invention also relates to a system based on the method for constructing the bank data warehouse ADM layer table structure, comprising:

[0300] Standardized data model design module 1 is used to classify data according to business topics and design a standardized data model containing entities, attributes and their relationships for each business topic; Data integration and processing module 2 is used to obtain basic data from the DWS layer and integrate and process it according to business needs;

[0301] Personalized service design module 3, used to design personalized data services according to the needs of different business departments and users;

[0302] Data security management module 4, used to strengthen the control of data access rights and ensure data security and privacy;

[0303] The continuous optimization and improvement module 5 is used to regularly evaluate and optimize the performance of the ADM layer table structure.

[0304] The above description is only a preferred specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can make equivalent replacements or changes according to the technical scheme and inventive concept of the present invention within the technical scope disclosed by the present invention, which should be covered by the protection scope of the present invention.

Claims

1. A method for constructing the ADM layer table structure of a bank data warehouse, characterized in that: The following steps are involved: a. Standardized data model design; b. Data integration and processing; c. Carry out personalized service design; d. Data security management; e.Continuous optimization and improvement.

2. The method for constructing the ADM layer table structure of a bank data warehouse according to claim 1 is characterized in that: The step a is specifically: Business subject classification: Use feature-based clustering algorithms to classify business data. Where J is the clustering cost function, k is the number of clusters, Si is the i-th cluster set, and μi is the center point of the i-th cluster; Entity-relationship model building: Apply ER model building tools to define entities and relationships and generate ER diagrams; Normalization: Use functional dependencies and multi-valued dependencies to determine the degree of normalization of the table and ensure that the data model meets at least the third normal form.

3. The method for constructing the ADM layer table structure of a bank data warehouse according to claim 1 is characterized in that: The step b is specifically: ETL process design: Design ETL templates and use automated data extraction, transformation and loading; Data cleaning: Introduce the statistical anomaly detection algorithm Grubbs'test to identify and process outliers. Where G is the Grubbs'test statistic, is the sample mean, is the population mean, s is the sample standard deviation; Data transformation: Use data transformation algorithms to process time series data or perform feature extraction.

4. The method for constructing the ADM layer table structure of a bank data warehouse according to claim 1 is characterized in that: The step c is specifically as follows: User demand analysis: Analyze user feedback through natural language processing technology to better understand user needs; Dynamic data service construction: Use the rule engine to dynamically adjust data services based on user needs.

5. The method for constructing the ADM layer table structure of a bank data warehouse according to claim 1 is characterized in that: The step d is specifically as follows: Access control strategy: implement attribute-based access control; Data desensitization: Apply data desensitization algorithms to sensitive data to protect personal privacy; 6. The method for constructing the ADM layer table structure of a bank data warehouse according to claim 1 is characterized in that: The step e is specifically as follows: Performance monitoring and optimization: Use application performance management tools to monitor application performance and optimize database queries; Feedback loop: Establish a feedback system based on machine learning to automatically adjust data services based on user feedback; Reinforcement learning update = α·reward + β·penalty, where α and β are learning rates that adjust the speed at which the algorithm learns based on rewards and penalties.

7. A system based on the method for constructing the bank data warehouse ADM layer table structure according to any one of claims 1 to 6, characterized in that: include: A standardized data model design module is used to classify data according to business topics and design a standardized data model containing entities, attributes and their relationships for each business topic; The data integration and processing module is used to obtain basic data from the DWS layer and integrate and process it according to business needs; Personalized service design module, used to design personalized data services according to the needs of different business departments and users; Data security management module, used to strengthen the control of data access rights and ensure data security and privacy; The continuous optimization and improvement module is used to regularly evaluate and optimize the performance of the ADM layer table structure.

Images