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:
$server = New-Object Microsoft.AnalysisServices.Server
$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
$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
B) Create a Step with Type as PoweShell and in Command Provide Path of PS1 File as shown below
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 –