Automate SQL Server Analysis Services Tasks with PowerShell - Part 2

Problem

In my last tip about PowerShell for SQL Server Analysis Services, we offered an introduction to PowerShell. In this second tip we will demonstrate how to do a few different discrete tasks including: creating a backup with the current date, using variables to specify the partition to be processed, run an XMLA script to create a new partition and call MDX scripts using PowerShell.  Check out this tip to learn more.

Solution

Let's dive right into our first of four examples of using PowerShell to automate SQL Server Analysis Services tasks.

Working with PowerShell Variables with SQL Server Analysis Services

1. Let's start working with variables. 

   $mydate="ssas powershell" 

   
   We created a variable named $mydate. The prefix $ is mandatory. We are assigning the value "ssas powershell"
   .
2. If you need to display the value of $mydate variable just write the variable name and press ENTER

   $mydate

   
   
   We created a variable named $mydate. The prefix $ is mandatory. We are assigning the value "ssas powershell".
3. Now we are going to assign to the variable, the current date:

$mydate=Get-Date


We used the Get-Date function to get the current date and we stored the value in the $mydate variable. Note that PowerShell does not require the data type for a variable:


$mydate




4. As you noticed the date is in long format and in Spanish (the language depends on the server configuration). Let's convert the current format to the format yyyyddMM (years, days and months)

   $mydate=Get-Date -format yyyyddMM 


Note that you are using the MM for month uppercased. This is because the mm lowercased means minutes.

5. You can verify the new format:

$mydate 



We now have a variable with the current date in the yyyyddMM format.

6. What we want to do now is to create a backup with the current date.

   backup-asdatabase d:\awdb-$mydate.abf "AdventureWorksDW2012Multidimensional-EE" -AllowOverwrite -ApplyCompression 

   
   
   We created a backup with the current date on the d drive and we concatenated the name awdb- with the current date. We also specified the database name, overwriting the existing backup and if we want to compress the backup.
   
   

Processing a SQL Server Analysis Services Partition with PowerShell

1. Now let’s move to the SQL Server Analysis Services partitions. As you know in SSAS the multidimensional database contains cubes, the cubes contain measures and the measures have partitions. The picture below shows the hierarchy.
   
   
   
   
2. Now let’s process the partition Internet_Orders_2008. To do this we are going to create a variable $myyear with the value 2008 and then process that partition.

   $myyear=2008
   Invoke-ProcessPartition –Name "Internet_Orders_"+$myyear –MeasureGroupname "Internet Orders" –CubeName "Adventure Works" –database "AdventureWorksDW2012Multidimensional-EE" –ProcessType "ProcessFull"
    

   
   In the code above, the variable "Internet_Orders_$myyear" is the partition named "Internet_Orders_2008". The MeasureGroupName is "Internet Orders" and the CubeName is "Adventure Works".

Calling an XMLA Script from PowerShell

1. Now we are going to call an XMLA script. XMLA is mainly used to create SSAS objects and process them. In this example we are going to generate a script to create a partition and then call it using PowerShell.  First, let's create a script to generate the partition "Internet_Orders_2008". 
   
   
   
   
2. 
   
Now, let's modify the script created in step 1 by changing the year from "2008" to "2009". What we are doing is modifying the script for the "Internet_Orders_2008" partition to create a new partition named "Internet_Orders_2009". 




3. We are going to save the script in the d:\powershell2\createPartition.xmla directory and we will use the Inkove-ASCmd PowerShell command to run the scripts.

   Invoke-ASCmd –InputFile:"d:\powershell2\createPartition.xmla" 

   
   The command Invoke-ASCmd is the PowerShell command used to run SSAS commands including xmla scripts, mdx scripts to run SSAS queries, MDX scripts to run multidimensional queries and DMX queries to run Data Mining Queries. If you refresh the SSAS instance in SSMS, you will notice that a new partition is created.
   
   
   

Run MDX Queries with PowerShell

1. We are going to run an MDX query using PowerShell. First we are going to generate the MDX queries.
In order to do that, open the SSMS and browse the Adventure Works Cube in the AdventureWorks DW2012 Database.




2. Drag and drop the Internet Order Count Measure to the query pane.





3. Press the design mode icon to view the MDX.





4. Copy the query and save it to a file named "mymdx.mdx" in the "d:\powershell2\" directory.
5. To run the mdx script in PowerShell run this command:

Invoke-ASCmd  -Database "AdventureWorksDW2012Multidimensional-EE"  -InputFile:"d:\powershell2\mymdx.mdx"| Out-file d:\Results\XMLAQueryOutput.xml 

What we did is to execute the mdx file in the AdventureWorksDW2012Multidimensional database. The results are displayed in a xml file named XMLAQueryOutput.xml.

6. The results can be displayed here when you open the xml file:

Source Collected from mssqltips.com