Staging Tables Reduce Database Calls in ERP Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional approaches to processing data in Enterprise Resource Planning (ERP) systems, particularly when dealing with large volumes of records from multiple tables, face performance issues due to excessive computing and networking overhead, leading to inefficient data retrieval and transfer between database and logic servers.
Innovation Solution
The method involves grouping data on the database server for unified batch transfer to the logic server, reducing the number of database calls by staging core and related data into predefined formats, allowing for optimized data transfer through fewer operations, leveraging database capabilities for efficient data manipulation and collation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional approaches are used to process data from multiple tables in ERP systems, then data can be retrieved from various sources, but performance deteriorates due to excessive computing and networking overhead
Solution Approach 1:
The patent applies preliminary action by creating materialized views that pre-compute and store aggregated data from multiple ERP tables before it is needed. These materialized views contain pre-calculated financial metrics, project data, and other business intelligence information, eliminating the need to perform complex joins and aggregations at query time. This pre-computation approach directly resolves the contradiction by maintaining reliable data retrieval from multiple sources while dramatically improving processing performance through reduced computing overhead.
Solution Approach 2:
The patent introduces materialized views as intermediary structures between the underlying ERP database tables and the application layer. These materialized views act as mediators that pre-process and organize data from multiple tables into optimized formats for specific business queries. This intermediary layer resolves the contradiction by enabling reliable access to integrated data from multiple sources while improving performance through pre-computed aggregations and reduced networking overhead between database and application servers.
2Adaptability or versatility
If data from hundreds of thousands of records across dozens of tables is processed using traditional methods, then comprehensive data views can be obtained, but the system fails to perform adequately due to excessive operations
Solution Approach 1:
The patent applies segmentation by dividing the monolithic data retrieval process into separate materialized views for different business domains (e.g., project financials, resource allocation, time tracking). Each materialized view is optimized for specific query patterns and stakeholder needs, allowing flexible data views without requiring processing of all hundreds of thousands of records from all tables. This segmentation resolves the contradiction by maintaining adaptability for different stakeholder perspectives while dramatically improving efficiency by processing only relevant segmented data sets.
Solution Approach 2:
The patent implements local quality by creating materialized views with data and aggregations specifically tailored to different stakeholder groups and business functions. Each materialized view contains locally optimized data structures and pre-computed metrics relevant to specific roles (project managers, executives, financial analysts), rather than a generic comprehensive view of all data. This resolves the contradiction by providing adaptable local views for different users while improving overall processing efficiency through specialized optimization.
3Loss of information
If multiple database calls are made to retrieve core data and related data from different tables, then complete information can be gathered, but the number of operations increases leading to poor performance
Solution Approach 1:
The patent applies merging by combining data from multiple ERP tables (project tables, financial tables, resource tables, time tables) into unified materialized views that contain integrated information. These materialized views consolidate what would otherwise require multiple separate database calls into single pre-computed structures. This resolves the contradiction by maintaining complete information from all relevant sources while dramatically reducing data retrieval time by eliminating multiple sequential database operations.
Data Source
AI summary
Disclosed are methods and apparatuses for reducing the number of database calls when transferring data from a database to a server. One embodiment provides an approach for utilizing staging tables to collate and transform core data and related data in the database before transferring the data to the server, so that the number of database calls used may be reduced.