Pivoting approaches for bulk extraction of Entity-Attribute-Value data.
Entity-Attribute-Value (EAV) data, as present in repositories of clinical patient data, must be transformed (pivoted) into one-column-per-parameter format before it can be used by a variety of analytical programs. Pivoting approaches have not been described in depth in the literature, and existing descriptions are dated. We describe and benchmark three alternative algorithms to perform pivoting of clinical data in the context of a clinical study data management system. We conclude that when the number of attributes to be returned is not too large, it is feasible to use static SQL as the basis for views on the data. An alternative but more complex approach that utilizes hash tables and the presence of abundant random-access-memory can achieve improved performance by reducing the load on the database server.
Dinu
Valentin
V
Center for Medical Informatics, Yale University School of Medicine, PO Box 208009, New Haven, CT 06520-8009, USA. Valentin.Dinu@yale.edu
Nadkarni
Prakash
P
Brandt
Cynthia
C
D000465:Algorithms; D002986:Clinical Trials as Topic / Q000706:statistics & numerical data*; D001330:Electronic Data Processing; D006801:Humans; D008491:Medical Informatics Applications; D008499:Medical Records / Q000706:statistics & numerical data*; D012984:Software; D014481:United States