Infotable Type : Is data table

[IA_10816220]

Hi,

i was going to use info tables to store data like the max and min water level per hour , every hour new row is add to the info table with the max and min value of the level , all of this is fine but i know that info tables use ram in case of persist and log, and i want to do both, so i am thinking of selecting "infotable type" option to set it to "Is data table" because i think my thing worx version does not has the datatable creating option, so does this option "Is Data Table" will make this infotable property act as datatable and store the values in the database and not the ram ? also should i select persist and log normally or there is something here ?

[Rocko]

1) It rarely makes sense to persist AND log. You should consider using a Value Stream for this type of data and only log them.

You will have to create a value stream entity, assign your thing to that value stream, and then set the property to "logged".

This will also allow you to query historical values of the water level e.g. by using QueryPropertyHistory service.

Do not store Time series data in a Data Table.

Here's a guideline when to use which storage mechanism. Also read this.

Reminder: Whenever you assign create a value stream, also think about when to remove (purge) data from the value stream to avoid filling up disk space.

2) As the popup on the question mark explains "Is Data Table" will only add a couple of standard fields which come with a datatable to the property:

TBH I never used this on a property but only in the result definition of a Service, when you want to return rows from a Data Table to be used in a mashup. In this case "Is Data Table" controls if you can see the DataTable standard columns (key, location, source, source type, tags and timestamp) in the "Returned Data" of the service in the mashups Data tab.

[IA_10816220]

so from what I understand I should only use log and only use info table to store the data needed to be shown on mashups ?

and if i selected "is data table" this doesn't mean it will be stored in database or hard desk but it will also store in ram ?! because it will help me a lot  if i can store it in database and not memory , i am planning on storing the data with the timestamp i want without considering the timestamp it is stored in the database with, 

[Rocko]

I would not use Infotable at all but keep your max and min water level per hour as individual, logged properties on the thing.

"is data table" does not really define how your values are stored. A Data Table is an entity of its own, it is a Thing with Base Thing Template "DataTable". You would have to create it explicitly and insert into it explicitly.

With Value Streams you don't have to care about that, when you update the property value is it written to the database with the current timestamp automatically.

[Constantine]

Hello, I'll try to visualize how it works.

When you create a logged property on your thing ("TestThing" in my case), and then change its values (here I changed it to 1, 2 and 3):

ThingWorx saves those changes into its database, like this:

To query this data you call QueryNumberPropertyHistory on the same thing:

There are no Infotables involved, those are simple NUMBER properties, as @Rocko suggests. Also, don't forget to associate a ValueStream with this thing ("VS" in my example).

/ Constantine

[IA_10816220]

great,

my final objective is to create a line chart that will show the max and min values per day for the past 30 days, so if i am just storing the max min value every time they change per day i will have a lot of max min values for each day, my idea was to store the max and min values in infotable and when thy change i will over ride them at the same row in the info table, and every new day i add a new row in the info table , now i understand that i shouldn't do that because of the RAM, so how i can achieve results like this so that when i query the max min values for the past 30 days i get only 30 values for max and 30 values for min , one for each day, how i do this override thing ?

thank you for your help 

[Rocko]

One solution could be to create 4 logged properties:

min_level_per_hour,max_level_per_hour

min_level_per_day,max_level_per_day

what is the min/max per day can only be known after the day. so create a scheduler that runs every night past midnight, runs QueryPropertyHistory on the per_hour properties, using start and end of previous day as interval limits. with the min max value from the result, update the per_day variable.

In your chart, run QueryPropertyHistory  against the per_day properties.

[Constantine]

Edit: Disregard this, it will create too many changes to "me.dailyMax", not what you need.

If you need min/max values during the day and don't want to wait till it's over: Create a STRING property called "lastToday" and a numeric "dailyMax". Then create a DataChange subcription to your "real-time" min/max properties. In that subscription do something like that:

// Assuming that "max" variable contains the new "real-time" max value
let today = dateFormat(new Date(), "yyyy-MM-dd");
if (me.lastToday !== today || me.dailyMax < max) {
    me.dailyMax = max;
    me.lastToday = today;
}

With this solution you pre-aggregate data as it arrives, and there's no need for schedulers and queries.

[Constantine · Accepted solution]

Depending on the number of your sensor readings, a straight query might work well. For example, if you get one reading per hour, then this query takes ~30ms on my laptop:

let rawData = me.QueryNumberPropertyHistory({ 
    propertyName: 'max',
    maxItems: 720,
    // TODO: Add date filters here
});
let withDates = Resources["InfoTableFunctions"].DeriveFields({
	types: 'STRING,NUMBER',
	t: rawData,
	columns: 'date,value',
	expressions: 'timestamp.toLocaleDateString(),value'
});
let result = Resources["InfoTableFunctions"].Aggregate({
	t: withDates,
	columns: 'value',
	aggregates: 'MAX',
	groupByColumns: 'date'
});

With this you don't need to create extra properties, schedulers, subscriptions, etc.