Remote and Local Servers (Analysis Services - Data Mining)
Data mining requires a connection to an instance of SQL Server Analysis Services. You do not need to create an Online Analytical Processing (OLAP) cube or use OLAP features to do data mining, but the Analysis Services engine provides many advantages, such as performance and scalability, and data mining shares many components with OLAP. This topic provides information that you need to know when connecting to a local or remote instance of SQL Server Analysis Services to create, process, deploy, or query data mining models.
Configuring Permissions and Server Properties
Data mining requires additional permissions on an Analysis Services database. Most data mining properties can be set by using the Analysis Server Properties Dialog Box (Analysis Services - Multidimensional Data). For more information about the properties that you can configure, see Analysis Services Configuration Properties.
The following server properties are of particular relevance to data mining:
AllowAdHocOpenRowsetQueries Controls ad hoc access to OLE DB providers, which are loaded directly into server memory space.
Important
To improve security, we recommend that you set this property to false. The default value is false. However, even if this property is set to false, users can continue to create singleton queries, and can use OPENQUERY on permitted data sources.
AllowedProvidersInOpenRowset Specifies the provider, if ad hoc access is enabled. You can specify multiple providers, by entering a comma-separated list of ProgIDs.
MaxConcurrentPredictionQueries Controls the load on the server caused by predictions. The default value of 0 allows unlimited queries for SQL Server Enterprise, and a maximum of five concurrent queries for SQL Server Standard. Queries above the limit are serialized and may time out.
The server provides additional properties that control which data mining algorithms are available, including any restrictions on the algorithms, and the defaults for all data mining services. However, there are no settings that allow you to control access to data mining stored procedures specifically. For more information, see Data Mining Properties.
You can also set properties that let you tune the server and control security for client usage. For more information, see Feature Properties.
Note Plug-in algorithms can be enabled only in SQL Server Enterprise.
Programmatic Access to Data Mining Objects
You can use the following object models to create a connection to an Analysis Services database and work with data mining objects:
ADO Uses OLE DB to connect to an Analysis Services server. When you use ADO, the client is limited to schema rowset queries and DMX statements.
ADO.NET Interacts with SQL Server providers better than other providers. Uses data adaptors to store dynamic rowsets. Uses the dataset object, which is a cache of the server data stored as data tables that can be updated or saved as XML.
ADOMD.NET A managed data provider that is optimized for working with data mining and OLAP. ADOMD.NET is faster and more memory-efficient than ADO.NET. ADOMD.NET also lets you retrieve metadata about server objects. Recommended for client applications except when .NET is not available.
Server ADOMD Object model for accessing Analysis Services objects directly on the server. Used by Analysis Services stored procedures; not for client use.
AMO Management interface for Analysis Services that replaces Decision Support Objects (DSO). Operations such as iterating objects require higher permissions when using AMO than when using other interfaces. That is because AMO accesses metadata directly, whereas ADOMD.NET and other interfaces access only the database schemas.
Browsing and Querying Models
You can perform all kinds of predictions by using either local or remote servers, with the following restrictions:
If you use server ADOMD, you can use DMX to access the server without making a connection. You can then copy the results directly into a data table. However, you cannot use Server ADOMD with remote instances; you can query only the local server.
ADO.NET does not support named parameters for data mining. You must use ADOMD.NET.
ADOMD.NET lets you pass an entire table to use as the parameter; therefore, you can use data on the client, or data that is unavailable to the server. You can also use shaped tables as prediction input.
Using Data Mining Stored Procedures
ADOMD.NET lets you browse model content in hierarchical form, and recurse through nodes. However, if you create a query against the model content, the query returns the entire model. To avoid bringing back too many rows, you can write stored procedures against an Analysis Services by using the ADOMD+ object model. A common use of stored procedures is to encapsulate queries for reuse.
The client can use CALL to run stored procedures, including Analysis Services system stored procedures. If the procedure returns a dataset, the client will receive a dataset or datatable with a nested table containing the rows. To write a server stored procedure, you must reference the Microsoft.AnalysisServices.AdomdServer namespace. For more information about how to create and use stored procedures, see User Defined Functions and Stored Procedures.
Note
Stored procedures cannot be used to change security on data server objects. When you execute a stored procedure, the user's current context is used to determine access to all server objects. Therefore, users must have appropriate permissions on any database objects that they access.
Moving Data Mining Objects between Databases or Servers
You can move data mining objects between databases or between instances of Analysis Services in the following ways: deploying a solution; backing up and then restoring a copy of the database; exporting and importing structures and models.
Deploying means the process of moving models from the test or development server to a different server, either for production, to share models, or to embed a model into an application. For more information about deploying Analysis Services solutions, see Deployment (Analysis Services - Multidimensional Data).
Backup and restore of an entire Analysis Services database is the method of choice if your data mining solution relies on OLAP objects. SQL Server 2008 provides new backup and restore functionality that makes database backups faster and easier. For more information about backup, see Managing Backing Up and Restoring (Analysis Services).
Importing and exporting mining models and structures by using DMX statements is the easiest way to move or back up individual relational data mining objects. For more information, see the following topics:
If you specify the INCLUDE DEPENDENCIES option, Analysis Services will also export the definition of any required data source views, and when you import the model or structure, it will re-create the data source view on the target server. After you have finished importing the model, make sure to set the necessary mining permissions on the object.
Note
You cannot export and import OLAP models by using DMX. If your mining model is based on an OLAP cube, you must use the Analysis Services backup and restore or deployment functionality on the entire database.