Hey Gang,
I have a slight issue with Time Series. I'm forecasting montlhy revenue, and I have historical data of revenue and industry trend. Now, I have value for industry trend for next 3 month, but how do I tell the time series engine to use the industry trend data to predict revenue for next 3 month?
Case table: [Product] (Product Name)
Nested table: [Product Item] (TimeKey, IndustryTrend, Revenue)
-Young K
Hi, all experts here,
Thank you very much for your kind attention.
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.
With best regards,
Yours sincerely,
Hi all,
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?
Hello,
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?
Thanks
Hi, all experts here,
Thank you very much for your kind attention.
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.
With best regards,
Yours sincerely,
Hi all,
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 ***
View 1 Replies View RelatedGiven the following table information:HOSTNAME DATETIMEWEBNYC001 2005-06-15 10:30AMWEBNYC001 2005-06-15 10:31AMWEBNYC001 2005-06-15 10:31AMWEBNYC001 2005-06-15 10:34AMWEBNYC001 2005-06-15 10:35AMWEBNYC001 2005-06-15 10:35AMWEBNYC002 2005-06-15 10:30AMWEBNYC002 2005-06-15 10:30AMWEBNYC002 2005-06-15 10:33AMWEBNYC002 2005-06-15 10:35AMWEBNYC002 2005-06-15 10:35AMWEBNYC002 2005-06-15 10:35AMHow can I easily return the following results:HOSTNAME DATETIME COUNTWEBNYC001 2005-06-15 10:30AM 1WEBNYC001 2005-06-15 10:31AM 2WEBNYC001 2005-06-15 10:32AM 0WEBNYC001 2005-06-15 10:33AM 0WEBNYC001 2005-06-15 10:34AM 1WEBNYC001 2005-06-15 10:35AM 2WEBNYC002 2005-06-15 10:30AM 2WEBNYC002 2005-06-15 10:31AM 0WEBNYC002 2005-06-15 10:32AM 0WEBNYC002 2005-06-15 10:33AM 1WEBNYC002 2005-06-15 10:34AM 0WEBNYC002 2005-06-15 10:35AM 3Thanks!
View 2 Replies View RelatedHello,
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.
Hi!
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.
Kind Regards
Thomas Ivarsson
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.
Hi!
I have a table Month_Sales(Month, product_1, .., product_n). The value of column product_i is the sale in this month.
so when i build MS Time Series for this domain, i want to query to find top m product is seld most in next month??
How do i buid that query???
I am trying to predict Revenue gererated by each Person.
My Input like this:
Month Person Revenue
-----------------------------------------
20050101 Person1 $1000
20050101 Person1 $2000
20050201 Person1 $1000
20050101 Person2 $5000
20050201 Person2 $2000
20050201 Person2 $3000
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?
Thnak you,
Tim.
Hi Jamie:
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.
Thanks,
i'm building a time series model in visual studio, forecasting sales. but it shows
The graph cannot be created because of the following error:
'All actual columns are ending in NULL: data unacceptable for viewer'. (Microsoft Visual Studio)
can anyone help solve this? the data is by month (for 3 years) and i want to forecast 3 months in the future.
thanks!
lxm
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.
Any insights would be appreciated!
Help me!!
1. Can you tell me the way to present Time Series Chart on The Web by ASP.Net 2.0
2. I have a Time Series Mining Model. Its structure is:
(Month datetime key time, Sales continous predict)
When the query runs:
select Flattened Predict(sales,5)
from Model_Name
The result is Month and sales in the future. But i wan also retrieve Month and Sales in the past . Tell me the way?????
Thank alots!!!!!!!
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?
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...
What can I do?
Thanks
Hi, all here,
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.
With best regards,
Yours sincerely,
Hi!
When I using SQL Server 2005 to buide a Time Series mining model, in the Charts tab, it show the chart about somthing to predict in the future.
How to view that Time Series Chart in C# . ( Or using a third component)
Hi
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?
Andriy Zhornyk
I have a relational table with daily sales for 364 days (52 week span) for 60 stores. I have created a Microsoft Time Series model using store as the case and the historical/actual line charts appear in the Charts viewer for each store. But only one prediction step is shown no matter how many prediction steps I select. I have tried this with an OLAP-based model and also tried a simple DMX query, all with the same result.
Thank you in advance for any help with this.
P Taylor
Is there any way to force a regressor using the time series algorithm?
I created a time series ming model which predicts the sales quantity of items for the next quarters but how do i write a query to display the data in reporting services.At present i used two diffrent queries to get the past data and the forecast data but i need both of them in the same data set to draw a graph with the data in reporing services.
//for forecast data
SELECT flattened [Item Id],
(SELECT $Time as Date,[Item Qty] as Values,PredictVariance([Item]) as variance
FROM PredictTimeSeries([Dim Time],6)) as PredictedValues
FROM [ItemAnalysis]
//For past data
SELECT flattened [Item Id],
(SELECT [Date] as Date ,[Item Qty] as Values FROM [Dim TIme]) as HistoricalData
FROM [ItemAnalysis] .cases
how can i merge both of them in a single query
regrads,
Swarna
Hello,
I was working with Microsoft Time Series (MTS) algorithm and simulated data in order to evaluate/know it a little more.
I simulated 24 points of the model y[t] = 5.74-0.1486 y[t-1] + e[t] and 19 points of the model y[t] = 10.48-0.0486 y[t-1] + e[t] (a change of level), where e ~ N(0,0.01).
The MTS output is: if time>=23.5 then AR(3) else AR(1): y[t] = 6.23-0.2536 y[t-1].
So, I am wondering:
how the algorithm works whit the time variable as a split variable? Like the other variables? Only considering 4 time points?
Why the MTS algorithm produces AR(p) models where p is a little large (like the example: I simulated an AR(1) model and the output is an AR(3) model), what about parsimony models?
A AR model is a stationary model, so what happen if some data have trend? We need eliminate the trend before the MTS algorithm can be used?Thanks for your time
Have an Excel Spreadsheet with two columns and 52 rows (retail sales)
Date Qty
1 5
2 8
etc
Tried to run the above Addin. Error message:
"Session mining object (...) can not be created on this instance."
Any ideas?
Thanks,
Sergei.
Hi,
I have built a Time Series model to forecast Sales Value. I have used data form Jan 2005 to Sep2006 to train the model.
When
I write a DMX query to forecast the sales value for next 5 months, it is throwing me the output in the following format.
$TIME Sales Value
200610 2874
200611 2875
200612 2874
200613 2874
200614 2874
whereas the output i expected was.
$TIME Sales Value
200610 2874
200611 2875
200612 2874
200701 2874
200702 2874
What changes should i make in the model to get the output in the above format.?
Hi all,
I have two tables (staging and Cdate) and neither objects has any constraints.
staging table has ID, date, A, B, and C fields and Cdate has id,date and day fields. I need to update/insert date from Vdate into staging where staging ID=' ' and date is null
Here is the code I wrote, however, it seemed the information was updated to one date only instead of time series - Cdate contains time series in column date.
Anyone can help to fix it? Thank you for the help!
update s
set s.date=c.date
FROM cdate c join staging s on(s.id=c.id)
Where s.date is null and id=2
hi to every one
and again i say my question
for the first time i thought if i ask my question from this forum i can give my answer exactly
but with these answers i see it was just a dream .
one person said you can see your answer in book with this title "the datamining with sql 2005 "but i cant find my request and then said you can find in datamning sql2005.com and in this site i cant find a sample about a form that i can show my result of prediftion with time series
i dont know how can i earn this code .and a sample about that .please please if every one can answer me ,answer with descripstion about code .i just want codes for connecting between c# and analysis serveice and a description about quality of this code,that means this code, how do it work?