Connect to Source Data (Data Mining Client for Excel)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
This topic describes how to create and use connections used for storing data mining models, and for accessing external data stored in Analysis Services.
Data mining connections. The initial connection that you create when you start the add-ins is used to access algorithms, analyze data, and store mining structure and models.
A connection to an instance of Analysis Services is required to use the modeling and visualization tools in the add-ins, because the add-ins depend on algorithms and data structures that are provided by Analysis Services.
Connections to external data sources. You can also create connections to external data as you are building models or saving the results. For example, you can create a data mining model on one server, and then perform a prediction query against the data mining model by using data stored in another instance of Analysis Services, in an Excel data table, or in an external data source such as Microsoft Access. Each time that you access a new data source, you will be prompted to create a connection by using a dialog box.
In This Topic
Prerequisites
Create Data Mining Server Connections
Change Current Connection or Edit Existing Connections
Using External Data Source
Prerequisites
This version of the add-ins requires that your instance of Analysis Services be SQL Server 2012. A separate version of the add-ins is available if you want to connect to an earlier version of Analysis Services. There are versions of the add-ins that support SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.
To connect to an Analysis Services database, you must have permissions to access the database server. Moreover, data mining sessions must be enabled, and you must have read or read/write permissions on database objects stored on the server.
For information about permissions, see Security Considerations for the Data Mining Client (SQL Server Data Mining Add-ins).
Creating Data Mining Server Connections
The Connections group in the Data Mining Client for Excel and the Table Analysis Tools for Excel provides tools for managing connections to an instance of SQL Server Analysis Services.
You can create the connection when you install the add-in, or you can add a connection later.
You can create multiple connections, and change connections at any time, unless you are in the process of creating or querying a model.
Do not change or close a connection when a data mining model is being processed. The data mining model might lose data, or the model might become unusable.
Only one connection can be active at a particular time.
Connections in the Excel Add-ins
The Connections group in the Data Mining Client for Excel and the Table Analysis Tools for Excel is where you manage connections to an instance of SQL Server Analysis Services.
Create a new server connection in the Excel add-ins
Click the Connection button on the Analyze or Data Mining ribbon.
Note
The text of the button indicates if a connection exists. When no connection has been made in the worksheet, the button contains the text "<No connection>." If a connection was previously made in the workbook, the name of that connection appears in the button.
In the Analysis Services Connections dialog box, click New.
In the New Analysis Services Connection dialog box, type the name of the server.
Specify the authentication method.
Select a database from the Catalog name drop-down list. If no database exists on the instance, select (default).
Type a friendly name for the connection.
Click Test Connection to verify that the server and database are available.
Click OK, and then click Close.
Connections using a Web Service
If you are using a thin-client architecture to enable browsing of Analysis Services cubes and data, you can also configure a connection to an Analysis Services server through Web services. For information about how to define a Web-based client, see SQL Server Books Online.
If you have access to a server that has been configured for Web services, you can specify the connection type when you first create the connection.
Create an HTTP connection to Analysis Services
Open the New Analysis Services Connection dialog box.
For the server name, type http:// followed by the URL assigned to the Analysis Services server.
Type the user name and the password that is required to access the Web service.
Connections in the Visio Add-In
Unlike Excel, Visio does not provide a tool ribbon, and there are no buttons specifically for creating or monitoring connections. Instead, the data connection is created when you first select a data mining shape and drop it onto a Visio page. A wizard will prompt you to select the model for the shape and to set other options.
If you have previously used a connection to an Analysis Services data source in Excel, these connections are listed as possible data sources from which to select.
Create a connection for a Visio shape
Open the Data Mining Template, and select one of the data mining shapes.
Drag and drop the shape to a blank page.
In the Select a data source dialog box, select a data source from the list, or click New.
If you select New, follow the procedure that is described earlier to specify a server and catalog name, or to connect through a Web service.
Changing Connections
You can create multiple connections in the same worksheet, but only one connection can be active at a time. The name of the current connection is displayed in the Connection button.
In the Data Mining Client for Excel, you can also verify the connection string and status for the current connection by clicking Trace and then clicking Current Connection.
Use a different server connection
Click Connection.
In the Analysis Services Connections pane, select a connection from the Other Connections list, and click Make Current.
Click Test Connection to verify that the connection is available.
After a mining model has finished processing, the results are stored locally, and there is no effect on the data if you close the connection to one server and then connect to another server. However, you should avoid changing connections or losing the connection when a data mining model is being processed, because this could corrupt data.
Modify an existing server connection
You cannot modify an existing connection; if you want to connect to a different database or a different server, you should create a new connection.
If you must modify the connection string to increase the query timeout or add other parameters specific to your instance of Analysis Services, one option is to edit the .dmc file, where the connection string is stored.
<drive:>\Users\<myusername>\AppData\Local\Microsoft\Data Mining Add-in
Connecting to External Data Sources
Whereas the tools in the Analyze ribbon work exclusively with data in Excel, the tools in the Data Mining ribbon let you connect directly to external data sources to use as inputs for your model, or for sampling.
The following tools in these add-ins support use of external data for data mining:
Using Analysis Services as a Data Source
You cannot directly access data stored in an Analysis Services cube or tabular model. Instead, create a connection in Excel to the Analysis Services server, and use the data to create a model.
Relational Data Sources
If you want to use data from a relational source as input to your model, you can connect to the following versions of SQL Server:
SQL Server 2008
SQL Server 2008 R2
SQL Server 2012
You can also get data from any other relational data source that is supported as a data source by Analysis Services. For information about supported data sources, see Data Sources in Multidimensional Models
Note that the following data types cannot be used for data mining and will result in an error if included when you build a model:
ntext
binary