Turning Public Information Into Powerful Insight

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.

1. 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.

2. 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.

3. 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.

   

4. 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.