Data storage

[jzoller]

There's nothing new here, but maybe someone will be kind enough to lend me their experience.

I need to store data taken at a series of points (>100k) over time (minutes to months). At each point in time, many (1000+) parameters will be tested, and each test will generate a single result (string, bool, dbl, etc). All of this data will be associated with some identifying and meta data (time stamp, serial number, etc). Not every parameter will be tested at every point in time, leading to some sparse result sets.

In the past, I've taken all of the data for a time point as a "row", with each test result as a single column. That row was written as a string to a tab-delimited file as every time point test completes. I'll skip all the problems this can lead to, but too much of my life was spent on fixing them.

So, I have a chance to start from scratch. I've been looking at a few options, and there seem to be issues on all of them:

1. NI's TDMS: doesn't seem to understand the concept of a row, only a column. Not expandable in ways that I'd like.

2. Database: ideal, but I've many non-networked computers... anyone know a good sneakernet DB?

3. Markup (XML and ini, for instance): poor scalability & poor LV tool support... anyone have an LV native SAX parser?

Of course the database folks want future compatibility for import into the grand plan, the engineers want to use Excel (sigh...), the web folks want to be able to pull reports in .NET/PHP, and I want to be able to add or remove tests any time I'd like.

What have you all done with your data systems?

Thanks,

Joe Z.

(My budget for this is currently whatever I have in my cubicle, pockets, and/or can sneak out of IS after midnight, so large scale proprietary systems are out. Please, feel free to respond here if you have a commercial system, but no emails.)

[Chris Davis]

QUOTE (jzoller @ May 9 2008, 01:09 PM)

2. Database: ideal, but I've many non-networked computers... anyone know a good sneakernet DB?

You might check out SQLite if you decide to go with a database. There used to be a LabView wrapper for SQLite on the NI web site, but last time I checked, it was gone. Rigth now I'm using the SQLite functionality built into AutoIt and I've built a standalone AutoIt executable that I call from the command line to query and input data.

The SQLite Database is a single file, which is cross-platform and portable, since its a single file on your hard drive, which might make it ideal for your sneakernet DB.

As for the rest of your question, I think you'll find that a database will suit your needs better than anything, but I haven't tackled a job like what you are describing (yet), so I'm interested in the responses you get too.

[Jim Kring]

QUOTE (jzoller @ May 9 2008, 11:09 AM)

{...snip...}

Markup (XML and ini, for instance): poor scalability & poor LV tool support... anyone have an LV native SAX parser?

{...snip...}

(My budget for this is currently whatever I have in my cubicle, pockets, and/or can sneak out of IS after midnight, so large scale proprietary systems are out. Please, feel free to respond here if you have a commercial system, but no emails.)

Joe,

If you're looking for a LabVIEW native XML parser that is easy to use and is well within your budget constraints, check out http://jkisoft.com/easyxml/' rel='nofollow' target="_blank">EasyXML. (Nobody saw that one coming, did they? )

Cheers,

-Jim

[jzoller]

Just a quick followup on this...

SQLite is really very cool (thanks Chris!). I'm tinkering with the ADO.NET provider for it from sourceforge (http://sourceforge.net/projects/sqlite-dotnet2). It seems to function well enough for my limited usage, though I haven't tested it under heavy loads. Long term, this looks like the solution I would like to transition to, I just need to make validated tools out of it.

In the short term, I'm playing with what I'm calling decorator files. Parametric (scalar) test data is stored in a single row header, tab delimited text files, one row per test. This allows raw data to be opened in ways that my engineers prefer (Excel, JMP, etc).

From there, I generate a series of (usually disposable) decorator files associated with the test data files. Need an index for quick searching on a particular column? It goes in the sorted, time stamped index file, which gets regenerated once in a while if the data file has changed. This allows the memory shadow and search time to be much reduced. Need to mark particular items inside the data file (say, making deviation notes on a given data point for an SPC chart)? This goes in an application specific XML file that "points" at a given row, column, or data point in the original data file. And so on, and so on... nothing brilliant or complicated, just trying to stick with the practical.

Thanks all,

Joe Z.