Skip to main content
H
hvanloggerenber1-Visitor
1-Visitor
July 6, 2017
Solved

How to analyse data in a data table ?

  • July 6, 2017
  • 1 reply
  • 3422 views

I am fairly new to ThingWorx and is still figuring my architecture on a project.

I have for now a data table.

My devices loads data into this table (as we speak).

I need to manipulate this data and draw statistics from it and write this stats into other data tables.

The stats I will then display in a mashup.

1. How do I write a service that will extract data from the data table (these will be similar to writing queries in a SQL DB)?

2. How will I add new sets of data into data tables (not row by row)?

3. Where can I find documentation to assist the above?

Best answer by supandey

Hi Hein, you could use out of the box services provided by the DataTable entity. The format is JS sort of similar to SQL but its not SQL. There are several services you can call to perform CRUD operations on the data in DataTable thing, you can check the method  descriptions in the APIDocumentation, here's quick screenshot

You can access this documentation from the ThingWorx Composer > Help > Documentation > API Documentation > ThingWorx API Documentation

What you may need to consider if you are planning to store large amount of data in the DataTable (over 100,000 rows) is the performance which may reduce in that case you may want to consider using a Table in the Database via an external database connection. I think this blog should help you decide Where Should I Store My Thingworx Data?

1 reply

S
supandey5-Regular MemberAnswer
5-Regular Member
July 6, 2017

Hi Hein, you could use out of the box services provided by the DataTable entity. The format is JS sort of similar to SQL but its not SQL. There are several services you can call to perform CRUD operations on the data in DataTable thing, you can check the method  descriptions in the APIDocumentation, here's quick screenshot

You can access this documentation from the ThingWorx Composer > Help > Documentation > API Documentation > ThingWorx API Documentation

What you may need to consider if you are planning to store large amount of data in the DataTable (over 100,000 rows) is the performance which may reduce in that case you may want to consider using a Table in the Database via an external database connection. I think this blog should help you decide Where Should I Store My Thingworx Data?

H
hvanloggerenber1-VisitorAuthor
1-Visitor
July 6, 2017

Thanks Sushant

On what type of model would such a system work?

I would guess the bulk of data is loaded into an external database.

From this data you then derive smaller sets of data which can then be loaded into data tables and mashups can visualise this data.

The "external system" should be responsible for populating this data into the TWX data tables.

S
supandey5-Regular Member
5-Regular Member
July 6, 2017

The external data store, which basically connects via JDBC driver to the ThingWorx server, can be queried upon directly. Meaning you'll need to create a Thing of the template type which basically defines the connection properties for that particular type of external data store/ external database and then you can create SQL services directly under that Thing via which you can perform CRUD operations on the database table directly and you can use these services to pull data directly on to the mashup.

For e.g. i have created a Thing of type OracleDBServer12 which basically is Template i imported from ThingWorx Marketplace extension called Relation Databases Connectors which contains JDBC drivers for connecting to some of the widely used RDBMS, here's screenshot from the extension containing following JDBC connector

Under this thing I can create Services of types SQL Query or SQL Command which can perform the CRUD operation. I have covered this in more detail in this blog How to configure Oracle 12c's Pluggable Database as External DataStorage for ThingWorx

In short when using external databases for storing your data you can create your own SQL services and which can directly output the data onto your Mashup. There's no need to first pass this data to a ThingWorx's DataTable entity to be able to display that data onto the mashup.

Hope this helps.

Terms & ConditionsCookie settingsAccessibility statement