Designing a Star Schema for Optimizing the Total Sales of Motorcycles
DOI:
https://doi.org/10.35134/jcsitech.v7i4.23Keywords:
motorcycle sales, big data, data warehouse, star schema, fact table, dimension tableAbstract
Motorcycle sales have increased significantly, motorcycle manufacturers are competing to produce the latest models which are then sold to consumers. As a result, motorcycle dealers are overwhelmed with more and more data, not knowing what to do with it. Motorcycle dealers also have difficulty calculating the total sales of motorcycles. We try to provide solutions to deal with data overflow. We propose designing a star schema as the basis for creating a data warehouse. To create a star schema, we propose a four-step sequence in creating an effective star schema, starting from requirements analysis and reporting, understanding business processes, connecting and matching business processes with suitable entities and determining the dimensions of the business processes. We get a star schema with 1 fact table, motorcycle_sales and 11 of dimension tables, such as brand, color, customer, customer_contract, distributor, district, motorcycle, repair_workshop, sell_location, type and time. The star schema is an optimized model that provides the best performance in presenting more complex information
References
A. D. Indriyanti, D. R. Prehanto, I. G. L. P. E. Prismana, and I. K. D. Nuryana, “The web-based estimation of motorcycles sales using linear regression method,” IOP Conf. Ser. Mater. Sci. Eng., vol. 1098, no. 5, p. 052115, 2021, doi: 10.1088/1757-899x/1098/5/052115.
R. Fujinuma and Y. Asahi, “Proposal of Credit Risk Model Using Machine Learning in Motorcycle Sales,” in Human Interface and the Management of Information. Information-Rich and Intelligent Environments, S. Yamamoto and H. Mori, Eds. Springer, Cham, 2021, pp. 353–363.
Windarto and Eridani, “Mathematical modelling of motorcycle sale competition in Indonesia,” Commun. Math. Biol. Neurosci., vol. 2021, pp. 1–10, 2021, doi: 10.28919/cmbn/5309.
W. Sutopo, I. Kurniyati, and R. Zakaria, “Markov Chain and Techno-Economic Analysis to Identify the Commercial Potential of New Technology: A Case Study of Motorcycle in Surakarta, Indonesia,” Technologies, vol. 6, no. 3, p. 73, 2018, doi: 10.3390/technologies6030073.
J. L. Monino, “Data Value, Big Data Analytics, and Decision-Making,” J. Knowl. Econ., vol. 12, no. 1, pp. 256–267, 2021, doi: 10.1007/s13132-016-0396-2.
K. Biesialska, X. Franch, and V. Muntés-Mulero, “Big Data analytics in Agile software development: A systematic mapping study,” Inf. Softw. Technol., vol. 132, p. 106448, 2021, doi: 10.1016/j.infsof.2020.106448.
H. S. H. Abdel Azez, M. H. Khafagy, and F. A. Omara, “Optimizing Join in HIVE Star Schema Using Key/Facts Indexing,” IETE Tech. Rev. (Institution Electron. Telecommun. Eng. India), vol. 35, no. 2, pp. 132–144, 2018, doi: 10.1080/02564602.2016.1260498.
K. S. Ranti, D. Tuapattinaya, C. Chang, and A. S. Girsang, “Data warehouse for analysing music sales on a digital media store,” J. Phys. Conf. Ser., vol. 1477, no. 3, 2020, doi: 10.1088/1742-6596/1477/3/032013.
P. Ghosh, D. Sadhu, and S. Sen, “A real-time business analysis framework using virtual data warehouse,” Int. Arab J. Inf. Technol., vol. 18, no. 4, pp. 585–595, 2021, doi: 10.34028/18/4/11.
N. Sanprasit, K. Jampachaisri, T. Titijaroonroj, and K. Kesorn, “Intelligent approach to automated star-schema construction using a knowledge base,” Expert Syst. Appl., vol. 182, no. December 2019, p. 115226, 2021, doi: 10.1016/j.eswa.2021.115226.
W. Jianmin, Z. Wenbin, F. Tongrang, Y. Shilong, and L. Hongwei, “An improved join-free snowflake schema for ETL and OLAP of data warehouse,” Concurr. Comput. Pract. Exp., vol. 32, no. 23, pp. 1–11, 2020, doi: 10.1002/cpe.5519.
T. M. J. Al Taleb, S. Hasan, and Y. Y. Mahd, “On-line analytical processing (OLAP) operation for outpatient healthcare,” Iraqi J. Sci., vol. 2021, pp. 225–231, 2021, doi: 10.24996/ijs.2021.SI.1.32.
E. P. W. Mandala, D. E. Putri, D. W. T. Putra, and D. P. Mulya, “OLAP Approach in Searching Manufacturing Industries in West Sumatera,” J. Phys. Conf. Ser., vol. 1339, no. 1, 2019, doi: 10.1088/1742-6596/1339/1/012052.
M. Bouakkaz, Y. Ouinten, S. Loudcher, and Y. Strekalova, “Textual aggregation approaches in OLAP context: A survey,” Int. J. Inf. Manage., vol. 37, no. 6, pp. 684–692, 2017, doi: 10.1016/j.ijinfomgt.2017.06.005.
S. Chakraborty and J. Doshi, Faster Result Retrieval from Health Care Product Sales Data Warehouse Using Materialized Queries, vol. 1176. Springer Singapore, 2021.
M. Francia, E. Gallinucci, and M. Golfarelli, “Conversational OLAP in action,” Adv. Database Technol. - EDBT, vol. 2021-March, pp. 646–649, 2021, doi: 10.5441/002/edbt.2021.74.
S. Banerjee, S. Bhaskar, A. Sarkar, and N. C. Debnath, “A unified conceptual model for data warehouses,” Ann. Emerg. Technol. Comput., vol. 5, no. Special issue 5, pp. 162–169, 2021, doi: 10.33166/AETiC.2021.05.020.
N. Sanprasit, T. Titijaroonroj, and K. Kesorn, “A semantic approach to automated design and construction of star schemas,” Eng. Appl. Sci. Res., vol. 48, no. 5, pp. 518–528, 2021, doi: 10.14456/easr.2021.54.
M. R. Effendy, T. F. Kusumasari, and M. A. Hasibuan, “Star Schema Implementation For Monitoring in Data Quality Management Tool (A Case Study at A Government Agency),” Proc. 2019 4th Int. Conf. Informatics Comput. ICIC 2019, 2019, doi: 10.1109/ICIC47613.2019.8985695.
R. J. Salaki, J. Waworuntu, and I. R. H. T. Tangkawarow, “Extract transformation loading from OLTP to OLAP data using pentaho data integration,” IOP Conf. Ser. Mater. Sci. Eng., vol. 128, no. 1, 2016, doi: 10.1088/1757-899X/128/1/012020.
Downloads
Published
How to Cite
Issue
Section
License
Copyright (c) 2021 Journal of Computer Science and Information Technology
This work is licensed under a Creative Commons Attribution 4.0 International License.
JCSITech has launched a new template since Vol. 11, No. 2 (2025).
