prediction join initiated from relational db?


All replies

  • Hi Bjorn,

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

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

    See for more information about how to use OPENROWSET in DMX.

    Additional helpful links: to describe the most common ways to specify source data in DMX.



    TechNet Subscriber Support in forum
    If you have any feedback on our support, please contact

    Wednesday, February 22, 2012 10:46 AM
  • 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

    Wednesday, February 22, 2012 7:15 PM
  • 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 for more information regarding using the Analysis Services DMX Query Designer (Reporting Services).

    To save the DMX result to a relational table, check .

    Hope this is what you are looking for.


    Thursday, February 23, 2012 1:54 AM
  • 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

    Thursday, February 23, 2012 6:18 AM
  • 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 for the above information.


    Thursday, February 23, 2012 6:38 AM
  • thats not what I had in mind;

    but seems we can do this?:


    * FROM Customers



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

    FROM [Customers - Clustering]



    B. D. Jensen

    Thursday, February 23, 2012 2:43 PM
  • 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 /

    Friday, February 24, 2012 7:24 AM
  • 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:

    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

    Monday, February 27, 2012 11:33 AM