Efficient Execution Methods of Pivoting for Bulk Extraction of Entity-Attribute-Value-Modeled Data

IEEE Journal of Biomedical and Health Informatics

Volumn 20, Issue 2, 2016, Pages 644-654

  Luo, Gang ^a   Frey, Lewis J ^b  

^a UNIVERSITY OF UTAH SCHOOL OF MEDICINE   (United States)

^b MEDICAL UNIVERSITY OF SOUTH CAROLINA   (United States)

Author keywords

Clinical study data management system (CSDMS); database; electronic medical record (EMR); entityattribute value (EAV); pivot

Indexed keywords

DATA WAREHOUSES; DATABASE SYSTEMS; EFFICIENCY; EXTRACTION; INFORMATION MANAGEMENT; MEDICAL COMPUTING; QUERY PROCESSING;

CLINICAL DATA WAREHOUSE; CLINICAL PARAMETERS; DATA MANAGEMENT SYSTEM; ELECTRONIC MEDICAL RECORD; ENTITYATTRIBUTE-VALUE (EAV); MULTIQUERY OPTIMIZATION; PIVOT; RELATIONAL TABLES;

DATA MINING;

CLINICAL TRIAL; DATA EXTRACTION; ELECTRONIC MEDICAL RECORD; EXTRACTION; FILTRATION; HUMAN; CLINICAL STUDY; DATA MINING; DATABASE MANAGEMENT SYSTEM; ELECTRONIC HEALTH RECORD; PROCEDURES; THEORETICAL MODEL;

CLINICAL STUDIES AS TOPIC; DATA MINING; DATABASE MANAGEMENT SYSTEMS; ELECTRONIC HEALTH RECORDS; HUMANS; MODELS, THEORETICAL;

EID: 84963776051     PISSN: 21682194     EISSN: 21682208     Source Type: Journal    
DOI: 10.1109/JBHI.2015.2392553     Document Type: Article

References (34)

1. V. Dinu and P. M. Nadkarni, "Guidelines for the effective use of entityattribute-valuemodeling for biomedical databases," Int. J. Med. Informat., vol. 76, nos. 11/12, pp. 769-779, 2007.
2. J. L. Beckmann, A. Halverson, R. Krishnamurthy, and J. F. Naughton, "Extending RDBMSs to support sparse datasets using an interpreted attribute storage format," in Proc. Int. Conf. Data Eng., 2006, p. 58.
3. J. Corwin, A. Silberschatz, P. L. Miller, and L. Marenco, "Dynamic tables: an architecture for managing evolving, heterogeneous biomedical data in relational database management systems," J. Am. Med. Informat. Assoc., vol. 14, no. 1, pp. 86-93, 2007.
4. C. J. McDonald, L. Blevins, W. M. Tierney, and D. K. Martin, "The Regenstrief medical records," MD Comput., vol. 5, no. 5, pp. 34-47, 1988.
5. C. Friedman, G. Hripcsak, S. B. Johnson, J. J. Cimino, and P. D. Clayton, "A generalized relational schema for an integrated clinical patient database," in Proc. Annu. Symp. Comput. Appl. Med. Care, 1990, pp. 335-339.
6. W. W. Stead, W. E. Hammond, and M. J. Straube, "A chartless record-Is it adequate?" in Proc. Annu. Symp. Comput. Appl. Med. Care, 1982, pp. 89-94.
7. H. R. Warner, C. M. Olmsted, and B. D. Rutherford, "HELP-A program for medical decision-making," Comput. Biomed. Res., vol. 5, no. 1, pp. 65-74, 1972.
8. (2014, May 4). Cerner's electronic medical record homepage. [Online]. Available: https://www.cerner.com/solutions/Hospitals-and-Health-Systems/Electronic-Medical-Record/
9. (2014, May 4). Oracle Clinical homepage. [Online]. Available: http://www.oracle.com/us/products/applications/health-sciences/eclinical/clinical/index.html
10. (2014, May 4). Oracle Health Sciences Clintrial homepage. [Online]. Available: http://www.oracle.com/us/industries/life-sciences/healthsciences-clintrial-363570.html
11. C. A. Brandt, P. Nadkarni, L. Marenco, B. T. Karras, C. Lu, L. Schacter, J. M. Fisk, and P. L. Miller, "Reengineering a database for clinical trials management: Lessons for system architects," Control Clin. Trials, vol. 21, no. 5, pp. 440-461, 2000.
12. (2014, May 4). [Online]. Available: https://metacpan.org/pod/CohortExplorer
13. P. A. Harris, R. Taylor, R. Thielke, J. Payne, N. Gonzalez, and J. G. Conde, "Research electronic data capture (REDCap) - a metadata-driven methodology And workflow process for providing translational research informatics support," J. Biomed. Informat., vol. 42, no. 2, pp. 377-381, 2009.
14. P. M. Nadkarni, Metadata-Driven Software Systems in Biomedicine: Designing Systems that can Adapt to Changing Knowledge. New York, NY, USA: Springer, 2011.
15. C. Brandt, R. Morse, K. Matthews, K. Sun, A. M. Deshpande, R. Gadagkar, D. B. Cohen, P. L. Miller, and P. M. Nadkarni, "Metadata-driven creation of data marts from an EAV-modeled clinical research database," Int. J. Med. Informat., vol. 65, no. 3, pp. 225-241, 2002.
16. R. Agrawal, A. Somani, and Y. Xu, "Storage and querying of E-commerce data," in Proc. 27th Int. Conf. Very Large Data Bases, 2001, pp. 149-158.
17. C. Cunningham, G. Graefe, and C. A. Galindo-Legaria, "PIVOT and UNPIVOT:Optimization and execution strategies in an RDBMS," in Proc. Int. Conf. Very Large Data Bases, 2004, pp. 998-1009.
18. L. V. S. Lakshmanan, F. Sadri, and S. N. Subramanian, "On efficiently implementing SchemaSQL on an SQL database system," in Proc. Int. Conf. Very Large Data Bases, 1999, pp. 471-482.
19. J. A. Lyman, K. Scully, and J. H. Jr. Harrison, "The development of health care data warehouses to support data mining," Clin. Lab Med., vol. 28, no. 1, pp. 55-71, 2008.
20. R. S. Evans, J. F. Lloyd, and L. A. Pierce, "Clinical use of an enterprise data warehouse," in Proc. AMIA Annu. Symp., 2012, pp. 189-198.
21. V. Dinu, P. M. Nadkarni, and C. Brandt, "Pivoting approaches for bulk extraction of entity-attribute-value data," Comput. Methods Programs Biomed., vol. 82, no. 1, pp. 38-43, 2006.
22. B. Rashid and M. S. Islam, "Role of materialized view maintenance with PIVOT and UNPIVOT operators," in Proc. IEEE Int. Adv. Comput. Conf., 2009, pp. 951-955.
23. S. Chen and E. A. Rundensteiner, "GPIVOT: Efficient incremental maintenance of complex ROLAP views," in Proc. Int. Conf. Data Eng., 2005, pp. 552-563.
24. H. Garcia-Molina, J. D. Ullman, and J. Widom, Database Systems: The Complete Book, 2nd ed. Upper Saddle River, NJ, USA: Prentice-Hall, 2008.
25. J. S. Vitter, "Algorithms and data structures for external memory," Found. Trends Theor. Comput. Sci., vol. 2, no. 4, pp. 305-474, 2006.
26. P. M. Nadkarni, L. N. Marenco, R. Chen, E. Skoufos, G. M. Shepherd, and P. L. Miller, "Organization of heterogeneous scientific data using the EAV/CR representation," JAMIA, vol. 6, no. 6, pp. 478-493, 1999.
27. P. M. Nadkarni and C. Brandt, "Data extraction and ad hoc query of an entity-attribute-value database," J. Am. Med. Informat. Assoc., vol. 5, no. 6, pp. 511-527, 1998.
28. P. Roy, S. Seshadri, S. Sudarshan, and S. Bhobe, "Efficient and extensible algorithms for multi query optimization," in Proc. ACM SIGMOD Int. Conf. Manag. Data, 2000, pp. 249-260.
29. T. K. Sellis, "Multiple-query optimization," TODS, vol. 13, no. 1, pp. 23-52, 1988.
30. C. A. Lang, B. Bhattacharjee, T. Malkemus, S. Padmanabhan, and K. Wong, "Increasing buffer-locality for multiple relational table scans through grouping and throttling," in Proc. Int. Conf. Data Eng., 2007, pp. 1136-1145.
31. C. A. Lang, B. Bhattacharjee, T. Malkemus, and K. Wong, "Increasing buffer-locality for multiple index based scans through intelligent placement and index scan speed control," in Proc. Int. Conf. Very Large Data Bases, 2007, pp. 1298-1309.
32. G. Luo, J. F. Naughton, C. J. Ellmann, and M. W. Watzke, "Transaction reordering," Data Knowl. Eng., vol. 69, no. 1, pp. 29-49, 2010.
33. M. Saeed, M. Villarroel, A. T. Reisner, G. Clifford, L. W. Lehman, G. Moody, T. Heldt, T. H. Kyaw, B. Moody, and R. G. Mark, "Multiparameter Intelligent Monitoring in Intensive Care II: A public-access intensive care unit database," Crit. Care Med., vol. 39, no. 5, pp. 952-960, 2011.
34. A. L. Goldberger, L. A. N. Amaral, L. Glass, J. M. Hausdorff, P. C. Ivanov, R. G. Mark, J. E. Mietus, G. B. Moody, C-K. Peng, and H. E. Stanley, "Physio Bank, Physiotoolkit, and physionet: Components of a new research resource for complex physiologic signals," Circulation, vol. 101, no. 23, pp. e215-e220, 2000.