July 12, 2012 at 12:43 pm
I have many tables in my database, each one representing telemetry data from a sensor, with each row having a timestamp (datetime), a ref to a log file the data came from, and some data. The data can be different, with some sensors reporting one data value per packet, and others reporting multiple values per packet, so we can't put them all in the same table directly - and we have a rule that we will store in the original format, all data coming from the telemetry system. We need to do analysis on the data after the telemetry is gathered, so what I have done is to create a rollup table that gets the most recent value from each source for a series of times. The problem is that the telemetry sources aren't synchronized, so one source might report at time 0, and the next one comes in at time 0.1, then the third source will report at time 1.0 and the first one will report again at time 1.0...
I can not post code, because of security, but I can show sample data if it will help. I'm not really looking for code anyway, I'm looking for a discussion of how would be the best way to analyze telemetry data from multiple sources which are not time-synced.
What I did was basically this... I look at each test run and figure out the timespan, then I create a row in the rollup table for each second (or half second or 0.1 seconds or whatever) and then I fill in the most recent data value from each source, which occurred before the rollup timestamp.
Here's my rollup table
CREATE TABLE dbo.TelemetryFramesRollup(
LogFileGuid uniqueidentifier NOT NULL,
FrameTime datetime NOT NULL,
FrameNumber int NOT NULL,
Sensor1 float NULL,
Sensor2 float NULL,
Sensor3 float NULL,
Sensor4 float NULL,
Sensor5 float NULL
)
This is my stored procedure - it's the big update at the end which I'm worried about, and the general question of whether this is a good method to produce data for subsequent analysis. This is aerospace data, so it's speeds, temperatures, positions of servos, etc. I have not been given a clear picture of what type of analysis will be done, but one big query is apparently "show me all the other telemetry from when temp sensor 5 was at a particular value" - so like, they want to see the airspeed and the camera shot and the temperature from all the times when the X servo was at 20 degrees. I think my rollup table will work for that - it will be imported into MatLab probably, so it needs to be fast at barfing up the rows because MatLab will be doing serious analysis on the little workstations people have.
ALTER procedure [dbo].[CreateTelemetryRollup]
@LogFileGuid uniqueidentifier,
@FrameIntervalSeconds decimal(5, 2) = 1.0 -- one second default
AS
declare @BeginTime datetime,
@EndTime datetime
-- first, update the summary data for this log file
if exists (select 1 from dbo.LogFileSummary where LogFileGuid = @LogFileGuid) begin
update LogFileSummary
set BeginTime = dbo.LogFileMinDate(LogFileGuid),
EndTime = dbo.LogFileMaxDate(LogFileGuid)
where LogFileGuid = @LogFileGuid
end else begin
insert LogFileSummary(LogFileGuid, BeginTime, EndTime)
select @LogFileGuid, dbo.LogFileMinDate(@LogFileGuid), dbo.LogFileMaxDate(@LogFileGuid)
end
--grab the timespan in temp vars for later
select @BeginTime = BeginTime,
@EndTime = EndTime
from LogFileSummary where LogFileGuid = @LogFileGuid
--delete the existing data for this log file
if exists (select 1 from dbo.TelemetryFramesRollup where LogFileGuid = @LogFileGuid) begin
delete dbo.TelemetryFramesRollup where LogFileGuid = @LogFileGuid
end
--populate the list of timestamps first
if (@FrameIntervalSeconds < 1) begin
insert TelemetryFramesRollup (LogFileGuid, FrameTime, FrameNumber)
select @LogFileGuid, DATEADD(millisecond, N * 1000.0 * @FrameIntervalSeconds, @BeginTime), N
from Numbers where DATEADD(millisecond, N * 1000.0 * @FrameIntervalSeconds, @BeginTime) < @EndTime
end else begin
insert TelemetryFramesRollup (LogFileGuid, FrameTime, FrameNumber)
select @LogFileGuid, DATEADD(second, N * @FrameIntervalSeconds, @BeginTime), N
from Numbers where DATEADD(second, N * @FrameIntervalSeconds, @BeginTime) < @EndTime
end
--now load up each of the sources
update TelemetryFramesRollup
set Sensor1 = (select top 1 SensorData
from dbo.SensorData_Temp1
where LogFileGuid = @LogFileGuid
and TelemetryTimestamp <= FrameTime
order by TelemetryTimestamp desc),
Sensor2 = (select top 1 SensorData
from dbo.SensorData_Temp2
where LogFileGuid = @LogFileGuid
and TelemetryTimestamp <= FrameTime
order by TelemetryTimestamp desc),
Sensor3 = (select top 1 SensorData
from dbo.SensorData_Temp3
where LogFileGuid = @LogFileGuid
and TelemetryTimestamp <= FrameTime
order by TelemetryTimestamp desc),
Sensor4 = (select top 1 SensorData
from dbo.SensorData_Temp4
where LogFileGuid = @LogFileGuid
and TelemetryTimestamp <= FrameTime
order by TelemetryTimestamp desc),
Sensor5 = (select top 1 SensorData
from dbo.SensorData_Temp5
where LogFileGuid = @LogFileGuid
and TelemetryTimestamp <= FrameTime
order by TelemetryTimestamp desc)
where LogFileGuid = @LogFileGuid
Please let me know if I left anything out that might help understand the situation. I don't want to post too much, this is a high security kind of thing - but it is the same as any general case of collecting telemetry from multiple sources. I did change the column and table names - our real names are more descriptive.
July 12, 2012 at 1:27 pm
The key to something like this is, what will the data be used for, by whom, and what would they prefer as far as rules for matching sources to time?
Something like this usually isn't a technical decision, it's a business-use decision. Would they prefer the most recent row from each table? Would they prefer the data from two of the tables that matches the state it was in when the third table got its data? Would they prefer some sort of x-second aggregate from each? A rolling last-x rows from each?
It's really going to be up to the people who will use the data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 12, 2012 at 3:49 pm
Well, I don't know that level of detail unfortunately. What I suspect will happen is they will just deal with what I give them. The camera images are critical (just got out of that meeting) and they are the main thing people are interested in. What they want to know is "when the camera took that image, what were the other telemetry values" - or "show me the image that happened after a certain value was collected"
I am giving them control over resolution, as you can see that's a parameter to the rollup procedure. I can't get them to agree on the rules for matching source data to frames. IMO, it should be whatever was the most recent value reported by the sensors before the start of a frame, but yeah we could do interpolation and a whole bunch of other methods.
I guess I'm mainly interested in whether people think my logic is correct in the proc. That is, does it actually pull the most recent telemetry value from each table? And then I'm interested in hearing from others about their experience working with this kind of data. I have worked with stock market data before, but for the most part, those data sources don't relate to each other, so it's easier than this stuff. I have worked with telemetry before, but it was synced up already - it reported packets containing all the telemetry sources together. So, I suppose what I'm trying to do is get this data into that format I'm familiar with, and I'm not positive that's the only way or the best way.
July 17, 2012 at 7:52 am
In that case, I'd say build what makes the most sense to you, document it clearly, and communicate it thoroughly, and then don't worry about it till some line-of-business person asks you to modify it or to give them options on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply