Gap filling in a time series

Hello,

I am new to SQL Server and learning lots very quickly! I am experienced at building databases in Access and using VBA in Access and Excel.

I have a time series of 1440 records that may have some gaps in it. I need to check the time series for gaps and then fill these or reject the time series.

The criteria for accepting and rejecting is a user defined number of time steps from 1 to 10. For example, if the user sets the maximum gap as 5 time steps and a gap has 5 or less then I simply want to lineraly interpolate betwen the two timesteps bounding the gap. If the gap is 6 time steps then I will reject the timeseries.

I have searched the BOL and MSDN for SQL Server and think there must be a solution using the PredictTimeSeries in DMX, but not quite sure if I can do this. I may be better off simply passing through the time series as a recordset and processing as I would have done in Access...(I am reluctant to do this as I have of the order 100 * 5 * 365 time series and growng by 100 each day and fear it will take quite some time...)

Can anyone help me by pointing me in the right direction please?

Unless there is a way of using PredictTimeSeries on its own, I think the solution is:

Identify if a record is the a valid one or part of a gap (ie missing values). Identify the longest gap and reject or process data on this value. Identify if a record preceedes or succeeds a gap. For each gap fill it using a linear interpolation.

Thanks,

Alan.

Hello,

Thank you for reading the problem above. I have moved on one step and decided that I need to store the data in a 2-d matrix with time as one dimension and space as another. I will then have to interpolate in both time and space. Also we think that we can work with a subset of just over half of the data so we are going to read the data in to an array and process it that way.

I am still interested in knowing if the DMX functions could be used to fill multiple variable length gaps in a table. Any links would be appreciated.

Thanks,

Alan.

|||

I don't think this would be a good application for the time series algorithm. You would have to create a new TS model for each series leading up to each gap and then do a prediction after creating the model. Given the data, the time series may not be able to fill the gap - since the algorithm stops predicting when it determines the predictions are unstable.

We are working on changes to the time series algorithm that may make it more suitable for such problems, but I can't share the details as of yet.