SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream
Locked
SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream
 
|
Hello, I’m using the GradPack version of SPSS Modeler 15.
I’ve read the on-line user doc concerning SQL generation, and I’m fairly confident to have everything configured correctly; however, I cannot get a simple
test case working. That is, I cannot successfully generate the entire SQL for a stream containing just 4 nodes: a source
Database node, a Filter node, a simple Derive node, and a
Table node). When I select the
Table node above and click on “Preview SQL Generation” icon (in toolbar at the top), only the source
Database node is highlighted in purple, and no SQL is generated in the stream messages view. My stream properties regarding SQL generation are as follows: ·
Options /
Logging and Status :
o
Display SQL in the messages log during stream execution (checked);
o
Display SQL generation details in the messages log during stream preparation (checked);
o
Reformat SQL for improved readability (checked). ·
Options /
Optimization :
o
Enable stream rewriting (checked and every sub-option checked);
o
Enable parallel processing (checked);
o
Generate SQL (checked and every sub-option checked); Did anyone get this to work using SPSS Modeler 15 (or even using version 14 since you may have a tip that applies to 15)? That is, can anyone confirm that if
they set up an equivalent test, select the Table node and click on “Preview SQL Generation” icon,
all three upstream nodes are highlighted in purple and the expected native SQL representing the whole data stream is properly generated in the stream messages view? Note that my Google searches so far only seem to point to the on-line SPSS Modeler
user doc and, as mentioned above, I’m fairly confident to have stuff configured correctly … but I could be missing something … is there a workaround, tip, or trick to get this to work? Much appreciated, Pierre |
Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream
|
|
As far as I know, SQL in Modeler only applies
to database import and export nodes. SQL is not the scripting language
that drives Modeler. Modeler uses ODBC-compliant SQL to communicate to
the database when importing and exporting data from and to the database.
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: "Iorio, Pierre" <[hidden email]> To: [hidden email], Date: 12/04/2013 01:41 PM Subject: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Sent by: "SPSSX(r) Discussion" <[hidden email]> Hello, I’m using the GradPack version of SPSS Modeler 15. I’ve read the on-line user doc concerning SQL generation, and I’m fairly confident to have everything configured correctly; however, I cannot get a simple test case working. That is, I cannot successfully generate the entire SQL for a stream containing just 4 nodes: a source Database node, a Filter node, a simple Derive node, and a Table node). When I select the Table node above and click on “Preview SQL Generation” icon (in toolbar at the top), only the source Database node is highlighted in purple, and no SQL is generated in the stream messages view. My stream properties regarding SQL generation are as follows: · Options / Logging and Status : o Display SQL in the messages log during stream execution (checked); o Display SQL generation details in the messages log during stream preparation (checked); o Reformat SQL for improved readability (checked). · Options / Optimization : o Enable stream rewriting (checked and every sub-option checked); o Enable parallel processing (checked); o Generate SQL (checked and every sub-option checked); Did anyone get this to work using SPSS Modeler 15 (or even using version 14 since you may have a tip that applies to 15)? That is, can anyone confirm that if they set up an equivalent test, select the Table node and click on “Preview SQL Generation” icon, all three upstream nodes are highlighted in purple and the expected native SQL representing the whole data stream is properly generated in the stream messages view? Note that my Google searches so far only seem to point to the on-line SPSS Modeler user doc and, as mentioned above, I’m fairly confident to have stuff configured correctly … but I could be missing something … is there a workaround, tip, or trick to get this to work? Much appreciated, Pierre |
Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream
|
|
In reply to this post by Iorio, Pierre
Thank you, but it should be supported for other nodes too. Have a look at this link: http://pic.dhe.ibm.com/infocenter/spssmodl/v15r0m0/index.jsp?topic=%2Fcom.ibm.spss.modeler.help%2Fsql_optimization_nodes.htm Regards, Pierre From: Rick Oliver [mailto:[hidden email]]
As far as I know, SQL in Modeler only applies to database import and export nodes. SQL is not the scripting language that drives Modeler. Modeler uses ODBC-compliant SQL to
communicate to the database when importing and exporting data from and to the database.
|
Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream
|
|
In reply to this post by Iorio, Pierre
Hi Pierre,
What kind of DB are you connecting to? What filter and derive operations are specified in those nodes? Alex From: "Iorio, Pierre" <[hidden email]> To: [hidden email], Date: 12/04/2013 02:41 PM Subject: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Sent by: "SPSSX(r) Discussion" <[hidden email]> Hello, I’m using the GradPack version of SPSS Modeler 15. I’ve read the on-line user doc concerning SQL generation, and I’m fairly confident to have everything configured correctly; however, I cannot get a simple test case working. That is, I cannot successfully generate the entire SQL for a stream containing just 4 nodes: a source Database node, a Filter node, a simple Derive node, and a Table node). When I select the Table node above and click on “Preview SQL Generation” icon (in toolbar at the top), only the source Database node is highlighted in purple, and no SQL is generated in the stream messages view. My stream properties regarding SQL generation are as follows: · Options / Logging and Status : o Display SQL in the messages log during stream execution (checked); o Display SQL generation details in the messages log during stream preparation (checked); o Reformat SQL for improved readability (checked). · Options / Optimization : o Enable stream rewriting (checked and every sub-option checked); o Enable parallel processing (checked); o Generate SQL (checked and every sub-option checked); Did anyone get this to work using SPSS Modeler 15 (or even using version 14 since you may have a tip that applies to 15)? That is, can anyone confirm that if they set up an equivalent test, select the Table node and click on “Preview SQL Generation” icon, all three upstream nodes are highlighted in purple and the expected native SQL representing the whole data stream is properly generated in the stream messages view? Note that my Google searches so far only seem to point to the on-line SPSS Modeler user doc and, as mentioned above, I’m fairly confident to have stuff configured correctly … but I could be missing something … is there a workaround, tip, or trick to get this to work? Much appreciated, Pierre |
Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream
|
|
In reply to this post by Iorio, Pierre
Hi Alex, ·
DB is MS Access; ·
Simple
Filter node that removes most fields except 3; ·
Simple
Derive node which derives FSA from postal code using this expression: substring(1,3,postal_code) Thanks Pierre From: SPSSX(r) Discussion [mailto:[hidden email]]
On Behalf Of Alex Reutter Hi Pierre,
|
Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream
|
|
In reply to this post by Iorio, Pierre
You are correct. Modeler supports "pushback"
to the database in some other nodes, but I don't know if there is any way
to expose the generated SQL since I think it only gets resolved when the
entire stream is executed. I could be wrong.
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: "Iorio, Pierre" <[hidden email]> To: [hidden email], Date: 12/04/2013 02:04 PM Subject: Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Sent by: "SPSSX(r) Discussion" <[hidden email]> Thank you, but it should be supported for other nodes too. Have a look at this link: http://pic.dhe.ibm.com/infocenter/spssmodl/v15r0m0/index.jsp?topic=%2Fcom.ibm.spss.modeler.help%2Fsql_optimization_nodes.htm Regards, Pierre From: Rick Oliver [mailto:oliverr@...] Sent: Wednesday, December 04, 2013 2:53 PM To: Iorio, Pierre Cc: [hidden email] Subject: Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream As far as I know, SQL in Modeler only applies to database import and export nodes. SQL is not the scripting language that drives Modeler. Modeler uses ODBC-compliant SQL to communicate to the database when importing and exporting data from and to the database. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: oliverr@... From: "Iorio, Pierre" <piorio@...> To: [hidden email], Date: 12/04/2013 01:41 PM Subject: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Sent by: "SPSSX(r) Discussion" <[hidden email]> Hello, I’m using the GradPack version of SPSS Modeler 15. I’ve read the on-line user doc concerning SQL generation, and I’m fairly confident to have everything configured correctly; however, I cannot get a simple test case working. That is, I cannot successfully generate the entire SQL for a stream containing just 4 nodes: a source Database node, a Filter node, a simple Derive node, and a Table node). When I select the Table node above and click on “Preview SQL Generation” icon (in toolbar at the top), only the source Database node is highlighted in purple, and no SQL is generated in the stream messages view. My stream properties regarding SQL generation are as follows: · Options / Logging and Status : o Display SQL in the messages log during stream execution (checked); o Display SQL generation details in the messages log during stream preparation (checked); o Reformat SQL for improved readability (checked). · Options / Optimization : o Enable stream rewriting (checked and every sub-option checked); o Enable parallel processing (checked); o Generate SQL (checked and every sub-option checked); Did anyone get this to work using SPSS Modeler 15 (or even using version 14 since you may have a tip that applies to 15)? That is, can anyone confirm that if they set up an equivalent test, select the Table node and click on “Preview SQL Generation” icon, all three upstream nodes are highlighted in purple and the expected native SQL representing the whole data stream is properly generated in the stream messages view? Note that my Google searches so far only seem to point to the on-line SPSS Modeler user doc and, as mentioned above, I’m fairly confident to have stuff configured correctly … but I could be missing something … is there a workaround, tip, or trick to get this to work? Much appreciated, Pierre |
Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream
|
|
Thanks Rick. I’m hoping someone will know how to view/log this pushback SQL code when a stream is executed (i.e. the SQL representing the entire sequence
of nodes operating on the data from the source DB node to the final output node). Pierre From: Rick Oliver [mailto:[hidden email]]
You are correct. Modeler supports "pushback" to the database in some other nodes, but I don't know if there is any way to expose the generated SQL since I think it only gets
resolved when the entire stream is executed. I could be wrong.
|
Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream
|
|
In reply to this post by Iorio, Pierre
Hi Pierre,
The issue is MS Access; SQL pushback isn't supported for that DB (see the "Prerequisites" tab of system requirements doc at: http://publib.boulder.ibm.com/infocenter/prodguid/v1r0/clarity-reports/report/html/softwareReqsForProductByComponent?deliverableId=1305206735795&duComponent=Desktop_AE6F0780122811E2BF6931D00925FE36; this is a report generated from http://publib.boulder.ibm.com/infocenter/prodguid/v1r0/clarity/softwareReqsForProduct.html) The stream won't produce SQL when connected to an unsupported DB because, as Rick mentioned, the SQL is generated at stream execution time. Alex From: "Iorio, Pierre" <[hidden email]> To: [hidden email], Date: 12/04/2013 03:11 PM Subject: Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Sent by: "SPSSX(r) Discussion" <[hidden email]> Hi Alex, · DB is MS Access; · Simple Filter node that removes most fields except 3; · Simple Derive node which derives FSA from postal code using this expression: substring(1,3,postal_code) Thanks Pierre From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Alex Reutter Sent: Wednesday, December 04, 2013 3:00 PM To: [hidden email] Subject: Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Hi Pierre, What kind of DB are you connecting to? What filter and derive operations are specified in those nodes? Alex From: "Iorio, Pierre" <piorio@...> To: [hidden email], Date: 12/04/2013 02:41 PM Subject: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Sent by: "SPSSX(r) Discussion" <[hidden email]> Hello, I’m using the GradPack version of SPSS Modeler 15. I’ve read the on-line user doc concerning SQL generation, and I’m fairly confident to have everything configured correctly; however, I cannot get a simple test case working. That is, I cannot successfully generate the entire SQL for a stream containing just 4 nodes: a source Database node, a Filter node, a simple Derive node, and a Table node). When I select the Table node above and click on “Preview SQL Generation” icon (in toolbar at the top), only the source Database node is highlighted in purple, and no SQL is generated in the stream messages view. My stream properties regarding SQL generation are as follows: · Options / Logging and Status : o Display SQL in the messages log during stream execution (checked); o Display SQL generation details in the messages log during stream preparation (checked); o Reformat SQL for improved readability (checked). · Options / Optimization : o Enable stream rewriting (checked and every sub-option checked); o Enable parallel processing (checked); o Generate SQL (checked and every sub-option checked); Did anyone get this to work using SPSS Modeler 15 (or even using version 14 since you may have a tip that applies to 15)? That is, can anyone confirm that if they set up an equivalent test, select the Table node and click on “Preview SQL Generation” icon, all three upstream nodes are highlighted in purple and the expected native SQL representing the whole data stream is properly generated in the stream messages view? Note that my Google searches so far only seem to point to the on-line SPSS Modeler user doc and, as mentioned above, I’m fairly confident to have stuff configured correctly … but I could be missing something … is there a workaround, tip, or trick to get this to work? Much appreciated, Pierre |
Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream
|
|
Assuming you are connecting to a supported
database, I don't know if there's a way to expose the generated SQL for
the "pushback" operations.
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Alex Reutter/Burlington/IBM@IBMUS To: [hidden email], Date: 12/04/2013 02:31 PM Subject: Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Sent by: "SPSSX(r) Discussion" <[hidden email]> Hi Pierre, The issue is MS Access; SQL pushback isn't supported for that DB (see the "Prerequisites" tab of system requirements doc at: http://publib.boulder.ibm.com/infocenter/prodguid/v1r0/clarity-reports/report/html/softwareReqsForProductByComponent?deliverableId=1305206735795&duComponent=Desktop_AE6F0780122811E2BF6931D00925FE36; this is a report generated from http://publib.boulder.ibm.com/infocenter/prodguid/v1r0/clarity/softwareReqsForProduct.html) The stream won't produce SQL when connected to an unsupported DB because, as Rick mentioned, the SQL is generated at stream execution time. Alex From: "Iorio, Pierre" <[hidden email]> To: [hidden email], Date: 12/04/2013 03:11 PM Subject: Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Sent by: "SPSSX(r) Discussion" <[hidden email]> Hi Alex, · DB is MS Access; · Simple Filter node that removes most fields except 3; · Simple Derive node which derives FSA from postal code using this expression: substring(1,3,postal_code) Thanks Pierre From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Alex Reutter Sent: Wednesday, December 04, 2013 3:00 PM To: [hidden email] Subject: Re: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Hi Pierre, What kind of DB are you connecting to? What filter and derive operations are specified in those nodes? Alex From: "Iorio, Pierre" <piorio@...> To: [hidden email], Date: 12/04/2013 02:41 PM Subject: SPSS Modeler - generating the SQL representing the sequence of nodes in a data stream Sent by: "SPSSX(r) Discussion" <[hidden email]> Hello, I’m using the GradPack version of SPSS Modeler 15. I’ve read the on-line user doc concerning SQL generation, and I’m fairly confident to have everything configured correctly; however, I cannot get a simple test case working. That is, I cannot successfully generate the entire SQL for a stream containing just 4 nodes: a source Database node, a Filter node, a simple Derive node, and a Table node). When I select the Table node above and click on “Preview SQL Generation” icon (in toolbar at the top), only the source Database node is highlighted in purple, and no SQL is generated in the stream messages view. My stream properties regarding SQL generation are as follows: · Options / Logging and Status : o Display SQL in the messages log during stream execution (checked); o Display SQL generation details in the messages log during stream preparation (checked); o Reformat SQL for improved readability (checked). · Options / Optimization : o Enable stream rewriting (checked and every sub-option checked); o Enable parallel processing (checked); o Generate SQL (checked and every sub-option checked); Did anyone get this to work using SPSS Modeler 15 (or even using version 14 since you may have a tip that applies to 15)? That is, can anyone confirm that if they set up an equivalent test, select the Table node and click on “Preview SQL Generation” icon, all three upstream nodes are highlighted in purple and the expected native SQL representing the whole data stream is properly generated in the stream messages view? Note that my Google searches so far only seem to point to the on-line SPSS Modeler user doc and, as mentioned above, I’m fairly confident to have stuff configured correctly … but I could be missing something … is there a workaround, tip, or trick to get this to work? Much appreciated, Pierre |
| Free forum by Nabble | Edit this page |