1

I have fault data in a database in the form of:

faultid | faultcode | startdatetime | enddatetime

Although the devices are polled every few seconds the entries in the database are only updated when the state of the device changes and so I might get a fault that lasts for minutes, hours or even days.

I want to chart the fault time as minutes against time in intervals, e.g. 30 min, 1 hr, 1 day intervals; such that a fault lasting several intervals will grow width-ways in the chart over time rather than just as a huge singular bar at the startdatetime. I would like to do this in a platform independent way if possible.

The charts-reports are run and viewed on demand from a Java application.

I tried googling for similar problems/solutions but either I'm googling the wrong stuff or not many people do this sort of thing or its more obviously done some way that I'm not getting -- am I going about it all wrong?

Things Tried:

I use BIRT for reporting, it alone does not seem capable to massage the data into the form I need for the chart I need unless I were to get knee-deep into BIRT script -- however the documentation and resources thus far have not been sufficient to allow me to even get close to doing this - any ideas?

I considered using BIRT's connection afterOpen script to perform custom SQL operations first but again the lack of usable documentation and resource made it seem impossible to do here.

In one instance I used a SQL Server stored procedure to produce the fill-in data, creating interval data where the enddatetime - startdatetime > interval. This procedure was called every time the report was called. This worked well but I would need to maintain a stored procedure for each of the different databases / reports I use and feel that there must be a better way.

We use Pentaho Kettle for data archiving, I tried using that to perform the data filling, however it seemed impossible to get Kettle to generate such data - any ideas?

I have considered generating the fill-in data from the Java application but that means that the application would need to know too much about the report in order to run it -- I want better abstraction that that so UI developer and report designers are totally separated.

Thanks.

Rob
  • 11
  • 2
  • Where's the larger problem? Is it guessing that _now_ is the `enddatetime` for faults when the unit hasn't reported back that it is healthy? Or is the problem something to do with creating multiple lines of `startdatetime` and `enddatetime` that are exactly aligned with the reporting period (30 mins, 1 hr, 1 day..)? – sarnold May 30 '12 at 00:15
  • @sarnold the problem is the latter -- multiple lines of startdatetime and enddatetime (or the effect of such). – Rob May 30 '12 at 00:22
  • i can't see why kettle shouldnt be able to do that. Just generate some data for your periods, and then do a lookup from your fault data to see if a fault exists within that period. ( Adding as a comment not an answer because I'm not 100% sure I understand the issue! ) – Codek Jun 11 '12 at 07:46
  • @Codek I could not get kettle to do it. I would have thought that kettle would have been ideal here too but the main problem I found in kettle is that there is no way to perform a loop. For example, I would need to loop for every 30 minutes between startdatetime and enddatetime to insert filler rows at each interval. – Rob Jun 12 '12 at 02:40
  • Just use a generate rows step to generate that data. Or even a query from some sort of data source. I do exactly this sort of thing to find out how many jobs are running on the cluster at any given time - I generate rows, calculate the time offset and then query the log table to see which jobs were running at that point in time. – Codek Jun 12 '12 at 06:40
  • @Codek I still do not see how this would work. How do you generate the correct number of rows with generate rows as it takes no inputs and the limit is static. – Rob Jun 28 '12 at 02:16
  • 2 options: Generate the rows with a query of some sort. Or calculate how many rows you need on a previous transform, and set the value into a variable, then use that in the generate rows step – Codek Jul 02 '12 at 10:50

0 Answers0