BACKGROUND: National Heritage Insurance Company (NHIC), a subsidiary of EDS, is a fiscal agent for the Texas Department of Health (TDH). NHIC processes Medicaid claims for TDH. The mission of Vision21 (the data warehousing/claims analysis project) is to improve the quality of life for Texans by providing TDH and NHIC the "Medicaid analysis" to make tactical healthcare and budgetary decisions.

PLATFORM: Vision21 runs on Tandem S70000 Servers employing NonStop SQL DBMS. The Windows 95 user interface is provided through MicroStrategy's DSS Suite. The data warehouse dimensional model/schema is architected by Anubis Constructa, Version 2.11, running on Windows NT.

PROBLEM SOLVED: The Vision21 data warehouse currently consists of 155 dimension tables, 29 fact tables, almost 1,200 attributes and more than a half terabyte of data at the conclusion of phase one. A major challenge was to integrate a data warehouse design tool that provided an intuitive way to build a large multidimensional model quickly and could manage the model during development and maintenance phases. In cases requiring the creation of different DSS objects, an impact analysis became imperative as the data model was modified. Additionally, Vision21 needed a dimensional modeling tool that could seamlessly export to MicroStrategy's DSS Architect, create DDL for the target platform and quickly generate data marts from an enterprise-wide data repository. There was no tool in the marketplace that we tested that could satisfy these conditions except Anubis Constructa.

STRENGTHS: Constructa is the first tool that actually references design components in dimensional modeling terms. It is a powerful environment for reinforcing dimensional modeling theory. When working with standard E/R diagrams, the user has to mentally conceptualize the dimensional model, which can be challenging for the unseasoned data warehouse architect. Constructa naturally references dimensions, facts, attributes, "snowflakes," lookup tables, etc. Visual representation of dimensional and physical models helps the user see the immediate impact of changes. Constructa is capable of generating DDL for Tandem NonStop SQL, DB2, Oracle, Informix, SQL Server, Sybase, Teradata and Microsoft Access. At the click of a button, Constructa allows the designer to modify various features of the logical model. Users can try snowflake or star schema representations, eight different DBMS syntaxes, data type specs and make name changes to model components. No additional work is required from the DBAs; the tool propagates changes across all model levels. Changes can be applied quickly enabling users to review and compare different representations of the model before proceeding with the physical design.

WEAKNESSES: Our phase one Constructa model consisted of eight primary subject areas, more than 100 dimensions or fact groups and several hundred more attributes and facts. A significant problem was the slow response time involved in maintaining the "master model." Another problem was the tool's inability to handle multiple concurrent updates by more than one designer. For this reason, modeling efforts were split across different subject areas and then merged into a master model. Because of the autonomy of the modeling efforts, we discovered flaws in the overall design of one or more individual sub-models. Since these discoveries were made, Anubis has made a number of enhancements and upgrades to Constructa that have addressed these issues.

SELECTION CRITERIA: Vision21 required a data warehouse design tool that rapidly builds the logical data model, provides a target specific DDL, creates and synchronizes "pruned" data models with an enterprise-wide data warehouse, provides impact analysis on DSS objects that have already been created, enforces dimensional modeling and exports the model to other data modeling tools including COOLGen/Composer.

DELIVERABLES: Constructa provided a logical data model that could be exported to DSS Architect, a physical model that could be exported to the Tandem platform, an export to the COOLGen/Composer encyclopedia and documentation of the properties of each attribute in the enterprise-wide data warehouse.

VENDOR SUPPORT: In relation to other tools used in the implementation, vendor support has been extraordinary. Anubis worked with NHIC/EDS to provide frequent product enhancements as well as telephone/on-site consulting to ensure timely implementation.

DOCUMENTATION: Constructa offers extensive documentation and on-line help at every level to assist in expediting logical modeling efforts. Adding a feature to pictorially represent the entire enterprise-wide data warehouse could further enhance documentation.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access