Ready to learn Business Intelligence? Browse courses developed by industry thought leaders and Experfy in Harvard Innovation Lab.
In this post, I will give you a walkthrough of the capabilities and the development process of Infobarris, a product we’ve developed for the Public Health Agency of Barcelona (only available in Catalan).
Institutional motivation
One of the main purposes of the ASPB (Catalan acronym for the Public Health Agency of Barcelona) is to ensure the health of Barcelona residents and visitors through knowledge of the population’s health status and its determinants. We therefore have the authority to develop tools to determine health status and to support public health policies such as preventive health programs, among others.
The tool
Infobarris is a tool to support the analysis of health and its determinants in the neighborhoods of the city of Barcelona. It contains a set of indicators available at the neighborhood level, with reference to the values of the district to which that neighborhood section belongs and to the entire city of Barcelona. The information is organized in a set of tabs according to several health topics, based on our conceptual framework of health determinants and health inequalities in urban areas developed at the ASPB (Borrell et al. Journal of Epidemiology and Community Health, 2013, available here)
Available topics (brief description):
- Barcelona Urban Heart (Urban Health Equity Assessment and Response Tool), which represents the array of Urban HEART indicators presented as a tool for visualization of health equity made by the World Health Organization (WHO) in collaboration with some of the investigators at the ASPB.
- Population (depicted by sex, age group or place of birth).
- Physical context (age of housing stock).
- Socioeconomic context (percentages of low education and unemployment, truancy index, among others, for each sex).
- Sexual and reproductive health (fertility, abortion and pregnancy rates, among others, in distinct maternal age groups and birth regions).
- Health-related behaviours (percentages of regular smokers, self-assessed poor health status, obesity, and risk of poor mental health-related issues, for each sex).
- Drug abuse (index of problematic drug use, drug-related death rates, and drug therapy admission rates devised by sex or substance among other indicators).
- Notifiable diseases (overall, tuberculosis and HIV incidence rates).
- Mortality (life expectancy, mortality rates by cause of death and premature death rate, for each sex).
- Use of health services (percentages of healthcare coverage and dental visits).
Directions for use
1. Select a tab according to the available health topics.
2. Select the reference neighborhood on the map of Barcelona (top left map).
3. Use the drop-down menu to choose the category you want to view (by sex or other available options depending on the selected tab).
How did we do it?
0) A working group composed of public health experts from distinct backgrounds (statisticians, physicians, computer engineers, psychologists) proposed a series of indicators according to the conceptual framework of health determinants and health inequalities in urban areas (previously described) to devise a health scorecard for the city of Barcelona, taking the neighborhood section as the reference area to be evaluated.
1) To identify the required indicators and final visualization model for each health topic, we built a preliminary prototype using a mockup tool. Then, we scheduled a series of periodic meetings with the different key users (working group members) in order to discuss and envision the graphical requirements of the project using the preliminary prototype (Agile methodology).
3) From the outset, our BI platform (SISalut) was key to organizing all the business logic and became the central repository to be used to systematise the generation of the indicators that would feed the Tableau dashboards. We reused many of the indicators that were already defined in our BI platform. We ingested into it all new data sources (ETL, validation and normalisation process). Quite often, we had to reformat the indicators (perform some extra aggregations, recode a few variables, column pivot or unpivot operations, etcetera), in order to smoothly integrate the resulting indicators as Data Sources of Tableau, avoiding further data transformation drawbacks inside Tableau. As an example of data uniqueness we computed all population-related rates using the very same slices of Population OLAP cubes available from our BI platform.
4) We decided to use Tableau for developing the interactive dashboard because we were used to working with it in previous projects to obtain appealing data visualizations easily and quickly. In addition, Tableau has achieved a consolidated and valued market position at an affordable license cost. Hopefully, the development was boosted by the help of an external consultant (thanks to Synergic Partners) who focussed on the rough part of turning data into beautiful charts and maps, while facing some rather tricky features of Tableau. By the end of the third month after the beginning of the project, a working version was ready to be tested.
5) Finally it took us around 2 extra months to solve some problems (faulty rendering of maps, required use of Tableau Javascript API to synchronise the selected neighborhood across the dashboards), and to state the final arrangements of the visualizations as we waited for advice from other experts.
In conclusion
We have designed an online interactive dashboard to display the status of health and its determinants in the neighborhoods of Barcelona applying Agile methodology to support translating the requirements to the final product in a short period of time without major issues. This experience has helped us to acquire a thorough knowledge of Tableau that will be useful for the development of other dashboards.
We are now ready to concentrate on improving this data product while measuring its impact and value in the context of public health in the city of Barcelona. In addition, we are prepared to feed the final product every year with the incoming health data following the same data processing strategy: ingesting data into our BI platform, extracting the resulting indicators and using them as Tableau data sources. Thus, Tableau will sketch old and newly added data according to the selected year.
The amazing team behind the project:
+Project Management:
Glòria Pérez
Pere C. Llimona
+Product assessment:
Carme Borrell
Maribel Passarín
GlòriaPerez
Maica Rodríguez-Sanz
Pere C. Llimona
+ Multidisciplinary team:
GlòriaPerez
Maica Rodríguez-Sanz
Ana Novoa
Albert Espelt
Patricia G. Olalla
Teresa Brugal
Albert Prats
+Development:
Synergic Partners
Pere C. Llimona
+Valuable assessments:
Sergi Hernández
