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

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: