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]

PREDICTION JOIN

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]

PREDICTION JOIN

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