Universe Migration Engine for Database Compatibility
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Migrating Business Intelligence applications from one database to another is a complex task due to differences in Structured Query Language (SQL) support, case sensitivity of tables and views, and the need to re-map database-specific parameters, as well as the absence of all tables in the new database, which complicates universe compatibility and data migration.
Innovation Solution
A method for universe migration that involves selecting a source universe, examining its components, generating scripts to create corresponding tables in the target database, checking table integrity, creating a new data foundation, and adapting reports to point to the new universe, using a migration engine to handle database function mapping, table creation, and security management.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If manual migration methods are used to migrate universes from one database to another, then compatibility and data integrity can be ensured through careful mapping, but the time and effort required for migration increases significantly
Solution Approach 1:
The system performs preliminary actions by automatically examining the source universe structure, identifying all objects and their dependencies, and pre-planning the migration steps before actual data transfer begins. This includes generating the migration script in advance based on the analyzed universe metadata.
Solution Approach 2:
The migration system is self-service in that it automatically examines the source database, generates migration scripts, executes the migration, and validates the results without requiring manual intervention. The system serves itself by autonomously completing the entire migration workflow from start to finish.
2Quantity of substance
If comprehensive universe migration is performed to ensure all objects are migrated, then completeness is achieved, but the complexity of handling database-specific parameters and SQL variations increases
Solution Approach 1:
The migration system is designed to be universal by supporting multiple database types and SQL variations through a single automated platform. It handles different database-specific parameters and SQL dialects uniformly by automatically adapting the migration process to the target database's requirements.
Solution Approach 2:
The system introduces an intermediary migration script that acts as a mediator between the source and target databases. This script serves as an intermediate layer that handles the translation and adaptation of database-specific parameters and SQL queries, simplifying the overall migration process.
3Productivity
If automated migration scripts are generated to reduce manual effort, then migration speed increases, but the risk of errors in script generation and execution increases
Solution Approach 1:
The system implements feedback mechanisms by validating the generated migration scripts against the source universe structure and checking the execution results. It compares the migrated objects with the original objects to ensure accuracy and provides feedback for corrective actions if discrepancies are detected.
Solution Approach 2:
The system provides beforehand cushioning by performing validation checks on the generated migration scripts before execution and preparing rollback mechanisms in advance. This prevents errors from propagating and allows recovery if issues occur during script execution.
4Quantity of substance
If all tables and objects are examined and migrated, then migration completeness is ensured, but the resources and time required for examination and script generation increase
Solution Approach 1:
The system segments the migration process into distinct phases: examining the source universe structure, generating the migration script, executing the script, and validating the results. This segmentation allows for efficient processing of large universes by breaking down the examination and migration tasks into manageable segments.
Solution Approach 2:
The system performs preliminary examination of the source universe to generate a comprehensive migration plan before actual data migration begins. This preliminary action includes identifying all objects, their relationships, and required transformations, which accelerates the subsequent migration execution.
Data Source
AI summary
A semantic layer (universe), which is created on a source database (DB), is migrated to a target DB. The migration includes pre-migration steps, actual migration and post-migration steps. The pre-migration steps prepare the target DB for the actual migration by configuring the target DB and determining the differences between the source DB and the target DB. During the actual migration, data, tables and views are migrated to the target DB conforming to the target database structure, functions and configuration. A new universe is created on the target database and all consumers of the source universe such as created reports are changed to refer to the new universe.