prediction join initiated from relational db?

Question

Hi!

The example 2

http://msdn.microsoft.com/en-us/library/ms132031.aspx

describes the situation where you are connected to Analysis Services,

but how to do it the other way around, when you are connected

to the relational db?

---

B. D. Jensen

Answer 1

Hi Bjorn,

For your case, use OPENROWSET to specify the data provider, connection string and queries. For example,

OPENROWSET
(
'SQLOLEDB.1',
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security     Info=False;Initial Catalog=AdventureWorksDW2008R2;Data Source=localhost',
'SELECT TOP 1000 * FROM vTargetMail'
)

See http://msdn.microsoft.com/en-us/library/ms131962.aspx for more information about how to use OPENROWSET in DMX.

Additional helpful links:http://msdn.microsoft.com/en-us/library/ms131958.aspx to describe the most common ways to specify source data in DMX.

 

Regards,
Jerry

TechNet Subscriber Support in forum
If you have any feedback on our support, please contact  [e-mail]

Answer 2

No, you misunderstand; I would like to do it the other way around:

connectet to the Database Engine, I would like to give new data as input to Analysis Service that should give me predictions...

These predicted values should then either be visible as report or be stored in a relational table...

---

B. D. Jensen

Answer 3

So you want to either use the predicted values as report or save them into a relational table? If so,

for report based on Predicted values by using DMX, use SQL Server Reporting Services (SSRS) where you can build a report with dataset coming from the DMX, See http://technet.microsoft.com/en-us/library/ms155812(v=sql.100).aspx for more information regarding using the Analysis Services DMX Query Designer (Reporting Services).

To save the DMX result to a relational table, check http://blogs.msdn.com/b/jamiemac/archive/2008/10/07/getting-data-mining-results-into-sql-tables.aspx .

Hope this is what you are looking for.

Thanks,
Jerry

Answer 4

Hi Jerry!

Your 2. link solves it I think, but before marking it as answer:

can it be expressed even simpler avoiding the double openquery that links back to the db it comes from?

---

B. D. Jensen

Answer 5

Open the data mining project within SQL Server Business Intelligence Development Studio(BIDS), then go to the Mining Model Prodiction tab where you can save the predicted result to a relational table.

See http://msdn.microsoft.com/en-us/library/ms178249.aspx for the above information.

thanks,
Jerry

Answer 6

thats not what I had in mind;

but seems we can do this?:

SELECT

* FROM Customers

NATURAL PREDICTION

JOIN OPENQUERY(LINKED_AS,

'SELECT Cluster() AS [Cluster], ClusterProbability() AS [Prob]

FROM [Customers - Clustering]

'

)

---

B. D. Jensen

Answer 7

Bjorn,

Are you trying to use Cluster() labels as inputs for a different mining model? Your query seems an AS query (contains a PREDICTION JOIN) agains a linked AS server

---

bogdan crivat / http://www.bogdancrivat.net/dm

Answer 8

No, I'm just trying to figure out if I could avoid the backlinking to relational database;

e.g. avoiding one of the openquery in this example:

http://blogs.msdn.com/b/jamiemac/archive/2008/10/07/getting-data-mining-results-into-sql-tables.aspx

In my case it would be natural to be connected to the relational database engine,

taking new data, give them as input to mining model and get some predictions in return which then

could be used in a report or stored in relational tables ...

---

B. D. Jensen