cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Data to Properties for Time Series chart or plotting directly from a SQL query

Participant

SQL Data to Properties for Time Series chart or plotting directly from a SQL query

Hi,

I have a database table were I keep the values of several parameters from aa machine on the shopfloor: temperature, current, quench flow, cycle time, etc. and a timestamp. Something like this:

idtimestamptemperaturecurrentquenchflowcycletime
1valuevaluevaluevaluevalue
2valuevaluevaluevaluevalue

I have a simple SQL query service that return the data in an Infotable with a simple SELECT statement. And, if I send this result directly to a Time Series chart (I use the timestamp column for the X axis) I can plot the data for all the variables in the same graph.

This is OK as test, but I´m wondering if I there is an option to provide a way to select just one of the columns from the SQL query, perhaps in a list and then use this to feed the Time Series chart and show only the data for that field.

I can iterate the Infotable, assign the values to the properties of a ThingHelper, mark this properties as logged to a Value Stream and after that I can use a QueryPropertyHistory service to get the data and plot to the TimeSeries chart. But I don't like this approach because it creates a copy of the data in Thingworx.

Anyone has ever tried this? O if you can give me some directions or examples I would really appreciate it.

Thans

3 REPLIES 3

Re: SQL Data to Properties for Time Series chart or plotting directly from a SQL query

Hi Omar, sorry i'm not completely clear of your statement that "This is OK as test, but I´m wondering if I there is an option to provide a way to select just one of the columns from the SQL query, "


Do you mean to select 1 column's output from the your select statement? May be with some database's materialised views you can completely restrict pulling of data from the Database which you don't intend to use in ThingWorx. Reason why i mentioned Views contrary to simply doing select on individual columns is for the performance, as these are precomputed in database. This way you will only get the data that you intend to use in your graph and you can use several of those views wrapped within a JS service

pseudo code for it could be something

if (option == 1){

me.CallToMaterializedView1SQLService;

} else if (option == 2) {

    me.CallToMaterializedView2SQLService;

    } else {

              me.CallToMaterializedView3SQLService;

              }

Feel free to correct me if you think this isn't what you are looking for.

Re: SQL Data to Properties for Time Series chart or plotting directly from a SQL query

Hello Omar,

I don't know if I get it right. But you can do two aproaches:

- SELECT just the columns you want by the SQL Query in your service.

Or

- In the widget configuration, you can select the data to shown, select one or more columns of your infotable to be show in the Times Series Chart.

TSC.png

Re: SQL Data to Properties for Time Series chart or plotting directly from a SQL query

Hi,

Thanks to all for your feedback.

What I was trying to do was to feed the Time Series Chart with the result from a SQL query to plot the data. At first I was retrieveing all the data columns in a single query, but since there are diferences between the value ranges of the variables the chart looks awkward also the number of variables to plot  is greater than the 8 time series that this widget support.

At the end I created a SQL service that takes the variable/property name as a a parameter to retrieve only the data for that column from the database and pass the result to the chart, that way the service is dynamic and I´m only using one chart to plot all the variables.

I'll try a solution similar to what Sushant Pandey proposes (there is no materialized views on MySQL/MSSQL) in the meantine I think that I'm OK with the result so I'm going to close this thread.

Again thank you for all you suppport