Business Intelligence Server Automates Data Mart Creation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The manual creation of aggregate tables in business intelligence servers is tedious, error-prone, and inefficient, especially as the number of aggregate tables increases, making it difficult to leverage their performance-enhancing capabilities effectively.
Innovation Solution
A business intelligence server provides a virtual semantic model that automates the creation and maintenance of data marts across various data sources, including relational and multidimensional databases, by allowing users to specify levels and measures for aggregation, which are then used to generate and store multidimensional cubes, thereby simplifying the process and improving query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If aggregate tables are manually created to improve query performance, then query response time is reduced, but the complexity and time required to create and maintain these tables increases
Solution Approach 1:
The system performs preliminary actions by automatically generating aggregate table creation scripts and metadata mappings before queries are executed. The business intelligence server proactively creates and maintains aggregate tables based on query patterns, eliminating the need for manual pre-computation setup by users.
Solution Approach 2:
The system enables self-service by allowing the business intelligence server to autonomously create, maintain, and optimize aggregate tables without requiring manual intervention. The server automatically generates DDL/DML scripts, maps metadata, and manages aggregate tables based on query analysis, making the system self-sufficient in performance optimization tasks.
2Speed
If more aggregate tables are created to improve query performance, then data access speed increases, but the difficulty of managing and utilizing these aggregates increases
Solution Approach 1:
The system implements feedback mechanisms by monitoring query patterns and performance metrics to automatically determine which aggregate tables should be created or modified. The business intelligence server continuously analyzes query workloads and adjusts the aggregate table structure accordingly, eliminating the need for manual management decisions.
Solution Approach 2:
The system achieves universality by creating a unified metadata layer that manages multiple aggregate tables across different data sources through a single interface. The business intelligence server provides a universal mechanism for creating and managing aggregates that works across relational databases, multidimensional databases, and other data sources, simplifying operations.
3Productivity
If automated scripts are used to create aggregate tables, then the creation process is accelerated, but the initial setup complexity increases
Solution Approach 1:
The system replaces manual mechanical processes with automated computational processes. Instead of users manually writing DDL/DML scripts and mapping metadata, the business intelligence server automatically generates these artifacts through computational analysis of query patterns and data structures, eliminating the need for users to understand complex scripting languages.
Solution Approach 2:
The system introduces an intermediary layer (the business intelligence server) that mediates between the user's query requirements and the database infrastructure. This intermediary automatically translates high-level query patterns into optimized aggregate table structures, shielding users from the complexity of script generation and metadata mapping while maintaining high productivity.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Systems and methods are described for automating the creation of data marts within an enterprise. Data is maintained in a plurality of data sources that include at least a relational database and a multidimensional database. The system includes a business intelligence server that provides a virtual logical semantic model to integrate all of the plurality of data sources. The user specifies a list of levels and measures on the virtual logical semantic model. The list of levels and measures can span data from multiple data sources. The user can also specify a location in the plurality of data sources that will store the aggregate matrix. Once the list of levels and measures are specified, the business intelligence server generates a multidimensional cube to store the data for the aggregate matrix and stores the multidimensional cube in the data source location.