Questions On Key Column In Case Table And Key Time Column In Nested Table Using Time Series Algorithm

Jun 4, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am confused on key column of case table and key time column of nested table by using Time Series algorithm.

In my case, the case table structure is as below:

Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),

While the nested table structure is as below:

Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)

So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?

Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?



Hope it is clear for your advices and help.

And I am looking forward to hearing from you shortly.



With best regards,



Yours sincerely,





View 1 Replies


ADVERTISEMENT

How To Create Key Time Column And Key Column For A Case Table And A Nested Table For Time Series Algorithm?

Jun 18, 2007

Hi, all experts here,



Thanks for your kind attention.



I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,



View 6 Replies View Related

Key Time Column For Time Series Algorithm

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

Questions About Microsift Time Series Algorithm

Sep 3, 2007

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

View 5 Replies View Related

Only One Case Table Allowed For Time Series Algortihm?

May 31, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I just encountered the problem that with Microsoft Time Series algorithm, no more than one case table is allowed? Mining structure is not allowed to contain any nested table?



Or did I miss out anything there? Thank you very much 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

Case Table And Nested Tables In Association Algorithm

Aug 30, 2006

hi

 

i m trying to build microsoft association model using Microsoft association algorithm. i got

1) patient table(patientid, name, city)

2) diseases(diseaseid, dieseasename)

It is M:N [many to many] relationship between above tables, so

3)Patient_diseases(patientid,disease_id). [RELATIONSHIP TABLE]

 i am trying to associate city in patient table --> disease in diseases table.   I want to build association data mining model and use it on web form, such a way when the user enters city associated disease will be displayed.

should i select all 3 table to build the model? could help me to decide what tables should i select as Case and what tables as Nested? what attributes from the table should i select as key, input, predictive ?

i am using data mining tutorials on sqlserverdatamining.com to build this model. is there anything further during my model building i get into confusion? please suggest me where i can find complete resource or inform here.

i appreciate Mr.Jamie for his guidance so far in my academic project. i do have the book 'Data mining with sql server 2005'.  I left with just one day to do this and document.

hoping someone could suggest. your help is much appreciated.

regards

raju

View 4 Replies View Related

Time Series Algorithm

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

Time Series Algorithm

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

Viewing The Result Of Predction With Time Series Algorithm In C#

Dec 17, 2007

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?

View 1 Replies View Related

Multiple Dimensions Or Keys Using Time Series Algorithm

Feb 21, 2007

Hi,

I'm trying to create a DM model using TS algorithm, to predict sales for different products and channels but I can only get it to work using one of those two "dimensions" or columns the other one is ignored (This is, my fact table contains a key for time, a key for channel a key for product and the metrics and the model only seems to allow working with time, the metrics and only one of the other dimensions product or channel ..) Am I missing something?

Thanks

View 1 Replies View Related

Strange Data Type Displayed On Time Series Algorithm Model

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

Questions About Time Series

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

Strange Problem With Time Series Data On Time Series Chart

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

How To Get Table Column Name At Run Time

Jun 18, 2008

I want to get a table column name at run time through query. Can any one help me.

ehsan ulhaq dar

View 5 Replies View Related

Column In Table - Text To Time

Sep 19, 2013

I have the following time value column in a table:

next_run_time
230000
73000
70000

The above actually translates to 23:00, 7:00AM,7:30AM, etc. How can I convert this into a meaningful time value?

[CODE]
SELECT NEXT_RUN_TIME FROM TABLE T1
[CODE]

View 2 Replies View Related

How To Update Time Series Based On Another Table(or View)??

Feb 15, 2005

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

View 3 Replies View Related

Transact SQL :: Time Series Table Which Generates A Flag For Every 10 Mins

May 15, 2015

i have a time series table which generates a flag for every 10 mins. the table looks as below. I am trying to write a sql query which gives me the start date and end date of a particular flag, each time the flag starts. i tried to do it using cursors and actually joining the table to itself based on a 10 min offset but couldn't get the required results.

startdate enddate
flag
2009-11-02 23:30 2009-11-02 23:50
-999
2009-12-08 04:50 2009-12-08 04:50 -990

View 3 Replies View Related

Nested Table Key Column Is Not Bound To An Input Rowset Column

Jan 11, 2008

Hi!
I have a "little" problem with nested case model:



-- "normal" database:

DROP TABLE [unitInfo] ;

GO

CREATE TABLE unitInfo (

unitID INT PRIMARY KEY

, beginDate SMALLDATETIME

, area VARCHAR(10)

, partSize INT

, y2predict MONEY

) ;

go

INSERT INTO unitInfo

VALUES (1, '2007-02-01', 'home', 42, 10.0) ;

INSERT INTO unitInfo

VALUES (2, '2007-03-05', 'home', 43, 11.0) ;

INSERT INTO unitInfo

VALUES (3, '2007-02-02', 'office', 11, 11.4) ;

INSERT INTO unitInfo

VALUES (4, '2007-02-01', 'office', 10, 33.6) ;

INSERT INTO unitInfo

VALUES (5, '2007-02-01', 'office', 42, 44.1) ;



CREATE TABLE unitLog (

id INT IDENTITY(1, 1)

PRIMARY KEY

, logtime SMALLDATETIME

, -- combination of logtime/unitID is unique

unitID INT

, -- "FK" on unitInfo

m1 FLOAT

, m2 FLOAT

)





INSERT INTO [unitLog]

VALUES ('2007-01-01', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-01', 2, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-01', 3, 63.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-02', 4, 432.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-02', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-03', 1, 423.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-04', 1, 432.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-05', 2, 43.0, 441.0)

INSERT INTO [unitLog]

VALUES ('2007-01-06', 2, 43.0, 4.0)

INSERT INTO [unitLog]

VALUES ('2007-01-06', 3, 43.0, 4.0)

INSERT INTO [unitLog]

VALUES ('2007-01-07', 1, 4.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 3.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-09', 2, 143.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-10', 3, 143.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-11', 4, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-11', 5, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-12', 2, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-13', 4, 413.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-14', 4, 43.0, 414.0)

INSERT INTO [unitLog]

VALUES ('2007-01-14', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-20', 1, 43.0, 414.0)

INSERT INTO [unitLog]

VALUES ('2007-01-22', 1, 43.0, 414.0)



-- SSAS:

CREATE MINING STRUCTURE NestedStructure

( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE

, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS

, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS

, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS

)

)

ALTER MINING STRUCTURE NestedStructure

ADD MINING MODEL nestedModel ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR

,y2predict REGRESSOR PREDICT_ONLY

, logdata ([id] , unitID

, m1, m2

)

) USING Microsoft_Decision_Trees

/* version 1*/

insert into NestedStructure ( unitID, beginDate, area, partSize, y2predict

, logdata(skip,unitID, m1, m2))

openrowset('sqloledb', Server=myserver;Trusted_Connection=yes;,

'Shape {select * FROM mydb.dbo.unitInfo }

Append ( { select id, unitID, m1, m2 from mydb.dbo.unitLog }

Relate unitID to unitID ) as logdata ')

Parsing the query ...

OLE DB error: OLE DB or ODBC error: Syntax error or access violation; 42000.

Parsing complete

Where is the error?



/*version 2*/

CREATE MINING STRUCTURE NestedStructure1

( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE

, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS

, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS

, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS

)

)

ALTER MINING STRUCTURE NestedStructure1

ADD MINING MODEL nestedModel1 ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR

,y2predict REGRESSOR PREDICT_ONLY

, logdata ([id] , unitID

, m1, m2

)

) USING Microsoft_Decision_Trees



insert into mining structure NestedStructure1 ( unitID, beginDate, area, partSize, y2predict

, logdata(skip,unitID, m1, m2))

Shape {openquery(dsnDB,'select * FROM mydb.dbo.unitInfo') }

Append ( { openquery(dsnDB,'select id, unitID, m1, m2 from mydb.dbo.unitLog') }

Relate unitID to unitID ) as logdata



Parsing the query ...

Error (Data mining):

INSERT INTO error: The '[logdata].[id]' nested table key column is not bound to an input rowset column.

Parsing complete








Remark that combination logtime/unitID is the natural key in unitLog.
"ID" is the surrugate key.

What is wrong here...?

View 6 Replies View Related

Transact SQL :: How To Set Case Sensitivity Of A Column In Design Time

Aug 3, 2015

With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?

N.B.: Using SQL Server 2005.

View 22 Replies View Related

SQL Server 2008 :: Calculated Time Difference Column In Create Table

Jul 14, 2015

How to include a time difference column using 2 other date columns during creation of a table ?

The requirement is to create a table and include the following columns:

1.Downtime start date & time
2.Downtime end date & time
3.Downtime Duration in hh:mm (calculated date difference between column 1 and 2)

View 3 Replies View Related

A Time Series Prediction Was Requested With A Start Time Further In The Past Than The Internal Models Of The Mining Model

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

A Key Time As A Split Variable In Microsoft Time Series?

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

Splitting SQL Server Date/Time Column Into Access Date Column And Access Time Column

Jan 24, 2008

I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.

What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?

Thanks,

Steve

View 3 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

MS Time Series - Quick To Process The Model But Takes Very Long Time To Open Mining Model Viewer

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

Why I Got The Message As You Need To Create The Many-to-one Relationship Between The Case Table And The Nested Table?

May 30, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?

Hope it is clear for your help.

Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 4 Replies View Related

Getting Time From Date/time Column

May 16, 2008

How can I obtain just the time portion from a date/time column?
My data contains "2008-05-19 09:30:00.000"
Actually, all I want/need is the hh:mm part of it.

Thanks,
Walt

View 2 Replies View Related

SQL 2012 :: Converting Time String In Temp Table To Military Time Then Cast As Integer?

Dec 26, 2014

I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:

Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')

how this can be done while my temp table is in session?

View 2 Replies View Related

Question On Case Table And Nested Table

Jun 5, 2007

Hi, all here,



As we are allowed to select one table as both case table and nested table, however what is the benefit of using one table as both case table and nested table? Thanks in advance for your advices.

I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 3 Replies View Related

Adding A Time Column To A Date Column

Jul 20, 2005

I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq

View 2 Replies View Related

How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?

Sep 27, 2006

For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View 2 Replies View Related

Question On Column Mappings Between Mining Structure And Case Table For Lift Chart

May 3, 2007

Hi, all experts here,

I am a bit confused for the model evaluation (lift chart), should we map all the columns for both the mining structure and the case table? I mean for those predictive models, we have a predict column, shouldnt we ignore the mapping of the predictive column between the mining structure and the case table? But it seemes we are not allowed to miss the predictive column mapping between the mining structure and the case table.

Why is that? Could any experts here give me some explanation on that?

Hope my question is clear for your help.

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 3 Replies View Related

Case &&amp; Nested Table Selection And Errors

Aug 31, 2006

hi

Here are the two tables again.

1)PATIENT(PATIENT_ID,NAME,CITY)

2) DISEASES(DISEASE_ID,NAME)

I am trying to select patient table as case and diseases table as nested to create an association model. i m getting following error.

Disease table cannot be used as a nested table because it does not have a many-to-one relationship with the case table. You need to create a many-to-one relationship between the two tables in the data source file.

i have created a relationship by dragging Disease_id from diseases table on Patient_id in patient table. when i am trying to select Patient_id as key, City as input, it is not showing disease_id to choose as a predict column.

please suggest me if i am doing anything wrong? i have not done any thing to do my datbase, just selected the tables i want to create an association model on and trying to create association model.

your help and insight is highly appreciated.

regards

Raju

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved