PREDICTION JOIN OPENQUERY against data source view does not work

Category: sql server datamining

Question

Lee Cascio on Tue, 11 Feb 2014 17:20:23


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."

Replies

Lee Cascio on Tue, 11 Feb 2014 20:20:46


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

Prashanth Jayaram on Tue, 11 Feb 2014 20:33:27


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

Lee Cascio on Tue, 11 Feb 2014 20:53:13


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.

Prav_SQL on Wed, 12 Feb 2014 00:43:06


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.

Lee Cascio on Sun, 23 Feb 2014 17:54:08


Hi Prav,

Yes, this was already set to True.

Thanks,

Lee