What should I use to play with over 400 million rows of data?

Oracle SQL is easy enough to pick up the basics in a few hours. It is much easier than excel or python. Plus you already have SQL developer.

quote:

---

column a&b = c&d and column x falls in between y&z

---

This is almost SQL.

PL/SQL is pretty straight forward and you could run through some code classes online and knock out what you need.

If you're more visual, check out Pentaho. There is a community version that you can use for free.

Pentaho

ETA: Your data isn't in Oracle SQL Developer. Your data is likely in an Oracle database. SQL Developer is simply a query tool.

ETA2: Pentaho has add ins for address data (Melissa data) and geo mapping.

This post tells me I am very glad to not be in logistics.

If you already have it in a relational database (Oracle) you can just use PL/SQL to manipulate/clean data and query data for reports. Do you have access to the Oracle server? Do you need nicely formatted reports or just tabular data is fine?

Tableau is amazing for visualizations but there are much better tools for data cleansing.

Alteryx integrated into Tableau too.

I'm not associated with the company but I do use the program from time to time and have been extremely impressed with its ease of use and speed of analysis.

quote:

---

My take based on his questions is that he's used to exporting data from the database and playing around with it in Excel.

---

excactly, I can visually see the errors. ie one vehicle kept reporting a single trip multiple times, another is breaking a single trip into multiple stops and simultaneaous starts. unless you "see" that trend you don't know its there....I'm guessing unless you write sometype of check code...if duration between x and x-1 is less than 1 min...flag error?

This is a new position for them(I am the only one they have) which involves alot of easy performance reports(no big deal) from their proprietary system. But now they want me to start getting deeper into the data because they have this gps tracking monitor on all vehicles that they want to incorporate into internal trip/route data.

I can play with one vehicle's data for a day, and give a report about that data, or use it in an investigation into a driver or something "pinpoint".....but when I look at an entire day of all vehicles....I crash before I can begin.

We have over 2000 vehicles on the road at a time, running even more drivers in different jurisdictions.....and I have to be able to:

ensure data integrity
have the quarterly report in such a manner that any question can be answered on the fly (like a pivot table)

"So kujo, what's the difference in westward bound route times in October versus December?"

Octber is 5% faster

"Really, Where is more time saved before noon or afternoon?"

Uh, frick, Imma have to relaod all the fraking data again and then try to write some time split code into it, and hopefully by the end of the day I can get that ONE question answered..with 80...60% confidence I did it right.

Any 3rd party solution you use is going to either run off of oracle or export the data to some oracle equivalent and then use that. So it will take as long or longer than doing it correctly in oracle.

400 million records is a lot. How far back does that data go? Is all of that time important for your reporting purposes? If not, you may consider automatically copying/summarizing small portions of that data into your own separate table (with indexes appropriate for your queries) on a nightly basis.

Your strategy in querying should be to minimize/eliminate your calls to the 400 million record table and use GROUP BY to minimize the rows returned.

GROUP BY turns a result like this:
Driver A 1/09/2017 6:45:12 - 6:53:33 1.4 miles
Driver A 1/09/2017 7:00:03 - 7:14:56 2.3 miles
Driver A 1/09/2017 7:20:03 - 7:22:01 0.3 miles
... (400000000 rows)

into something more manageable like this:
Driver A 1/09/2017 22 miles
Driver B 1/09/2017 38 miles
... (xxxxxx rows)

or:
All drivers 1/09/2017 687 miles
All drivers 1/08/2017 722 miles
... (xxxx rows)

or even:
All drivers All dates 57892346 miles
(1 row)

(no message)

What company if you dont mind? I am in transportation as well. I can't imagine doing that much data! That is nuts. I've gotten into dedicated so I just worry about the same 40 guys every day and those are my guys. It's easy to see arrival/departure times each day versus planned times and we can see where we are losing time. But then again, it's only 40 drivers :)