Association Mining Model Predictions

Hi,

I've been playing around with the association mining model in SQL server 2005 and built a market-basket analysis of my data that I'm pretty happy with. The next task for me is figuring out how to run DMX queries against the data that I've just mined, so we may possibly use it in a web based application. This wouldn't necessarily be a difficult problem (and still may not be), but every example I've seen for the Mining Model Prediction Designer uses relational databases and I built my mining model off OLAP. Therefore, my predictable attribute is nested and when relating the mining model structure to the relational database that the cube was built off always gives me an error:

"Errors in the high-level relational engine. The 'CompanyName' column could not be found in the top-level clause of the SHAPE statement."

What I would like to do, and I'm not really even sure how I should structure any of my queries, is feed the model a product and have it return a listing of all the products it predicts. Currently, I've only been able to get the designer mode to process a singleton query, and even that didn't return any useful data. I know that this probably can be done pretty easily so any advice you may be able to offer would be greatly appreciated!!

So you may better understand my question, my association mining structure hierarchy looks as this..

[Model] ProductRecommend

[Case][Key]CustomerList

[Case][Attribute]CompanyName

[NestedTable]Product

[Nested][Key]PRODUCTCLASSID

[Nested][Attribute]PRODUCT

With that in mind, I'm trying to perform a query simliar to this:

SELECT

PredictProbability([ProductRecommend].[Product].[PRODUCTCLASSID]), <- Throws Error for PredictProbability syntax no matter what I try to get to [PRODUCTCLASSID]

(SELECT [PRODUCT] FROM [ProductRecommend].[Product])

From

[ProductRecommend]

NATURAL PREDICTION JOIN

(SELECT 'test' AS [COMPANYNAME],

(SELECT '1234' AS [PRODUCTCLASSID],

'ProductA' AS [PRODUCT]) AS [Product]) AS t

Thanks again for any help!

Just incase someone out there runs into the same trouble I had, I think I've made some progress. My mistake was that I was using the PredictProbability function when I should have been using the PredictAssociation function. PredictAssociation does exactly what I wanted to do with the query, now I just need to figure out how to structure the query so it's simply based off an input product.|||What do you mean "simply based off an input product"?|||

Well it looks like 'simply based off an input product' isn't so simple... Since I've finally gotten queries to run, they've all pretty much returned the same associations. I feel like I've been everywhere on the internet looking for how to do what I described above, but just can't find enough information to solve my problem. My overall goal is to create a recommendation system similiar to Amazon.com. I'm pretty sure my mining model achieves this goal, but writing the DMX do so is a little bit difficult based on the structure of our database, and the fact that I modeled the Mining Structure off our OLAP cube.

Basically I just want to say:

SELECT

Predict Association([Product],10)

FROM

[ProductRecommendation]

NATURAL PREDICTION JOIN

(SELECT (SELECT 'ProductCustomerHasSelected' AS [ProductName], 'ProductKey' AS [PRODUCTCLASSID]) AS [Product])

And have it return:

ProductCustomerHasSelected -- Predicts Product A, B, D, and E

Is this the wrong way to go about doing that?

|||

BTW, have you looked at Raman Iyer and Jesper Lind's article? Create a Web Cross-sell Application

It has code examples, including how to construct the queries. You can see the app they build in action on a little webcast I did at.

In this article they use the following query:

SELECT FLATTENED

TopCount(Predict([Customer Movies], INCLUDE_STATISTICS), $AdjustedProbability, 5)

FROM [Movie Recommendations]

NATURAL PREDICTION JOIN

( SELECT ( SELECT 'Star Wars' AS [Movie] UNION SELECT 'The Matrix' AS [Movie] ) AS [Customer Movies] ) AS t

This returns the top 5 movies associated with the input movies: Star Wars and the Matrix in this case.

There is an excellent explanation of the thinking behind this query in the article.

hth

assertion task or something like it

I am playing around trying to develop a thing in SSIS that would be like an assertion elsewhere -- e.g., test a precondition and raise an exception if the precondition isn't true. I'm posting this to see if folks have already done something like this or have suggestions how to go about it in a semi-general way that could be re-used.

My toy thingamabob right now has the goal of comparing the grand totals for a numeric field in two xml files and raising an exception if they aren't equal. I've implemented it as two XML tasks, each of which uses XSLT to compute the total and post it as a string into a variable, then a script task to convert the string variables into numbers, compare them, and take appropriate action.

chw

Nothing in the box, and nothing else I have seen. Script tasks and a "debug" variable taht you set yourself would seem to be the obvious method. Perhaps use an environment variable or similar configuration method to set the Debug variable.

assembly for stored procedure

I was playing around with the CLR in writing assemblies for the sql server 2005 stored procedure. I guess the example i found was for the beta version

This line is from the beta but no longer works. Any ideas what will fix this. There is no longer GetCommand property.

SqlCommand cmd =SqlContext.GetCommand();

Example

publicpartialclassStoredProcedures{[Microsoft.SqlServer.Server.SqlProcedure] publicstaticvoid StoredProcedure1()

{

// Put your code here

SqlCommand cmd =SqlContext.GetCommand();

cmd.CommandText="select firstname + ' ' + lastname + as [name] from person.contact";

SqlDataReader rdr = cmd.ExecuteReader();

SqlPipe sp =SqlContext.GetPipe();

sp.Send(rdr);

}};

Hi,

yes that has changed quite a bit till SQL 2005's RTM. Something like

using(SqlConnection connection = new SqlConnection("context connection=true"))
{

connection.Open();
SqlCommand cmd=new SqlCommand("select firstname + ' ' + lastname + as [name] from person.contact",connection);

SqlContext.Pipe.ExecuteAndSend(cmd);

}

For more information:http://msdn2.microsoft.com/en-us/library/ms190790.aspx