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 SubThe 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() 
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] 
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())
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