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

Data Mining: Part 13 Powershell

By Daniel Calbimonte,

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

Resources:

createDatabase.xmla | PowerShellRunScript.xmla

This article is part of the series Data Mining with Daniel Calbimonte:

Total article views: 4766 | Views in the last 30 days: 856
 
Related Articles
ARTICLE

Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO.

FORUM

To Script database through a command

Is it possible to script the entire database through a command or a query

FORUM

full cycle structure...command select *

full cycle structure...command select *

FORUM

Basic Database Structure

Basic Database Structure

BLOG

Performing an INSERT from a PowerShell script

This code demonstrates how to do an INSERT into SQL Server from a PowerShell script using an ADO.NET...

Tags
data mining    
powershell    
 
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