OLE DB Data Mining SQL syntax

OLE DB Data Mining SQL syntax

Post by John Sandifor » Sun, 08 Dec 2002 01:05:19



Hi,
I am having difficulty running the following set of Data
Mining statements in the DMSample application, using the
dmtest.mdb database available from the Data Mining User
Group downloads.  Any help would really help me out!

I am trying to use the prediction output from one data
mining model as an input to another data mining model
WITHOUT using a temporary table.

Here are the queries I am trying, which I am aware would
make no sense in a real scenario!  It is the last
statement I am having problems with...

(N.B. connection properties are : Server = "",
Provider="MSDMine", Mining Location = "C:\Temp")

The following scripts are also attached in DMSample xml
format
///////////////////////////////////////////

CREATE MINING MODEL Preprocess
(
    TheKey long KEY,
    Age double CONTINUOUS,
    Gender text DISCRETE,
    CarColor text DISCRETE,
    HairColor text DISCRETE PREDICT

) USING Microsoft_Decision_Trees (Minimum_Leaf_Cases = 1,
Complexity_Penalty = 0)

////////////////////////////////////////////////

insert into [Preprocess]
(
TheKey, Age, Gender, CarColor, HairColor
)
Openrowset('Microsoft.jet.oledb.4.0', 'data source =
dmtest.mdb', 'Select [Key], Age, Gender, CarColor,
HairColor From People')

////////////////////////////////////////////////

Select t.[KEY] As [Key], t.Age As Age, t.Gender As Gender,
t.CarColor As CarColor, Predict([Preprocess].[HairColor])
As HairColor
From Preprocess PREDICTION JOIN
OPENROWSET
(
        'Microsoft.Jet.OLEDB.4.0',
        'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
dmtest.mdb;Persist Security Info=False',
        'Select [Key],[Age],[Gender],[CarColor] From
People'
) As t ON
        [Preprocess].[TheKey] = t.[Key] AND
        [Preprocess].[Age] = t.[Age] AND
        [Preprocess].[Gender] = t.[Gender] AND
        [Preprocess].[CarColor] = t.[CarColor]

/////////////////////////////////////////////////////

CREATE MINING MODEL MainModel
(
    TheKey long KEY,
    Age double CONTINUOUS,
    Gender text DISCRETE,
    CarColor text DISCRETE,
    HairColor text DISCRETE PREDICT
) USING Microsoft_Decision_Trees (Minimum_Leaf_Cases = 1,
Complexity_Penalty = 0)

/////////////////////////////////////////////////////

insert into [MainModel]
(
TheKey, Age, Gender, CarColor, HairColor
)
OPENROWSET ('MSDMine'
, 'location=C:\Temp'
, 'Select t.[KEY] As [Key], t.Age As Age, t.Gender As
Gender, t.CarColor As CarColor, Predict([Preprocess].
[HairColor]) As HairColor
From Preprocess PREDICTION JOIN
OPENROWSET
(
        'Microsoft.Jet.OLEDB.4.0',
        'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
dmtest.mdb;Persist Security Info=False',
        'Select [Key],[Age],[Gender],[CarColor] From
People'
) As t ON
        [Preprocess].[TheKey] = t.[Key] AND
        [Preprocess].[Age] = t.[Age] AND
        [Preprocess].[Gender] = t.[Gender] AND
        [Preprocess].[CarColor] = t.[CarColor]'
)

////////////////////////////////////////////////////

With the last query I get the following error message...
Syntax error at line 11, offset 3, token 'Microsoft'

Thanks for any help...

  Question.xml
2K Download
 
 
 

OLE DB Data Mining SQL syntax

Post by Peter Kim [MS » Sun, 08 Dec 2002 06:30:38


In DMX, just like other SQL, single quote(') is a string-delimiter.
You have to escape a single quote inside string by putting
two single quotes.

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


> Hi,
> I am having difficulty running the following set of Data
> Mining statements in the DMSample application, using the
> dmtest.mdb database available from the Data Mining User
> Group downloads.  Any help would really help me out!

> I am trying to use the prediction output from one data
> mining model as an input to another data mining model
> WITHOUT using a temporary table.

> Here are the queries I am trying, which I am aware would
> make no sense in a real scenario!  It is the last
> statement I am having problems with...

> (N.B. connection properties are : Server = "",
> Provider="MSDMine", Mining Location = "C:\Temp")

> The following scripts are also attached in DMSample xml
> format
> ///////////////////////////////////////////

> CREATE MINING MODEL Preprocess
> (
>     TheKey long KEY,
>     Age double CONTINUOUS,
>     Gender text DISCRETE,
>     CarColor text DISCRETE,
>     HairColor text DISCRETE PREDICT

> ) USING Microsoft_Decision_Trees (Minimum_Leaf_Cases = 1,
> Complexity_Penalty = 0)

> ////////////////////////////////////////////////

> insert into [Preprocess]
> (
> TheKey, Age, Gender, CarColor, HairColor
> )
> Openrowset('Microsoft.jet.oledb.4.0', 'data source =
> dmtest.mdb', 'Select [Key], Age, Gender, CarColor,
> HairColor From People')

> ////////////////////////////////////////////////

> Select t.[KEY] As [Key], t.Age As Age, t.Gender As Gender,
> t.CarColor As CarColor, Predict([Preprocess].[HairColor])
> As HairColor
> From Preprocess PREDICTION JOIN
> OPENROWSET
> (
> 'Microsoft.Jet.OLEDB.4.0',
> 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
> dmtest.mdb;Persist Security Info=False',
> 'Select [Key],[Age],[Gender],[CarColor] From
> People'
> ) As t ON
> [Preprocess].[TheKey] = t.[Key] AND
> [Preprocess].[Age] = t.[Age] AND
> [Preprocess].[Gender] = t.[Gender] AND
> [Preprocess].[CarColor] = t.[CarColor]

> /////////////////////////////////////////////////////

> CREATE MINING MODEL MainModel
> (
>     TheKey long KEY,
>     Age double CONTINUOUS,
>     Gender text DISCRETE,
>     CarColor text DISCRETE,
>     HairColor text DISCRETE PREDICT
> ) USING Microsoft_Decision_Trees (Minimum_Leaf_Cases = 1,
> Complexity_Penalty = 0)

> /////////////////////////////////////////////////////

> insert into [MainModel]
> (
> TheKey, Age, Gender, CarColor, HairColor
> )
> OPENROWSET ('MSDMine'
> , 'location=C:\Temp'
> , 'Select t.[KEY] As [Key], t.Age As Age, t.Gender As
> Gender, t.CarColor As CarColor, Predict([Preprocess].
> [HairColor]) As HairColor
> From Preprocess PREDICTION JOIN
> OPENROWSET
> (
> 'Microsoft.Jet.OLEDB.4.0',
> 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
> dmtest.mdb;Persist Security Info=False',
> 'Select [Key],[Age],[Gender],[CarColor] From
> People'
> ) As t ON
> [Preprocess].[TheKey] = t.[Key] AND
> [Preprocess].[Age] = t.[Age] AND
> [Preprocess].[Gender] = t.[Gender] AND
> [Preprocess].[CarColor] = t.[CarColor]'
> )

> ////////////////////////////////////////////////////

> With the last query I get the following error message...
> Syntax error at line 11, offset 3, token 'Microsoft'

> Thanks for any help...


 
 
 

OLE DB Data Mining SQL syntax

Post by Jamie MacLennan [MS » Tue, 10 Dec 2002 15:20:15


Just an FYI - "MSDMine" is not a supported OLEDB provider and it is just
coincidence that you can connect to it.  Any functionality you derive by
connecting directly to MSDMine is likely not to work in future versions of
Analysis Services Data Mining.

--
Jamie MacLennan
SQL Server Data Mining
-----------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.


Quote:> Hi,
> I am having difficulty running the following set of Data
> Mining statements in the DMSample application, using the
> dmtest.mdb database available from the Data Mining User
> Group downloads.  Any help would really help me out!

> I am trying to use the prediction output from one data
> mining model as an input to another data mining model
> WITHOUT using a temporary table.

> Here are the queries I am trying, which I am aware would
> make no sense in a real scenario!  It is the last
> statement I am having problems with...

> (N.B. connection properties are : Server = "",
> Provider="MSDMine", Mining Location = "C:\Temp")

> The following scripts are also attached in DMSample xml
> format
> ///////////////////////////////////////////

> CREATE MINING MODEL Preprocess
> (
>     TheKey long KEY,
>     Age double CONTINUOUS,
>     Gender text DISCRETE,
>     CarColor text DISCRETE,
>     HairColor text DISCRETE PREDICT

> ) USING Microsoft_Decision_Trees (Minimum_Leaf_Cases = 1,
> Complexity_Penalty = 0)

> ////////////////////////////////////////////////

> insert into [Preprocess]
> (
> TheKey, Age, Gender, CarColor, HairColor
> )
> Openrowset('Microsoft.jet.oledb.4.0', 'data source =
> dmtest.mdb', 'Select [Key], Age, Gender, CarColor,
> HairColor From People')

> ////////////////////////////////////////////////

> Select t.[KEY] As [Key], t.Age As Age, t.Gender As Gender,
> t.CarColor As CarColor, Predict([Preprocess].[HairColor])
> As HairColor
> From Preprocess PREDICTION JOIN
> OPENROWSET
> (
> 'Microsoft.Jet.OLEDB.4.0',
> 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
> dmtest.mdb;Persist Security Info=False',
> 'Select [Key],[Age],[Gender],[CarColor] From
> People'
> ) As t ON
> [Preprocess].[TheKey] = t.[Key] AND
> [Preprocess].[Age] = t.[Age] AND
> [Preprocess].[Gender] = t.[Gender] AND
> [Preprocess].[CarColor] = t.[CarColor]

> /////////////////////////////////////////////////////

> CREATE MINING MODEL MainModel
> (
>     TheKey long KEY,
>     Age double CONTINUOUS,
>     Gender text DISCRETE,
>     CarColor text DISCRETE,
>     HairColor text DISCRETE PREDICT
> ) USING Microsoft_Decision_Trees (Minimum_Leaf_Cases = 1,
> Complexity_Penalty = 0)

> /////////////////////////////////////////////////////

> insert into [MainModel]
> (
> TheKey, Age, Gender, CarColor, HairColor
> )
> OPENROWSET ('MSDMine'
> , 'location=C:\Temp'
> , 'Select t.[KEY] As [Key], t.Age As Age, t.Gender As
> Gender, t.CarColor As CarColor, Predict([Preprocess].
> [HairColor]) As HairColor
> From Preprocess PREDICTION JOIN
> OPENROWSET
> (
> 'Microsoft.Jet.OLEDB.4.0',
> 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
> dmtest.mdb;Persist Security Info=False',
> 'Select [Key],[Age],[Gender],[CarColor] From
> People'
> ) As t ON
> [Preprocess].[TheKey] = t.[Key] AND
> [Preprocess].[Age] = t.[Age] AND
> [Preprocess].[Gender] = t.[Gender] AND
> [Preprocess].[CarColor] = t.[CarColor]'
> )

> ////////////////////////////////////////////////////

> With the last query I get the following error message...
> Syntax error at line 11, offset 3, token 'Microsoft'

> Thanks for any help...

 
 
 

OLE DB Data Mining SQL syntax

Post by John Sandifor » Tue, 10 Dec 2002 20:43:24


Thanks,
I've got it working now with MSOLAP.2
 
 
 

OLE DB Data Mining SQL syntax

Post by John Sandifor » Tue, 10 Dec 2002 20:44:15


Thanks Peter, I've got it working now.
 
 
 

1. OLE DB for Data Mining Sample Provider Help

You could download DMSample appl from http://groups.msn.com/AnalysisServicesDataMining.
The DMSample app is a VB program (available as source code) that allows you to create
OLEDB DM commands (CREATE/INSERT/SELECT and etc) and send them to the Analysis
server, and display the result of execution. For the detail of the syntax, please look at the OLEDB DM spec
(http://www.microsoft.com/data/oledb/dm.htm).

I also suggest to visit the FAQ page (http://groups.msn.com/AnalysisServicesDataMining/faq.msnw)
for many other useful info.

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

2. Operand type clash: datetime is incompatible with bit

3. Some OLE DB Data Mining questions

4. Incorrect syntax near the keyword

5. OLE DB for Data Mining Sample Provider

6. North Carolina-Oracle DBA

7. OLE DB FOR DATA MINING SAMPLE PROVIDER

8. Ingres on Solaris 2.3

9. OLE DB for Data Mining

10. OLE DB for Data Mining Sample Provider

11. Sample from send-stone in OLE DB for Data Mining Resource Kit

12. OLE DB for Data Mining Sample Provider