Discussion:
mining process flow
(too old to reply)
Paulo
2008-09-22 12:43:01 UTC
Permalink
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
Bogdan Crivat [MSFT]
2008-09-22 17:31:41 UTC
Permalink
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(<training data reference>) 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/Default.aspx?tabid=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
Post by Paulo
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
Paulo
2008-09-22 18:24:01 UTC
Permalink
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
Post by Bogdan Crivat [MSFT]
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(<training data reference>) 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.
http://sqlserverdatamining.com/ssdm/Default.aspx?tabid=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
Post by Paulo
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
Bogdan Crivat [MSFT]
2008-09-22 18:33:03 UTC
Permalink
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/Default.aspx?tabid=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
Post by Paulo
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?
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
Post by Bogdan Crivat [MSFT]
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(<training data reference>) 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.
http://sqlserverdatamining.com/ssdm/Default.aspx?tabid=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
Post by Paulo
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
Loading...