Hi I created a DMX query to retrieve predictions based on previous customer purchases and wanted to filter out my input data by only purchases made in the current year. I keep receiving this error:
Code Snippet
===================================
Internal error: An unexpected error occurred (file 'dmxinit.cpp', line 1343, function 'DMXNodeInput::InitFromASTOpenRowset'). (Microsoft SQL Server 2005 Analysis Services)
Program Location:
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Execute(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.Execute()
at Microsoft.AnalysisServices.Controls.QueryResultGridStorage.ThreadProc()
And, here's my query:
Code Snippet
SELECTFLATTENED
(SELECT *
FROMPredictAssociation([PredictTable],
10,
INCLUDE_NODE_ID,
INCLUDE_STATISTICS
)
WHERE$NODEID <> ''
)
FROM
[Mining Model]
NATURALPREDICTIONJOIN
SHAPE {
OPENQUERY( [datasrc],
'SELECT ''1234'' AS [Customer_D_SID]'
)
} APPEND ({
SHAPE {
OPENQUERY( [datasrc],
'SELECT [Product_D_SID],[Customer_D_SID], [Transaction_Date]
FROM [Base_Sales_F]
WHERE [Customer_D_SID] = ''1234'' '
)
} APPEND ({
OPENQUERY( [datasrc],
'SELECT [Calendar_D_SID],[CALENDAR_YR_NBR]
FROM [dbo].[Calendar_D]
WHERE [CALENDAR_YR_NBR] >= ''2007'' '
)
} RELATE [Calendar_D_SID] TO [Transaction_Date]) AS B
} RELATE B.[Customer_D_SID] TO [Customer_D_SID]) AS [PredictTable]
AS T
I figured the only way to associate the calendar table with the sales table was to use a nested shape statement... is this wrong? Thanks for any help!
The internal error is being raised because your SHAPE statement is generating 2 levels of nesting which doesn't match your model defnition (SQL Server DM only supports single-level nesting i.e. nested tables cannot have table columns).
You need to remove the second nested join and instead, use a view on the transaction table that includes the column (CALENDAR_YR_NBR) you want to filter on.
|||Thank you! Making a view solved my problem.sql