how to create key time column and key column for a case table and a nested table for time series

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,

Can you please clarify if you're creating an OLAP mining model or a relational mining model?

Thanks

|||

Hi, Shuvro,

At the moment I am trying a create a relational mining model.

But it will be brilliant to hear from you for both how to create an OLAP mining model as well using case table and nested table with Time Series Algorithm.

Thanks and I am looking forward to hearing from you further for your advices.

With best regards,

Yours sincerely,

|||

Can you please post the schema of your case and nested table for the relational scenario?

For the OLAP mining model, the design would be on the same lines as the relational, but instead using the dimension attribute and the measure. The TIME KEY usually can be selected from your date dimension, depending on the granularity of your data, but you'll have to do some work to make it correctly sortable. (e.g. Month names are not a valid time key).

|||

You can mine just the fact table and ignore the date table. The fact table needs to have the date value anyway. Your model would look something like this

CREATE MINING STRUCTURE TS_Structure

(

[Date] LONG KEY TIME,

[Region] TEXT KEY,

Value1 DOUBLE CONTINUOUS PREDICT,

Value2 DOUBLE CONTINUOUS PREDICT

)

ALTER MINING STRUCTURE ADD MINING MODEL TS_Model

(

[Date],

[Region],

Value1 PREDICT,

Value2 PREDICT

) USING Microsoft_Time_Series

When using the tools, you would simply mark your source table as the "Case" table (not nested) and then mark both the [Date] column and the [Region] column as keys. The tools will figure it out.|||

Hi, Jamie,

Thanks for the advices.

With best regards,

Yours sincerely,

how to create index (Case when)

How to create index when there is a SQL statement like
Select count(1) as [Total],
sum(Case when Field1 < Field2 then 1 else 0 End) as [Selected]
from Table1
Thx in Adv
XLDBUse one index on each column ( field 1 field 2 ) ...use nonclustered for each

however, if table is small ( say less than 1000 row s ) SQL probably wont use indexes..it will scan whole table.|||the table has 2 million records|||actually, there are 2 similar tables that i use the query on...

the table with 2 million records is taking 57 seconds
the table with 40,000 records is taking 1 second

there is only 1 index on "Field1" in "Case When" statement

How to create a second independent transaction inside a CLR stored procedure?

I use the context connection for the "normal" work in the CLR procedure.
But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.

What is the best way to do this inside a CLR procedure?

Thanx a lot

Hi!

Create another connection to the server and create a transaction on this second connection. Please note that since your main connection is inproc (“context connection=true” in the connection string), the second connection should be non-inproc (network. Of cause, you can connect to the same server.).

|||Thank you,

I tried this already. But I got an error when I call BeginTransaction on the second connection. When I remember correctly it as "Transcation already in use by another session". I call the procedure from t-sql inside a begin tran block

Any idea about that?

I will try to build a small example an post it here.|||

Hello,
I made a small example to reproduce the error.

CLR procedure
<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub Test(ByVal sConnectString As String)

Dim t As SqlClient.SqlTransaction = Nothing

Dim c As SqlClient.SqlConnection = Nothing

c = New SqlClient.SqlConnection(sConnectString)

c.Open()

t = c.BeginTransaction()

t.Commit()

c.Close()

End Sub

T-SQL caller:
begin tran
execute Test 'Server=(local);database=cpwerx;user Id=sa;password=xyz'
commit tran

Error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Cas':
System.Data.SqlClient.SqlException: Transaction context in use by another session.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.PropagateTransactionCookie(Byte[] cookie)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

|||

Hi!

Modify your connection string so that it has "Enlist=false" in it. Otherwise the connection inherits current transaction.

More on "Enlist" option is in doc on SqlConnection.ConnectionString property