Imports Microsoft.AnalysisServices
Imports System.Data.OleDb
Imports System.Data
Imports Microsoft.AnalysisServices.AdomdClient

Public Class FormMain

    Private mServer As New Server
    Private mDatabase As Database
    Private mDataSource As DataSource
    Private mDataSourceView As DataSourceView

    Private mConnectionString As String = _
            "Provider=SQLOLEDB.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW"

    Public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        Try
            mServer.Connect("localhost")
        Catch ex As Exception
            MsgBox("Exception: " & ex.Message & vbCrLf & _
                  "InnerException: " & ex.InnerException.Message)
            Application.Exit()  'Anwendung wird abgebrochen
        End Try

    End Sub


    Function createDatabase(ByVal aServer As Server, ByVal aDatenbankName As String) As Database

        'Prüfen, ob Datenbank bereits vorhanden
        If aServer.Databases.ContainsName(aDatenbankName) Then
            MsgBox("Datenbank " & aDatenbankName & " ist bereits vorhanden!")
            Return aServer.Databases.GetByName(aDatenbankName)
        End If

        'Datenbank erzeugen
        Dim lDatabase As Database = New Database(aDatenbankName)

        'Datenbank zu Server hinzufügen
        aServer.Databases.Add(lDatabase)
        lDatabase.Update()

        Return lDatabase
    End Function


    Function defineDataSource(ByVal aDatabase As Database, _
                              ByVal aDataSourceName As String) As DataSource

        'Prüfen, ob Data Source vorhanden ist
        If aDatabase.DataSources.ContainsName(aDataSourceName) Then
            MsgBox("Data Source " & aDataSourceName & " ist bereits vorhanden!")
            Return aDatabase.DataSources.GetByName(aDataSourceName)
        End If

        'Daten Quelle erzeugen
        Dim lDataSource As New RelationalDataSource(aDataSourceName, _
                                       Utils.GetSyntacticallyValidID(aDataSourceName, GetType(RelationalDataSource)))


        'Zur Datenbank hinzufügen
        lDataSource.ConnectionString = mConnectionString
        aDatabase.DataSources.Add(lDataSource)
        lDataSource.Update()

        Return lDataSource
    End Function

    Sub createDatabaseView()
        Dim lCommand As New System.Data.OleDb.OleDbCommand()
        With lCommand
            .CommandText = "create view vSale as " & _
                              "SELECT DimCustomer.CustomerKey AS Customer, " & _
                                     "DimProduct.ProductKey AS Product, " & _
                                     "DimProduct.EnglishProductName AS ProductName " & _
                              "FROM DimCustomer INNER JOIN " & _
                                   "FactInternetSales ON " & _
                                            "DimCustomer.CustomerKey = FactInternetSales.CustomerKey " & _
                                   "INNER JOIN DimProduct ON " & _
                                            "FactInternetSales.ProductKey = DimProduct.ProductKey"
            .Connection = New System.Data.OleDb.OleDbConnection(mConnectionString)
            .Connection.Open()
            .ExecuteNonQuery()
            .Connection.Close()
        End With
    End Sub

    Function createDataSourceView(ByVal aDatabase As Database, _
                                  ByVal aDataSource As DataSource, _
                                  ByVal aDataSourceViewName As String) As DataSourceView

        'Prüfen, ob bereits vorhanden
        If aDatabase.DataSourceViews.ContainsName(aDataSourceViewName) Then
            MsgBox("Data Source View " & aDataSourceViewName & " bereits vorhanden!")
            Return aDatabase.DataSourceViews.GetByName(aDataSourceViewName)
        End If

        Dim lDataSourceView As New DataSourceView(aDataSourceViewName)
        lDataSourceView.DataSourceID = aDataSource.ID
        aDatabase.DataSourceViews.Add(lDataSourceView)


        Dim lCommand As New OleDbCommand("vSale", New OleDbConnection(mConnectionString))
        lCommand.CommandType = Data.CommandType.TableDirect

        Dim lDataSet As New DataSet
        Dim lDataAdapter As New OleDbDataAdapter(lCommand)
        lDataAdapter.FillSchema(lDataSet, SchemaType.Source)

        'Muss geclont werden, da orginal DataTable bereits zu einem 
        'Dataset gehört
        With lDataSourceView
            .Schema = New DataSet
            .Schema.Tables.Add(lDataSet.Tables(0).Clone())
            .Update()
        End With

        Return lDataSourceView

    End Function

    
    Private Sub createMiningModel()
        Dim lCommand As New AdomdCommand
        With lCommand
            .Connection = New AdomdConnection("Data Source=localhost; Catalog=webSaleDB")
            .Connection.Open()
            .CommandText = "CREATE MINING MODEL WebSale " & _
                                       "( Customer  LONG   KEY, " & _
                                       "  Products TABLE  PREDICT " & _
                                       "  ( Product TEXT KEY) " & _
                                       ") USING Microsoft_Association_Rules"
            .ExecuteNonQuery()
            .Connection.Close()
        End With
    End Sub

    

    Private Sub trainMiningModel()
        Dim lCommand As New AdomdCommand
        With lCommand
            .Connection = New AdomdConnection("Data Source=localhost; Catalog=webSaleDB")
            .Connection.Open()
            .CommandText = "INSERT INTO WebSale " & _
                               "( Customer, Products (SKIP, Product)) " & _
                           "SHAPE { " & _
                               "OPENQUERY(webSaleSource,'select distinct Customer from vSale order by Customer') " & _
                           "} APPEND (" & _
                               "{OPENQUERY(webSaleSource,'select Customer, ProductName " & _
                                                  "from vSale order by Customer,Product')}" & _
                              " RELATE Customer TO Customer) as CustomerProducts"

            .ExecuteNonQuery()
            .Connection.Close()
        End With
    End Sub

#Region "Buttons"
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        mDatabase = createDatabase(mServer, txtAnalysisDatenbank.Text)
        MsgBox("Datenbank erzeugt bzw. vorhanden")
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        mDataSource = defineDataSource(mDatabase, txtDataSourceName.Text)
        MsgBox("DatenQuelle hinzugefügt")
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        createDatabaseView()
        MsgBox("View vSale wurde erfolgreich erzeugt!")
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        createMiningModel()
        MsgBox("Mining Model wurde erfolgreich erzeugt!")
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        trainMiningModel()
        MsgBox("Mining Model wurde erfolgreich trainiert!")
    End Sub
#End Region

End Class
