Formula Script Conversion Platform for Cloud Analytics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing systems lack a user-friendly and high-performance method for generating complex business logic scripts to retrieve business information from databases, requiring knowledge of SQL, MDX, and MDS, which many users do not possess, and are inflexible in handling various business calculations.
Innovation Solution
An advanced formulas planning script conversion platform that allows users to generate scripts through a user interface, stores them, and automatically converts them into structured query language (SQL) procedures for execution on an analytic data cube, enabling flexible calculations with different points of view.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If users directly write SQL, MDX, or MDS queries to retrieve business information, then query performance and precision are improved, but user complexity and difficulty increase significantly
Solution Approach 1:
The patent introduces an intermediary layer (formula planning script conversion platform) between users and the database. Users write high-level formulas in a simplified language, which the platform automatically translates into optimized SQL, MDX, or MDS queries. This mediator handles the complexity of query generation while users work with intuitive business logic expressions.
Solution Approach 2:
The system creates and maintains multiple representations of the same query logic: a user-friendly formula version and an optimized execution version (SQL/MDX/MDS). The conversion platform acts as a copy mechanism that transforms the simplified formula into the appropriate query language while preserving the intended logic and optimizing for performance.
2Adaptability or versatility
If users write complex business logic scripts manually, then calculation flexibility is improved, but development time and complexity increase
Solution Approach 1:
The conversion platform provides self-service capabilities by automatically analyzing user formulas and generating the appropriate complex business logic scripts. The system serves itself by handling the translation and optimization processes without requiring users to manually write complex scripts, thus maintaining flexibility while reducing complexity.
Solution Approach 2:
The system changes parameters between different representation layers: formulas use high-level business parameters, while the generated scripts use detailed technical parameters. The conversion platform manages these parameter transformations, allowing users to work with simple parameters while the system handles complex parameter relationships in the generated scripts.
3Ease of operation
If existing systems use automated query generation, then ease of use is improved, but query performance and handling of complicated business logic deteriorate
Solution Approach 1:
The system performs preliminary actions by pre-compiling and optimizing query templates and execution plans during the formula-to-script conversion process. The conversion platform prepares optimized query structures in advance, so when formulas are executed, they benefit from pre-planned optimization strategies rather than being generated ad-hoc at runtime.
Solution Approach 2:
The system implements feedback mechanisms where execution results and performance metrics are analyzed to improve future query generation. The conversion platform learns from actual query performance and adjusts its translation strategies, creating a feedback loop that continuously improves both ease of use and query performance based on real-world usage patterns.
Data Source
AI summary
Some embodiments are associated with an analytics cloud environment. A user interface may facilitate generation of an advanced formulas planning script by a user. The advanced formulas planning script may be stored, for example, in a planning script data store. An analytic data cube may contain a multidimensional dataset usable for analysis via queries. A conversion platform may receive the advanced formulas planning script and automatically create a structured query language stored procedure based on the advanced formulas planning script. The conversion platform may then execute the structured query language stored procedure on the analytic data cube to calculate a result comprising a base cell and at least one cell has a different point of view associated with the analytic data cube as compared to a calculation source. The calculated result man then be provided to the user.