Transforming Public Healthcare Supply Chains: A Framework to Measure Efficiency of Heterogeneous Public Healthcare Supply Chains across Nation for Improving Drug Availability
Subject Areas : Decision support systemsAbhishek Verma 1 , Rekha Agarwal 2 , Jitendra Singh 3
1 -
2 -
3 -
Keywords: Public Health Informatics, Indian Health Framework, Deduplication, Digital Health, Drugs and Vaccines Availability, Essential Drugs and Vaccines, Extraction Transformation and loading (ETL), Public Health, Supply Chain, Warehouse,
Abstract :
Health in Indian scenario is a state subject which means that states usually use their independent respective IT systems based on their specific needs and requirements. This brings a big challenge in terms of diversified nomenclatures used in across Indian states and Union Territories (UTs). Centralized data analysis is required by central agencies like Ministry of Health and Family Welfare (MoHFW) and the National Health Systems Resource Centre (NHSRC) for performance monitoring and policy making. This diversified nomenclature poses a significant hindrance in the process. The aggregation, standardization, deduplication, and visualization of data from these heterogeneous sources is both complex and resource intensive.This paper presents solution for the above challenges and propose a comprehensive framework for analyzing data from heterogeneous sources related to supply chain of drugs and vaccines. The framework incorporates fuzzy logic-based algorithms for deduplication of drug and vaccine nomenclature and supports real-time data analysis through the use of Key Performance Indicators (KPIs). It further enables centralized monitoring and decision-making via a built-in visualization layer, accessible to stakeholders at multiple administrative levels.While the framework has been tailored to the Indian public health context, its modular design makes it broadly applicable to other domains requiring integration of diverse data sources for strategic planning and policy implementation.The use of open-source technologies for development of various configurable and integrated layers like ETL, Deduplication, Standardization and Visualization layers encompassing into a single framework ensuring cost effectiveness in delivering the end-to-end solution makes it a novel and impactful for adoption specially in resource constrained environments.
[1] L. Dandona et al., “Nations within a nation: variations in epidemiological transition across the states of India, 1990–2016 in the Global Burden of Disease Study,” The Lancet, vol. 390, no. 10111, pp. 2437–2460, Dec. 2017, doi: 10.1016/S0140-6736(17)32804-0.
[2] The Lancet, “India—a tale of one country, but stories of many states,” The Lancet, vol. 390, no. 10111, p. 2413, Dec. 2017, doi: 10.1016/S0140-6736(17)32867-2.
[3] MoHFW, “MINISTRY OF HEALTH AND FAMILY WELFARE Major Schemes and Programmes Government of India New Delhi,” 2000. [Online]. Available: http://mohfw.nic.in.
[4] S. Saebao, S. Matayong, and N. trakulmaykee, “QoX based ETL Design for Business Intelligence System of Lecturers’ Qualifications Analysis,” in 2020 17th International Conference on Electrical Engineering/Electronics, Computer, Telecommunications and Information Technology (ECTI-CON), IEEE, Jun. 2020, pp. 539–542. doi: 10.1109/ECTI-CON49241.2020.9158113.
[5] S. Zhao, “What is ETL? (Extract, Transform, Load),” www.edq.com.
[6] T. Pott and lain Thomson, “Extract, transform, load? More like extremely tough to load, amirite?,” www.theregister.com.
[7] S. Sivabalan and R. I. Minu, “Heterogeneous Data Integration with ELT and Analytical MPP Database for Data Analysis Application,” in 2021 Innovations in Power and Advanced Computing Technologies (i-PACT), IEEE, Nov. 2021, pp. 1–5. doi: 10.1109/i-PACT52855.2021.9696841.
[8] B. Pan, G. Zhang, and X. Qin, “Design and realization of an ETL method in business intelligence project,” in 2018 IEEE 3rd International Conference on Cloud Computing and Big Data Analysis (ICCCBDA), IEEE, Apr. 2018, pp. 275–279. doi: 10.1109/ICCCBDA.2018.8386526.
[9] Shiva Kumar Vuppala and Manohar Reddy Sokkula, “Challenges in Streaming ETL Pipelines for High-Frequency Data Ingestion and Real-Time Processing,” International Journal For Multidisciplinary Research, vol. 6, no. 6, Dec. 2024, doi: 10.36948/ijfmr.2024.v06i06.33506.
[10] A. Kabiri, F. Wadjinny, and D. Chiadmi, “Towards a Framework for Conceptual Modeling of ETL Processes,” 2011, pp. 146–160. doi: 10.1007/978-3-642-27337-7_14.
[11] T. Jörg and S. Dessloch, “Near Real-Time Data Warehousing Using State-of-the-Art ETL Tools,” 2010, pp. 100–117. doi: 10.1007/978-3-642-14559-9_7.
[12] A. Dixit, S. Routroy, and S. K. Dubey, “A systematic literature review of healthcare supply chain and implications of future research,” Int. J. Pharm. Healthc. Mark., vol. 13, no. 4, pp. 405–435, Nov. 2019, doi: 10.1108/IJPHM-05-2018-0028.
[13] R. A. Atinga, S. Dery, S. P. Katongole, and M. Aikins, “Capacity for optimal performance of healthcare supply chain functions: competency, structural and resource gaps in the Northern Region of Ghana,” J. Health Organ. Manag., vol. 34, no. 8, pp. 899–914, Oct. 2020, doi: 10.1108/JHOM-09-2019-0283.
[14] K. Ahmad et al., “Public health supply chain for iron and folic acid supplementation in India: Status, bottlenecks and an agenda for corrective action under Anemia Mukt Bharat strategy,” PLoS One, vol. 18, no. 2, p. e0279827, Feb. 2023, doi: 10.1371/journal.pone.0279827.
[15] A. Verma, A. Rana, H. Monga, A. Chaudhary, and J. Singh, “Distribution Management of Drugs/medicines and vaccines vis-a-vis Free Drugs Service Initiative (FDSI) of Ministry of Health and Family Welfare (MoHFW), Government of India in the Indian States,” in 2021 9th International Conference on Reliability, Infocom Technologies and Optimization (Trends and Future Directions) (ICRITO), IEEE, Sep. 2021, pp. 1–5. doi: 10.1109/ICRITO51393.2021.9596365.
[16] SAS, “SAS Enterprise Guide,” https://support.sas.com/documentation/onlinedoc/guide/tut8/en/m0_2.htm. Accessed: Jun. 18, 2025. [Online]. Available: https://support.sas.com/documentation/onlinedoc/guide/tut8/en/m0_2.htm.
[17] E. Kodhai, K. Divakar, A. Andrews, Y. Pachipala, and J. Alzubi, “MANAGING THE CLOUD STORAGE USING DE-DUPLICATION AND SECURED FUZZY KEYWORD SEARCH FOR MULTIPLE DATA OWNERS,” International Journal of Pure and Applied Mathematics, 2018.
[18] R. Kumar, J. Lachure, and R. Doriya, “Use of Hybrid ECC to enhance Security and Privacy with Data Deduplication,” in 2021 Second International Conference on Electronics and Sustainable Communication Systems (ICESC), IEEE, Aug. 2021, pp. 934–941. doi: 10.1109/ICESC51422.2021.9532948.
[19] SQL Server, “SQL Server Integration Services,” https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver15. Accessed: Jun. 18, 2025. [Online]. Available: https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver15.
[20] Informatica, “Informatica.com,” https://www.informatica.com/de/products/data-integration/powercenter.html. Accessed: Jun. 18, 2025. [Online]. Available: https://www.informatica.com/de/products/data-integration/powercenter.html.
[21] Pedersen T and Mohania M, Data Warehousing and Knowledge Discovery, vol. 5691. Berlin, Heidelberg: Springer Berlin Heidelberg, 2009. doi: 10.1007/978-3-642-03730-6.
[22] https://en.wikipedia.org/, “Pentaho,” https://en.wikipedia.org/wiki/Pentaho. Accessed: Jun. 18, 2025. [Online]. Available: https://en.wikipedia.org/wiki/Pentaho.
[23] Ibm, “Datastage,” https://www.ibm.com/products/datastage. Accessed: Jun. 18, 2025. [Online]. Available: https://www.ibm.com/products/datastage.
[24] www.adeptia.com, “www.adeptia.com,” https://www.adeptia.com/products/connect. Accessed: Jun. 18, 2025. [Online]. Available: https://www.adeptia.com/products/connect.
[25] www.adeptia.com, “www.adeptia.com,” https://www.adeptia.com/solutions. Accessed: Jun. 18, 2025. [Online]. Available: https://www.adeptia.com/solutions.
[26] tutorialspoint.com, “Talend,” https://www.tutorialspoint.com/talend/index.htm. Accessed: Jun. 18, 2025. [Online]. Available: https://www.tutorialspoint.com/talend/index.htm.
[27] talend.com, “TalendSolutions,” https://www.talend.com/products/talend-open-studio/. Accessed: Jun. 18, 2025. [Online]. Available: https://www.talend.com/products/talend-open-studio/.
[28] Clover ETL Reference Manual,” https://docs.huihoo.com/cloveretl/server/CloverETLServer-ReferenceManual-4_7_0_016M1.pdf. Accessed: Jun. 18, 2025. [Online]. Available: https://docs.huihoo.com/cloveretl/server/CloverETLServer-ReferenceManual-4_7_0_016M1.pdf.
[29] Hevodata, “Hevo-features,” https://docs.hevodata.com/introduction/hevo-features/. Accessed: Jun. 18, 2025. [Online]. Available: https://docs.hevodata.com/introduction/hevo-features/.
[30] “Data-integrator,” https://www.oracle.com/in/middleware/technologies/data-integrator.html. Accessed: Jun. 18, 2025. [Online]. Available: https://www.oracle.com/in/middleware/technologies/data-integrator.html.
[31] S. I. Khan and A. S. Md. L. Hoque, “Towards development of health Data Warehouse: Bangladesh perspective,” in 2015 International Conference on Electrical Engineering and Information Communication Technology (ICEEICT), IEEE, May 2015, pp. 1–6. doi: 10.1109/ICEEICT.2015.7307514.
[32] Md. B. Biplob, G. A. Sheraji, and Khan Shahidul Islam, 2018 International Conference on Innovations in Science, Engineering and Technology : ICISET 2018 : International Islamic University Chittagong, Chittagong, Bangladesh : 27-28 October 2018. IEEE, 2018. doi: 10.1109/iciset.2018.8745574.
[33] A. R. Chaturvedi, A. K. Choubey, and Jinsheng Roan, “Scheduling the allocation of data fragments in a distributed database environment: a machine learning approach,” IEEE Trans. Eng. Manag., vol. 41, no. 2, pp. 194–207, May 1994, doi: 10.1109/17.293386.
[34] A. Simitisis, P. Vassiliadis, S. Skiadopoulos, and T. Sellis, Data Warehouses and OLAP. IGI Global, 2007. doi: 10.4018/978-1-59904-364-7.
[35] P. Vassiliadis and A. Simitsis, “EXTRACTION, TRANSFORMATION, AND LOADING,” 2009.
[36] C. Joe and K. Ralph, The data warehouse etl toolkit : practical techniques for extracting, cleaning, conforming, and delivering data. Wiley, Hoboken, N.J., 2013, 2011.
[37] W. W. Eckerson and R. Sponsors, “Achieving Business Success through a Commitment to High Quality Data TDWI REPORT SERIES DATA QUALITY AND THE BOTTOM LINE.” [Online]. Available: www.dw-institute.com.
[38] Drug_nomenclature,” https://en.wikipedia.org/wiki/Drug_nomenclature. Accessed: Jun. 18, 2025. [Online]. Available: https://en.wikipedia.org/wiki/Drug_nomenclature.
[39] First Nations Health Authority, “Generic vs Brand-name prescription drugs faq,” https://www.fnha.ca/about/news-and-events/news/generic-vs-brand-name-prescription-drugs-faq. Accessed: Jun. 18, 2025. [Online]. Available: https://www.fnha.ca/about/news-and-events/news/generic-vs-brand-name-prescription-drugs-faq.
[40] “Understanding expressions of drug amounts,” http://courses.washington.edu/pharm309/calculations/Lesson2.pdf. Accessed: Jun. 18, 2025. [Online]. Available: Understanding expressions of drug amounts.
[41] www.who.int, “ATC Classification,” https://www.who.int/tools/atc-ddd-toolkit/atc-classification. Accessed: Jun. 18, 2025. [Online]. Available: https://www.who.int/tools/atc-ddd-toolkit/atc-classification.
[42] S. Rudrappa, D. V. Agarkhed, and S. S. Vaidya, “Healthcare Systems: India,” in Quality Spine Care, Cham: Springer International Publishing, 2019, pp. 211–224. doi: 10.1007/978-3-319-97990-8_13.
[43] Soundex,” https://en.wikipedia.org/wiki/Soundex. Accessed: Jun. 18, 2025. [Online]. Available: https://en.wikipedia.org/wiki/Soundex.
[44] Metaphone,” https://en.wikipedia.org/wiki/Metaphone. Accessed: Jun. 18, 2025. [Online]. Available: https://en.wikipedia.org/wiki/Metaphone.
[45] “Double_Metaphone,” https://en.wikipedia.org/wiki/Metaphone#Double_Metaphone. Accessed: Jun. 18, 2025. [Online]. Available: https://en.wikipedia.org/wiki/Metaphone#Double_Metaphone.
[46] Lawrence Philips, “The double metaphone search algorithm,” C/C++ Users Journal, vol. 18, no. 6, pp. 38–43, 2000.
[47] Levenshtein_distance,” https://en.wikipedia.org/wiki/Levenshtein_distance. Accessed: Jun. 18, 2025. [Online]. Available: https://en.wikipedia.org/wiki/Levenshtein_distance.
[48] S. Al Zoubi, L. Gharaibeh, H. M. Jaber, and Z. Al-Zoubi, “Household Drug Stockpiling and Panic Buying of Drugs During the COVID-19 Pandemic: A Study From Jordan,” Front. Pharmacol., vol. 12, Dec. 2021, doi: 10.3389/fphar.2021.813405.
