DMX Shape query error

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