Search ETDs:
Migrating an Operational Database Schema to Data Warehouse Schemas
PHIPPS, CASSANDRA J

2002, MS, University of Cincinnati, Engineering : Computer Engineering.
The popularity of data warehouses for analysis of data has grown tremendously, but much of the creation of data warehouses is currently done manually. Although the initial design process is labor-intensive and expensive, research towards automating data warehouse creation has been limited. We propose and illustrate algorithms for automatic schema development. Our first algorithm uses a conceptual enterprise schema of an operational database as a starting point for source-driven data warehouse schema design. Candidate conceptual data warehouse schemas are created in ME/R model form. We extend the ME/R modeling notation to note where additional user input can be used to further refine a schema. Our second algorithm follows a user-driven requirements approach that utilizes queries to guide selection of candidate schemas most likely to meet user needs. We propose a guideline of manual steps to refine the conceptual schemas to suit additional user needs, for example, the level of detail needed for date fields. The selected and possibly refined schemas are now ready to be transformed into logical schemas. The third algorithm creates logical schemas in Star model notation from the conceptual schemas in ME/R notation. The logical model provides a basis for physical modeling and the data warehouse implementation. Our algorithms provide a foundation for an automated software tool to create and evaluate data warehouse schemas. The algorithms are illustrated using the TPC-H Benchmark schema and queries. The popularity of data warehouses for analysis of data has grown tremendously, but much of the creation of data warehouses is currently done manually. Although the initial design process is labor-intensive and expensive, research towards automating data warehouse creation has been limited. We propose and illustrate algorithms for automatic schema development. Our first algorithm uses a conceptual enterprise schema of an operational database as a starting point for source-driven data warehouse schema design. Candidate conceptual data warehouse schemas are created in ME/R model form. We extend the ME/R modeling notation to note where additional user input can be used to further refine a schema. Our second algorithm follows a user-driven requirements approach that utilizes queries to guide selection of candidate schemas most likely to meet user needs. We propose a guideline of manual steps to refine the conceptual schemas to suit additional user needs, for example, the level of detail needed for date fields. The selected and possibly refined schemas are now ready to be transformed into logical schemas. The third algorithm creates logical schemas in Star model notation from the conceptual schemas in ME/R notation. The logical model provides a basis for physical modeling and the data warehouse implementation. Our algorithms provide a foundation for an automated software tool to create and evaluate data warehouse schemas. The algorithms are illustrated using the TPC-H Benchmark schema and queries.
Dr. Karen Davis (Advisor)
156 p.

Recommended Citations

Hide/Show APA Citation

PHIPPS, C. (2002). Migrating an Operational Database Schema to Data Warehouse Schemas. (Electronic Thesis or Dissertation). Retrieved from https://etd.ohiolink.edu/

Hide/Show MLA Citation

PHIPPS, CASSANDRA. "Migrating an Operational Database Schema to Data Warehouse Schemas." Electronic Thesis or Dissertation. University of Cincinnati, 2002. OhioLINK Electronic Theses and Dissertations Center. 11 Dec 2017.

Hide/Show Chicago Citation

PHIPPS, CASSANDRA "Migrating an Operational Database Schema to Data Warehouse Schemas." Electronic Thesis or Dissertation. University of Cincinnati, 2002. https://etd.ohiolink.edu/

Files

ucin1019667418.pdf (1.55 MB) View|Download