Profile-IQ: Web-Based Data Query System for Local Health Department Infrastructure and Activities Gulzar H. Shah, PhD, Mstat, MS; Carolyn J. Leep, MS, MPH; Dayna Alexander, MSPH, CHES rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr
Objectives: To demonstrate the use of National Association of County & City Health Officials’ Profile-IQ, a Web-based data query system, and how policy makers, researchers, the general public, and public health professionals can use the system to generate descriptive statistics on local health departments. Design: This article is a descriptive account of an important health informatics tool based on information from the project charter for Profile-IQ and the authors’ experience and knowledge in design and use of this query system. Conclusion and Implications: Profile-IQ is a Web-based data query system that is based on open-source software: MySQL 5.5, Google Web Toolkit 2.2.0, Apache Commons Math library, Google Chart API, and Tomcat 6.0 Web server deployed on an Amazon EC2 server. It supports dynamic queries of National Profile of Local Health Departments data on local health department finances, workforce, and activities. Profile-IQ’s customizable queries provide a variety of statistics not available in published reports and support the growing information needs of users who do not wish to work directly with data files for lack of staff skills or time, or to avoid a data use agreement. Profile-IQ also meets the growing demand of public health practitioners and policy makers for data to support quality improvement, community health assessment, and other processes associated with voluntary public health accreditation. It represents a step forward in the recent health informatics movement of data liberation and use of open source information technology solutions to promote public health.
Local health departments (LHDs), like many organizations, face a critical need to systematically use information to make sense of changes in their environment, create new knowledge for innovation, and make decisions about courses of action.1 An area where this is evident in public health is the growing emphasis on quality improvement and voluntary public health accreditation that requires regular use of data to monitor key performance measures.2-4 This increased use of information systems and technologies is essential for public health practice, research, and learning.5 To meet growing information needs, LHDs and their stakeholders require access to summary statistics about LHDs and their communities. Most LHDs may lack time and staff resources to analyze raw data, particularly due to dwindling public health budgets resulting from persistent economic pressures.6-10 A public health informatics tool, referred to as a Web-based data query system (WDQS), allows public health professionals to easily access public health data held on Web servers. A WDQS enables data access through dynamic interfaces on the Internet, without the user having to run analysis software or access raw data.11,12 The purpose of this article is to describe and demonstrate the use of National Association of County & City Health Officials’ (NACCHO’s) Profile-IQ to obtain customized LHD statistics without analyzing raw data.13 This tool is available on NACCHO’S Web site (www.naccho.org/profile).
KEY WORDS: activities, finances, local health departments,
The development of the Profile-IQ system was supported by the Robert Wood Johnson Foundation (RWJF). The Profile survey is supported by the RWJF and the Centers for Disease Control and Prevention. The authors greatly appreciate review and edits by Ms Jan Wilhoit.
Profile-IQ, Web-based data query systems (WDQS), workforce
Author Affiliations: Jiann-Ping Hsu College of Public Health, Georgia Southern University, Statesboro (Dr Shah and Ms Alexander); and National Association of County & City Health Officials, Washington, District of Columbia (Ms Leep).
The authors declare no conflicts of interest.
J Public Health Management Practice, 2014, 20(2), 168–174 C 2014 Wolters Kluwer Health | Lippincott Williams & Wilkins Copyright
Correspondence: Gulzar H. Shah, PhD, Mstat, MS, Jiann-Ping Hsu College of Public Health, Georgia Southern University, 21 Jef Rd, Statesboro, GA 30458 ([email protected]). DOI: 10.1097/PHH.0b013e3182a1beb7
168 Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
Profile-IQ: Web-Based Data Query System
● Web-Based Data Query Systems A WDQS is an Internet-based application that supports the dynamic query of databases hosted on remote servers. These systems facilitate information retrieval by allowing users to directly manipulate query variables, consistent with functionalities available on the specific WDQS, and rapidly retrieve query results.11 Through the use of WDQS, users can produce various outputs based on their queries, including numeric tabulations, statistics, graphs, and maps.12 A WDQS is becoming an increasingly useful tool that allows public health agencies to respond to data requests in a timely manner, provide data to a broader audience, and assist in the development of community health assessments and strategic planning.11,14 In addition, the WDQS allows individuals to manipulate query variables, producing results that policy makers, researchers, and the general public, can use.12
WDQS in public health A carefully designed WDQS is very useful in public health because it can improve the reliability of data and reduce the risk of disclosing personal health information.15 Although the current use of WDQS is estimated to be much higher, in 2006, its use was documented in 27 states, implemented under the Centers for Disease Control and Prevention Assessment Initiatives.12,16 Current systems, mostly maintained by state health departments, differ from each other across several dimensions, including the purpose, intended users, and functionalities.12 However, these WDQSs consist of many similar functionalities and data sets that have been available to state and LHDs for many years. For instance, the WDQS of Utah State Department of Health, called IBIS-PH (http://ibis.health.utah.gov/), one of the many comprehensive WDQSs, consists of the following data sets: Birth; Fetal Death; Infant Death; Mortality; Inpatient Hospital Discharge; Emergency Department Encounter; Emergency Department Encounters for Primary Care Sensitive Conditions; BRFSS (Behavioral Risk Factor Surveillance System); UHAS (Utah Healthcare Access Survey); PRAMS (Pregnancy Risk Assessment and Monitoring System); YRBS (Youth Risk Behavior Survey); Utah Cancer Registry; Population Estimates; and National Toxic Substance Incidents Program. Some states have further customized their WDQSs to address local priorities including Missouri’s Information for Community Assessment Priority Setting Model (Priority MICA), Florida’s Community Health Assessment Resource Tool Set (Florida CHARTS), and South Carolina’s Access Network (SCAN).17,18
❘ 169
A WDQS allows the health department staff to pose data questions about public health events and population health status indicators and have them answered in a timely manner. The access by LHDs of population health data supports community assessments and planning, as well as dissemination of data, and allows the staff to compile summary statistics when writing grant proposals.11,12 Data on types of services and infrastructural capacity of LHDs, including public health financing and workforce, were not available for Internet-based querying of summary statistics until the creation of Profile-IQ, the system that is the primary focus of this article.
● Development and Use of Profile-IQ Profile-IQ is a WDQS, developed to provide easy access to data on local public health infrastructure (governance, finances, and workforce) and activities, for all types of users. Although NACCHO makes Profile data files available to researchers through a data use agreement, some potential data users (eg, LHD staff, students) do not wish to work directly with data files, because they lack staff skills or time to analyze raw data or because they do not wish to complete a data use agreement. NACCHO also reports summary statistics on these and other topics in its written reports on each Profile study, but these reports include limited subgroup analysis. Profile-IQ’s customizable queries provide a variety of statistics not available in reports, such as state-specific statistics, comparisons among geographic areas, and statistics on LHDs serving jurisdictions of specific sizes. As a result, Profile-IQ supports users, LHDs, and other stakeholders in a variety of tasks: benchmarking, planning, research, preparation for accreditation, quality improvement, and grant writing.
Technical information: Query process The query process consists of 6 steps: (1) the user makes online queries through interactive Web forms; (2) the queries, or inputs, are analyzed by the WDQS and translated into Structured Query Language (SQL), an international standard for manipulation of database; (3) SQL retrieves relevant data from the MySQL database, an open-source relational database management; (4) the data analyzer module performs preprogrammed statistics to match the user query on the retrieved data; (5) the data analyzer module performs data filtering, grouping, or other data manipulations consistent with the user’s query; and (6) the outputs are forwarded to the table maker and/or chart maker module to generate the data view. Figure 1 is a visual depiction of how the Profile-IQ WDQS works.
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
170 ❘ Journal of Public Health Management and Practice FIGURE 1 ● Technical Steps in Query Process
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
Open-source software The Profile-IQ system is implemented using multiple open-source software packages. For example, the backend database for storing and organizing the 2010 Profile data is MySQL 5.5.19 The Web front end for query building and presenting data was developed using Google Web Toolkit 2.2.0.20 Within the system, statistical analysis is implemented using Apache Commons Math library.21 Data charts and maps are generated using Google Chart API.22 The whole Web system is run on the Tomcat 6.0 Web server, which is deployed on an Amazon EC2 server.
Cell suppression and accounting for nonresponses Profile-IQ provides the ability to customize user queries and offers other benefits over summary statistics available through Profile study reports (www.naccho.org/ profile). However, 2 features of Profile-IQ must be kept in mind, as they might pose limitations: data suppression for small numbers of observations, and the absence of estimation weights to account for nonresponse. Being a publicly available system for summary statistics, Profile-IQ suppresses results for any cell with fewer than 5 observations to protect the anonymity and privacy of individual LHD information.15 In addition, while the 2010 Profile survey had a high response rate (>83%), the estimates for all LHDs presented in the Profile main reports are generated using estimation weights to account for differences in response rate among LHDs serving various jurisdiction sizes. In addition, the item nonresponse for the finance section is relatively higher, necessitating special weights to account for missing data. More information about statistical weights used in the 2010 Profile study can be found in the 2010 Profile report on page 8.13 Statistical weights are not applied in Profile-IQ. Users seeking estimates for all LHDs, rather than just those who responded to the Profile survey and provided valid data, should use statistics from the main Profile report when publishing statistics on all LHDs.
Capabilities of Profile-IQ Statistical measures Statistical measures available in Profile-IQ depend upon the level of measurement of the variables involved. For dichotomous variables, Profile-IQ generates percentages. For continuous or ratio variables, Profile-IQ generates means, medians, and first and third percentiles. In the “Number of LHDs” section, the outputs generated are total values.
Data set and topics and variables
Grouping and filtering
Currently, the WDQS allows users to perform customized queries on 3 main topics covered in the 2010 Profile survey: financing, workforce, and activities.23 In addition, by selecting the tab, the number of LHDs, users can determine the total number of LHDs with certain characteristics (eg, jurisdiction population, governance, or state); Profile-IQ does not provide data on individual LHDs. The 2010 Profile study was a census-style survey administered to all 2565 LHDs in the country to collect information on their jurisdictions and infrastructure, including governance, financing, leaders, workforce, and activities. The Table shows the variables and attributes available for each of the 3 topics.
Query users can choose 1 or 2 grouping variables if user wishes to compute subgroup comparisons (eg, 3 jurisdiction population size categories). In addition, users are allowed to filter (select a subset of observations) on the basis of up to 2 grouping characteristics. For example, the users may want to limit their query to LHDs with a population size of fewer than 50 000 and/or a governance category “local”. The categorical variables available for grouping or filtering are as follows: (1) 3 jurisdiction population size categories; (2) 5 jurisdiction population size categories; (3) 7 jurisdiction population size categories; (4) define your own population categories; (5) governance category; (6) local board of health status; (7) geographic regions; and (8) state. The
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
Profile-IQ: Web-Based Data Query System
❘ 171
TABLE ● Variables Available for Querying in Profile-IQ qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
Financing 1. Total annual revenues and expenditures 2. Annual revenues and expenditures per capita 3. Revenue sourcesa 4. Revenue Sourcesa (per capita) 5. Revenue sourcesa (%)
Workforce
Activities
1. Staff size: FTE positions; no. of employees 2. Staff size: Per 100 000 population 3. Staff race and ethnicity (no. of employees): Staff Hispanic ethnicity Staff race (other than white) 4. Staff race and ethnicity (% of employees): Staff Hispanic ethnicity Staff race (other than white) 5. No. of FTEs in each occupation: Public health managers, public health nurse, public health physician, environmental health worker, epidemiologist, health educator, nutritionist, public health informatics specialist, public information specialist, behavioral health professional, emergency preparedness staff, and administrative or clerical personnel 6. % of LHDs employing selected occupations: Public health managers, public health nurse, public health physician, environmental health worker, epidemiologist, health educator, nutritionist, public health informatics specialist, public information specialist, behavioral health professional, emergency preparedness staff, and administrative or clerical personnel
1. Immunization: Adult and child immunizations 2. Screen for diseases/conditions: HIV/AIDS, other STDs, tuberculosis, cancer, cardiovascular, diabetes, high blood pressure, blood lead screening 3. Treatment of communicable diseases: HIV/AIDS, other STDs, and tuberculosis treatment 4. Maternal and child health: Family planning, prenatal care, obstetrical care, WIC, MCH home visits, EPSDT, and Well Child clinic 5. Other health service: Comprehensive primary care, home health care, oral health, behavioral/mental health, and substance abuse services 6. Epidemiology and surveillance activities: Communicable/infectious disease, chronic disease, injury, behavioral risk factors, environmental health, syndromic, and MCH surveillance 7. Population-based primary prevention activities: Injury, unintended pregnancy, chronic disease programs, nutrition promotion, physical Activity promotion, violence, tobacco, substance abuse, and mental illness prevention 8. Regulation, inspection, and/or licensing activities: Mobile homes, campgrounds & RVs, solid waste disposal sites, solid waste haulers, septic systems, hotels/motels, schools/daycares, children’s camps, cosmetology businesses, body art, public swimming pools, tobacco retailers, smoke-free ordinances, lead inspection, food processing, milk processing, public drinking water, private drinking water, food service establishments, health-related facilities, and housing inspections regulation 9. Other environmental health activities: Indoor air quality, food safety education, radiation control, vector control, land use planning, groundwater protection, surface water protection, HazMat response, hazardous waste disposal, pollution prevention, air pollution control, noise pollution control, and collection of unused pharmaceuticals activities 10. Other activities: Emergency medical services, animal control, occupational safety and health, veterinarian public health activities, laboratory services, outreach and enrollment for medical insurance (including Medicaid), school-based clinics, school health, asthma prevention and/or management, correctional health, vital records, and medical examiner’s office
Abbreviations: ARRA, American Reinvestment and Recovery Act; EPSDT, Early and Periodic Screening, Diagnostic and Treatment; FTE, full-time equivalent; MCH, maternal and child health; PHER, Public Health Emergency Response; STD, sexually transmitted disease; WIC, Special Supplemental Nutrition for Women, Infants, and Children. a City township town sources, county sources, state direct sources (excluding federal pass through), federal sources passed through by state (excluding PHER and ARRA funds), federal sources direct, PHER funds, ARRA funds, Medicaid, Medicare, private foundations, private health insurance, patient personal fees, nonclinical fees and fines, tribal sources, and other revenue sources.
user may select 1 or 2 variables from the list to generate customized statistics.23
Query types and ways to generate statistics with Profile-IQ Once the topic is selected from 1 of the 3 topic areas, a user may perform a query by selecting predefined queries or step-by-step queries. All of the queries a user selects or creates during a login session are recorded, so the user can easily revisit them by selecting the “Recent Queries” option.
grouping variables; and (4) choose a subset of observations based on up to 2 grouping characteristics.23 Steps 1 and 2 are required; steps 3 and 4 are optional. Users can click the variable group name (eg, the number of fulltime equivalents in each occupation) and select individual or multiple variables (eg, epidemiologist, health educator) belonging to the same group. However, variables in different groups cannot be selected for the same query. The Table shows the available variables in all 3 areas: financing, workforce, and activities.
Conducting a step-by-step query
Predefined queries
Step-by-step queries involve 4 steps: (1) select the variable; (2) select a statistical measure; (3) choose 1 or 2
Predefined queries are popular combinations of variables and measures. For instance, the following items
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
172 ❘ Journal of Public Health Management and Practice are available as predefined queries about LHD finances: (1) median total annual expenditures and revenues for all LHDs; (2) mean total annual expenditures and revenues for all LHDs; (3) median annual per capita expenditures and revenues; (4) mean annual per capita expenditures and revenues; and (5) mean percentage of LHD revenues from selected sources, as shown in Figure 2, which is a screenshot of the query page.16 To generate statistics in predefined queries, the user simply clicks on the query title. However, once predefined query results are displayed, users can use the grouping and filtering functions to modify the query for group comparisons and/or for selecting a subset of observations.
Options for displaying data Profile-IQ supports 3 display options for output for query results: tables, charts, and maps (color-coded on the state level). In the table view, results are displayed as a sortable data table. If user clicks on the “Export” button, data are sent to a Microsoft Excel file. In addition, users can click a button to send an e-mail with the query result. In the chart view, query results are displayed in an interactive data chart. Map view is used only for viewing query results grouped by state; how-
ever, results will not appear for states with fewer than 5 observations (eg, District of Columbia, Delaware).
● Summary and Conclusions This article describes how policy makers, researchers, the general public, and public health professionals can use NACCHO’s Profile-IQ to obtain customized statistics on LHDs by selecting query topics and generating tables, figures, or maps. Profile-IQ is a WDQS that is based on open-source software: MySQL 5.5,19 Google Web Toolkit 2.2.0,20 Apache Commons Math library, Google Chart API, and Tomcat 6.0 Web server deployed on an Amazon EC2 server. Open-source software is the process of producing software based on unconstrained access to source code and permission to distribute it to anyone for any purpose.24 The use of open-source software will allow other public health agencies to adopt Profile-IQ for developing their own query systems and NACCHO to add additional data sets in the future without incurring any ongoing licensing costs for the software used. Profile-IQ is also built on a new technology model called “cloud computing.” Cloud computing technologies are primarily enabled by virtualization and autonomic computing with the underlying aim of allowing users to cut costs and take advantage of information technologies, without having
FIGURE 2 ● Predefined Queries for LHD Financing Section
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
Abbreviation: LHD, local health department.
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
Profile-IQ: Web-Based Data Query System
expertise or knowledge to use them independently.25 NACCHO chose cloud computing because it was the most economical solution. Data needs of public health practitioners and policy makers are growing while their resources to meet these needs are shrinking due to dwindling budgets and a shrinking workforce. Some of these data needs stem from a growing emphasis on quality improvement, community health assessment, and other processes associated with voluntary public health accreditation. Use of Web-based interactive query systems such as Profile-IQ can be instrumental for public health professionals in meeting some of these needs, without requiring them to acquire raw data or employ staff trained in the use of data analysis software. Profile-IQ allows users to obtain customized information about numerous variables on 3 topics—LHD financing, workforce, and activities—without having to analyze raw data. NACCHO anticipated that LHD staff and students would be key users of Profile-IQ, and the initial patterns of use of Profile-IQ support this expectation. As of the end of 2012, around 43% of users are LHD staff and 31% are students. The staff at nonprofit organizations, including NACCHO staff, comprise the next largest user category at 13%. Users have employed Profile-IQ for several purposes. Public health professionals in Ohio used ProfileIQ to explore the question: Can merging 2 or more county LHDs to create economies of scale save these LHDs money and bring more federal and state funding?26 This led to more in-depth research on this topic. A faculty member at a school of public health described using the system in a variety of ways: in classroom exercises to acquaint students with LHD services and what data are available; to quickly generate statistics to use for background in writing grant proposals; to conduct basic analyses; to explore data to select appropriate methodology; and to perform analyses for independent verification of basic analysis results.26 ProfileIQ is a step forward in the recent health informatics movement of data liberation and use of open-source information technology solutions to promote public health.
REFERENCES 1. Choo CW. The knowing organization: how organizations use information to construct meaning, create knowledge and make decisions. Int J Inf Manage. 1996;16(5): 329-340. 2. Riley W, Parsons H, Duffy G, Moran J, Henry B. Realizing transformational change through quality improvement in public health. J Public Health Manag Pract. 2010;16(1): 72-78.
❘ 173
3. Riley W, Lownik B, Halverson P, et al. Developing a taxonomy for the science of improvement in public health. J Public Health Manag Pract. 2012;18(6):506-514. 4. Riley WJ, Lownik EM, Scutchfield FD, Mays GP, Corso LC, Beitsch LM. Public health department accreditation: setting the research agenda. Am J Prev Med. 2012;42(3):263-271. doi:10.1016/j.amepre.2011.10.021. 5. Yasnoff WA, O Carroll PW, Koo D, Linkins RW, Kilbourne EM. Public health informatics: improving and transforming public health in the information age. J Public Health Manag Pract. 2000;6(6):67-75. 6. Willard R, Shah G, Leep C, Ku L. Impact of the 2008-2010 economic recession on local health departments. J Public Health Manag Pract. 2012;18(2):106-114. 7. Chen LW, Jacobson J, Roberts S, Palm D. Resource allocation and funding challenges for regional local health departments in Nebraska. J Public Health Manag Pract. 2012;18(2): 141-147. 8. Kuehnert PL, McConnaughay KS. Tough choices in tough times: enhancing public health value in an era of declining resources. J Public Health Manag Pract. 2012;18(2): 115-125. 9. Institute of Medicine. For the Public’s Health: Investing in a Healthier Future. Washington, DC: National Academies Press; 2012. 10. Novick LF. Local health departments: time of challenge and change. J Public Health Manag Pract. 2012;18(2):103. 11. Love D, Shah GH. Reflections on organizational issues in developing, implementing, and maintaining state Webbased data query systems. J Public Health Manag Pract. 2006;12(2):184-188. 12. Friedman DJ, Parrish RG. Characteristics, desired functionalities, and datasets of state Web-based data query systems. J Public Health Manag Pract. 2006;12(2):119-129. 13. National Association of County & City Health Officials. 2010 National Profile of Local Health Departments. http://www.naccho.org/topics/infrastructure/profile/ resources/2010report/index.cfm. Accessed November 12, 2012. 14. Ostrovsky A, Katz M. The San Francisco Community Vital Signs: using Web-based tools to facilitate the Mobilizing for Action through Planning and Partnerships process. J Public Health Manag Pract. 2011;17(5):457-471. 15. Rudolph BA, Shah GH, Love D. Small numbers, disclosure risk, security, and reliability issues in Web-based data query systems. J Public Health Manage Pract. 2006;12(2): 176-183. 16. Gjelsvik A, Buechner J. States’ approaches to implementing a Web-based data query system. J Public Health Manag Pract. 2006;12(2):170-175. 17. Simoes EJ, Land G, Metzger R, Mokdad A. Prioritization MICA: a Web-based application to prioritize public health resources. J Public Health Manag Pract. 2006;12(2):161-169. 18. Grigg M, Alfred B, Keller C, Steele JA. Implementation of an Internet-based geographic information system: the Florida experience. J Public Health Manag Pract. 2006;12(2): 139-145. 19. MySQL. MySQL Cluster 7.2. http://www.mysql.com. Published 2012. Accessed September 15, 2012.
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
174 ❘ Journal of Public Health Management and Practice 20. Google Developers. Google Web Toolkit. https://developers .google.com/web-toolkit. Published 2012. Accessed September 15, 2012. 21. Apache Commons. Commons Math. http://commons. apache.org/math. Published 2012. Accessed September 15, 2012. 22. Google Developers. Google Chart Tools. https:// developers.google.com/chart. Published 2012. Accessed December 13, 2012. 23. National Association of County & City Health Officials. Profile-IQ. http://profile-iq.naccho.org. Published 2012. Accessed September 11, 2012.
24. Bonaccorsi A, Rossi C. Why open source software can succeed. Res Policy. 2003;32:1243-1258. 25. Hamdaqa M. Cloud Computing uncovered: a research landscape human errors. Adv Comput. 2012;86:4184. http://www.stargroup.uwaterloo.ca/∼mhamdaqa/ publications/Cloud Computing Uncovered.pdf. Accessed March 24, 2013. 26. National Association of County & City Health Officials. Profile-IQ: online data query system. http: //naccho.org/topics/infrastructure/profile/resources/ /ProfileIQ051212/lib/playback.html. Published 2013. Accessed March 24, 2013.
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
Objectives: To demonstrate the use of National Association of County & City Health Officials’ Profile-IQ, a Web-based data query system, and how policy makers, researchers, the general public, and public health professionals can use the system to generate descriptive statistics on local health departments. Design: This article is a descriptive account of an important health informatics tool based on information from the project charter for Profile-IQ and the authors’ experience and knowledge in design and use of this query system. Conclusion and Implications: Profile-IQ is a Web-based data query system that is based on open-source software: MySQL 5.5, Google Web Toolkit 2.2.0, Apache Commons Math library, Google Chart API, and Tomcat 6.0 Web server deployed on an Amazon EC2 server. It supports dynamic queries of National Profile of Local Health Departments data on local health department finances, workforce, and activities. Profile-IQ’s customizable queries provide a variety of statistics not available in published reports and support the growing information needs of users who do not wish to work directly with data files for lack of staff skills or time, or to avoid a data use agreement. Profile-IQ also meets the growing demand of public health practitioners and policy makers for data to support quality improvement, community health assessment, and other processes associated with voluntary public health accreditation. It represents a step forward in the recent health informatics movement of data liberation and use of open source information technology solutions to promote public health.
Local health departments (LHDs), like many organizations, face a critical need to systematically use information to make sense of changes in their environment, create new knowledge for innovation, and make decisions about courses of action.1 An area where this is evident in public health is the growing emphasis on quality improvement and voluntary public health accreditation that requires regular use of data to monitor key performance measures.2-4 This increased use of information systems and technologies is essential for public health practice, research, and learning.5 To meet growing information needs, LHDs and their stakeholders require access to summary statistics about LHDs and their communities. Most LHDs may lack time and staff resources to analyze raw data, particularly due to dwindling public health budgets resulting from persistent economic pressures.6-10 A public health informatics tool, referred to as a Web-based data query system (WDQS), allows public health professionals to easily access public health data held on Web servers. A WDQS enables data access through dynamic interfaces on the Internet, without the user having to run analysis software or access raw data.11,12 The purpose of this article is to describe and demonstrate the use of National Association of County & City Health Officials’ (NACCHO’s) Profile-IQ to obtain customized LHD statistics without analyzing raw data.13 This tool is available on NACCHO’S Web site (www.naccho.org/profile).
KEY WORDS: activities, finances, local health departments,
The development of the Profile-IQ system was supported by the Robert Wood Johnson Foundation (RWJF). The Profile survey is supported by the RWJF and the Centers for Disease Control and Prevention. The authors greatly appreciate review and edits by Ms Jan Wilhoit.
Profile-IQ, Web-based data query systems (WDQS), workforce
Author Affiliations: Jiann-Ping Hsu College of Public Health, Georgia Southern University, Statesboro (Dr Shah and Ms Alexander); and National Association of County & City Health Officials, Washington, District of Columbia (Ms Leep).
The authors declare no conflicts of interest.
J Public Health Management Practice, 2014, 20(2), 168–174 C 2014 Wolters Kluwer Health | Lippincott Williams & Wilkins Copyright
Correspondence: Gulzar H. Shah, PhD, Mstat, MS, Jiann-Ping Hsu College of Public Health, Georgia Southern University, 21 Jef Rd, Statesboro, GA 30458 ([email protected]). DOI: 10.1097/PHH.0b013e3182a1beb7
168 Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
Profile-IQ: Web-Based Data Query System
● Web-Based Data Query Systems A WDQS is an Internet-based application that supports the dynamic query of databases hosted on remote servers. These systems facilitate information retrieval by allowing users to directly manipulate query variables, consistent with functionalities available on the specific WDQS, and rapidly retrieve query results.11 Through the use of WDQS, users can produce various outputs based on their queries, including numeric tabulations, statistics, graphs, and maps.12 A WDQS is becoming an increasingly useful tool that allows public health agencies to respond to data requests in a timely manner, provide data to a broader audience, and assist in the development of community health assessments and strategic planning.11,14 In addition, the WDQS allows individuals to manipulate query variables, producing results that policy makers, researchers, and the general public, can use.12
WDQS in public health A carefully designed WDQS is very useful in public health because it can improve the reliability of data and reduce the risk of disclosing personal health information.15 Although the current use of WDQS is estimated to be much higher, in 2006, its use was documented in 27 states, implemented under the Centers for Disease Control and Prevention Assessment Initiatives.12,16 Current systems, mostly maintained by state health departments, differ from each other across several dimensions, including the purpose, intended users, and functionalities.12 However, these WDQSs consist of many similar functionalities and data sets that have been available to state and LHDs for many years. For instance, the WDQS of Utah State Department of Health, called IBIS-PH (http://ibis.health.utah.gov/), one of the many comprehensive WDQSs, consists of the following data sets: Birth; Fetal Death; Infant Death; Mortality; Inpatient Hospital Discharge; Emergency Department Encounter; Emergency Department Encounters for Primary Care Sensitive Conditions; BRFSS (Behavioral Risk Factor Surveillance System); UHAS (Utah Healthcare Access Survey); PRAMS (Pregnancy Risk Assessment and Monitoring System); YRBS (Youth Risk Behavior Survey); Utah Cancer Registry; Population Estimates; and National Toxic Substance Incidents Program. Some states have further customized their WDQSs to address local priorities including Missouri’s Information for Community Assessment Priority Setting Model (Priority MICA), Florida’s Community Health Assessment Resource Tool Set (Florida CHARTS), and South Carolina’s Access Network (SCAN).17,18
❘ 169
A WDQS allows the health department staff to pose data questions about public health events and population health status indicators and have them answered in a timely manner. The access by LHDs of population health data supports community assessments and planning, as well as dissemination of data, and allows the staff to compile summary statistics when writing grant proposals.11,12 Data on types of services and infrastructural capacity of LHDs, including public health financing and workforce, were not available for Internet-based querying of summary statistics until the creation of Profile-IQ, the system that is the primary focus of this article.
● Development and Use of Profile-IQ Profile-IQ is a WDQS, developed to provide easy access to data on local public health infrastructure (governance, finances, and workforce) and activities, for all types of users. Although NACCHO makes Profile data files available to researchers through a data use agreement, some potential data users (eg, LHD staff, students) do not wish to work directly with data files, because they lack staff skills or time to analyze raw data or because they do not wish to complete a data use agreement. NACCHO also reports summary statistics on these and other topics in its written reports on each Profile study, but these reports include limited subgroup analysis. Profile-IQ’s customizable queries provide a variety of statistics not available in reports, such as state-specific statistics, comparisons among geographic areas, and statistics on LHDs serving jurisdictions of specific sizes. As a result, Profile-IQ supports users, LHDs, and other stakeholders in a variety of tasks: benchmarking, planning, research, preparation for accreditation, quality improvement, and grant writing.
Technical information: Query process The query process consists of 6 steps: (1) the user makes online queries through interactive Web forms; (2) the queries, or inputs, are analyzed by the WDQS and translated into Structured Query Language (SQL), an international standard for manipulation of database; (3) SQL retrieves relevant data from the MySQL database, an open-source relational database management; (4) the data analyzer module performs preprogrammed statistics to match the user query on the retrieved data; (5) the data analyzer module performs data filtering, grouping, or other data manipulations consistent with the user’s query; and (6) the outputs are forwarded to the table maker and/or chart maker module to generate the data view. Figure 1 is a visual depiction of how the Profile-IQ WDQS works.
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
170 ❘ Journal of Public Health Management and Practice FIGURE 1 ● Technical Steps in Query Process
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
Open-source software The Profile-IQ system is implemented using multiple open-source software packages. For example, the backend database for storing and organizing the 2010 Profile data is MySQL 5.5.19 The Web front end for query building and presenting data was developed using Google Web Toolkit 2.2.0.20 Within the system, statistical analysis is implemented using Apache Commons Math library.21 Data charts and maps are generated using Google Chart API.22 The whole Web system is run on the Tomcat 6.0 Web server, which is deployed on an Amazon EC2 server.
Cell suppression and accounting for nonresponses Profile-IQ provides the ability to customize user queries and offers other benefits over summary statistics available through Profile study reports (www.naccho.org/ profile). However, 2 features of Profile-IQ must be kept in mind, as they might pose limitations: data suppression for small numbers of observations, and the absence of estimation weights to account for nonresponse. Being a publicly available system for summary statistics, Profile-IQ suppresses results for any cell with fewer than 5 observations to protect the anonymity and privacy of individual LHD information.15 In addition, while the 2010 Profile survey had a high response rate (>83%), the estimates for all LHDs presented in the Profile main reports are generated using estimation weights to account for differences in response rate among LHDs serving various jurisdiction sizes. In addition, the item nonresponse for the finance section is relatively higher, necessitating special weights to account for missing data. More information about statistical weights used in the 2010 Profile study can be found in the 2010 Profile report on page 8.13 Statistical weights are not applied in Profile-IQ. Users seeking estimates for all LHDs, rather than just those who responded to the Profile survey and provided valid data, should use statistics from the main Profile report when publishing statistics on all LHDs.
Capabilities of Profile-IQ Statistical measures Statistical measures available in Profile-IQ depend upon the level of measurement of the variables involved. For dichotomous variables, Profile-IQ generates percentages. For continuous or ratio variables, Profile-IQ generates means, medians, and first and third percentiles. In the “Number of LHDs” section, the outputs generated are total values.
Data set and topics and variables
Grouping and filtering
Currently, the WDQS allows users to perform customized queries on 3 main topics covered in the 2010 Profile survey: financing, workforce, and activities.23 In addition, by selecting the tab, the number of LHDs, users can determine the total number of LHDs with certain characteristics (eg, jurisdiction population, governance, or state); Profile-IQ does not provide data on individual LHDs. The 2010 Profile study was a census-style survey administered to all 2565 LHDs in the country to collect information on their jurisdictions and infrastructure, including governance, financing, leaders, workforce, and activities. The Table shows the variables and attributes available for each of the 3 topics.
Query users can choose 1 or 2 grouping variables if user wishes to compute subgroup comparisons (eg, 3 jurisdiction population size categories). In addition, users are allowed to filter (select a subset of observations) on the basis of up to 2 grouping characteristics. For example, the users may want to limit their query to LHDs with a population size of fewer than 50 000 and/or a governance category “local”. The categorical variables available for grouping or filtering are as follows: (1) 3 jurisdiction population size categories; (2) 5 jurisdiction population size categories; (3) 7 jurisdiction population size categories; (4) define your own population categories; (5) governance category; (6) local board of health status; (7) geographic regions; and (8) state. The
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
Profile-IQ: Web-Based Data Query System
❘ 171
TABLE ● Variables Available for Querying in Profile-IQ qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
Financing 1. Total annual revenues and expenditures 2. Annual revenues and expenditures per capita 3. Revenue sourcesa 4. Revenue Sourcesa (per capita) 5. Revenue sourcesa (%)
Workforce
Activities
1. Staff size: FTE positions; no. of employees 2. Staff size: Per 100 000 population 3. Staff race and ethnicity (no. of employees): Staff Hispanic ethnicity Staff race (other than white) 4. Staff race and ethnicity (% of employees): Staff Hispanic ethnicity Staff race (other than white) 5. No. of FTEs in each occupation: Public health managers, public health nurse, public health physician, environmental health worker, epidemiologist, health educator, nutritionist, public health informatics specialist, public information specialist, behavioral health professional, emergency preparedness staff, and administrative or clerical personnel 6. % of LHDs employing selected occupations: Public health managers, public health nurse, public health physician, environmental health worker, epidemiologist, health educator, nutritionist, public health informatics specialist, public information specialist, behavioral health professional, emergency preparedness staff, and administrative or clerical personnel
1. Immunization: Adult and child immunizations 2. Screen for diseases/conditions: HIV/AIDS, other STDs, tuberculosis, cancer, cardiovascular, diabetes, high blood pressure, blood lead screening 3. Treatment of communicable diseases: HIV/AIDS, other STDs, and tuberculosis treatment 4. Maternal and child health: Family planning, prenatal care, obstetrical care, WIC, MCH home visits, EPSDT, and Well Child clinic 5. Other health service: Comprehensive primary care, home health care, oral health, behavioral/mental health, and substance abuse services 6. Epidemiology and surveillance activities: Communicable/infectious disease, chronic disease, injury, behavioral risk factors, environmental health, syndromic, and MCH surveillance 7. Population-based primary prevention activities: Injury, unintended pregnancy, chronic disease programs, nutrition promotion, physical Activity promotion, violence, tobacco, substance abuse, and mental illness prevention 8. Regulation, inspection, and/or licensing activities: Mobile homes, campgrounds & RVs, solid waste disposal sites, solid waste haulers, septic systems, hotels/motels, schools/daycares, children’s camps, cosmetology businesses, body art, public swimming pools, tobacco retailers, smoke-free ordinances, lead inspection, food processing, milk processing, public drinking water, private drinking water, food service establishments, health-related facilities, and housing inspections regulation 9. Other environmental health activities: Indoor air quality, food safety education, radiation control, vector control, land use planning, groundwater protection, surface water protection, HazMat response, hazardous waste disposal, pollution prevention, air pollution control, noise pollution control, and collection of unused pharmaceuticals activities 10. Other activities: Emergency medical services, animal control, occupational safety and health, veterinarian public health activities, laboratory services, outreach and enrollment for medical insurance (including Medicaid), school-based clinics, school health, asthma prevention and/or management, correctional health, vital records, and medical examiner’s office
Abbreviations: ARRA, American Reinvestment and Recovery Act; EPSDT, Early and Periodic Screening, Diagnostic and Treatment; FTE, full-time equivalent; MCH, maternal and child health; PHER, Public Health Emergency Response; STD, sexually transmitted disease; WIC, Special Supplemental Nutrition for Women, Infants, and Children. a City township town sources, county sources, state direct sources (excluding federal pass through), federal sources passed through by state (excluding PHER and ARRA funds), federal sources direct, PHER funds, ARRA funds, Medicaid, Medicare, private foundations, private health insurance, patient personal fees, nonclinical fees and fines, tribal sources, and other revenue sources.
user may select 1 or 2 variables from the list to generate customized statistics.23
Query types and ways to generate statistics with Profile-IQ Once the topic is selected from 1 of the 3 topic areas, a user may perform a query by selecting predefined queries or step-by-step queries. All of the queries a user selects or creates during a login session are recorded, so the user can easily revisit them by selecting the “Recent Queries” option.
grouping variables; and (4) choose a subset of observations based on up to 2 grouping characteristics.23 Steps 1 and 2 are required; steps 3 and 4 are optional. Users can click the variable group name (eg, the number of fulltime equivalents in each occupation) and select individual or multiple variables (eg, epidemiologist, health educator) belonging to the same group. However, variables in different groups cannot be selected for the same query. The Table shows the available variables in all 3 areas: financing, workforce, and activities.
Conducting a step-by-step query
Predefined queries
Step-by-step queries involve 4 steps: (1) select the variable; (2) select a statistical measure; (3) choose 1 or 2
Predefined queries are popular combinations of variables and measures. For instance, the following items
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
172 ❘ Journal of Public Health Management and Practice are available as predefined queries about LHD finances: (1) median total annual expenditures and revenues for all LHDs; (2) mean total annual expenditures and revenues for all LHDs; (3) median annual per capita expenditures and revenues; (4) mean annual per capita expenditures and revenues; and (5) mean percentage of LHD revenues from selected sources, as shown in Figure 2, which is a screenshot of the query page.16 To generate statistics in predefined queries, the user simply clicks on the query title. However, once predefined query results are displayed, users can use the grouping and filtering functions to modify the query for group comparisons and/or for selecting a subset of observations.
Options for displaying data Profile-IQ supports 3 display options for output for query results: tables, charts, and maps (color-coded on the state level). In the table view, results are displayed as a sortable data table. If user clicks on the “Export” button, data are sent to a Microsoft Excel file. In addition, users can click a button to send an e-mail with the query result. In the chart view, query results are displayed in an interactive data chart. Map view is used only for viewing query results grouped by state; how-
ever, results will not appear for states with fewer than 5 observations (eg, District of Columbia, Delaware).
● Summary and Conclusions This article describes how policy makers, researchers, the general public, and public health professionals can use NACCHO’s Profile-IQ to obtain customized statistics on LHDs by selecting query topics and generating tables, figures, or maps. Profile-IQ is a WDQS that is based on open-source software: MySQL 5.5,19 Google Web Toolkit 2.2.0,20 Apache Commons Math library, Google Chart API, and Tomcat 6.0 Web server deployed on an Amazon EC2 server. Open-source software is the process of producing software based on unconstrained access to source code and permission to distribute it to anyone for any purpose.24 The use of open-source software will allow other public health agencies to adopt Profile-IQ for developing their own query systems and NACCHO to add additional data sets in the future without incurring any ongoing licensing costs for the software used. Profile-IQ is also built on a new technology model called “cloud computing.” Cloud computing technologies are primarily enabled by virtualization and autonomic computing with the underlying aim of allowing users to cut costs and take advantage of information technologies, without having
FIGURE 2 ● Predefined Queries for LHD Financing Section
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
Abbreviation: LHD, local health department.
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
Profile-IQ: Web-Based Data Query System
expertise or knowledge to use them independently.25 NACCHO chose cloud computing because it was the most economical solution. Data needs of public health practitioners and policy makers are growing while their resources to meet these needs are shrinking due to dwindling budgets and a shrinking workforce. Some of these data needs stem from a growing emphasis on quality improvement, community health assessment, and other processes associated with voluntary public health accreditation. Use of Web-based interactive query systems such as Profile-IQ can be instrumental for public health professionals in meeting some of these needs, without requiring them to acquire raw data or employ staff trained in the use of data analysis software. Profile-IQ allows users to obtain customized information about numerous variables on 3 topics—LHD financing, workforce, and activities—without having to analyze raw data. NACCHO anticipated that LHD staff and students would be key users of Profile-IQ, and the initial patterns of use of Profile-IQ support this expectation. As of the end of 2012, around 43% of users are LHD staff and 31% are students. The staff at nonprofit organizations, including NACCHO staff, comprise the next largest user category at 13%. Users have employed Profile-IQ for several purposes. Public health professionals in Ohio used ProfileIQ to explore the question: Can merging 2 or more county LHDs to create economies of scale save these LHDs money and bring more federal and state funding?26 This led to more in-depth research on this topic. A faculty member at a school of public health described using the system in a variety of ways: in classroom exercises to acquaint students with LHD services and what data are available; to quickly generate statistics to use for background in writing grant proposals; to conduct basic analyses; to explore data to select appropriate methodology; and to perform analyses for independent verification of basic analysis results.26 ProfileIQ is a step forward in the recent health informatics movement of data liberation and use of open-source information technology solutions to promote public health.
REFERENCES 1. Choo CW. The knowing organization: how organizations use information to construct meaning, create knowledge and make decisions. Int J Inf Manage. 1996;16(5): 329-340. 2. Riley W, Parsons H, Duffy G, Moran J, Henry B. Realizing transformational change through quality improvement in public health. J Public Health Manag Pract. 2010;16(1): 72-78.
❘ 173
3. Riley W, Lownik B, Halverson P, et al. Developing a taxonomy for the science of improvement in public health. J Public Health Manag Pract. 2012;18(6):506-514. 4. Riley WJ, Lownik EM, Scutchfield FD, Mays GP, Corso LC, Beitsch LM. Public health department accreditation: setting the research agenda. Am J Prev Med. 2012;42(3):263-271. doi:10.1016/j.amepre.2011.10.021. 5. Yasnoff WA, O Carroll PW, Koo D, Linkins RW, Kilbourne EM. Public health informatics: improving and transforming public health in the information age. J Public Health Manag Pract. 2000;6(6):67-75. 6. Willard R, Shah G, Leep C, Ku L. Impact of the 2008-2010 economic recession on local health departments. J Public Health Manag Pract. 2012;18(2):106-114. 7. Chen LW, Jacobson J, Roberts S, Palm D. Resource allocation and funding challenges for regional local health departments in Nebraska. J Public Health Manag Pract. 2012;18(2): 141-147. 8. Kuehnert PL, McConnaughay KS. Tough choices in tough times: enhancing public health value in an era of declining resources. J Public Health Manag Pract. 2012;18(2): 115-125. 9. Institute of Medicine. For the Public’s Health: Investing in a Healthier Future. Washington, DC: National Academies Press; 2012. 10. Novick LF. Local health departments: time of challenge and change. J Public Health Manag Pract. 2012;18(2):103. 11. Love D, Shah GH. Reflections on organizational issues in developing, implementing, and maintaining state Webbased data query systems. J Public Health Manag Pract. 2006;12(2):184-188. 12. Friedman DJ, Parrish RG. Characteristics, desired functionalities, and datasets of state Web-based data query systems. J Public Health Manag Pract. 2006;12(2):119-129. 13. National Association of County & City Health Officials. 2010 National Profile of Local Health Departments. http://www.naccho.org/topics/infrastructure/profile/ resources/2010report/index.cfm. Accessed November 12, 2012. 14. Ostrovsky A, Katz M. The San Francisco Community Vital Signs: using Web-based tools to facilitate the Mobilizing for Action through Planning and Partnerships process. J Public Health Manag Pract. 2011;17(5):457-471. 15. Rudolph BA, Shah GH, Love D. Small numbers, disclosure risk, security, and reliability issues in Web-based data query systems. J Public Health Manage Pract. 2006;12(2): 176-183. 16. Gjelsvik A, Buechner J. States’ approaches to implementing a Web-based data query system. J Public Health Manag Pract. 2006;12(2):170-175. 17. Simoes EJ, Land G, Metzger R, Mokdad A. Prioritization MICA: a Web-based application to prioritize public health resources. J Public Health Manag Pract. 2006;12(2):161-169. 18. Grigg M, Alfred B, Keller C, Steele JA. Implementation of an Internet-based geographic information system: the Florida experience. J Public Health Manag Pract. 2006;12(2): 139-145. 19. MySQL. MySQL Cluster 7.2. http://www.mysql.com. Published 2012. Accessed September 15, 2012.
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
174 ❘ Journal of Public Health Management and Practice 20. Google Developers. Google Web Toolkit. https://developers .google.com/web-toolkit. Published 2012. Accessed September 15, 2012. 21. Apache Commons. Commons Math. http://commons. apache.org/math. Published 2012. Accessed September 15, 2012. 22. Google Developers. Google Chart Tools. https:// developers.google.com/chart. Published 2012. Accessed December 13, 2012. 23. National Association of County & City Health Officials. Profile-IQ. http://profile-iq.naccho.org. Published 2012. Accessed September 11, 2012.
24. Bonaccorsi A, Rossi C. Why open source software can succeed. Res Policy. 2003;32:1243-1258. 25. Hamdaqa M. Cloud Computing uncovered: a research landscape human errors. Adv Comput. 2012;86:4184. http://www.stargroup.uwaterloo.ca/∼mhamdaqa/ publications/Cloud Computing Uncovered.pdf. Accessed March 24, 2013. 26. National Association of County & City Health Officials. Profile-IQ: online data query system. http: //naccho.org/topics/infrastructure/profile/resources/ /ProfileIQ051212/lib/playback.html. Published 2013. Accessed March 24, 2013.
Copyright © 2014 Lippincott Williams & Wilkins. Unauthorized reproduction of this article is prohibited.
