PowerShell: Getting Cube XMLA
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
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 -