How to visualize data on Dashboards loaded from an Open Data Portal?

EN | ES

Datasources Creation

1. To represent the data in the dashboard, you can use an intermediate structure called "Datasource". A "Datasource" is a data abstraction from one or several ontologies, so you can define the necessary entities to be represented:

2. Create your first Datasource 1: City Historical Population

The first datasource will be the raw representation of all data in the ontology. In the "Visualization → My DataSources" menu,

click on +Create:

Then fill in the "identification" with CityHistoricalPopulation and "Datasource Query" fields. The query can be validated by using the "Execute query" button.

  
To create the datasource, press New.

3. Create Datasource 2: City Population 2015:

In this case, the datasource creation to show the 2015 population would be:

Create the datasource with the name "CityPopulation2015" by pressing New.

4. Datasource 3: SampleCitiesPopulation: Say you want to compare the population of three cities per year. To do so, filter by these cities to create a simpler work environment.

RAW DATA sample:

Datasource expected result for the RAW sample:

The datasource allows for the independence between stored information format and the visualization format. In this case, you can create the "SamplecitiesPopulation" datasource with the following query:

select
sum(D.HelsinkiPopulation) as HelsinkiPopulation,
sum(D.HamburgPopulation) as HamburgPopulation,
sum(D.CopenhagenPopulation) as CopenhagenPopulation,
sum(D.StockholmPopulation) as StockholmPopulation,
sum(D.BrusselPopulation) as BrusselPopulation,
year
from(
select 
(Case when C.CityPopulation.CityName ="Helsinki" then C.CityPopulation.Population else 0 end) as HelsinkiPopulation,
(Case when C.CityPopulation.CityName ="Hamburg" then C.CityPopulation.Population else 0 end) as HamburgPopulation,
(Case when C.CityPopulation.CityName ="København (Copenhagen)" then C.CityPopulation.Population else 0 end) as CopenhagenPopulation,
(Case when C.CityPopulation.CityName ="Stockholm" then C.CityPopulation.Population else 0 end) as StockholmPopulation,
(Case when C.CityPopulation.CityName ="Bruxelles-Brussel" then C.CityPopulation.Population else 0 end) as BrusselPopulation,
C.CityPopulation.Year as year
from CityPopulation as C where 
C.CityPopulation.CityName in ("Bruxelles-Brussel", "Hamburg", "København (Copenhagen)", "Stockholm", "Helsinki") group by C.CityPopulation.Year limit 500) as D 

 
The "Datasource query results" field will show the expected result.

5. Datasource 4: YearsForCityPopulation: The last datasource example will be creating a list with the population values per year. That query would be:

Gadget Creation

1. Gadget creation: Once the datasources are defined, create the data's graphical representation. Go to the gadgets in the Visualization → My Gadgets menu:

Then click on Create.

For this example, you will create four gadgets:

Gadget 1: Population 2015 Map: By using the datasource "CityPopulation2015" and selecting the map representation:

 

 
Fill in the fields as follows:

• Geographical coordinates: latitude and longitude.
• Point Id: "CityPopulation.CityName".
• PopUp: Fields shown when clicking in the pin: country, city and population.
• Name: Name of the represented data.

After selecting the fields, you can see a previsualization of the gadget: 

Once finished, press New to save the gadget.

Gadget 2: Historical Population: The next gadget will be a line graph:

After selecting Line, you must fill in two sections:

• Gadget Axis config: you can create as many scales as needed in the Y axis. This is very useful to represent different scale series. In this example, name it "Thousands of inhabitants".
• Measurements: it represents the series to be painted; in this example, years in X axis and population on Y axis.

The result should be:

Gadget 3: PopulationComparisonBar:

In the next gadget, comparative of different cities, select the "SampleCitiesPopulation" datasource and the graph type BAR:

The measures you will select are the temporal series of every city (Copenhagen, Stockholm and Helsinki). 

Gadget 4 : PopulationComparisonRadar: Another option to compare population between cities is through a RADAR graph. In this example, you will use the same datasource "SampleCitiesPopulation", and then you will define the same temporal series:
|

Dashboard Creation

1. Once the gadgets are created, you must create the dashboard on the Visualization → Dashboards menu:
   

Create a new dashboard "Historical Population".

By default, it will appear as a blank dashboard.

Different options to work with the dashboards are indicated in the bar tool:

The options are:

• • Add Gadget to dashboard (1).
  • Datalink: To use gadgets as filter for other gadgets (2).
  • Dashboard page configuration (3).
  • Save changes (4).
  • Toggle Menu: Shows or hides the lateral menu to navigate through the dashboard pages (5).

Every added gadget has its own properties that can be also modified.

Firstly, add the map gadget created in the previous step by clicking in add element and then drag the MAP element into the workspace:

Then, select the "Population 2015 map" gadget.

Once the gadget is in the screen, you can move it and scale it:

Then you can add the rest of the gadgets:

• LINE Gadget "Historical Population".
• RADAR Gadget "PopulationComparisonRadar".
• BAR Gadget "PopulationComparisonBar".

The result will be something similar to:

Now add a LiveHtml component that will be used as year filtering:

You can edit the LiveHtml component by selecting the year datasource:

For this element, remove the headers by clicking on the next icon: 

And then by deactivating the Enable widget header check.

 
Once you are done, you will have to link all gadgets to be used as filters among them, through the datalink functionality, in two examples:

Dashboard Filtering

1. Map as a filter for the line graph. Go to "Datalink" button and select:

• Source gadget → Population 2015 Map (map).
• Source Field → CityPopulation.CityName
• Target gadget → Historical population (line).
• Target Field → CityPopulation.CityName

Then press + to add the link.

2. LiveHtml as year selector for the Bar chart:

• Source gadget → Population 2015 Map (map).
• Source Field → CityPopulation.CityName
• Target gadget → Historical population (line).
• Target Field → CityPopulation.CityName

A navigation example would be: 

To remove the filter, you can press on the icon appearing in the filtered gadgets: 

By clicking on the icon, you can remove the filters: 

Once removed the content in the gadget will be updated.