Challenge
The utility of a sub-second refresh rate is limited to monitoring use-cases. This, of course, is not BI, but there may be cases where a BI use-case also incorporates live streaming data to trigger an action or decision.
An example is if there is a power surge, a worker may want to immediately turn off the flow of electricity and then use BI to analyze what the cause of the surge was.
The challenge is presented when we try to query large bodies of data and ingest them with a dashboard quickly. With data sources like memSQL, it is certainly possible to query and display 100K records every second, but this requires a specific set of tools and configurations.
Solution
As a general user with a generic MSQL database but perhaps with a REST endpoint attached to IOT devices, it is certainly easier to make a query for the latest point of data and reflect this in the dashboard. This is precicely what we will do.
Creating The Base Widget:
This widget will need base data to create a Sisense widget. Once the widget is created we can apply a script to feed in live values.
There are three requirements to the base widget you should consider:
- How many values do you want to see in your chart
- The value the base widget will show before new values are passed in
BASE VALUES
It’s best to set the widget up with values that align with the data you will query. If you do not have data in your EC that corresponds with the live data you can feed in a hardcoded 0 into the value panel.
NUMBER OF VALUES INSIDE THE LIVE CHART
The number of values will stay consistent as more values are populated. As new values are populated, old values are dropped. Set the initial range of values by applying a filter to the X-axis and specify that you want to see the latest X units. In the example above, the chart will always display 30 units.
Setting The Widget Script
This script will do the following:
- Query a REST endpoint every X milliseconds
- Push the result into a line chart
- Update the Sisense Chart
Copy and paste the widget script below into a widget script. Save the script, refresh the widget, and apply it.
I recommend creating new functions for every widget you apply this on to avoid function conflicts.
COMPLETE CODE WITH COMMENTS
widget.on('domready', () => {
asyncCallForDeviceA();
})
var getNewDataPointForDeviceA = function(){
// assign a value to a variable "quit" in the console to stop getting new values
if (typeof quit == "undefined"){
var newValue;
var newTime;
setTimeout( () => {
//Get data with REST API
$.getJSON( 'https://api.exchangeratesapi.io/latest', function( json ) {
//get value
newValue = json.rates.AUD;
//get time at which the data was retrieved
newTime = new Date().toString().slice(4,24);
//update the widget data
widget.rawQueryResult.values.push(
[
{data:String(newTime), text:String(newTime)},
{data:newValue, text:String(newValue)}
]
);
//drop the first value
widget.rawQueryResult.values.shift()
//redraw the widget with the new value
widget.redraw();
});
}, 800); //800 means this function will occur every 800 milliseconds
}
}
var asyncCallForDeviceA = async function(){
await getNewDataPointForDeviceA();
}
6
Post actions
