I am trying to get along with SQL server 2005, just made the mining model and i use this DMX query to get the time series prediction. Now this is working and i get results in SQL server management studio.
SELECT PredictTimeSeries([Apot Sales],5)
FROM [Sales Bycom]
I tried running this as a query in ASP but obviously this can't be done
Here is my question. I have made an SQL server connection in ASP. But how can I get the prediction results displayed in to ASP?
Thanx
You will need to make an Analysis Server connection to execute the DMX query from ASP. Take a look at the code download at the bottom of this article for an example of how to use DMX queries from ASP: http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp.|||Ok thanx I will check it out|||After spending some weeks of testing and reading I came up with this:
Its a part of the code of cross web application, I managed to make a model of timeseries in SQL server and I have hopefully (since I dont get any errors) run my dmx query through aspx. BUT I can get the result to display in aspx. My guess is because the original code was made for string variables and I am tring to get numeric variables in it. I suppose that all I need to do is to get the result from the DMX query in the array
'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If
'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then
If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End If
End While
And here is my DMX query
Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)
'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]"
********************************
I would appriciate any answers since this project is for my diploma and I really cant seem to get through
Thanks
|||What error(s) are you seeing? Keep in mind that PredictTimeSeries(<col>, N) returns a table with two columns - the first column is a time index ($TIME) and the second column contains the predicted values for the column you're predicting.|||Thanks Raman Iyer , The weird thing is that I dont get any errors neither any results
in the place where the results appear I get "Microsoft.AnalysisServices.AdomdClient.AdomdDataReader"
here is the whole code from predict.vb.asp
Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Drawing
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports Microsoft.AnalysisServices.AdomdClient
Namespace MovieCrossSellApplication
Partial Public Class ShoppingBasket_Recommendations
Inherits System.Web.UI.Page
Protected Overrides Sub OnInit(ByVal e As EventArgs)
'
' CODEGEN: This call is required by the ASP.NET Web Form Designer.
'
InitializeComponent()
MyBase.OnInit(e)
End Sub 'OnInit
'/ <summary>
'/ Required method for Designer support - do not modify
'/ the contents of this method with the code editor.
'/ </summary>
Private Sub InitializeComponent()
End Sub 'InitializeComponent
Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)
'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5) " + _
"FROM [Sales] "
' "SELECT FLATTENED TopCount(" + _
' "Predict([Customer Movies], INCLUDE_STATISTICS)," + _
' "$AdjustedProbability, 5) From [Movie Recommendations] " + _
' "NATURAL PREDICTION JOIN (SELECT ("
'Dim strDMX2 As String = ") AS [Customer Movies]) AS t"
'Iterate shopping basket and produce input case
'Dim cItems As Integer = vInputItems.Count
' Dim strDMX As String = ""
' Dim i As Integer
'For i = 0 To cItems - 1
' Dim item As String = vInputItems(i).ToString()
' item = item.Replace("’", "’’")
'strDMX += "SELECT " + "'" + item + "' AS " + "[Movie]"
' If i < cItems - 1 Then
' strDMX += " UNION "
'End If
'Next i
'Put together DMX prediction query to get 5 recommendations
'strDMX = strDMX1 + strDMX '+ strDMX2
'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If
'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then
'If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
'End If
End While
'Disconnect from Analysis Server
asSession.DisConnect()
End Sub 'GetRecommendations
Public Sub Button1_Click( _
ByVal sender As Object, _
ByVal e As System.EventArgs) 'Handles Me.Button1.Click
' Parse the input into an ArrayList of strings.
Dim alInputItems As New ArrayList()
Dim splitchar As Char() = {";"c}
Dim szInputItems As String() = Me.TextBox1.Text.Split(splitchar, 20)
Dim i As Integer
For i = 0 To szInputItems.Length - 1
alInputItems.Add(szInputItems(i).Trim())
Next i
' Add items to the shopping basket
dgShoppingBasket.DataSource = alInputItems
dgShoppingBasket.DataBind()
' Get top 5 recommendations
Dim alRecommendedItems As New ArrayList(5)
GetRecommendations(alInputItems, alRecommendedItems)
' Display recommendations
dgRecommendations.DataSource = alRecommendedItems
dgRecommendations.DataBind()
End Sub 'Button1_Click
End Class 'ShoppingBasket_Recommendations
'
' AnalysisServerSession manages
' - connecting to Analysis Server using ADOMD.NET,
' - executing commands and
' - fetching results
'
' Need to add reference to Microsoft.AnalysisServices.AdomdClient.dll
' (located under Program Files\Microsoft.NET\ADOMD.NET\90).
' You may also change this class to use ADO.NET (System.Data.Oledb)
' instead if neccessary, by replacing the AdomdConnection, AdomdCommand
' and AdomdDataReader with OledbConnection, OledbCommand and
' OledbDataReader. The rest of the code should stay the same.
'
Public Class AnalysisServerSession
Protected asCommand As Microsoft.AnalysisServices.AdomdClient.AdomdCommand
Protected asConnection As Microsoft.AnalysisServices.AdomdClient.AdomdConnection
Public asDataReader As Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
Public szServer As String = "localhost"
Public szCatalog As String = "myDSS"
Public Sub New()
asCommand = Nothing
asConnection = Nothing
asDataReader = Nothing
End Sub 'New
Public Function Connect() As Boolean
Dim asConnectionString As String = _
"Provider=MSOLAP.3;Data Source=" + _
szServer + ";Initial Catalog=" + szCatalog
asConnection = New AdomdConnection(asConnectionString)
asConnection.Open()
Return True
End Function 'Connect
Public Function ExecuteAndFetchResult(ByVal strCommand As string) As Boolean
If asConnection Is Nothing Then
Return False
End If
If asCommand Is Nothing Then
asCommand = New AdomdCommand()
End If
strCommand = strCommand.Replace("NaN", "null")
strCommand = strCommand.Replace("Infinity", "null")
Try
If Not (asDataReader Is Nothing) Then
If Not asDataReader.IsClosed Then
asDataReader.Close()
End If
End If
asCommand.Connection = asConnection
asCommand.CommandText = strCommand
asDataReader = asCommand.ExecuteReader()
Catch e As Exception
Log(e.Message)
Return False
End Try
Return True
End Function 'ExecuteAndFetchResult
Public Function DisConnect() As Boolean
Try
If Not (asConnection Is Nothing) Then
asConnection.Close()
End If
If Not (asCommand Is Nothing) Then
asCommand.Connection = Nothing
End If
If Not (asDataReader Is Nothing) Then
asDataReader.Close()
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Return True
End Function 'DisConnect
Private Sub Log(ByVal message As String)
'Log the message to some place
System.Diagnostics.Debug.Assert(False, message)
Return
End Sub 'Log
End Class 'AnalysisServerSession
End Namespace 'MovieCrossSellApplication
Any help appreciated
|||OK I think I am getting somewhere......
Raman Iyer you should be right I must have 2 collums in order to save the results in an array,
can't figure out how I can do that checked some tutorials but came up with nothing.....
If anyone could help or suggest a web site?
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If
'Dim vDSSItems(10) as string
While asSession.asDataReader.Read()
Dim val As string = asSession.asDataReader.GetString(0)
vDSSItems.add(val)
end while
