Data Storage
Data Tables
A data table is similar to a standard relational database table, but differs greatly in terms of performance. Generally, data tables should be used if you have less than 100,000 rows of data. For larger datasets, a relational database should be used and then connected via a Database Thing template. A Data Shape defines the columns or fields of the data table.
 |
See Model and Data Best Practices for additional information on data tables.
|
Possible use cases for data tables are maintenance work orders for a site or crew, or production orders for a manufacturing line. Storing this data in data tables makes it easy to create a custom mashup for the consumers of the data.
A data table has the following predefined fields:
• Timestamp: The time the entry was created. It is also possible to provide a timestamp when adding a data table entry.
|
|
Each data table Thing service has a predefined timestamp common property of type DATETIME. If a data table is using a custom data shape that defines the timestamp field with a different data type, an error will occur when executing a query service.
|
• Tag: Each data table entry can be tagged. Data tags help to search for and consume specific run time data.
• Source: The source of the data table entry. This is usually the name of the Thing writing to the data table or an identifier of an external system.
• SourceType: The entity type of the source (such as Thing or user).
• Location: The location of the data table entry's source.
Data Table Templates
|
Name
|
Description
|
|---|---|
|
ContentCrawler
|
A content crawler Thing is used to call a service on another entity that returns an infotable of data that is then stored in the data table of the content crawler Thing. See Content Crawler for more information.
|
|
DataTable
|
Stores non time-series data. For more information, reference:
|
|
RemoteDataTable
|
Creates a local proxy object to a data table Thing that is running and persisting data on another ThingWorx server. For more information, see Remote Things.
|
Configuration of Index Settings
The configuration for a data table allows you to define additional table indexes. This is similar to a relational database table, where in addition to the primary key (the primary key is defined in the Data Shape), you need to query the table based on other fields. You should create an index for each set of filter criteria commonly used. This will have a significant impact on query performance.
|
|
When you add a new index or indexes, you must run the Reindex service on the data table in order to have the data indexed according to your new definitions. Verify that all custom index fields have values. The service will fail if a custom index field doesn’t have values. This may take a few minutes on a large data table.
|
Let's take an example of a sales order. A sales order data table might look like the following: SalesOrderID (primary key) CustomerName CustomerRegion OrderAmount SalesRep DueDate
When you create the Data Shape and define the primary key, the system will automatically create a table index for the SalesOrderID. But, in reality you probably query the table by other columns rather than the primary key.
|
|
The compound index has two field names separated by a semicolon. This is a required format, and should not contain spaces.
|
Example
For example, two common queries could be:
1. Get table data where CustomerName = 'Some Customer Name'
2. Get table data where SalesRep = 'rep name' AND CustomerRegion = 'Northeast Region'
For this example, you would create two indexes, one for each common query. The index name is up to you - it is a semantic name and not used in the query execution. So the indexes might look like the following: Index Name Index Field Names Customer CustomerName RepRegion SalesRep;CustomerRegion
Related Links