Re: accessing data mining model via web/PDA

From: Raman Iyer [MS] (ramaniy_at_online.microsoft.com)
Date: 02/16/05 

Date: Tue, 15 Feb 2005 18:22:08 -0800

Some questions:
1. What error is reported if you run the prediction query?
2. Is your OPENROWSET clause connecting to a SQL Server 2000 database?
3. Are you sending the query from a client app running on the same machine
that's hosting your Analysis Server?
4. Is your SQL Server instance running on the same machine as your Analysis
Server?
5. Is your SQL Server instance set up to use standard (SQL) security with
username/password or Windows (integrated)security? 

-- 
Raman Iyer
SQL Server Data Mining
[Please do not send email directly to this alias. This alias is for
newsgroup purposes and is intended to prevent automated spam. This posting
is provided "AS IS" with no warranties, and confers no rights.]
.
"Jacky Foo" <wjfoo83@hotmail.com> wrote in message
news:00f301c512d4$a81da910$a601280a@phx.gbl...
> thanks for justifying me, i was confused with dmx and mdx.
> dmx code is quite rare and hard to find on the web.
> anyway, the DMSample is quite useful for me to come out
> with my codes.
>
> with the openrowset that you have mentioned, let say i
> need to query data from a model which i had processed in
> the AS, what would be the openrowset argument?
>
> i couldnt retrieve data from the model in the AS with the
> openrowset arguments which you have gave me even i changed
> to the respective db name and server name. i believe there
> is something wrong with my code.
>
>
> SELECT t.PRODUCT_NAME,
> t.PRODUCT_PRICE_RANGE,t.PRODUCT_PURPOSE,t.SPEED_RANGE
> FROM test
>
> PREDICTION JOIN
> OPENROWSET( 'SQLOLEDB.1',
>  'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
> Security
> Info=False;Initial Catalog=computer2;Data Source=johnboy',
> 'SELECT PRODUCT_ID, PRODUCT_PRICE_RANGE, PRODUCT_NAME,
> SPEED_RANGE, PRODUCT_PURPOSE FROM PRODUCT') AS t
> ON  test.PRODUCT_PRICE_RANGE = t.PRODUCT_PRICE_RANGE
> AND  test.PRODUCT_PURPOSE = t.PRODUCT_PURPOSE AND
>      test.SPEED_RANGE = t.SPEED_RANGE
> WHERE
> t.PRODUCT_PRICE_RANGE = '$2501-$5000'
> AND t.PRODUCT_PURPOSE = 'SERVER'
> AND t.SPEED_RANGE = '2.1GHZ - 3.0GHZ'
>
>
>
> sorry for the last question, what i meant was the
> ADOMD.net connectivity inside my mobile application.
> the following connection was given by Jamie. i need some
> help for the connection string, let say to access the
> mining model in the AS.
>
> AdomdCommand cmd;
> AdomdConnection con;
>
> con.ConnectionString = MyConnectionString;
> con.Open();
> cmd.Connection = con;
> cmd.CommandText = MyQueryString;
>
> AdomdDataReader dataReader=cmd.ExecuteReader();
> while(dataReader.Read())
> {
> // Access row data
> }
> dataReader.Close();
> con.Close();
>
>
>
> >-----Original Message-----
> >Yes, it should work and have the same result.
> >BTW, the sample you sent is not MDX, but DMX. MDX is the
> multidimensional
> >language for our OLAP server, while DMX is the Data
> Mining Extension
> >language.
> >An openrowset statement can be graphically created and
> tested in DMSample.
> >Use the "New Open Rowset" button on the right side of the
> trr containing the
> >mining models to create an OPENROWSET statement
> >
> >For the openrowset parameter:
> >    OpenRowset takes 3 parameters. The first is the
> provider name, the
> >second is the connection string while the 3rd is the
> query to be executed.
> >    For connecting to SQL Server 2000, OPENROWSET may
> look like below:
> >OPENROWSET
> >(
> > 'SQLOLEDB.1',
> > 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
> Security
> >Info=False;Initial Catalog=TheDatabaseName;Data
> Source=TheSQL2000Server',
> > 'SELECT Name, Value FROM Table'
> >)
> >
> >
> >I do no understand your last question:
> >> if the connection need to be made on the analysis server
> >> thru asp.net by using ADOMD, wat is the connection
> string
> >> that is required?
> >>
> >
> >Are you interested in the connection string for ADOMD,
> i.e. the one for the
> >data mining query? Or the connection string to be
> included in the OPENROWSET
> >statement?
> >
> >
> >-- 
> >--
> >--
> >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
> >
> >"Jacky Foo" <wjfoo83@hotmail.com> wrote in message
> >news:2dfe01c512bf$b804c120$a601280a@phx.gbl...
> >> this was the sample mdx which i have created in DMSamp
> >> application, it was created successfully. if i would use
> >> the ADOMD command to execute this statement as the
> command
> >> text, will it work & hv the same result?
> >>
> >> wat is the openrowset argument if i need to take data
> from
> >> analysis manager or sql 2000 server?
> >>
> >> if the connection need to be made on the analysis server
> >> thru asp.net by using ADOMD, wat is the connection
> string
> >> that is required?
> >>
> >> any help will do, thanks!
> >>
> >> Create Mining Model test
> >> (
> >> PRODUCT_ID text key,
> >> PRODUCT_PRICE_RANGE text discrete,
> >> PRODUCT_NAME text discrete predict,
> >> SPEED_RANGE text discrete,
> >> PRODUCT_PURPOSE text discrete
> >> ) using microsoft_decision_trees
> >>
> >> Insert Into test
> >> (PRODUCT_ID, PRODUCT_PRICE_RANGE, PRODUCT_NAME,
> >> SPEED_RANGE, PRODUCT_PURPOSE)
> >> OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> >> 'data source=NEWComputer.mdb',
> >> 'SELECT PRODUCT_ID, PRODUCT_PRICE_RANGE, PRODUCT_NAME,
> >> SPEED_RANGE, PRODUCT_PURPOSE FROM PRODUCT')
> >> SE,t.SPEED_RANGE
> >> FROM test
> >>
> >> >-----Original Message-----
> >> >MDX at sqlserverdatamining.com?  Not possible.  There
> >> should be only DMX
> >> >samples querying models there not MDX samples querying
> >> cubes.  Look at the
> >> >DataValidation sample application which queries a
> >> clustering model from a
> >> >web page.  This should be pretty much excatly what you
> >> want.  The running
> >> >sample plus the source code are both on the site.  The
> >> sample is SQL 2005,
> >> >but the methodology is the same (PredictCaseLikelihood
> is
> >> a SQL 2005 only
> >> >function)
> >> >
> >> >You can also use ADO.Net instead of ADOMD.Net to query
> >> the Analysis Server
> >> >as well as long as the OLEDB provider is on your IIS
> box.
> >> >
> >> >-- 
> >> >
> >> >-Jamie MacLennan
> >> >SQL Server Data Mining
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"Jacky" <wjfoo83@hotmail.com> wrote in message
> >> >news:120201c4fb8a$1f434f20$a301280a@phx.gbl...
> >> >> Thanks for the consistent reply.
> >> >>
> >> >> The page that i will develop on client's PDA will be
> >> using
> >> >> ASP.net. The website which you have recommended me
> >> >> http://www.sqlserverdatamining.com really helps, but
> the
> >> >> MDX samples were focused on querying the cubes
> instead
> >> of
> >> >> the result from the Decision Tree.
> >> >>
> >> >> I have red thru several books but none of them
> provide
> >> >> details on OLEDB and ADOMD. What they mentioned was
> just
> >> >> the concept.
> >> >>
> >> >> I hope there are more sample codes or application
> which
> >> >> assist my development. Thanks!
> >> >>
> >> >>
> >> >> >-----Original Message-----
> >> >> >The easiest way off the top of my head is to use
> XMLA -
> >> >> I'll ask around to
> >> >> >see if there is a PDA client for XMLA that you can
> use.
> >> >> In the meantie, you
> >> >> >can always write your own webservice in IIS that
> >> queries
> >> >> the model on behalf
> >> >> >of your PDA and returns the result to the PDA.  The
> >> >> webservice function
> >> >> >would open a connection to the Analysis Services
> >> server,
> >> >> create a command
> >> >> >object, execute a DMX query and return the result to
> >> the
> >> >> client.  Given the
> >> >> >frequency of calls to the server, you will likely
> want
> >> to
> >> >> perform some kind
> >> >> >of connection pooling as opening the connection can
> be
> >> >> expensive.
> >> >> >
> >> >> >Let me know if this helps
> >> >> >
> >> >> >
> >> >> >
> >> >> >-- 
> >> >> >
> >> >> >-Jamie MacLennan
> >> >> >SQL Server Data Mining
> >> >> >This posting is provided "AS IS" with no warranties,
> >> and
> >> >> confers no rights.
> >> >> >"Jacky" <Jacky@discussions.microsoft.com> wrote in
> >> message
> >> >> >news:F84D18C8-F26C-4628-914C-
> >> ED6A75C045B2@microsoft.com...
> >> >> >> here are some short description:
> >> >> >>
> >> >> >> there is a decision tree in my sql 2000 server,
> from
> >> >> this tree, i need to
> >> >> >> get the decision or result so that it will be
> display
> >> >> in my PDA. for
> >> >> >example:
> >> >> >> the tree will determine the all the CPU which i
> >> >> requested, the data of the
> >> >> >> CPU will be displayed in my PDA. is this sample
> clear
> >> >> enough?
> >> >> >>
> >> >> >> i m still a beginner in this DM tool, i guess i
> need
> >> >> more advice and
> >> >> >> suggestions in order for me to understand all the
> >> steps
> >> >> well. thanks!
> >> >> >>
> >> >> >> "Jamie MacLennan (MS)" wrote:
> >> >> >>
> >> >> >> > Can you provide more details on exactly what you
> >> want
> >> >> on the PDA?  It's
> >> >> >> > fairly easy to get results to a web page, so you
> >> can
> >> >> simply display that
> >> >> >web
> >> >> >> > page on a PDA.  Mining models can be access
> through
> >> >> OLEDB, ADO,
> >> >> >ADOMD.Net or
> >> >> >> > XMLA
> >> >> >> >
> >> >> >> > For example, you can check out the XMLA Thin
> Miner
> >> >> sample and other
> >> >> >samples
> >> >> >> > at http://www.sqlserverdatamining.com.  The Data
> >> >> Validation sample
> >> >> >provides
> >> >> >> > some easy to use source code.  Although these
> >> samples
> >> >> are for SQL 2005,
> >> >> >the
> >> >> >> > basic concepts of how to connect and retrieve
> data
> >> >> from the server are
> >> >> >the
> >> >> >> > same.
> >> >> >> >
> >> >> >> > -- 
> >> >> >> >
> >> >> >> > -Jamie MacLennan
> >> >> >> > SQL Server Data Mining
> >> >> >> > This posting is provided "AS IS" with no
> >> warranties,
> >> >> and confers no
> >> >> >rights.
> >> >> >> > "Jacky" <wjfoo83@hotmail.com> wrote in message
> >> >> >> > news:132f01c4f742$546cb890$a501280a@phx.gbl...
> >> >> >> > > hi, i m a beginner in handling data mining
> >> analysis
> >> >> >> > > services for sql 2000. based on a decision
> tree
> >> >> model
> >> >> >> > > which i had created, i would like to retrieve
> >> back
> >> >> the
> >> >> >> > > resulting data via web/PDA. is it posible to
> do
> >> >> that? how?
> >> >> >> > > i hv gone thru several articles, suggested
> tool
> >> is
> >> >> English
> >> >> >> > > Query. is there other methods?
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >