An Automatic Tool to Transform Star Schema Data Warehouse to Physical Data Model

Abstract

Data warehouse is used to store very large data for supporting company to perform data analysis. Star schema is data warehouse model most widely used by companies today. Sometimes, data stored in star schema need to be exported to conventional model so that others may use them without knowing the OLTP (Online Transaction Processing) or source model, particularly for backup and recovery case. Therefore, this research aimed to transform star schema data model to physical data model. Two cases have been identified case, which are: 1) the star schema with simple star schema and the multifact star schema (standard case); and 2) the multi star schema (nonstandard case). There are five processes to build the physical model from the star schema model, namely: 1) finding fact table, 2)finding dimension table, 3) deleting time dimension table, and adding date attribute to fact table, 4) changing fact table to relational table, and 5) changing dimension table to relational table. The prototype was built to implement this phase, and it was tested using some cases. The prototype transformed star schema to physical data model properly (complete design with table, attribute, relation, data type). Some results were different (were not consistent) from the source model because there are many possibilities of star schema for one model, and there is no metadata that are stored when the star schema model was built.