3 Replies Latest reply on Sep 21, 2012 2:10 PM by Eric Meger

    Average Time Between inputs

    Eric Meger

      I have a large data set (500M+ records) of sensor data. If I take a subset of about 3 days worth of sensor data then I am working aith about 10M records.

       

      Each of about 100,000 sensors has a unique ID number and transmits a message to me periodically (ranging from seconds to minutes). My system only detects and records a subset of the transmissions from these sensors.

       

      What I need is to be able to get a figure of merit for our detection rate. I am looking now for average time between detections for each sensor.

       

      the tables have many fields but the relevant ones are:

       

      SENSORID

      DateTime OF TRANSMISSION

       

      Data is sorted randomly (according to arrival time at the database rather than the transmission times (which can be hours apart, random in sequence

       

      I'd like to be able to create a Viz (efficiently!) that would show daily or monthly average time between transmissions for each SENSORID

       

      I cannot seem do moving average differences on a Dimension like DateTime.

       

      Any suggestions would welcome!

        • 1. Re: Average Time Between inputs
          Alex Kerin

          You will have to use a table function, probably lookup() to get the prior value for an individual sensor. There are a few things to set on the partitioning and ordering, so it would be helpful if you could post a file with some of the data and expected result.

          • 2. Re: Average Time Between inputs
            Richard Leeke

            I really don't think you'll be able to do this with table calculations on that sort of volume of data. In fact I'm sure you won't.

             

            Table calculations rely on pulling all of the rows back to Tableau. The upper limit on the number of rows you can manage depends on various things including row size, but I really don't think you will manage even to retrieve 10 million rows (let alone 500 million) back to Tableau, let alone do table calculations on them.

             

            The are a couple of approaches I would consider for this, depending on how you are storing your data.

             

            If it's in a decent database with analytic functions available I'd look at doing the heavy lifting of the calculation in the database (possibly via a custom SQL connection, though that might place some constraints on the use of analytic functions).

             

            If you don't have a database with analytic functions, the only other way I can think of is to pre-process the data. Personally I'd probably do it by writing a perl script (though the random sequence of arrival might be a challenge with 500 M rows). Another option might be ETL tools - though that's not something I know anything about. Others on the forum know about them, though, so someone else may chime in.

            • 3. Re: Average Time Between inputs
              Eric Meger

              Thanks. We are using Oracle so I am sure we have all the tools. I just need to find someone able to build the queries for me (our DBA is way busy on more important projects than mine).