Skip to Content
1
Feb 07, 2014 at 03:03 PM

Compiling column-wise time series data with DS for data-mining

41 Views

Dear SCN users with experience using BODS for time-series / data-mining,

I have several data series in tables loaded from xlsx files.

The data is organized in fields as follows:

H01 H02 H05 ... H25 MN012013 MN022013 MN032013 ... Time Series Key descript. descript anything time series -> --> --> Time Series Key descript. descript. anything time series -> --> -->

* where MN<month><year> gives the time-series.

I will be loading about 50 different time-series in this way. There are several xls files with different time ranges. As a C programmer, I am used to thinking in multi-dimensional arrays, where declaring a dynamic structure to store variable length series would be straightforward.

I can think of two solutions:

  1. Transpose (flip) each time-series and create a table for each (tables would need to be created at run-time, have not found how to go about this)
  2. create one large transaction-like table, where a (degenerate) timestamp and a series key identify time-ordered entries. Here I would need to insert each timestep of a series into a different place in a large table.

As a beginner in Data Services but not in programming, I have looked into scripting with python, or Data services scripting, but keep coming to the same problem: I need access to the field names as strings at run-time. This would allow me to loop through them MN____ columns, treating values column-by-column.

It is not clear to me what is possible with the DS scripting -- and I cannot seem to find useful documentation (sbo41_ds_reference.pdf is all I have). Is the main use of this scripting to contain SQL statements via the sql(...) funtion? Any resources here would be greatly appreciated.

I apologize for the vagueness of this question's formulation. I would greatly appreciate some direction here.

Regards