consolidated_db Normal / ID_BATCHYID_BATCHCHANNEL_IDYCHANNEL_IDTRANSNAMEYTRANSNAMESTATUSYSTATUSLINES_READYLINES_READLINES_WRITTENYLINES_WRITTENLINES_UPDATEDYLINES_UPDATEDLINES_INPUTYLINES_INPUTLINES_OUTPUTYLINES_OUTPUTLINES_REJECTEDYLINES_REJECTEDERRORSYERRORSSTARTDATEYSTARTDATEENDDATEYENDDATELOGDATEYLOGDATEDEPDATEYDEPDATEREPLAYDATEYREPLAYDATELOG_FIELDYLOG_FIELD ID_BATCHYID_BATCHSEQ_NRYSEQ_NRLOGDATEYLOGDATETRANSNAMEYTRANSNAMESTEPNAMEYSTEPNAMESTEP_COPYYSTEP_COPYLINES_READYLINES_READLINES_WRITTENYLINES_WRITTENLINES_UPDATEDYLINES_UPDATEDLINES_INPUTYLINES_INPUTLINES_OUTPUTYLINES_OUTPUTLINES_REJECTEDYLINES_REJECTEDERRORSYERRORSINPUT_BUFFER_ROWSYINPUT_BUFFER_ROWSOUTPUT_BUFFER_ROWSYOUTPUT_BUFFER_ROWS ID_BATCHYID_BATCHCHANNEL_IDYCHANNEL_IDLOG_DATEYLOG_DATELOGGING_OBJECT_TYPEYLOGGING_OBJECT_TYPEOBJECT_NAMEYOBJECT_NAMEOBJECT_COPYYOBJECT_COPYREPOSITORY_DIRECTORYYREPOSITORY_DIRECTORYFILENAMEYFILENAMEOBJECT_IDYOBJECT_IDOBJECT_REVISIONYOBJECT_REVISIONPARENT_CHANNEL_IDYPARENT_CHANNEL_IDROOT_CHANNEL_IDYROOT_CHANNEL_ID ID_BATCHYID_BATCHCHANNEL_IDYCHANNEL_IDLOG_DATEYLOG_DATETRANSNAMEYTRANSNAMESTEPNAMEYSTEPNAMESTEP_COPYYSTEP_COPYLINES_READYLINES_READLINES_WRITTENYLINES_WRITTENLINES_UPDATEDYLINES_UPDATEDLINES_INPUTYLINES_INPUTLINES_OUTPUTYLINES_OUTPUTLINES_REJECTEDYLINES_REJECTEDERRORSYERRORSLOG_FIELDNLOG_FIELD 0.0 0.0 10000 50 50 N Y 50000 Y N 1000 100 Slave3localhost8084clusterEncrypted 2be98afc86aa7f2e4cb1aa265cd86aac8N Slave2localhost8083clusterEncrypted 2be98afc86aa7f2e4cb1aa265cd86aac8N Slave1localhost8082clusterEncrypted 2be98afc86aa7f2e4cb1aa265cd86aac8N DI Serverlocalhost9080pentaho-dijoeEncrypted 2be98afc86aa7f2e4bb18bd63c99dbddeN Masterlocalhost8081clusterEncrypted 2be98afc86aa7f2e4cb1aa265cd86aac8Y ClusterSchema 40000 2000 5000 Y N Slave2 Slave1 Master - 2011/04/26 12:54:07.020 - 2011/04/26 12:54:07.020 Just a quick POC to read in iPhones consolidated.db 15 15 266 22 Microsoft Sans Serif 8 N N 0 0 0 255 165 0 100 100 100 Y Calculate the date. What about timezones? Could by optimized by a User Defined Java Expression 152 178 226 47 Microsoft Sans Serif 8 N N 0 0 0 255 165 0 100 100 100 Y For some reasons there are invalid data, filter them out. 417 179 213 35 Microsoft Sans Serif 8 N N 0 0 0 255 165 0 100 100 100 Y Let's do some simplification and group on a daily basis 625 69 174 35 Microsoft Sans Serif 8 N N 0 0 0 255 165 0 100 100 100 Y You could look up the data by regions, city etc. store them in a database, visualize .... 590 306 242 35 Microsoft Sans Serif 8 N N 0 0 0 255 165 0 100 100 100 Y Just use the CELLLOCATION table at this time. Other tables of interest are in there.... 19 73 247 35 Microsoft Sans Serif 8 N N 0 0 0 255 165 0 100 100 100 Y consolidated.db Dummy SQLITE Native ${Internal.Transformation.Filename.Directory}/consolidated.db Encrypted FORCE_IDENTIFIERS_TO_LOWERCASEN FORCE_IDENTIFIERS_TO_UPPERCASEN IS_CLUSTEREDN QUOTE_ALL_FIELDSN SUPPORTS_BOOLEAN_DATA_TYPEN USE_POOLINGN CELLLOCATIONconvert Mac Absolute TimeY Filter 0 rowsGroup by DailyY convert Mac Absolute TimeFilter 0 rowsY Group by DailyEverything.....Y CELLLOCATION TableInput Y 1 none consolidated.db SELECT MCC , MNC , LAC , CI , Timestamp , Latitude , Longitude , HorizontalAccuracy , Altitude , VerticalAccuracy , Speed , Course , Confidence FROM CELLLOCATION 0 N N N

65 125 Y convert Mac Absolute Time Calculator Y 1 none tmp1000 CONSTANT 1000 Integer -1 -1 Y tmpMacAbsoluteTimeConv MULTIPLY Timestamp tmp1000 Integer -1 -1 Y tmpYear2001asDate CONSTANT 2001-01-01 Date -1 -1 Y yyyy-MM-dd tmpYear1970asDate CONSTANT 1970-01-01 Date -1 -1 Y yyyy-MM-dd tmp30Years SUBTRACT tmpYear2001asDate tmpYear1970asDate Integer -1 -1 Y logDateWithTime ADD tmpMacAbsoluteTimeConv tmp30Years Date -1 -1 Y yyyy-MM-dd logDate REMOVE_TIME_FROM_DATE logDateWithTime Date -1 -1 N yyyy-MM-dd

265 125 Y Group by Daily GroupBy Y 1 none N N %%java.io.tmpdir%% grp N N logDate LatitudeDaily Latitude FIRST_INCL_NULL LongitudeDaily Longitude FIRST_INCL_NULL

705 125 Y Filter 0 rows FilterRows Y 1 none Y N Latitude = constantNumber0.0-1-1N#.# N OR Longitude = constantNumber0.0-1-1N#

505 125 Y Everything..... Dummy Y 1 none

705 245 Y N