Database Calculation Engine Star Schema Transformation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Complex business warehouse queries consume significant resources due to numerous database queries, leading to high CPU and memory usage and prolonged query times, especially when processing large data sets.
Innovation Solution
A calculation engine optimizes business warehouse queries by transforming calculation nodes into a star schema, executing operations efficiently, and minimizing unnecessary data processing through on-the-fly instantiation and use of a join with aggregation operation, which reduces intermediate result materialization and optimizes data flow.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If complex calculations are performed using traditional engines on the application server, then calculation flexibility is maintained, but CPU and memory consumption increases significantly and query times are prolonged
Solution Approach 1:
The patent introduces a database server as an intermediary between the application server and the data source. The database server executes calculation scenarios directly, transforming complex calculation operations into optimized database queries. This intermediary approach eliminates the need for the application server to perform resource-intensive calculations, thereby reducing CPU and memory consumption while maintaining calculation flexibility through the database server's calculation engine.
Solution Approach 2:
The patent segments the calculation workload by dividing it into calculation scenarios that can be executed independently on the database server. Each calculation scenario represents a discrete unit of computation that can be optimized and executed separately, allowing the system to process complex calculations efficiently without overwhelming the application server's resources.
2Reliability
If numerous queries are generated to handle complex calculations, then calculation completeness is achieved, but data transport overhead and processing time increase
Solution Approach 1:
The patent merges multiple individual queries into a single calculation scenario that is executed as one unified operation on the database server. By combining multiple data retrieval and calculation operations into a single optimized query, the system achieves the same calculation completeness while dramatically reducing the overhead associated with multiple separate queries, including data transport overhead and query processing time.
3Reliability
If all paths and attributes from the calculation scenario are processed, then result completeness is ensured, but unnecessary data processing increases resource consumption
Solution Approach 1:
The patent extracts and processes only the necessary paths and attributes from the calculation scenario that are required to answer the specific query. The system analyzes the query requirements and selectively processes only the relevant data elements, eliminating unnecessary data processing. This approach ensures result completeness by including all necessary data while reducing resource consumption by excluding irrelevant paths and attributes from the calculation execution.
Data Source
AI summary
A query is received by a database server from a remote application server. The query is associated with a calculation scenario that defines a data flow model that includes one or more calculation nodes with each calculation node defining one or more operations to execute on the database server. Thereafter, at least one of the calculation nodes is transformed into a star schema. Next, the database server instantiates the calculation scenario with the transformed at least one calculation node. Subsequently, a calculation engine of the database server executes the operations defined by the calculation nodes of the instantiated calculation scenario other than the transformed at least one calculation node and, additionally, an OLAP engine executes the transformed at least one calculation node to collectively result in a responsive data set.