Overview and Example

The problem addressed is illustrated by the following example:

• The data warehouse (DW) contains a large multi-billion transaction fact and five major dimensions
• Cubing Services accesses seven relational data marts (DMs) built from a subset of the DW data. Private dimensions are introduced, specific to each data mart, to support the cube models. These data marts are identical in structure and functionality, but defined over separate subsets of the fact after being transformed through views, one for each data mart.
• Three MQTs are defined over each of the data marts to enhance performance of queries issued from Cubing Services
• Two additional MQTs are defined directly over the DW fact to support reporting off the DW fact
• A daily process is kicked-off overnight, following the DW fact maintenance window, that maintains the DM facts, the DM MQTs and the DW MQTs. The daily process is incremental and ensures that all derived structures are consistent with their based-on views.
• The DMs and MQTs can also be refreshed fully using full refresh scripts on an as needed basis. Utility functions are also provided to do reorganize, activate/deactivate MQTs and check the status of the DMs and MQTs
• Two copies of each data mart are maintained, so that while one is maintained the other one is accessed by Cubing Services. At the end of the maintenance cycle, the cubing service is briefly stopped, aliases are switched to point to the updated DMs, and the cubing service is restarted to refresh the cache.
• Changes to any of the structures involved in the DW, DMs or MQTs cause changes in DDL and SQL scripts
• Indexing, and MQT levels are occasionally adjusted, requiring consistent code changes across all scripts affected

The VDMGEN framework uses metadata stored in manifest files and templates to generate the following:

• DDL for all DM and MQT definition views and tables that create or recreate all structures
• SQL script libraries to fully refresh DM and MQTs
• SQL script libraries to incrementally refresh DM and MQTs
• SQL script libraries to perform housekeeping tasks, e.g. reorganize DMs, activate and deactivate MQTs

Top-level ksh93 scripts drive necessary maintenance tasks, and they are provided as part of the initial integration and customization services. Such scripts may include

• Building/rebuilding all objects associated with DMs and MQTs
• Full or incremental refresh of all DM tables and MQTs
• Switching of aliases
• Stopping/restarting cubing service remotely using ssh
• Coordinating and if necessary restarting maintenance processes
• Retaining dimension versions,
• Recasting
• Administrative utilities, such as check DM status, validitate informational constraints and others

These scripts utilize the VDMGEN libraries and can be tailored to the environment. They can be executed by the scheduler or at will. Additional scripts for each DM family provide status, check the validity of informational constraints, switch user access and refresh cubing services cache. The VDM aggregation methodology guides the tasks of modeling, population of manifests and customization of templates. The solution includes customization services and extensive knowledge transfer.