Getting the Model's (Decision tree) PMML

Hi

Can anyone tell me the steps involved in retrieving a model's (decision tree) pmml and use the model content to devleop a web based interface. I am using SQL Server 2005.

Thanks,

Nathan

To get the PMML is relatively easy. Just execute a query like:

SELECT MODEL_PMML FROM <model>.PMML

This query will return the PMML rendering of the model content, as long as:

- the model contains a single predictable variable (asingle tree)

- the model does not include a date time column

Both these limitations derived from limitations of the PMML 2.1 standard which is supported by Analysis Services 2005.

Now, to use the content for a web interface -- not sure whether PMML is the best path. You can, of course, traverse the PMML using an XML reader and render the content in any form you like. You can do the same thing and get rid of the limitations above by issuing content queries against Analysis Services

E.g. SELECT * FROM <model>.CONTENT

Also, SQL Server 2005 provides a sample called Data mining Web Controls which include a web rendering for decision trees (again, without the limitations above). The sample includes source code.

You can try the sample at this location: http://www.sqlserverdatamining.com/MovieSurveyDemo

Just expand the Forests node and click on a tree (say, Demographic Forest)

|||You had mentioned that the sample includes source code...but I did not find any...can you tell me specifically where it is...|||

The source code is included in the samples coming with SQL Server.

It was also recently uploaded on the web at http://www.codeplex.com/MSFTASProdSamples/Wiki/View.aspx?title=SS2005%21Data%20Mining%20Web%20Controls%20Library&referringTitle=Home

Getting the Model's (Decision tree) PMML

Hi

Can anyone tell me the steps involved in retrieving a model's (decision tree) pmml and use the model content to devleop a web based interface. I am using SQL Server 2005.

Thanks,

Nathan

To get the PMML is relatively easy. Just execute a query like:

SELECT MODEL_PMML FROM <model>.PMML

This query will return the PMML rendering of the model content, as long as:

- the model contains a single predictable variable (asingle tree)

- the model does not include a date time column

Both these limitations derived from limitations of the PMML 2.1 standard which is supported by Analysis Services 2005.

Now, to use the content for a web interface -- not sure whether PMML is the best path. You can, of course, traverse the PMML using an XML reader and render the content in any form you like. You can do the same thing and get rid of the limitations above by issuing content queries against Analysis Services

E.g. SELECT * FROM <model>.CONTENT

Also, SQL Server 2005 provides a sample called Data mining Web Controls which include a web rendering for decision trees (again, without the limitations above). The sample includes source code.

You can try the sample at this location: http://www.sqlserverdatamining.com/MovieSurveyDemo

Just expand the Forests node and click on a tree (say, Demographic Forest)

|||You had mentioned that the sample includes source code...but I did not find any...can you tell me specifically where it is...|||

The source code is included in the samples coming with SQL Server.

It was also recently uploaded on the web at http://www.codeplex.com/MSFTASProdSamples/Wiki/View.aspx?title=SS2005%21Data%20Mining%20Web%20Controls%20Library&referringTitle=Home

Getting Extra Notifications after Content Formatter completes!

Hi,

I am getting extra notifications which I am really not sure how and why its hapening.

I did try debugging, and everything seems perfect till ContentFormatter. I am getting exact number of messages for a

particular event and they are getting formatted perfectly by the custom content formatter.

But when I check the NS<ClassInstanceName>NotificationDistribution in the Notification DB, I have multiple records.

I did check the underlying query for this View and the issue is with NSDistributionLog. In some of these extra records I

can see the NotificationText that was never generated by the content formatter for this particular event.

We are supporting only one DeviceType 'Email' as of now.

Any suggestions/ideas why this is happening?

Hi Poonam -

Are you using any custom components?

Joe|||

Hi Joe,

Yes I have a custom content formatter in place and thanks for helping me on this.

But I have figured out the problem and posting it here for reference. Here is what was happening:

Notification Service Instance creates a Work Item for each Batch of notification. But the View

NS<ClassInstanceName>NotificationDistribution doesn't consider BatchIDs. Rather it considers only the NotificationIDs.

Notifications IDs can be repeated with in a Batch.

So this view returns the records (for all the batch ids) where there is a match in the NotificationID irrespective of the if this

Notification is part of the batch or not.

So here is the output:

You get correct no. of emails formatted from content formatter

Correct no and content is delivered to the Delivery Channel

But

When we see the NS<ClassInstanceName>NotificationDistribution, there are extra records with

Different NotificationText that was actually never related to this Event.

Below is the correct query with the reference to Batch ID of the notifications actually generated:

SELECT *

FROM [...].[NS<ClassInstanceName>Notifications] N WITH (READUNCOMMITTED)

JOIN [...].[NSDistributorWorkItems] DW WITH (NOLOCK)

ON N.NotificationBatchID = DW.BatchID

JOIN [...].[NSSubscriberDevices] SD WITH (READUNCOMMITTED)

ON SD.SubscriberId = N.SubscriberId

AND SD.DeviceName = N.DeviceName

JOIN [...].[NSDistributionLog] DL WITH (READUNCOMMITTED)

ON DL.NotificationId = N.NotificationId

AND DW.DistributorWorkItemID = DL.DistributorWorkItemID

JOIN [...].[NSDistributors] D WITH (READUNCOMMITTED)

ON D.DistributorId = DL.DistributorId

JOIN [...].[NSNotificationDeliveryStatusCodes] DSC WITH (READUNCOMMITTED)

ON DSC.NotificationDeliveryStatusCode = DL.DeliveryStatusCode

WHERE

DL.NotificationClassId = 1

[...] - are corresponding Schema Names.