Retrieving Actual Sales Value From The Time Series Model
Jul 27, 2006
Hi,
I have built a Sales Forecast model to predict the sales
value.
Along with making historic predictions for previous time
periods I also want to retrieve the actual sales values for those periods.
How can I achieve this in a time series model?
I also would like to know how do mining models store the data.
Do they store the data in the same table/view format
as their respecive data source view or in the Model Content format.
View 3 Replies
ADVERTISEMENT
Oct 27, 2007
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?
View 1 Replies
View Related
Feb 19, 2008
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?
View 3 Replies
View Related
May 29, 2007
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
View 1 Replies
View Related
Jun 5, 2007
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,
View 3 Replies
View Related
Jul 15, 2006
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.?
View 4 Replies
View Related
Nov 8, 2006
As the Microsoft Time Series algorithm implementation is based upon the Autoregressive Tree approach described in:
C. Meek, D. M. Chickering, D. Heckerman. Autoregressive Tree Models for Time-Series Analysis. In Proc. 2nd Intl. SIAM Conf. on Data Mining, 2002 (SDM-02). SIAM, pp. 229 €“ 244. http://www.siam.org/meetings/sdm02/proceedings/sdm02-14.pdf.
The model estimated is refererred to as an instance of "... autoregressive tree models of length p, denoted ART(p). An ART(p) model is an ART model in which each leaf node of the decision tree contains an AR(p) model, and the split variables for the decision tree are chosen from among the previous p variables in the time series..." (see the last paragraph of p. 2 of the paper).
What is the value of "p" used in the Microsoft Time Series implementation -- specifically, how many previous time series variables are used in estimating the model? It doesn't appear that this value can be specified in the algorithm parameters -- is that correct?
Thanks,
- Paul
View 1 Replies
View Related
Jun 5, 2007
Hi, all,
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.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Mar 15, 2008
I probably posted my question in the wrong forum so please check here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3009605&SiteID=17
thanks
View 6 Replies
View Related
Sep 30, 2015
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
View 2 Replies
View Related
May 31, 2007
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,
View 1 Replies
View Related
Aug 5, 2006
I have few more clarification regarding time series.
Firstly
In my model the month level product sales value represented across 1st day of every month. So that the key time column is of datetime datatype containing a sequence of dates representing the 1st day of every month of the year.
Eg: 2006-01-01, 2006-02-01€¦€¦€¦. etc. all in (yy-mm-dd) format
But
when I make prediction for next five months, though it makes monthly
predictions the date part for the months are random whereas I expect
the date part to be 1st of every month. What is the reason for this and how can I overcome it.
Secondly
Predicted
sales values for some time period are negative though I do not have any
negative value in the training data. What is the reason for this and
how can I rectify it?
Thirdly
In
one of your earlier posts you had said that the time series algorithm
does not have any built in time intelligence but uses the key time
column as a time sequence stamp. So If have to make predictions for a
particular time period where the time slice for each time period is 25
days or 50 days etc, then I understand that the input data used to
train the model should be in the same time sequence.
Or
Can I specify the span of the time period according to which the prediction needs to be made?
Basically
how can I use the same time series model to make monthly, yearly,
quarterly, daily or predictions or for custom time period like I have
mentioned above.
View 3 Replies
View Related
Jun 21, 2006
Read only transacted replication to about 60 clients. 100 or so transactions a day (8 hours), transaction (record) is probably about 8K/transaction. Reliable connection over 100base-T. Latency - 15 minutes would be great.
This doable? -- I couldn't find a timing/ sizing model any place.
View 1 Replies
View Related
Aug 2, 2006
Hi,
I have built a time series model to forecast sales value
I have data from jan 2004 to jan 2006 and the sales value is
at a day level in my database. But I am aggregating it to month level in the
DSV of the mining model.
I am required to make only historical predictions using the
above model starting form jan 2004 to jan 2006 for every month.
I have set Historical_Model_Count
and Historical_Model_Gap parameter
values to 24 and 10 respectively, and trying to predict for the past few months
(PredictTImeseries(SalesValue,-1,1))
But its throwing me the following error
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, Sales Forecast, specified in the
HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process
In fact it throws the above error irrespective of what the Historical_Model_Count and Historical_Model_Gap parameter values
are
I am not able to figure our why this problem is happening?
What should the parameter values for the above scenario?
It would also be helpful if I can get an explanation on how
these two parameters affect the historical predictions. I kind of understand
that these two parameters are important for historical predictions but don€™t
know why or how.
View 4 Replies
View Related
Aug 3, 2006
Hello...
I am new to SSAS and i want to try to build a "Sales" model. I will have some "Usage" data for some timespanns, but I am not quite sure how to tackle this. Is there somewhere a "Howto" for this?
Edit: There are several locations, and for each location a forecast is needed. And the Icing would be If I would be able to tell where my supplies must go 1st to achieve the best sales...
The potential Client wants to use Oracle but I would like to show them that SQL Server is the better tool for this ;)
View 1 Replies
View Related
Jul 4, 2007
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
View 1 Replies
View Related
May 31, 2007
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,
View 3 Replies
View Related
Dec 13, 2007
Hello everyone!
Whenever i insert a new row i would like to have the current date and time in the column DATE. How is that possible?
Thanks a lot and greetings from Vienna
Landau
View 3 Replies
View Related
Nov 11, 2012
how can I get the execution time in millisecond of an MDX query in SSAS?For sql we can get it by:
Code:
set statistics time on
--query----
set statistics time off.But I am not getting anything for MDX.
View 6 Replies
View Related
Nov 2, 2006
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?
View 5 Replies
View Related
May 15, 2008
My main datasource is a rather poorly written and documented SQL database. I am currently working in SQL Server 2000 but will be upgrading to 2005 in 6 months to a year. There are three sales order tables.
SOMAST Sales Order Master Table
SOITEMS SO Items Table
SORELS SO Releases Table
My employer wants to track how our sales orders change over time. This would be a nightly process. They want to track changes in certain columns such as price and quantity to see if they differed from yesterday and to keep those changes separate in another table or set of tables to track them. This of course would include newly entered sales orders for that day as well. Our current erp system does not support this.
This seems like a huge task to a neophyte like myself, but I am tasked with doing this. Am I correct in assuming the correct method would be a stored procedure that does the following:
1. Check the current tables at end of day today and compare them with a saved version of yesterday's tables.
2. Insert into a 3rd table (or set of them) the differences.
3. Copy today's tables over yesterday's tables so they are available tomorrow.
I realize this task is difficult, but am I at least starting in the right direction?
Experts Only Please. (jk)
View 17 Replies
View Related
Nov 10, 2015
Having a SQL Server 2012 Enterprise (x64) on a Windows 2012 R2. We need to know, a reliable way, the number of processor sql server is using at a give time. We already know how many total processor are available to sql by getting info from sys.dm_os_sys_info.
For instance, a server has 40 processors, we want to know how many of those are being used at a given time. Since the load on the server may not be that high, we would like to know how many processors we can eliminate and the load will still be unaffected.
After watching the server performance for a while, we are predicting we may only need 16. But we would like to get some statistics before we reduce it to this number.
View 12 Replies
View Related
Mar 9, 2005
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
View 8 Replies
View Related
Jul 23, 2005
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 Related
Jul 23, 2005
Given 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 Related
Feb 26, 2007
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.
View 3 Replies
View Related
Mar 5, 2008
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
View 5 Replies
View Related
Feb 19, 2007
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:
View 8 Replies
View Related
Apr 4, 2006
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.
View 1 Replies
View Related
May 23, 2006
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???
View 1 Replies
View Related
Aug 17, 2006
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.
View 5 Replies
View Related
Jan 18, 2007
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,
View 1 Replies
View Related
Mar 26, 2008
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!
View 7 Replies
View Related
