mining process flow

[Database Administrator]

Hi guys

I need to save the result clusters in a table by code, how can I do that?

I'm creating a process flow, using sql data mining structures. This
process consist in run clusters and for each cluster, run a logistic
regression. The mining strucuture for that is already, but I need to find a
way to save the cluster groups in a specific table.
I made a mistake with the name of this group but I know that you guys are
experts in SQL, and can help me to solve this trouble.

best
--
Paulo Carvalho
Statistics Master
PhD Markovs Chain
Univ S.Paulo

[Database Administrator]

The simplest way to to this is to build a a query in the BI Dev Studio tool
click the Save Query Result button.
The query should be something like
SELECT T.Key, Cluster() AS [Cluster] FROM YourModel PREDICTION JOIN
OPENQUERY() AS T...

Another more scalable solution is to use the Data Mining Query task in
Integration Services to execute the same kind of query and save the results
in a database.

Your query should return at least the key column from the training data and
the cluster label.



If you are using SQL Server 2008 you can simplify the process and build the
LogReg models directly on top of the DMX query, without saving the results
in a separate table. Also, you could have all the LogReg models in the same
mining structure, using the DMX Filtering feature.
More details :
- for training a model only with cases in a specific cluster:
http://sqlserverdatamining.com/ssdm/...abid=102&Id=24
- for DMX filtering: http://www.bogdancrivat.net/dm/archives/17




--
--
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Please do not send email directly to this alias. It is for newsgroup
purposes only.

thanks,
bogdan

"Paulo" wrote in message
news:96E9237D-E7B1-42BF-9879-EC99424C1CBC@microsoft.com...
> Hi guys
>
> I need to save the result clusters in a table by code, how can I do that?
>
> I'm creating a process flow, using sql data mining structures. This
> process consist in run clusters and for each cluster, run a logistic
> regression. The mining strucuture for that is already, but I need to find
> a
> way to save the cluster groups in a specific table.
> I made a mistake with the name of this group but I know that you guys are
> experts in SQL, and can help me to solve this trouble.
>
> best
> --
> Paulo Carvalho
> Statistics Master
> PhD Markovs Chain
> Univ S.Paulo

[Database Administrator]

tks boddan
as of now my select in running perfectly

so how can I save this select as table on my Database?
I know that I need to use INSERT INTO, but how can I insert it into my
Database?


find code below:

SELECT t.CPF, Cluster() AS cl
FROM Cluster1
PREDICTION JOIN
OPENROWSET
(
' SQLOLEDB.1',
' Provider=SQLOLEDB.1;Persist Security Info=False
; Initial Catalog=DADOS_GERAIS ; Data Source=bmw330i;User
ID=paulo_carvalho;Password=teste;' ,
' SELECT * FROM destab'
) AS T
ON
Cluster1.cpf=T.cpf
and Cluster1.fl_capital=T.fl_capital
and Cluster1.fl_carro=T.fl_carro
and Cluster1.fl_masc=T.fl_masc
and Cluster1.idade=T.idade
and Cluster1.renda_pres_est=T.renda_pres_est
and Cluster1.stats_novo_num=T.stats_novo_num




tks,

--
Paulo Carvalho


"Bogdan Crivat [MSFT]" wrote:

> The simplest way to to this is to build a a query in the BI Dev Studio tool
> click the Save Query Result button.
> The query should be something like
> SELECT T.Key, Cluster() AS [Cluster] FROM YourModel PREDICTION JOIN
> OPENQUERY() AS T...
>
> Another more scalable solution is to use the Data Mining Query task in
> Integration Services to execute the same kind of query and save the results
> in a database.
>
> Your query should return at least the key column from the training data and
> the cluster label.
>
>
>
> If you are using SQL Server 2008 you can simplify the process and build the
> LogReg models directly on top of the DMX query, without saving the results
> in a separate table. Also, you could have all the LogReg models in the same
> mining structure, using the DMX Filtering feature.
> More details :
> - for training a model only with cases in a specific cluster:
> http://sqlserverdatamining.com/ssdm/...abid=102&Id=24
> - for DMX filtering: http://www.bogdancrivat.net/dm/archives/17
>
>
>
>
> --
> --
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Please do not send email directly to this alias. It is for newsgroup
> purposes only.
>
> thanks,
> bogdan
>
> "Paulo" wrote in message
> news:96E9237D-E7B1-42BF-9879-EC99424C1CBC@microsoft.com...
> > Hi guys
> >
> > I need to save the result clusters in a table by code, how can I do that?
> >
> > I'm creating a process flow, using sql data mining structures. This
> > process consist in run clusters and for each cluster, run a logistic
> > regression. The mining strucuture for that is already, but I need to find
> > a
> > way to save the cluster groups in a specific table.
> > I made a mistake with the name of this group but I know that you guys are
> > experts in SQL, and can help me to solve this trouble.
> >
> > best
> > --
> > Paulo Carvalho
> > Statistics Master
> > PhD Markovs Chain
> > Univ S.Paulo
>
>
>

[Database Administrator]

You don't really need to use INSERT INTO. In BI Dev Studio Mining Model
Prediction pane there is a button, I think the icon represents a Floppy
Disk. That button allows you to save prediction results to a database.

If you want to do it with INSERT INTO (i.e. programmatically) you will need
to
- create a Linked Server instance in SQL Server, pointing to the Analysis
Services instance
- call INSERT INTO in SQL using the data from your query
An example here:
http://sqlserverdatamining.com/ssdm/...abid=102&Id=15


--
--
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Please do not send email directly to this alias. It is for newsgroup
purposes only.

thanks,
bogdan

"Paulo" wrote in message
news:2BFB33A5-7AC7-47AB-9AD7-73984CFC55EC@microsoft.com...
> tks boddan
> as of now my select in running perfectly
>
> so how can I save this select as table on my Database?
> I know that I need to use INSERT INTO, but how can I insert it into my
> Database?
>
>
> find code below:
>
> SELECT t.CPF, Cluster() AS cl
> FROM Cluster1
> PREDICTION JOIN
> OPENROWSET
> (
> ' SQLOLEDB.1',
> ' Provider=SQLOLEDB.1;Persist Security Info=False
> ; Initial Catalog=DADOS_GERAIS ; Data Source=bmw330i;User
> ID=paulo_carvalho;Password=teste;' ,
> ' SELECT * FROM destab'
> ) AS T
> ON
> Cluster1.cpf=T.cpf
> and Cluster1.fl_capital=T.fl_capital
> and Cluster1.fl_carro=T.fl_carro
> and Cluster1.fl_masc=T.fl_masc
> and Cluster1.idade=T.idade
> and Cluster1.renda_pres_est=T.renda_pres_est
> and Cluster1.stats_novo_num=T.stats_novo_num
>
>
>
>
> tks,
>
> --
> Paulo Carvalho
>
>
> "Bogdan Crivat [MSFT]" wrote:
>
>> The simplest way to to this is to build a a query in the BI Dev Studio
>> tool
>> click the Save Query Result button.
>> The query should be something like
>> SELECT T.Key, Cluster() AS [Cluster] FROM YourModel PREDICTION JOIN
>> OPENQUERY() AS T...
>>
>> Another more scalable solution is to use the Data Mining Query task in
>> Integration Services to execute the same kind of query and save the
>> results
>> in a database.
>>
>> Your query should return at least the key column from the training data
>> and
>> the cluster label.
>>
>>
>>
>> If you are using SQL Server 2008 you can simplify the process and build
>> the
>> LogReg models directly on top of the DMX query, without saving the
>> results
>> in a separate table. Also, you could have all the LogReg models in the
>> same
>> mining structure, using the DMX Filtering feature.
>> More details :
>> - for training a model only with cases in a specific cluster:
>> http://sqlserverdatamining.com/ssdm/...abid=102&Id=24
>> - for DMX filtering: http://www.bogdancrivat.net/dm/archives/17
>>
>>
>>
>>
>> --
>> --
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Please do not send email directly to this alias. It is for newsgroup
>> purposes only.
>>
>> thanks,
>> bogdan
>>
>> "Paulo" wrote in message
>> news:96E9237D-E7B1-42BF-9879-EC99424C1CBC@microsoft.com...
>> > Hi guys
>> >
>> > I need to save the result clusters in a table by code, how can I do
>> > that?
>> >
>> > I'm creating a process flow, using sql data mining structures. This
>> > process consist in run clusters and for each cluster, run a logistic
>> > regression. The mining strucuture for that is already, but I need to
>> > find
>> > a
>> > way to save the cluster groups in a specific table.
>> > I made a mistake with the name of this group but I know that you guys
>> > are
>> > experts in SQL, and can help me to solve this trouble.
>> >
>> > best
>> > --
>> > Paulo Carvalho
>> > Statistics Master
>> > PhD Markovs Chain
>> > Univ S.Paulo
>>
>>
>>