It’s well known that data analytics helps businesses solve some of their most-complex problems. What’s lesser known, but equally exciting, is that analytics can also be used to help governments solve some challenging issues related to public services, population trends, public safety, and more. Using public information to generate dashboards can lead to some really cool insights about human behavior, and teach us something about the way we interact with the world around us.
To show just how impactful this type ofinsight can be, let’s take a look at a dashboard I created using publicly-available data on traffic incidents in Spain.
- Finding the Data
I pulled data from the website of the organization that regulates traffic in Spain, the DGT. The data shows real-time information, but it does not provide information on the most affected communities, the roads that present the greatest number of problems, and total area affected. To get these data points, I had to pull in a few other public sources.
- Wrangling the Data
Using MicroStrategy’s data import capabilities, I connected to my chosen data sources, and established relationships between corresponding information categories. Then, I parsed the information in the descriptions of the incidents to obtain the name of the road, population, number of kilometers affected, etc. By doing this, I was able to create all the necessary dimensions for my visualization using the create attributes capability in MicroStrategy.
Finally, I created a table with the GPS coordinates of every Spanish city and town, by using MicroStrategy’s data blending feature. This ensures the data will be plotted accurately on a map.
- Building the Dashboard
When visualizing traffic incidents by location, a map of Spain is the most intuitive choice. Bar charts are also an asset, as they can easily show the number of kilometers, incidents, etc. based on different filter criteria.
Beyond that, I wanted to draw attention to the neural chart that relates cases with types of incidents. In a visual way, this chart helps us analyze where prevention has to be improved to have the greatest impact on reducing the number of accidents.

- Deriving Insights
Looking at the data, it’s easy to see that the Mediterranean coastal region generally has the highest volume of traffic incidents. The resulting traffic, caused by other drivers slowing down to view accidents, compounds and creates additional congestion. These insights can help the government identify where to invest more in infrastructure and personnel to more efficiently avoid future traffic incidences and boost satisfaction. Check out the dashboard yourself and see what other insights you can draw.
Want to learn more about how data visualization can be used to draw insight from public information? Check out our white paper on digital transformation in municipal government.
Senior Product Marketing Manager at MicroStrategy
Ms. Jain is a Senior Product Marketing Manager at MicroStrategy. Her responsibilities include defining product strategy and positioning for MicroStrategy BI software product line, working closely with the Technology team to drive product development and roadmap, support the global sales team, and tracking the BI market. She first joined MicroStrategy in 2004 as a Tech Support Engineer, gradually becoming a Premium Support Engineer (PSE). From 2007 to 2010, she worked as a BI architect at World Wrestling Entertainment. She re-joined MicroStrategy in 2010 as a Senior Product Manager before moving to her current role in 2015. Ms. Jain graduated from University of Maryland with a BS in Computer Science. Her passions include photography and writing reviews on Yelp.