How to store measurement data in a single database row

[Chris_BE]

I have to store time-data series into a database and do some analysis using Matlab later. Therefore the question might be more a database question rather than Diadem itself. Nevertheless I'm interested if anyone has some best practices for this issue.

I have a database which holds lifecycle records for certain components of same nature and different variants. Depending on the variant I have test setups which record certain data. There is a common set of component properties and a varying number of environmental measurements to be included. Also the duration of data acquisition varies across the variants.

Therefore having tables appears to be non-optimal solution for storing the data because the needed number of columns is unknown. Additionally I cannot create individual tables for each sample of a variant. This would produce to many tables.

So there are different approaches I have thought of:

1. Saving the TDM and TDX files as text respectively as BLOB

This makes it necessary to use intermediate files.

2. Saving the data as XML text

I don’t know yet if I can process XML data in Matlab

Has anybody an advice on that problem?

Regards

Chris

[AndreasK]

Matlab can directly read TDM(s) files using NI niddclib.

http://www.ni.com/white-paper/7446/en/

Wouldn't this be a way?

[Chris_BE]

Hello Andreas,

thanks for reply.

This does not really hit the point. Currently the algorithms to be used are under development with data provided in Diadem format. That works so far pretty well.

The future of the project is that all the test data is to be stored in a (my)SQL database. At the end of the day this database will be filled so it holds archived data of diverent variants of the last 2 decades. This will be 10k + sets of time-data datasets where each originates from Diadem files. Based on the information in the Diadem files which will be turned to fields in tha database we'll be able to execute specific analysis on datasets. The structure of supplementary information in Diadem files is clear an can be modeled in the database where the structure of measured data is not fixed.

The approach will be like that:

1. Select components for which the analysis is to be performed

2. Select analysis method

3. Determine adapter function (programming pattern)

4. Execute analysis

5. Writeback data to database

Since a component is one row in a table I'm struggling with the way how the measured data can be stored in the best way.

So I thought, there must be someone who had this challenge before and could give some advice. As I outlined before The method of storing the file as TEXT and BLOB respectively was discussed as an option. But maybe there are more comfortable ways to do it.

BR

Chris

[Pesmith8]

Chris

Sorry for the lateness in replying to your post. 

I have done quite a bit of using a Database to store test results.  (In my case this was Oracle DB, using a system called ASAM-ODS)

My 2 Cents:

Three functions were needed by users for me.  1) To search and find the tests,  and  2)  To take the list of Tests and process the data into a report/output summary. 2) If the file size is large, then being able to import the data quickly into analysis tool speeds up processing.

1) Searching for test results.  This all depends on what parameters are of value for searching.  In practice this is a smaller list of values(usually under 20), but I have had great difficulty getting users to agree on what these parameters are. They tend to want to search for anything.   The organization of the searching parameters has direct relationship to how you can search.   The NI Datafinder does a nice job of searching for parameters, so long as the parameter data are stored in properties of Channel Groups or Channels. It does not search or index if the values are in channel values.

Another note: Given these are searchable parameters, it helps greatly if these parameters have a controlled entry, so that the parameters are consistent over all tests, and not dependent on free form entry by each operator. Searching becomes impossible if the operators enter dates/ names in wildly different formats.

2) Similar issue exists if put values into databases. (I will use the database terms of Table and column(Parameter) and Row (instance of Data that would be one test record.)

The sql select statement, can do fast finds, if store the searchable parameters in rows of a table. Where would have one row for each Test Record.   The files I worked with have more than 2000 parameters.   Making a table that would store all of these, and be able to search for all of these, makes a very large number of rows. I did not like this approach, as it has substantial maintenance time, and when changes are made, things get ugly quick.

3)This is where having the file format be something that the analysis tool can quickly load up is beneficial.   Especially if the data files are large. In DIAdem's case, it reads TDM,TDMS files very quickly into the dataportal.   It can also read in the MDF or HDF files, but these are hierarchical structures that require custom code to traverse the information, and get the information into dataportal to use in Analysis /reporting. (Takes more time to read data, but have much more flexibility in the data structure than the two level tdm/tdms format.)

My personal preferences

I would not want to put the test data into a Table row. Each of the columns would be fixed and the table would be very wide in columns.

>

I personally like to put the test Data into a file, like TDMS, MDF, or HDF and then the database would have a entry for the reference to the attachment. The values that are in the database is just the parameters that are used for test Searching, either in datafinder or in sql commands in the user interface.

Hopefully these comments help your tasks some.

Respectfully,

Paul

1. tdmsmith.com