Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Data Mining: Part 14 Export DMX results with Integration Services

By Daniel Calbimonte,

Introduction

In this chapter, we will have a brief introduction to SQL Server Integration Services (SSIS) and the Data Mining Query Task Component.

SSIS is a component included in SQL Server to integrate different databases with Excel, XML, Web Services, C#, VB Code. In this lesson, we will show how to save the data mining results in a SQL Server table using SSIS.

The Data Mining Query Task is a visual task created to create data mining queries and export the results to SQL Server or Oracle or Excel or any other database. With SSIS you can easily interact between heterogeneous databases. 

Requirements

We are using SQL Server 2014 for this lesson, but the SQL Server 2005/2008/2012 and 2014 can be also be used.

We are assuming that you already have the Data Mining Project used in the Data Mining Part 13. If you do not, you can install it or use any other Data Mining Project already installed.

Getting Started

1. You can use the Visual Studio to open a SSIS project.

2. In the Menu, go to File>New Project and choose Integration Services Project. Specify the Name and Path.

3. There are many tasks in SSIS to work with Web Services, create backups, execute queries, etc. In this lesson, we are going to use the Data Mining Query Task. Double click it.

4. In the design pane, you will see the Task. Double click it.

5. We need to connect to the Data Mining. Press the New button to create a new Connection.

6. Press the New Button to create a connection.

7. In the Provider Combobox Select the Provider for Analysis Services. In the Server or file name, specify the name of the server and in the Initial Catalog select the database with the Data Mining Model. In this case, we are using the database used in chapter 13.

8. Once you have selected the data connection, press OK.

9. Select a Mining Structure and a Mining Model and Click on the Query Tab.

10. Here you can create your Data Mining Query visually. We are going to create a query here. Press the select model button.

11. I am not going to explain in detail how to create queries here. You can refer to the Data Mining Introduction for more details, Predict the future, steps 1 to 8. You will specify the customer characteristics of the Data Mining Query. With that information, create a query similar to the picture.

12. Once finished, you can observe the DMX query generated. For more information about DMX, go to the lesson 12. Click in the Output tab.

13.  In the output table, write test1. This is the table used to store the Data Mining Queries.

In the Connection press the New Button.

14. Specify the Provider. In this case we will store the information in SQL Server. That is why we are using the SqlClient Data Provider, but we could store the information in Oracle or other Database. Select the database server name and select or enter the database name. In this sample, we will work with the AdventureWorks database. Press OK. 

15. Now, right click on the task and select Execute Task.

16. If everything is OK, you can open the SQL Server Management Studio and check in the AdventureWorks if the new table named dbo.

17. Verify the data in the Table.

Conclusions

As you can see, you can easily export your Data Mining Query results in a SQL Server table in Oracle or any other database using the SSIS components.

References

http://technet.microsoft.com/en-us/library/ms141026.aspx

http://msdn.microsoft.com/en-us/library/ms141728.aspx

Total article views: 2045 | Views in the last 30 days: 62
 
Related Articles
FORUM

select query

select query

FORUM

Select query

Select query

FORUM

Auto-Select Database Name

Auto-Select Database Name

FORUM

Creating Select Query with User Supplied Value?

Need to create a select query where I can supply a range of data or value for retrieval.

ARTICLE

How to Create a Corrupt SQL Server Database

This article details how to create a corrupt SQL Server database for testing purposes

Tags
data mining    
integers    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones