Time Series, Incorporating Future Input Data For Prediction
Sep 25, 2006
Hi,
I've got a time series of the number of new customer subscriptions, which
is the target attribute to predict. The number of subscriptions depends on
various marketing activities, such as mailings, which are known within
the time series for the past.
If I train an ART (MS Autoregression Tree), it learns the trend pattern
as well as the correlations between the marketing activities and the
target (cross-correlations), right?
What I would like to do is, providing the model with some marketing activity
planning for the future and let the model predict the number of new
subscriptions based on a) the past trend pattern and b) the future activities.
Unfortunately a time series algorithm does not provide some kind of scoring for input data.
What would be the best approach to solve this problem? How about linear regression?
But how to train a regression model with trend patterns?
I have a very simple time series model which processing works fine without any problem. However when I run the following query
SELECT
[TimeSeries].[PriceChange],
[TimeSeries].[Symbol],
PredictTimeSeries(PriceChange, -3, 2)
From
[TimeSeries]
WHERE
[TimeSeries].[Symbol] = 'x'
I get the following error:
TITLE: Microsoft SQL Server 2005 Analysis Services ------------------------------ Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process.
The following is the excerpt of the minding model script related to the two parameters:
<AlgorithmParameters>
<AlgorithmParameter>
<Name>MISSING_VALUE_SUBSTITUTION</Name>
<Value xsi:type="xsdtring">Previous</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_GAP</Name>
<Value xsi:type="xsd:int">1</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_COUNT</Name>
<Value xsi:type="xsd:int">10</Value>
</AlgorithmParameter>
</AlgorithmParameters>
These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?
Hi I made an model based on MS time series and want to see some result in Mining Model Viewer (SSAS 2005) The chart displayed under tab "Charts" is as expected, but when i increase "predictions steps" onlsy the shadowed part of chart becomes bigger, but the prediction curve behaves unchanged...
Hi In this site sample €œgenerate DMX creation statement for a server mining structure and contained model€? I didn€™t understand, what is output? You say output is like a table, but I don€™t know what usage of this sample is because my request was about showing the result of time series algorithm. please notice my question, I should use a query like this for connecting with forecasting model €œSELECT PredictTimeSeries(amount) From [Forecasting]€? And then save this value in the text box? Also I should have training for showing value in the text box, which stage I should do it, this stage is after creating model or no? In time series algorithm, training is equivalent of historic prediction? In this code why these items are unknown and after running we have error In server. Connect for server and in database db for database and in MiningStructure ms for MiningStructurewe have error .
private void button1_Click(object sender, EventArgs e) { Microsoft.AnalysisServices.Server server = new Server(); server.Connect("data source=localhost"); Database db = server.Databases["DMClass"];
foreach (MiningStructure ms in db.MiningStructures) { MessageBox.Show("Processing " + ms.Name); ms.Process(ProcessType.ProcessDefault); }
MiningStructure msIris = db.MiningStructures["Iris"]; MiningModel mm = msIris.CreateMiningModel(true, "newModel");
} Please alittle define this code. What is diffrence between amo and adomd ? And for viweing prediction vlue in textbox and connecting I can use both of them And how can I build a child form?Thanks a lot for your answers
hi i have aques tion about mining model and time series after predicting how can i show the result of the prediction in the c# form and why i cant use the all of the prediction function in the mining model prediction for time series and how i can see the script of the building mining model in the model because i can see just the wizard of the model and how can i undrestand othe about time series formula in the chart thanks alot if answer me
i need to be able to do an sql call whereby i can check the day and hour for publishing.eg select story where day = ?? AND hour = ?? any one know the best way to do this. i need to call out date as a day number or someting and the getdate as just the hour??? anyone know how??
I encountered a very strange problem again. Why the time series displayed on the chart are so strange? The Key time column I chose for my time series algorithm is cal_month(e.g 199001...), but why the date displayed on the time series chart is like :05/06/2448? (it should be like 199001..?) What is that data? And where exactly did it come from? What is the exact cause of this?
Hope it is clear for your help.
I am really confused on this and thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.
Is it possible to group many time series into clusters by using the clustering algorithm of the SQL server 2005. The same question applies to "association rules" technique. Any examples?
I'm wondering what is the best way to store time-series data in an SQL database?? I've done a bit of investigating on the rrdtool (round robin database tool) that is used in a lot of nix based solutions for monitoring network equipment. I have a need to collect performance data from servers and routers and then produces some nice graphs from that data. I'm just not sure who i should store that data without the database growing to some huge size.
Again I encountered a very strange problem which displayed the predicted attribute values as percentage format? The data type of the attribute is actually double, why is that?
That's really frustrated.
Thanks a lot in advance for your kind advices and I am looking forward to hearing from you shortly.
Is there any way to incorporate the SQL 2005 Mining Wizard into my program? Sure, I can create my own mining wizard using DMX, but that's quite a bit of work. Has anyone tried this? Is it possible? Any pointers?
I was working with Microsoft Time Series model (MTS) with some data, when in the mining model viewer, decision tree tab, I realized that the key time variable that I define, it was acting like a split variable.
So, I ask you, this is possible?, because, for me, this should not happen€¦.
After, I review the Data Mining Tutorial by Seth Paul, Jamie MacLennan, Zhaohui Tang and Scott Oveson, and I found, in the Forecasting part, that the key time variable (Time Index) it was acting like a split variable too, in for example, M200 pacific:Quantity and R250 Europe:Quantity.
So people, it€™s possible that a key time variable act like a split variable in a MTS model?
I am confused on key time column selection. e.g, I want to predict monthly sales amount, then what column in date dimension should I choose to be the key time column? Is it calendar_date (the key of date dimension) column or calendar_month?
Thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.
I am working on a stock price analysis project. Stock prices are stored in SQL Server as tick by tick data (that means in a high frequency) and need to be grouped by minutes in order to display them as as high, low, open and close prices in a candlestick or similar chart.
The fields in the table are:
Time (for every stock price tick) Price (stock price) Symbol (of the stock)
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
I m using the Time Series Algorithm to forecast sales across regions for various products. Assume the model is built with last 3 years data with the periodicity being monthly.
Is it possible that sometimes I can make predictions based on just 1 yr or 2 yrs data for certain products alone or certain regions alone? Can this be done without having to retrain the already built model?
Also, is it possible that using the model, i can predict week-wise / month-wise / quarterly sales as well?
Hi All, I have a table Test1: ID date Value AAUGVAL 2/27/198760.848 AAUGVAL 3/2/1987 64.288 AAUGVAL 3/3/1987 63.77 AAUGVAL 3/4/1987 62.495 AAUGVAL 3/5/1987 62.65 AAUGVAL 3/6/1987 62.548 AAUGVAL 3/9/1987 62.292 AAUGVAL 3/10/198763.045 AAUGVAL 3/11/198763.021 .... I am trying to see the value % changes day by day and here is is the code I wrote: select starttime=cast(v.date as char(8)), endtime=cast(a.date as char(8)), startval=v.Value, endval=a.Value, change=substring('- +', sign((a.Value-v.Value)+2,1)+ cast(abs(a.Value-v.Value) as varchar) from (select date,Value, ranking =(select count(distinct date) from Test1 T where T.Value<=S.Value) from Test1 S) v left outer join (select date,Value, ranking=(select count(distinct date) from Test1 T where T.Value<=S.Value) from Test1 S) a on( a.ranking=v.ranking+1)
I got the following error message: Server: Msg 174, Level 15, State 1, Line 4 The sign function requires 1 arguments. Server: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near 'v'. Server: Msg 170, Level 15, State 1, Line 9 Line 9: Incorrect syntax near 'a'.
Could someone please help with this? Thank you in advance! shiparsons
I am trying to write a stored proc the calculates a moving average overthree periods. In the following example, I need to stratify the data bypersonID and RecordID in the #Temp table, but I am not sure how to doit. Right now I am restricting the data I use to build my time series bypersonID and I get the results I want *by PersonID*. If I can figure outhow stratify by personID so I don't have to use this restriction, I'msure I can extend it to the RecordID.Create Table #Temp(tmpID int identity,DetailID int,RecordID int,AdminDate Datetime,AdminTime datetime,Status tinyint,--decimal(9,2),Location varchar(100),PersonID char(9),PatientName varchar(100),DOB Datetime,Drug varchar(100),Sort varchar(10))--populate with data by personIDinsert into#Temp(DetailID,RecordID,AdminDate,AdminTime,Status ,Location,PersonID,PatientName,DOB,Drug,Sort)Select MD.PatMedOrderDetailID, MD.PatMedOrderID, M.Date as AdminDate,Case M.Time When 'A' then '8:00:00 AM' When 'N' then '12:00:00 AM' When'P' then '4:00:00 AM'When 'H' then '8:00:00 PM' else M.Time End as Admintime,100*M.Status, P.Location,P.PersonID, P.Name as PatientName, P.DOB,D.GenericName + ' (' + D.TradeName + ') ' +D.Strength,Left(P.Location,3)From PatMedOrderDetail MD Inner Join PatMedOrder MO on MD.PatMedOrderID= MO.PatMedOrderIDinner Join PatMedPass M on MD.PatMedOrderDetailID =M.PatMedOrderDetailIDinner join Patient P on M.PersonID = P.PersonIDinner join Drugs D on MO.DrugID = D.DrugIDWhere P.PersonID = '000126230'Order by P.PersonID,MD.patMedorderID, M.Date, M.TimeSelect * from #Temp -- to view entire set--returns relevant rowsSelect Derived.RefusalRate,T.* from #Temp T inner join(select t1.tmpID, avg(t2.Status) as RefusalRatefrom #Temp t1 cross join #Temp t2WHERE t1.tmpID>=3 AND t1.tmpID BETWEEN t2.tmpID AND t2.tmpID+2group by T1.tmpIDhaving avg(t2.Status)< 100) as Derived on T.tmpID = Derived.tmpIDDrop Table #Temp*** Sent via Developersdex http://www.developersdex.com ***
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.
First question: How many months of data do you need to make this algorithm to work in Excel 2007? And is there an issue about data types in Excel for this algorithm?
I have found some odd behaviours regarding this. If I use the DM sample Excel 2007 with time series data everything works fine. If I copy and paste data into Excel 2007, from another data source, I can get a forecast of repeating values, that is one value, that will be repeated for each month that I am trying to do a forecast.
Should I avoid having time members for forecast dates in a column? Sometimes my forecast values will be placed below my dates that do not have values. If I am forecasting months in 2008, with month values from 2007 and 2006 the forecast values will be placed below my 2008 empty months.
I'm trying to learn about time series algorithm but I can't set the time periodicity right. I have information stored 2 times a year (semester) so I'll should set up a PERIODICITY_HINT = {2}, right? but it does not change anything.
Here is a screenshot that might help understand the problem:
I'm going to create an analysis report based on time range. The data is grouped by the hourly range. There're two problems that I'm facing.
1. How can I generate such result set so that it will give me 0 count instead of missing that column?
2. How can I vary the start and end time which depends on another table?
I believe this is quite hard to be complete within a single SQL. However, I would still want to try. The SQL server is the Express version. No analysis service is available.
Obviosly for Person1 and 200501 I expect to see on MS Time Series Viewer $3000, correct? Instead I see REVENUE(actual) - 200501 VALUE =XXX, Where XXX is absolutly different number.
Also there are negative numbers in forecast area which is not correct form business point Person1 who is tough guy tryed to shoot me. What I am doing wrong. Could you please give me an idea how to extract correct historical and predict information?
I am building data mining models to predict the amount of data storage in GB we will need in the future based on what we have used in the past. I have a table for each device with the amount of storage on that device for each day going back one year. I am using the Time Series algorithm to build these mining models. In many cases, where the storage size does not change abruptly, the model is able to predict several periods forward. However, when there are abrupt changes in storage size (due to factors such as truncating transaction logs on the database ), the mining model will not predict more than two periods. Is there something I can change in terms of the parameters the Time Series Algorithm uses so that it can predict farther forward in time or is this the wrong Algorithm to deal with data patterns that have a saw tooth pattern with a negative linear component.
In SQL Server 2005, I want to do a set query on the following data that results in 3 groups:
Id EventName EventTime
1 First 41:40.2
2 First 41:41.6
3 First 41:43.1
4 First 41:44.4
5 Second 41:46.4
6 Second 41:48.3
7 Second 41:49.7
8 First 41:51.2
9 First 41:53.3
10 First 41:55.0
So, I want to have a query that returns one aggregate row for each of rows 1-4, 5-7 and 8-10 based on the EventName. Every time EventName 'changes' in the order that I sort it, I want to start a new grouping:
Group EventName Count
1 First 4
2 Second 3
3 First 3
With this query, I could also get the Min() and Max() EventTime for each group, etc.
However, this is proving difficult to do in set SQL. Obviously, if I group on EventName, then rows 1-4 *and* 8-10 will be rolled into my 'First' group. However, there is no other partitioning information that I can factor in that splits this data into *only* 3 groups, based on the order of the Event Time.
I have tried the various ranking functions, but the problem persists through any combination of function, PARTITION BY and ORDER BY that I can find.
I want to hear from you for your experiences on how can we be convinced by what the model predicts particularly for Time Series algorithm? As we are not able to see the model accuracy chart for that, in this case, how can use the model results? E.g. we wanna know the possible sales amount of next month for a particular store in order to buy in the goods, in this case, how can we make the most of the prediction by the model? To shop ower, well, if the result is too far away from the usual sales amount, then it is unbelievable, thus, in this case, what else can we try? Keep training the models until its results sounds reasonable? Or what else can we try?Thanks in advance for your advices and help and I am looking forward to hearing from you shortly.
How can I visualize Microsoft Time Series prediction chart for web users? (for Internet Explorer browser)
Is there a way to do it using Reporting Services or special web controls?
I hoped Data Mining Web Controls helped me but they didn't support Microsoft Time Series.
------------------------- Supported Algorithms At this time, Data Mining Web Controls include the following:
- DMClusterViewer €“ For the Microsoft_Clustering algorithm. - DMDecisionTreeViewer €“ For the Microsoft_Decision_Trees algorithm. - DMNaiveBayesViewer €“ For the Microsoft_Naive_Bayes algorithm.
Models using other algorithms cannot be displayed with the Web controls.
-------------------------
Is there a way to visualize Microsoft Time Series in browser ? When will there be such facility?
