Test Case Title
TC1.7 - Dashboard Builder getting data from data sources
If I am ToolAdmin can:
This feature is accessible only for OnPremise solutions or for RootAdmin in the oncloud solution
The user is registered and logged in the system
Using a PC or Mobile with a web browser. Accessing to a number of data sources to connect them with the Dashboard Engine.
Expected successful result
In Dashboards of Dashboard Builder, each Widget can use one or more Metrics. Metrics are defined by creating specific queries on Data Sources. The Data Sources are defined via the Dashboard Builder and used by the Dashboard Engine for collecting Metrics values. Possible Data Sources for the Dashboard Builder are: MySQL, RDF store in SPARQL, Mongo, Hbase with Phoenix, etc.
Dashboards are capable to explore data/sources of several kinds, for example:
- Historical data with all the above-mentioned widget kinds;
- Maps: ServiceMap, ServiceMap3D, Open Street Map, ...;
- Entities and relationships via ServiceMap, ServiceMap3D, LOG, FLINT;
- Composed Dashboards from Dashboard Builder;
- Composed Dashboards from NodeRED applications dashboards.
Data Sources are feed by processes for:
- Data gathering, harvesting, crawling, etc., typically implemented in ETL, NodeRED, Java.
- Data Analytics: making predictions, estimating OD matrices, estimating trajectories, etc., typically implemented in R, Java, ETL, Python, etc.;
- IOT applications that may generate some results, implemented in NodeRED.
- External tools providing data views directly into the dashboard widget.
To add a new data source to the Dashboard Builder tool, please follow the following step:
- Assuming to have a MySQL DB at 192.168.0.1 with database named "myDB" accessible with user "user" and password "password", first of all to define in the dashboard system this "datasource" is needed.
- Enter in snap4city portal and login with TootAdmin credential.
- In the left main menu column, click on Settings à Dashboard Data Sources
To add a new data source click the button on the top right;
- The following are the fields to be filled.
- These parameters are used by the data ingestion process that import the data and are necessary to connect to the data source of every active metric
- Name: unique identifier of the data source
- URL: the complete URL of the server to call. This has to include eventual parameters (e.g. protocols, port, specific parameters, etc.)
- Database type: e.g. MySQL, POSTGRES, SQLite, etc.
- Database name: name of instance in the schema
- Database username
- Database password
- A new pop-up is opened with two tabs to be filled as shown in the following picture:
- Name: SMap
- URL: jdbc:mysql://192.168.0.1:3306
- Database type: MySQL
- Database name: myDB
- Database username: user
- Database password: password
- Click Confirm to save the new data source. A confirmation message is shown.
- If the data filled in the form are wrong, an error message is shown.
- In case of error, click the EDIT button of a data source to open the form, check and correct the data.
- After the setting of the data source it is necessary to define the corresponding metric(s) associated to the data.
- On the main left column menu, click on Settings --> Dashboard metrics
- Click the button on the top right to add a new metric.
- Assuming to have a table "MyData" in the myDB database where almost every 10 minutes a new row is added with some data, to define a metric with the current row count, the following are the two tabs to be completed to define a metric.
- Tab General
- Metric name: mydatacount
- Short description: OPTIONAL (for example “mydatacount”)
- Full description: OPTIONAL (for example “mydatacount”)
- Result type: Float
- Update frequency: 0h 10m 0s
- Data source type: SQL
- Negative values: No
- Ingestion process: Main process
- Same data alarm count: Not active
- Storing data: Yes
- Old data evaluation time: Not active
- Ingestion agent method: Numeric
- Time range: No
- City context: No
- Tab Datasources & queries
- Data source 1: SMap
- Data source 2: None
- Data source(s) description: Optional
- Data area(s) description: Optional
- Query 1: select count(*) from MyData
- Query 2
Click Confirm to save the new data source. A confirmation message is shown.
The following are the fields to be filled:
- Metric Name: unique identifier of the metric
- Short description: brief description (not mandatory)
- Full description: not mandatory
- Result type: type of data produced by the metric (the following are the valid possible values: 'Text', 'Percent', 'Table', 'Integer', 'Float', 'Percent/285', 'Percent/83', 'Percent/757', ‘Web server status’)
- Update frequency (hh:mm:ss): interval in that represents each time the new data is downloaded for that metric. It is the update interval.
- Data source type: type of data source (‘Sparql’, ‘SQL’, ‘Web server (status)’)
- Negative values: set to YES if the metric generates negative numeric values.
- Ingestion agent: ‘Main process’ or ‘Web servers testers’.
- Same data alarm count: number of consecutive times with constant data beyond which an alert is triggered for constant data too many times
- Storing data: Set to YES if the metric stores the data.
- Old data evaluation time: the time after which the most recent data acquired for this metric is considered too old and therefore a too old data alert is triggered
- Ingestion agent method: method that execute the data import in the process (‘Numeric’, ‘Percent’, ‘Table’, ‘API’, ‘ATAF ride (specific)’, ‘Sce on nodes (specific)’, ‘Parkings (specific)’, ‘Wifi operatives (specific)’, ‘SmartDS (specific)’, ‘Tweets/Retweet (specific)’, ‘Not defined’).
- Time range: ‘Yes’, ‘No’. Deprecated, it is referred to specific metrics.
- City context: Set to YES if the metrics has a parameter referred to a municipality (for example the weather widget allows to set the municipality).
Datasources & queries Tab
- Data source 1 and 2: indicates one or two of the data source available. Pointing to a DB that resides on a PC.
- Data source description: optional
- Data area description: informal description of the relevance area of data sources
- Queries 1 and 2: The queries that are executed on the data source to find the most updated data of the metric.
Once the metric has been created, it is possible to start the java process, and, if all is ok, it isgin to populate the "dashboard.Data" table. So that it is possible to define a new dashboard with a widget showing the acquired value.
In case of problems in editing the metric, it is possible to modify the Descriptions table directly from the db. Please. remember to restart the java process after every metric update.
In the Dashboard Builder tools there are some predefined metrics that are placeholders (e.g. Button, clock, separator) they are not used for data ingestion and allow using some widgets that are not directly related with external data. Using these predefined "metrics" you can define a dashboard even without a running java process.