Sunday, March 11, 2012

Error in Prediction query

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.

No comments:

Post a Comment