Problem Description:

Automate the steps of

1. Connecting to SSMS

2. Select Cube

3. Right click and generate XMLA “Create Cube”

4. Schedule from SQL Job agent

For last few days I was playing around with PowerShell so thought of achieving this using PS.

Code which I wrote for getting cube Create XMLA is:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL

$server = New-Object Microsoft.AnalysisServices.Server

$server.connect(".\sql2008r2")

$advdb=$server.Databases.FindByName("Adventure Works DW 2008R2")

$Adventcube = $advdb.Cubes.FindByName("Adventure Works")

$stringbuilder = new-Object System.Text.StringBuilder

$stringwriter = new-Object System.IO.StringWriter($stringbuilder)

$xmlOut = New-Object System.Xml.XmlTextWriter($stringwriter)

$xmlOut.Formatting = [System.Xml.Formatting]::Indented

$scriptObject = New-Object Microsoft.AnalysisServices.Scripter

$MSASObject=[Microsoft.AnalysisServices.MajorObject[]] @($Adventcube)

$ScriptObject.ScriptCreate($MSASObject,$xmlOut,$false)

$stringbuilder.ToString() |out-file -filepath c:\AdventDb_Adventureworks_cube.xmla

How to Schedule it:

So I saved this Script on my C Drive and name it as test.ps1.

for Scheduling it as a job, firstly I will configure PS on my SQL Engine. Ensure that Policy is enable to run the script remotely.

once that’s is done you have 2 options.

A) Create a step with Type CMDEXEC and in Command Provide

image

B) Create a Step with Type as PoweShell and in Command Provide Path of PS1 File as shown below

image 

 

There you go…..

In Script – Change Server Name, Database and Cube Name matching to your Environment.

 

Thanks Ram Madhurakavi  and Arvind for encouraging me to start learning PowerShell

 

PS Help -

http://technet.microsoft.com/en-us/library/ee692764.aspx