Addicted To Stored Procedures: A Time Series Problem. Is It Possible?
Oct 31, 2006
It's been about two weeks since I first began using SQL Stored procedures and now I am thinking already about changing many of my plans and doing those operations in them. There is a problem I want to address and see if it is possible to accomplish it in SQL Server instead of the client C# code.
I have a time series. In simplified presentation it is a table of two columns. The first one is float, the second is DateTime. There might be quite a few rows in the table:
A1 ... DT1
A2 ... DT2
................
An.... DTn
What I need to do is to find patters among A1,A2,....An elements. They vary in magnitude in a rather random order. They constitute what some would call a Markov chain. I demonstrate what I want in a couple of real life examples that I would not have a problem to run in my C# code at all.
(1) element Ak may be only 90% of the element in the previous row Ak-1. I want to catch it. I want to get the DateTime stamp when it happened. The next step is: I want to go down the chain of rows and find the element Am which rebounds to the magnitude of the element Ak. In other words Am==Ak. I want to remember its DateTime stamp.Then I want to find all elements in the chain of rows that follow the same pattern.
How can I do it in SQL language? Is it possible? I've been thinking about creating a bunch of temporary tables with rows shifted up by one and and destroying them after a pattern has been found in the same rows of many tables.
(2) I want to collect a distribution of many such patterns and analyze then in C# code but the elementary block is the operation I described in point (1). If I could get a cue as to how to go about it I could figure out the rest, I hope.
I learn by examples, by looking at code samples. So far I haven't seen anything that fits this task. If anyone could send me in such direction it would be great. Most of the things I've seen is "SELECT FROM CUSTOMERS," etc.
A somewhat related question. Years ago I stumbled in MSN help on a set of Math routines (they could have been part of a class) that were designed specifically for analyzing patters. It is not the Math class, I have looked into it. It is something different. I cannot find them now. I did not save a bookmark back then.
Does anyone know what I am talking about: math functions for time series analysis?
Thanks.
View 11 Replies
ADVERTISEMENT
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
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
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
Jan 12, 2000
How can I compile all the stored procedures in the database at one shot?
thanks in advance
Pranav
View 2 Replies
View Related
Apr 25, 2001
Hi
I have many existing tables within my db with the date format mm/dd/yyyy
Is it possible to run a stored procedure in order to convert the EXISTING records to the date format dd/mm/yyyy?
Thanks
View 2 Replies
View Related
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
Dec 3, 2006
Background: We have SQL Server 2005 x64 running on a quad-core (dual dual-core) machine with 16GB of RAM. The database is about 10GB in size and we execute around a million stored procedures a day on it. Our application uses about 1000 different stored procedures on this machine. The application is a transactional B2B web-app with about 2000 users.
The problem we have is a really odd one that I can't seem to find much information on. We have a small number (3-4) of stored procedures that's exibiting this problem.
The stored proc in question takes on average 100ms CPU time to execute. It's a fairly complex stored proc, about 300 lines long, 6-7 select statements and it uses temp tables. No updates / inserts except for on the temp tables. It's executed about 5000 times per day. About once a week, though, execution times will suddenly jump up to 3000 ms average. This happens randomly during the day, although it seems to happen more often on Monday mornings (the DB is mostly unutilized over the weekend)
To fix this, I force the DB to recalculate the execution plan by adding / removing (depending what I did last time around) the line 'set arithabort on' at the top of the stored procedure. I have no idea why this works, but it does. Within seconds of changing it, the stored proc execution time will go back to it's normal range of 60-150ms.
I've tried setting the execution plan of the stored procedure but I can't get it to work - the execution plan is very long and I don't know how to debug the error I get.
What is happening? This happens with a couple of stored procedures - usually the more complex ones. Has anyone seen anything like this?
View 4 Replies
View Related
Oct 13, 2005
Dear group,is it possible in SQL-Server to see when a stored procedure wasexecuted ?I would say it is only possible with some traces but not with thestandard settings.For a short answer on that matter i'd be thankful.RegardsUli
View 4 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
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
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
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
May 28, 2006
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!!!!!!!
View 4 Replies
View Related
May 21, 2006
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?
View 1 Replies
View Related
Jan 19, 2008
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
View 4 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
May 22, 2006
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)
View 5 Replies
View Related
Dec 7, 2007
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
View 5 Replies
View Related
Sep 15, 2006
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
View 19 Replies
View Related
Apr 24, 2007
Is there any way to force a regressor using the time series algorithm?
View 4 Replies
View Related
Jan 24, 2008
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
View 7 Replies
View Related
Mar 9, 2007
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
View 3 Replies
View Related
