Data Mining Part 28: Programming with AMO and VB

,

Introduction

In this new chapter, we will connect to our Data Mining models using VB.NET. We could use the Visual Studio, but in this case, we will use the Script task in SSIS.

Requirements

Before we begin, we must be sure that the following are completed:

  • We have the AdventureworksDW database installed
  • The multidimensional Adventureworks project is installed.
  • The AMO installer has been run. This is an installer with the libraries of the Analysis Services Management Objects. Once you download the installer, run it to install the objects.
  • We have the SSAS database installed
  • SSDT for business intelligence is installed.

Getting Started

The first example will show how to open an SSIS task, add the references to Analysis Services objects and then display the database name where the Mining Structure and models are stored.

1. In order to start, open the SQL Server Data Tools.

2. In SSDT, go to File>New>Project

3. Select the Integration Services Project

4. Drag and drop the script task to the design pane.

5. In the language script, select Microsoft Visual Basic and then press the edit button.

6. A new window will be displayed similar to the picture below. In the VB solution, right click and press add reference.

7. In assemblies (or in the .net tab), go to extensions and select Analysis Management Objects (as shown below). You are adding the reference to the project to accessthe Management Objects installed.

8. In the Imports region, you can add the reference to Microsoft.AnalysisServices.

10. In the public sub main, write this code.

    Public Sub Main()
        '
        ' Add your code here
        '
        'Create the AS Server variable
        Dim DM_Server As New Microsoft.AnalysisServices.Server
        'Create the database variable
        Dim AS_Database As New Microsoft.AnalysisServices.Database
        'Connect to the database
        DM_Server.Connect("Data Source=infra4;Initial Catalog=AdventureWorksDW2014Multidimensional-EE")
        'Specify the database name
        AS_Database = DM_Server.Databases("AdventureWorksDW2014Multidimensional-EE")
        'show the SSAS database
        MessageBox.Show("Database: " + AS_Database.Name)
    End Sub

The code connects to the SSAS server named Infra4 and thedDatabase named AdventureWorks2014Multidimensional-EE. If the name of your database is different, change the code accordingly.

11. Once the code is done, save the VB script.

12. Accept the changes in the Script Task Editor

13. Run the package with the Start button.

14. The code retrieves the database name:

Working with variables

In this new example, we will retrieve the last time that the mining structure was processed and store that value in a table.

1. In the SSDT menu go to SSIS>Variables

2. In Variables, create a variable named lastProcessed with the scope package and data type string.

3. Open the script task used in the basic example and in click on ReadWriteVariables

4. Select the variable created in step 2. 

5. Press Edit Script to modify the script.

6. Add this line of code:

        Dts.Variables("lastProcessed").Value = AS_Database.MiningStructures(1).LastProcessed.ToString()
This line of code stores in the lastProcessed variable the date of the last time that the mining structure was processed.
7. Add an Execute SQL Task and join to the Script Task.

8. Now, create a table to store the values. In this case, we will create a table named t1 with a column named c1:

CREATE TABLE [dbo].[t1](
[c1] [nchar](50) NULL
) ON [PRIMARY]
9. Open the Execute SQL Task. In connection, select a new connection.

10. If you do not have a connection, press the new button.

11. Connect to the Server of the SQL and the database where you created a table in step 8. In my example, the database is AdventureworksDW2014.

12. Once selected, press OK.

13. On the execute statement, write the T-SQL sentence to insert the data on the table created on step 8.

insert into table t1 values(?)

The ? will be the parameter created in step 2.

14. Go to the Parameter Mapping page and add a new Variable. Add the variable created in step 2. The direction is input, the datatype will be NVARCHAR, and the parameter name 0. It is 0 because the enumeration of the parameters starts at 0. If there were two parameters, the first parameter name would be 0 and the second would be 1. 

15. Save the task and run the package with the start button.

16. In order to check that everything was OK, query the table.

17. As you can see, the last day that the structure was processed was stored in the table.

Retrieving Data Mining Information

The next example will obtain Data Mining structure information and write the information in a text file.

The Data Mining structure is stored in an Analysis Server, and the server contains a database. In the database there are one or more data mining structures. Inside the structures there are data mining models. The following example will show information of the Server, Database, Structures and models.

For this purpose, we will use the script task. We will modify the script task used in earlier examples of this chapter.

1. Add this code to the script in the region section:

Imports System.IO

The System.IO is used to store information in a file.

2. Add this code in the script task created on the step 4 of the basic example:

        Dim Path As String = "c:\scripts\amoresults.txt"
        Dim sw As StreamWriter = File.AppendText(Path)
        sw.WriteLine("Server Information ")
        sw.WriteLine("Server name: " + DM_Server.Name)
        sw.WriteLine("Server version: " + DM_Server.Version)
        sw.WriteLine("Server id: " + DM_Server.ID)
        sw.WriteLine("Database Information ")
        sw.WriteLine("Database name: " + AS_Database.Name)
        sw.WriteLine("Last Processed Date: " + AS_Database.LastProcessed.ToString())
        sw.WriteLine("Database ID: " + AS_Database.ID)
        sw.WriteLine("Database Last Update: " + AS_Database.LastUpdate.ToString())
        sw.WriteLine("First Mining Structure Information ")
        sw.WriteLine("Mining Structure Name: " + AS_Database.MiningStructures(0).Name)
        sw.WriteLine("Mining structure holdout cases: " + AS_Database.MiningStructures(0).HoldoutMaxCases.ToString())
        sw.WriteLine("Mining structure Holdout Actual Size: " + AS_Database.MiningStructures(0).HoldoutActualSize.ToString())
        sw.WriteLine("Mining Structure ID: " + AS_Database.MiningStructures(0).ID)
        sw.WriteLine("SecondMining Structure Information ")
        sw.WriteLine("Mining Structure Name: " + AS_Database.MiningStructures(1).Name)
        sw.WriteLine("Mining structure holdout cases: " + AS_Database.MiningStructures(1).HoldoutMaxCases.ToString())
        sw.WriteLine("Mining structure Holdout Actual Size: " + AS_Database.MiningStructures(1).HoldoutActualSize.ToString())
        sw.WriteLine("Mining Structure ID: " + AS_Database.MiningStructures(1).ID)
        sw.WriteLine("First Mining Model Information ")
        sw.WriteLine("Mining Model Name: " + AS_Database.MiningStructures(0).MiningModels(0).Name)
        sw.WriteLine("Mining Model ID: " + AS_Database.MiningStructures(0).MiningModels(0).ID)
        sw.WriteLine("Mining Model State: " + AS_Database.MiningStructures(0).MiningModels(0).State.ToString())
        sw.WriteLine("Last Processed Date: " + AS_Database.MiningStructures(0).MiningModels(0).LastProcessed.ToString())
The code writes information in a file in the c:\scripts\amoresults.txt file. Change the file according to your own folders and OS security.
3. If you open the amoresults.txf file, you will have the following information:

4. You can verify the information by opening the SQL Server Management Studio and connection to the SSAS Server.

5. In the amoresults file, we find that the Database name is the following: 

Database name: AdventureWorksDW2014Multidimensional-EE

This comes from this code:

        sw.WriteLine("Database name: " + AS_Database.Name)

6. The first Mining structure is the Customer Mining (number 0)

Mining Structure Name: Customer Mining

        sw.WriteLine("Mining Structure Name: " + AS_Database.MiningStructures(0).Name)

7. The second Mining structure is the Customer Mining (number 1)

Mining Structure Name: Forecasting

        sw.WriteLine("Mining Structure Name: " + AS_Database.MiningStructures(1).Name)

8. The first mining model from the first structure is the Customer Cluster.

Mining Model Name: Customer Clusters

sw.WriteLine("Mining Model Name: " + AS_Database.MiningStructures(0).MiningModels(0).Name)

Conclusion

In this chapter, we learned how to connect to the Data Mining using Visual Basic and SSIS. We learned how to store the information in a text file and how to store the information in a SQL Server table using parameters.

References

AMO data mining classes

Programming AMO data mining objects

Rate

Share

Share

Rate