Introduction

In this lesson, we will learn how to use Powershell for Data Mining models. For more information about Powershell, read the article Powershell for SQL Server.

Powershell is a very powerful tool to automate tasks in Data Mining. In this lesson, we will show how to navigate and watch some properties, how to run scripts and how to create data mining objects with the script.

Requirements

1. We are using SQL Server 2014, but the Powershell commands are available in SQL 2012. Earlier versions of SQL Server do not support the some PowerShell Commands.
2. We are using a script named CreateDatabase.xmla (in the resource files) to create a simple multidimensional database with a Mining structure and mining model attached to this article. You can use that project or use your own projects. If you do not know how to run a multidimensional script, read my xmla article.

Getting Started

Start the Powershell command prompt. The icon looks like the following:

In the Powershell window, enter "SQLPS" (SQL Powershell):

You can move to different Data Mining Directories using Powershell. The structure in SQL Server Management Studio (SSMS) is the following:

In PowerShell the structure of the directories is similar. For example, to move to the Decision Trees folder you have to use the following path:

1. cd sqlas\ServerName\default\databases\MultidimensionalProject1\MiningStructures\decision trees

Where

• sqlas is SQL Server Analysis Services.
• ServerName is the name of the Analysis Services name, which by default is the ServerName.
• Default is the name of the instance whose name is default (by default).
• databases is the database created by default with the script mentioned in the requirements that contains the MultidemensionalProject1 Database.
• Inside the Database, you have the MiningStructures.
• Inside the Mining Structure you have the Mining Model, which in this case is decision trees.

You can see what is inside each directory using the gci command, which is similar to the dir command used in UNIX or DOS. Run the gci command:

gci

In this case, you will see these results:

The GCI shows the component inside the decision trees model. Now move the the MiningModels directory:

cd .\MiningModels

The cd command lets you move in the different data mining directories in the same way that you do using DOS or LINUX/UNIX.

Verify the properties of the MiningModels using the gci command.

gci

We can see the results here:

If you need to watch specific properties of the mining structures, you can use the gci command combined with the select clause:

cd sqlas\ServerName\default\databases\MultidimensionalProject1\MiningStructures
gci | Select Name,ID,state,miningmodels

The cd (Change Directory) command changes the directory to the mining structures and shows the name of the structures, the ID, the state of the structure and the mining models available.

To see the complete list of properties available use this command: 

gci | Get-member

In order to create a new Database, we are going to use a script named PowerShellRunScript.xmla, which is included in the resource files section. To run the script in PowerShell run this command:

Invoke-ASCmd –InputFile:"C:\scripts\PowerShellRunScript.xmla"

Make sure to copy the script in the c:\script path or modify the command line to the path where the PowerShellRunScript.xmla was copied.

If everything is OK, you will have a new Database named Multidimensionalproject2 created:

Conclusions

As you can see, Powershell can be used to navigate and watch the properties related to Data Mining. Powershell can be used to backup the multidimensional Databases, Process Mining Structures, create roles, etc. For more information about backups, roles you can see the references.

References

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