Lee Cascio on Tue, 11 Feb 2014 17:20:23
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?
') AS t
[ModelName].[Column3] = t.[Column3]
OLE DB error: OLE DB or ODBC error: Invalid object name 'R Staging.dbo.TestSet'.; 42S02."
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:
Prashanth Jayaram on Tue, 11 Feb 2014 20:33:27
Refer this link...May be helpful to your requirement
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
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_SQL on Wed, 12 Feb 2014 22:12:41
This may be the reason
Lee Cascio on Sun, 23 Feb 2014 17:54:08
Yes, this was already set to True.