Hi,
I have the following association model Structure
Where I am trying to find out the associations between various service activities so that when a customer buys a service activity we can recommend him/her others
CaseTable
CustomerId à Key
IncidentId
Nested Table
ServiceId à Key
ServiceName à Input, Predict
IncidentId à Link to the case table
Firstly is the above structure correct?
Secondly
I have the following prediction query
SELECT
t.[ServiceId],
Predict ([ServicerossSell].[ServiceCross-SellRecommend],3)
From
[ServicerossSell]
PREDICTIONJOIN
SHAPE
{OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT ServiceId FROM Service ORDER BY ServiceId]')
}
APPEND
( {OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT ServiceId FROM Service ORDER BY ServiceId]')
}
RELATE [ServiceId]To [ServiceId]
)
AS [Service]
AS t
ON
[ServicerossSell].[ ServiceCross-SellRecommend].[ServiceId] = t.[Service].[ServiceId]
It throws the following error
“The DMX column was not found in the context”
I am not able to figure out why, any help would be most appretaiated
The SHAPE clause is wrong - it should be joining the customer/incident table with the incident/service table, not doing a self-join.
Given that your case is a customer with the nested table being the services purchased by them (across multiple incidents), I recommend simplifying the input data as well as the modeling by using a named query in the DSV as follows:
SELECT a.CustomerId, b.ServiceName
FROM CustomerIncident a INNER JOIN IncidentService b
ON a.IncidentId = b.IncidentId
ORDER BY CustomerId
The mining structure can then look like this:
CREATE MINING MODEL CustomerServiceAssociations
(
CustomerId TEXT KEY,
CustomerServices TABLE PREDICT
(
ServiceName TEXT KEY
)
)
USING Microsoft_Association_Rules
|||Hi Raman,
I took your suggestion and modified the dsv and the mining model as mentioned by you.
And I also modified the prediction query as follows.
SELECT
t.[ServiceId],
PredictAssociation([Association].[Services],3)
From
[Association]
PREDICTIONJOIN
OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT
[ProductId]
FROM
(SELECT ServiceId FROM ServiceBase) as [Service]
')AS t
ON
[Association].[Service Id] = t.[ServiceId]
I am facing a new problem now.
The model is predicting the same set of services for every case. Even changes in the algorithm parameter value do not have any impact on the result.
What is the reason for this and how can u rectify it?
I am facing this problem with other association based mining models also.
