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

Authors

  • Humasak Simanjuntak Institut Teknologi Del, Faculty of Electrical Engineering and Informatics, Information System Department, Sitoluama, Indonesia.
  • Ardo Nainggolan Institut Teknologi Del, Faculty of Electrical Engineering and Informatics, Information System Department, Sitoluama, Indonesia.
  • Dameria Simatupang Institut Teknologi Del, Faculty of Electrical Engineering and Informatics, Information System Department, Sitoluama, Indonesia.
  • Delia De Venty Manurung Institut Teknologi Del, Faculty of Electrical Engineering and Informatics, Information System Department, Sitoluama, Indonesia.

Keywords:

Data Warehouse, Star Schema, Fact Table, Relational Table.

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.

References

Inmon, W.H. Building the Data Warehouse. 3rd ed. 2008. New York: Wiley Publishing Inc.

Chauduri S, Dayal U. 1997. An Overview of Data Warehousing and OLAP Technology. Microsoft Research. ACM SIGMOD Record, New York: 65-74.

Bebel B, Eder J, Koncilia C, etc. 2004. Creation and Management of Versions in Multiversion Data warehouses. SAC '04 Proceedings of the 2004 ACM symposium on applied computing. New York: 717-723.

Rainardi Vincent. 2008. Building a Data warehouse: With Examples in SQL Server 2008. New York: Apress.

Ballard Chuck, Herreman D, Schau D, Bell R. 1998. Data Modeling Techniques for Data Warehousing. California: IBM Corp.

Krippendorf, M. and Song Il-Yeol. 1997. The Translation of Star Schema into Entity-Relationship Diagrams. DEXA '97 Proceedings of the 8th International Workshop on Database and Expert Systems Applications. 390.

Simanjuntak Humasak, Pangaribuan Andreas, Nababan Daniel, Sihotang Rina. 2012. Transformator Entity Relationship Model to Star Schema. National Proceeding in National Conference ICT-M Politeknik Telkom. ISSN: 2302-1896.

Lumbantoruan Rosni, Sibarani Elisa, Sitorus Monica, Mindari Ayunisa, Sinaga Suhendrowan. 2014. An Approach for Automatically Generating Star Schema from Natural Language. TELKOMNIKA Journal. 12(2): 501-510.

Yen-Ting Chen, Ping-Yu Hsu. 2005. An Efficient and Grain Preservation Mapping Algorithm: from ER Diagram to Multidimensional Model. Proceedings of the 5th international conference on Advanced Distributed Systems. Springer-Verlag Berlin:

-346.

Golfarelli, M. Maio, D. Rizzi, S. 1998. Conceptual Design of Data Warehouses from E/R Schema. Proceedings of the Thirty-First Annual Hawaii International Conference on System Sciences. IEEE Computer Society Washington. 7: 334.

Song IY, Khare R, Dai B. 2007. SAMSTAR: A Semi-Automated Lexical Method for Generating Star Schemas from an EntityRelationship Diagram. 10th ACM Int’l Workshop on Data Warehousing and OLAP (DOLAP 2007). ACM New York: 9-16.

http://www.birst.com/product/technology/data-warehouseautomation. Automatic star schema generation, BIRST.

Il Yeol Song. Ritu Khare. Bing Dai. 2007. SAMSTAR: A SemiAutomated Lexical Method for Generating Star Schemas from an Entity-Relationship Diagram. DOLAP '07 Proceedings of the ACM tenth international workshop on Data warehousing and OLAP. ACM New York: 9-16.

Luca Cabibbo . Riccardo Torlone. 1998. A Logical Approach to Multidimensional Databases. EDBT '98 Proceedings of the 6th International Conference on Extending Database Technology: Advances in Database Technology. Springer-Verlag London: 183-197.

James Dullea. Il-Yeol Song. Ioanna Lamprou. 2003. An analysis of structural validity in entity-relationship modeling. Journal Data & Knowledge Engineering. 47(2): 167 – 205.

Muller Hausi, Jahnke Jens. 2000. Reverse Engineering: A Roadmap. Proceedings of the Conference on the Future of Software Engineering. ACM New York: 47-60.

Downloads

Published

2017-06-01

How to Cite

Simanjuntak, H., Nainggolan, A., Simatupang, D., & De Venty Manurung, D. (2017). An Automatic Tool to Transform Star Schema Data Warehouse to Physical Data Model. Journal of Telecommunication, Electronic and Computer Engineering (JTEC), 9(2-3), 55–59. Retrieved from https://jtec.utem.edu.my/jtec/article/view/2273

Issue

Vol. 9 No. 2-3: Circuit and System Advancement in Modern World II

Section

Articles

License

TRANSFER OF COPYRIGHT AGREEMENT

The manuscript is herewith submitted for publication in the Journal of Telecommunication, Electronic and Computer Engineering (JTEC). It has not been published before, and it is not under consideration for publication in any other journals. It contains no material that is scandalous, obscene, libelous or otherwise contrary to law. When the manuscript is accepted for publication, I, as the author, hereby agree to transfer to JTEC, all rights including those pertaining to electronic forms and transmissions, under existing copyright laws, except for the following, which the author(s) specifically retain(s):

  1. All proprietary right other than copyright, such as patent rights
  2. The right to make further copies of all or part of the published article for my use in classroom teaching
  3. The right to reuse all or part of this manuscript in a compilation of my own works or in a textbook of which I am the author; and
  4. The right to make copies of the published work for internal distribution within the institution that employs me

I agree that copies made under these circumstances will continue to carry the copyright notice that appears in the original published work. I agree to inform my co-authors, if any, of the above terms. I certify that I have obtained written permission for the use of text, tables, and/or illustrations from any copyrighted source(s), and I agree to supply such written permission(s) to JTEC upon request.