Schema Design Trade-offs in Healthcare Analytics a Comparative Study of Star and Snowflake Models for Performance and Scalability
Abstract
With the recent acceleration of the digitalization process in healthcare services across the globe, organizations are dealing with large volumes of Clinical and Operational Data that must be analyzed and transformed into knowledge to support decision-making. Data Warehouses (DW) are the data management solutions that combine the online analysis of large amount of heterogeneous data with flexible query capabilities, integrated into Enterprise-Wide Decision Support System (EWDSS). Data Warehouse development is a critical task due to the particular structure of these DW. The two most commonly accepted approaches for building Data Warehouse Schemas are the Star Schema, where one or multiple Fact Tables are independent from each other, and the Snowflake Schema where the hierarchy embedded into the Dimension Tables is fully normalized. These two approaches differ in terms of response time, performance under concurrent access by the users rush hour and data volume, the cost of joins and aggregations, impact on Maintenance and Evolution of the Data Warehouse and. A thorough discussion of the advantages and disadvantages of the two approaches is presented, with a focus on healthcare analytics and an experimental assessment based on a novel Dynamic CDW Testbed. The trend towards the parallelization of database query execution through the implementation of Data Warehouse-as-a-Service is increasing the convergence of the two approaches and introducing new design factors into the decision.
Article Information
Journal |
International Journal of Advanced Engineering Science and Information Technology (IJAESIT) |
|---|---|
Volume (Issue) |
Vol. 5 No. 5 (2022): International Journal of Advanced Engineering Science and Information Technology (IJAESIT) |
DOI |
|
Pages |
9999-10011 |
Published |
October 7, 2022 |
| Copyright | |
Open Access |
This work is licensed under a Creative Commons Attribution 4.0 International License. |
How to Cite |
Ghatoth Mishra (2022). Schema Design Trade-offs in Healthcare Analytics a Comparative Study of Star and Snowflake Models for Performance and Scalability. International Journal of Advanced Engineering Science and Information Technology (IJAESIT) , Vol. 5 No. 5 (2022): International Journal of Advanced Engineering Science and Information Technology (IJAESIT) , pp. 9999-10011. https://doi.org/10.15662/IJAESIT.2022.0505002 |
References
2. Peddi, R. K. (2021). Optimizing Case Management Workflows in Global Data Center Colocation Services. Universal Journal of Computer Sciences and Communications, 1(1), 1-21.
3. Nunes, P., Santos, J., & Rocha, E. (2022). Challenges in predictive maintenance – A review. CIRP Journal of Manufacturing Science and Technology, 40, 53–67.
4. Segireddy, A. R. (2020). Cloud Migration Strategies for High-Volume Financial Messaging Systems.
5. Carvalho, T. P., Soares, F. A. A. M. N., Vita, R., Francisco, R. P., Basto, J. P., & Alcalá, S. G. (2022). A systematic literature review of machine learning methods applied to predictive maintenance. Computers & Industrial Engineering, 137, 106024.
6. Ahmed, M., Khan, S., & Gupta, R. (2023). Intelligent predictive maintenance framework using industrial IoT and edge analytics. Sensors, 24(5), 1728.
7. Davuluri, P. N. (2022). Cloud-Native Data Platform Modernization for Regulatory Compliance in Global Banking.
8. Zhang, W., Yang, D., & Wang, H. (2022). Data-driven methods for predictive maintenance of industrial equipment: A survey. IEEE Systems Journal, 16(2), 2398–2410.
9. Zhao, R., Yan, R., Chen, Z., Mao, K., Wang, P., & Gao, R. X. (2022). Deep learning and its applications to machine health monitoring. Mechanical Systems and Signal Processing, 115, 213–237.
10. Mangalampalli, B. M. (2022). Automated Invoice Validation Systems Using Advanced SQL Analytics in Healthcare Insurance. Front Health Inform, 11.
11. Lei, Y., Li, N., Guo, L., Li, N., Yan, T., & Lin, J. (2022). Machinery health prognostics: A systematic review from data acquisition to remaining useful life prediction. Mechanical Systems and Signal Processing, 104, 799–834.
12. Susto, G. A., Schirru, A., Pampuri, S., McLoone, S., & Beghi, A. (2022). Machine learning for predictive maintenance: A multiple classifier approach. IEEE Transactions on Industrial Informatics, 11(3), 812–820.
13. Mangala, N. (2021). Optimizing Large-Scale ETL Pipelines Using Medallion Architecture on Azure Data Lake. Journal of Artificial Intelligence and Big Data, 1(1), 1-20.
14. Khan, S., Yairi, T., & Ueno, M. (2022). Deep learning-based prognostics and health management: State of the art and challenges. Sensors, 22(7), 2517.
15. Loganathan, R. (2021). Integrated Risk and Compliance Frameworks for Global Data Center Operations: A Governance-Centric Approach. Universal Journal of Computer Sciences and Communications, 1(1), 1-26.
16. Baptista, M., Sankararaman, S., de Medeiros, I. P., Nascimento, C., Prendinger, H., & Henriques, E. M. P. (2022). Forecasting fault events for predictive maintenance using data-driven techniques and ARMA modeling. Computers & Industrial Engineering, 115, 41–53.
17. Zhang, T., Liu, Q., Chen, Y., & Zhou, X. (2023). Deep learning-based fault diagnosis and predictive maintenance for industrial cyber-physical systems. Computers in Industry, 148, 103895.
18. Inala, R. Advancing Group Insurance Solutions Through Ai-Enhanced Technology Architectures And Big Data Insights.
19. Wen, L., Li, X., & Gao, L. (2022). A new convolutional neural network-based data-driven fault diagnosis method. IEEE Transactions on Industrial Electronics, 65(7), 5990–5998.
20. Gottimukkala, V. R. R. (2020). Energy-Efficient Design Patterns for Large-Scale Banking Applications Deployed on AWS Cloud. power, 9(12).
21. Pan, E., Li, X., Mei, J., & Wang, H. (2022). Industrial Internet of Things-enabled predictive maintenance: A comprehensive review. Journal of Manufacturing Systems, 68, 112–130.
22. Javed, K., Gouriveau, R., & Zerhouni, N. (2022). State of the art and taxonomy of prognostics approaches, trends of prognostics applications and open issues towards maturity at different technology readiness levels. Mechanical Systems and Signal Processing, 94, 214–236.
23. Reddy, V. A. R. (2021). Challenges in Standardizing Member Eligibility Data Across Multi-Payer Healthcare Ecosystems. International Journal of Medical Toxicology and Legal Medicine, 24(3), 1-19.
24. Bousdekis, A., Apostolou, D., & Mentzas, G. (2022). Predictive maintenance in the Industry 4.0 era: A systematic literature review. International Journal of Production Research, 60(15), 4696–4724.
25. Luo, H., Wang, D., & Sun, Y. (2023). Vision-based defect detection and predictive maintenance using deep convolutional neural networks. Expert Systems with Applications, 221, 119744.
26. Wang, K., Wang, Y., Sun, Y., Guo, S., & Wu, J. (2022). Green industrial Internet of Things architecture: An energy-efficient perspective. IEEE Communications Magazine, 54(12), 48–54.
27. Yandamuri, U. S. (2021). A Comparative Study of Traditional Reporting Systems versus Real-Time Analytics Dashboards in Enterprise Operations. Universal Journal of Business and Management.
28. Javaid, M., Haleem, A., Singh, R. P., Khan, S., & Suman, R. (2022). Industrial Internet of Things (IIoT) applications for smart manufacturing. Materials Today: Proceedings, 49, 585–600.
29. Zhao, L., Huang, J., & Li, X. (2023). Remaining useful life prediction using deep learning and edge computing in industrial assets. Reliability Engineering & System Safety, 236, 109278.
30. Tao, F., Qi, Q., Wang, L., & Nee, A. Y. C. (2022). Digital twins and cyber–physical systems toward smart manufacturing and Industry 4.0. Engineering, 5(4), 653–661.
31. Kumar, A., Singh, R., & Sharma, P. (2023). Edge AI-enabled condition monitoring and predictive maintenance for smart factories. Journal of Manufacturing Systems, 68, 134–148.
32. Rahman, M. M., Hasan, M. K., & Islam, S. (2023). AI-powered predictive maintenance for smart factories: A systematic review. IEEE Access, 12, 61542–61567.
33. Kolla, S. H. (2022). Knowledge Retrieval Systems for Enterprise Service Environments. International Journal of Intelligent Systems and Applications in Engineering, 10, 495-506.
34. Liu, Y., Yang, C., Jiang, L., Xie, S., & Zhang, Y. (2022). Intelligent edge computing for IoT-based predictive maintenance. Future Generation Computer Systems, 132, 211–224.
35. Yang, C., Xu, H., & Wu, J. (2023). Digital twin-driven predictive maintenance and asset resilience in smart manufacturing. Robotics and Computer-Integrated Manufacturing, 82, 102534.
36. Wang, K., Yang, Y., Ren, J., & Zhang, L. (2023). Federated learning enabled predictive maintenance for industrial IoT systems. IEEE Transactions on Industrial Informatics, 19(8), 8471–8482.
37. Alshammari, F., Alotaibi, B., & Alghamdi, M. (2023). Edge computing and machine learning integration for industrial asset health management. Future Generation Computer Systems, 152, 101–114.
38. Wan, J., Tang, S., Shu, Z., Li, D., Wang, S., Imran, M., & Vasilakos, A. V. (2022). Software-defined industrial Internet of Things in the context of Industry 4.0. IEEE Sensors Journal, 16(20), 7373–7380.
39. Ferreira, P., Oliveira, M., & Santos, T. (2023). Asset resilience through AI-enabled monitoring and predictive analytics in Industry 4.0. Sustainability, 15(18), 13722.
40. Mangalampalli, B. M. (2021). Scalable Data Warehouse Architecture for Population Health Management and Predictive Analytics. World Journal of Clinical Medicine Research, 1(1), 1-18.
41. Chen, X., Zhang, Y., Wang, H., & Li, J. (2023). Edge intelligence for predictive maintenance in smart manufacturing systems. IEEE Internet of Things Journal, 10(14), 12188–12203.
42. Lu, Y. (2022). Industry 4.0: A survey on technologies, applications and open research issues. Journal of Industrial Information Integration, 6, 1–10.
43. Wu, Z., Chen, M., & Li, P. (2023). Vision transformers for predictive maintenance and anomaly detection in manufacturing environments. Engineering Applications of Artificial Intelligence, 128, 107533.
44. Reddy, V. A. R. (2022). Designing Fault-Tolerant Data Ingestion Pipelines for High-Volume Healthcare Transactions. Frontiers in Health Informatics, 11, 861-889.
45. Aly, M., Rahouma, K. H., & Ramzy, K. (2022). Predictive maintenance using machine learning algorithms in industrial IoT environments. Sensors, 23(8), 3891.
46. Mangala, N. (2021). CI/CD Pipeline Automation for Enterprise Data Artifacts Using Azure DevOps. Universal Journal of Business and Management, 1(1), 1-18.
47. Yan, J., Meng, Y., Lu, L., & Li, L. (2022). Industrial big data in an Industry 4.0 environment: Challenges, schemes, and applications for predictive maintenance. IEEE Access, 10, 8212–8228.
48. Kolla, S. K. (2021). Designing Scalable Healthcare Data Pipelines for Multi-Hospital Networks. World Journal of Clinical Medicine Research, 1(1), 1-14.
49. Peng, Y., Dong, M., & Zuo, M. J. (2022). Current status of machine prognostics in condition-based maintenance: A review. International Journal of Advanced Manufacturing Technology, 50(1–4), 297–313.
50. Bala, A., Jusoh, A. R. Z., Ismail, I., Oliva, D., Muhammad, N., Sait, S. M., Al-Utaibi, K. A., Amosa, T. I., & Memon, K. A. (2023). Artificial intelligence and edge computing for machine maintenance: A review. Artificial Intelligence Review, 57(5), 119.
51. Civerchia, F., Bocchino, S., Salvadori, C., Rossi, E., Maggiani, L., & Petracca, M. (2023). Industrial IoT monitoring and predictive maintenance architecture for resilient manufacturing ecosystems. Sensors, 23(14), 6318.
52. Hamasha, M. M., Albedoor, Q., Hamasha, S., Ali, H., Qamar, A., & Berrah, F. (2023). A comprehensive framework for IoT-driven predictive maintenance: Leveraging AI and edge computing for enhanced equipment reliability. Journal of Applied Engineering Science, 23(3), 471–486.
53. Tang, C., Liu, F., & Zhang, Y. (2023). Vision transformer-based defect inspection for intelligent manufacturing systems. Engineering Applications of Artificial Intelligence, 124, 106582.
54. Li, X., Ding, Q., & Sun, J. Q. (2022). Remaining useful life estimation in prognostics using deep learning approaches: A review. Reliability Engineering & System Safety, 172, 1–15.
55. Rajesh Mattaparthi (2021). Unified Data Lineage and Quality Governance Framework for Multi-Source Sensor Streams in Heavy-Duty Powertrain Manufacturing. Online Journal of Mechanical Engineering, 1(1), 1-15. https://doi.org/10.31586/ojme.2021.1365
56. Bousdekis, A., Apostolou, D., Mentzas, G., & Stojanovic, N. (2023). Predictive maintenance in the era of Industry 4.0: State of the art and future directions. Computers in Industry, 146, 103849.
57. Tidriri, K., Chatti, N. Y., Verron, S., & Tiplica, T. (2022). Bridging data-driven and model-based approaches for process fault diagnosis and health monitoring. Control Engineering Practice, 92, 104098.
58. Borgia, E., Conti, M., & Dargahi, T. (2023). Secure edge computing for industrial Internet of Things environments. Future Internet, 15(6), 205.
59. Djenouri, Y., Srivastava, G., Lin, J. C. W., & Chatterjee, P. (2023). Edge intelligence for industrial IoT: Architectures, applications, and research challenges. IEEE Internet of Things Magazine, 6(2), 40–46.
60. Ahmad, R., & Kamaruddin, S. (2022). An overview of time-based and condition-based maintenance in industrial application. Computers & Industrial Engineering, 63(1), 135–149.
61. Kolla, S. H. INTELLIGENT SYSTEMS! ND! PPLIC! TIONS IN ENGINEERING.
62. Chhetri, S. R., Faezi, S., Canedo, A., Al Faruque, M. A., & Wan, J. (2023). IoT and edge computing for asset monitoring and predictive analytics in smart industries. IEEE Transactions on Industrial Informatics, 19(9), 10082–10094.
63. Li, Y., Wang, X., Ding, K., & Feng, S. (2023). Explainable artificial intelligence for smart manufacturing systems: A review. Robotics and Computer-Integrated Manufacturing, 84, 102582.
64. Inala, R. Designing Scalable Technology Architectures for Customer Data in Group Insurance and Investment Platforms.
65. Wang, H., Ma, S., Zhao, X., & Zhang, J. (2022). Edge intelligence-enabled predictive maintenance for industrial cyber-physical systems. IEEE Internet of Things Journal, 10(9), 7741–7755.
66. Ucar, A., Karakose, M., & Kırımça, N. (2023). Artificial intelligence for predictive maintenance applications: Key components, trustworthiness, and future trends. Applied Sciences, 14(2), 898.
67. Li, C., Sánchez, R. V., Zurita, G., Cerrada, M., & Cabrera, D. (2022). Multimodal deep support vector classification with homologous features and its application to gearbox fault diagnosis. Neurocomputing, 168, 119–127.
68. Mistry, M., Gupta, N., & Patel, R. (2023). Deep learning-enabled anomaly detection in industrial cyber-physical systems. Expert Systems with Applications, 223, 119905.
69. Liu, R., Yang, B., Zio, E., & Chen, X. (2022). Artificial intelligence for fault diagnosis of rotating machinery: A review. Mechanical Systems and Signal Processing, 108, 33–47.
70. Tao, F., Zhang, H., Liu, A., & Nee, A. Y. C. (2022). Digital twin in Industry 4.0: State-of-the-art and future trends. Robotics and Computer-Integrated Manufacturing, 51, 1–15.