none
PREDICTION JOIN OPENQUERY against data source view does not work

    Question

  • Hi,

     I am trying to follow the example in this link: http://technet.microsoft.com/en-us/library/ms132031.aspx

    to do a prediction join with a table defined in a data source view of our cube/mining structures.  No matter how I specify the table in the OPENQUERY statement I get: "OLE DB error: OLE DB or ODBC error: Invalid object name 'DataSourceView.dbo.TableName'.; 42S02."  I've tried specifying the table name in 1, 2, and 3 parts, with and without the '[]' brackets but get the same error every time.  I thought something might be wrong with the table in the DSV so tried putting other tables in the query, but that produces the same error.  Any ideas on the problem?

    SELECT FLATTENED
            t.[Column1],
            t.[Column2],
            t.[Column3],
            PredictTimeSeries([ModelName].[Column3],5)
    From
      [ModelName]
    PREDICTION JOIN
      OPENQUERY([DataSourceView],
        'SELECT
            [Column1],
            [Column2],
            [Column3]
        FROM
          [DataSourceView].[dbo].[TableName]
        ') AS t
    ON
            [ModelName].[Column3] = t.[Column3]

    OLE DB error: OLE DB or ODBC error: Invalid object name 'R Staging.dbo.TestSet'.; 42S02."

    Tuesday, February 11, 2014 5:20 PM

All replies

  • I want to be able to query a data source view table/named query.  This TechNet article seems to imply it is as simple as running the following in a DMX window:

         OPENQUERY ([MyDatasourceView],'select Column1 from DataSourceTable')

    I've also tried:

         select * from OPENQUERY ([MyDatasourceView],'select Column1 from DataSourceTable')

    Both result in:

        "Query (1, 1) Parser: The syntax for 'OPENQUERY' is incorrect."

    Can we query a DSV table from a DMX query directly with OPENQUERY, or does the OPENQUERY only work within a PREDICTION JOIN?  Seems like such a simple case for it not to work.

    Following the example in this article:

         http://technet.microsoft.com/en-us/library/ms132173.aspx

    Tuesday, February 11, 2014 8:20 PM
  • Refer this link...May be helpful to your requirement

    http://social.msdn.microsoft.com/Forums/en-US/0121c67d-25e7-42a0-ac6c-36ef9372e3e0/does-not-recognize-openquery-keyword-in-ssms

    Select Flattened 
    [Model Region], 
    Quantity , 
    PredictTimeSeries(Amount, 50)
    FROM 
    Forecasting
    Prediction JOIN 
    Openquery([Adventure Works DW], 
    'Select 
    ModelRegion, TimeIndex, Amount, Quantity , CalendarYear
    from 
    dbo.vTimeSeries
    order by TimeIndex') as t 
    on 
    Forecasting.[Model Region] = t.ModelRegion AND
    Forecasting.[Amount] = t.Amount AND
    Forecasting.[Time Index] = t.TimeIndex AND
    Forecasting.[Quantity] = t.Quantity
    
    --Prashanth

    Tuesday, February 11, 2014 8:33 PM
  • The article is informative but doesn't have a conclusion why it doesn't work.  The concerning thing is, it is now 4 years since that post and this feature still doesn't work.
    Tuesday, February 11, 2014 8:53 PM
  • Hi

    Just checking, have you enabled the DataMining\AllowAdHocOpenRowsetQueries option in your SSAS server properties.

    DataMining\AllowAdHocOpenRowsetQueries  and DataMining\AllowSessionMiningModels properties needs to enable i.e. Value to True to execute OPENROWSET for mining models.


    Prav

    Wednesday, February 12, 2014 12:43 AM
  • Hi Prav,

    Yes, this was already set to True.

    Thanks,

    Lee

    Sunday, February 23, 2014 5:54 PM