Moving Data Mining Objects

Applies to: SQL Server 2019 and earlier Analysis Services Azure Analysis Services Fabric/Power BI Premium

Important

Data mining was deprecated in SQL Server 2017 Analysis Services and now discontinued in SQL Server 2022 Analysis Services. Documentation is not updated for deprecated and discontinued features. To learn more, see Analysis Services backward compatibility.

The most common scenarios for moving data mining objects are to deploy a model from a testing or analysis environment to a production environment, or to share models with other users.

This topic describes how you can use the tools and scripting languages provided by SQL Server Analysis Services, for moving data mining objects.

Moving Data Mining Objects between Databases or Servers

You can move data mining objects between SQL Server Analysis Services databases or between instances of SQL Server Analysis Services in the following ways:

  • Re-deploying the solution to a different database.

  • Scripting individual objects.

  • Backing up and then restoring a copy of the database.

  • Exporting and importing structures and models.

The following section explains these options in more detail.

Deploying

Deploying the solution to a different server or database requires that you have the solution file that was created by using SQL Server Data Tools.

For more information about deploying Analysis Services solutions, see Deploy Analysis Services Projects (SSDT).

Scripting

SQL Server Analysis Services provides several languages that you can use to script objects.

  • XMLA: You can script objects using XMLA by right-clicking objects in SQL Server Management Studio. To execute the script, open it in an XMLA Query window on the target server.

  • DMX: You can create scripts by using templates or one of the query builders provided in SQL Server Data Tools and SQL Server Management Studio.

Note, however, that there are differences in the tasks that you can perform with each scripting language:

  • Properties such as the object description and data bindings can only by created or changed by using SQL Server Analysis Services DDL languages, not by using DMX.

  • Only DMX supports the import and export of mining objects.

  • Only DMX supports generating PMML or importing model definitions from PMML.

  • Only DMX supports training a model with application data. Moreover, the DMX INSERT INTO statement supports training a model without providing values for a key column.

For more information, see Developing with Analysis Services Scripting Language (ASSL).

Backup and Restore

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 2017 provides backup and restore functionality that makes database backups faster and easier.

For more information about backup, see Backup and Restore of Analysis Services Databases.

Exporting and Importing

Exporting and then re-importing 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 about the DMX syntax for these operations, see the following topics:

If you specify the INCLUDE DEPENDENCIES option, SQL Server 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 functionality provided by SQL Server Analysis Services for backing up and restoring an entire database, or redeploy the cube and its models.

See Also

Management of Data Mining Solutions and Objects