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

ASPNET account permissions

I am new to Reporting Services and still learning, but it is a very nice exciting program. I built a new Report and was experimenting with Grouping in reports and using the parameters. Now I would like to Deploy the report to the Report Server so I can use this report on our intranet, however I get the error that the ASPNET account does not have the proper permissions. I am sure that I need to set up ASPNET to have "write" abilities.

What folder(s) do I need to make sure that ASPNET has this? Or should I be looking elsewhere to set up the ASPNET account on the Report Server?

Thanks for the information.

Will you post the exact error message you are seeing? Does it indicate which resource is not permissioned?|||

This is the exact error message when I try to deploy the report:

Error 1 The permissions granted to user 'BRAD\ASPNET' are insufficient for performing this operation. 0 0

|||The folder that needs permission is the root folder in the report server namespace. But running as the ASPNet account is usually not what you want, since all users will be connecting as the same user account. It looks like your report server config file has impersonation turned off. Is this something you changed intentionally?|||

Brian,

For now I did go into Report Services through the SQL Management Studios, went into properties and did see that ASPNET was unchecked. I checked it and now the reports deploy just fine.

Thanks for the great information.

ASP.net app TO SQL SERVER REPORTING DATABASE.

Hi guys; hope you can help me
I have built a web app that sits on our web server (asp.net 2.0).
this connects to our SQL server (2005) reporting services database on
dataserver.
Anonymous access must be switched of on IIS (integrated windows only
on).
Once the report has been selected by the user, I query the database to
get the parameters for selected report (loop through parameters
collection). I then check the required parameter controls for the
parameter values. I was using Anonymous access and this was working
fine. But since changing to not allowing Anonymous access I get an
"The request failed with HTTP status 401: Access Denied" error when
using the GetReportParameters method. My code is below
Dim rs As New washington.ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim report As String = strReportPath & strReportName
Dim forRendering As Boolean = False
Dim historyID As String = Nothing
Dim values As washington.ParameterValue() = Nothing
Dim credentials As washington.DataSourceCredentials() = Nothing
Dim parameters As washington.ReportParameter() = Nothing
parameters = rs.GetReportParameters(report, historyID, forRendering,
values, credentials)
Dim intParamCount As Integer = parameters.Length
Dim intLoopCounter As Integer
Dim parmArray(intParamCount - 1) As ReportParameter
If Not (parameters Is Nothing) Then
Dim rp As washington.ReportParameter
For Each rp In parameters
' loop collection
Next rp
End if
as i understand it i should be passing the credentials to the report
but when call
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
the credentials are empty
am i on the correct path
cheers
pHi, Phil
so, you're using the SOAP API to query parameters for a specific
report, and when you switch the ReportServer web app from anonymous to
Integrated Auth only, you get Access denied. Have you tried hitting
the report server URL from Internet Explorer after you switch to Integ.
Auth? Can you see the Report Server web app? Does it display any
catalog items like folders and reports? Does it display the report
you're trying to query for parameters, and if so, does it allow you to
run it from the browser?
If you answer yes, then your current user context has access to the
report server (by default the installation grants the local
administrators group full access to the report catalog from the root).
Also, when you try to "debug" or output the credentials from the
DefaultCredentials it will always have an empty value. Try deploying
your code to another server, instead of the localhost, and the
CredentialCache property should work fine. If you would still like to
test from your localhost, instead of using
CredentialCache.DefaultCredentials, try creating a new
NetworkCredential(string user, string pwd, string domain). You can
hardcode your credentials there, and test your code that way just to
test if you are actually authenticating at the server side with the web
service. Note that this option is for a test scenario, not for
production, as you would not want to bake in credentials in code.
If you answer no, then simply login to the box as a local admin account
(or open IE using the "Run As" option and enter the credentials of an
admin account on the box). Once you're logged in as admin and open IE
to the Report Manager URL (http://<machinename or localhost>/Reports),
you can view the properties of the folder or item (report) and add a
user account and permissions set for access to that catalog item ( you
can give it Content Manager, Browser, etc).
Regards,
Thiago Silva
On Nov 23, 10:59 am, "Phils" <phil.sm...@.iresponse.co.uk> wrote:
> Hi guys; hope you can help me
> I have built a web app that sits on our web server (asp.net 2.0).
> this connects to our SQL server (2005) reporting services database on
> dataserver.
> Anonymous access must be switched of on IIS (integrated windows only
> on).
> Once the report has been selected by the user, I query the database to
> get the parameters for selected report (loop through parameters
> collection). I then check the required parameter controls for the
> parameter values. I was using Anonymous access and this was working
> fine. But since changing to not allowing Anonymous access I get an
> "The request failed with HTTP status 401: Access Denied" error when
> using the GetReportParameters method. My code is below
> Dim rs As New washington.ReportingService()
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> Dim report As String = strReportPath & strReportName
> Dim forRendering As Boolean = False
> Dim historyID As String = Nothing
> Dim values As washington.ParameterValue() = Nothing
> Dim credentials As washington.DataSourceCredentials() = Nothing
> Dim parameters As washington.ReportParameter() = Nothing
> parameters = rs.GetReportParameters(report, historyID, forRendering,
> values, credentials)
> Dim intParamCount As Integer = parameters.Length
> Dim intLoopCounter As Integer
> Dim parmArray(intParamCount - 1) As ReportParameter
> If Not (parameters Is Nothing) Then
> Dim rp As washington.ReportParameter
> For Each rp In parameters
> ' loop collection
> Next rp
> End if
> as i understand it i should be passing the credentials to the report
> but when call
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> the credentials are empty
> am i on the correct path
> cheers
> p|||Once you make the site non-anonymous, the credentials are no longer
anonymous. You have to actually query the user. I did this a few months ago
at another job, but do not have access to the code. I remember experimenting
with impersonation and believe that was the first part of the solution.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
********************************************
Think outside the box!
********************************************
"Phils" <phil.smith@.iresponse.co.uk> wrote in message
news:1164301180.467523.79980@.k70g2000cwa.googlegroups.com...
> Hi guys; hope you can help me
>
> I have built a web app that sits on our web server (asp.net 2.0).
> this connects to our SQL server (2005) reporting services database on
> dataserver.
> Anonymous access must be switched of on IIS (integrated windows only
> on).
>
> Once the report has been selected by the user, I query the database to
> get the parameters for selected report (loop through parameters
> collection). I then check the required parameter controls for the
> parameter values. I was using Anonymous access and this was working
> fine. But since changing to not allowing Anonymous access I get an
> "The request failed with HTTP status 401: Access Denied" error when
> using the GetReportParameters method. My code is below
>
> Dim rs As New washington.ReportingService()
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
>
> Dim report As String = strReportPath & strReportName
> Dim forRendering As Boolean = False
> Dim historyID As String = Nothing
> Dim values As washington.ParameterValue() = Nothing
> Dim credentials As washington.DataSourceCredentials() = Nothing
> Dim parameters As washington.ReportParameter() = Nothing
>
> parameters = rs.GetReportParameters(report, historyID, forRendering,
> values, credentials)
>
> Dim intParamCount As Integer = parameters.Length
> Dim intLoopCounter As Integer
> Dim parmArray(intParamCount - 1) As ReportParameter
> If Not (parameters Is Nothing) Then
> Dim rp As washington.ReportParameter
> For Each rp In parameters
> ' loop collection
>
> Next rp
> End if
>
> as i understand it i should be passing the credentials to the report
> but when call
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
>
> the credentials are empty
> am i on the correct path
> cheers
> p
>