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.

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.

Funky formatting of percentages in Excel export

I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Sorry, I didn't see that anything was different in the code you included. Yes, that does work. However, the problem is that I don't know of a way to control what Reporting Services tells Excel to use as a formatting code. So, the question remains, why does Reporting Services create an Excel formatting code that causes inconsistent behavior? Is there a workaround for this? If this is a bug, how can I report it so that it gets looked at?

This report runs and exports using a subscription and is emailed out automatically to a group of people. The whole point of automating this was that nobody would have to go in and change anything. This certainly isn't a critical issue, it just looks unprofessional.

Funky formatting of percentages in Excel export

I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Sorry, I didn't see that anything was different in the code you included. Yes, that does work. However, the problem is that I don't know of a way to control what Reporting Services tells Excel to use as a formatting code. So, the question remains, why does Reporting Services create an Excel formatting code that causes inconsistent behavior? Is there a workaround for this? If this is a bug, how can I report it so that it gets looked at?

This report runs and exports using a subscription and is emailed out automatically to a group of people. The whole point of automating this was that nobody would have to go in and change anything. This certainly isn't a critical issue, it just looks unprofessional.

Function to determine weekoftheyear.

I looking a function to determine the w of the year. In excel I use
WNum, but I need this for sql server.
thank you.select DATEPART(wk,getdate())
or
select DATEPART(ww,getdate())
"Miguel Arenas" wrote:

> I looking a function to determine the w of the year. In excel I use
> WNum, but I need this for sql server.
> thank you.|||Both work.
MattB wrote:
> select DATEPART(wk,getdate())
> or
> select DATEPART(ww,getdate())
> "Miguel Arenas" wrote:
>|||SQL Server w is different than the ISO w
Do a search for FUNCTION dbo.ISOw, you can also find the code here
(http://msdn2.microsoft.com/en-us/library/ms186755.aspx) if you scroll
down
Denis the SQL Menace
http://sqlservercode.blogspot.com/
MattB wrote:
> select DATEPART(wk,getdate())
> or
> select DATEPART(ww,getdate())
> "Miguel Arenas" wrote:
>