Automate SQL Server Analysis Services Tasks with PowerShell - Part 2

By:   |   Comments   |   Related: > Analysis Services Administration


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.


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

    Variable results

    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:
  4. $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:


    Run command

  5. 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 
  6. Note that you are using the MM for month uppercased. This is because the mm lowercased means minutes.

  7. You can verify the new format:
  8. $mydate 

    powershell format date

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

  9. 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 

    backup ssas with powershell

    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.

    ssas backup file

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.

    ssas hierarchies

  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.
    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".

    ssas partitions

  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".

    xmla script

  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.

    SSAS new partition

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.
  2. In order to do that, open the SSMS and browse the Adventure Works Cube in the AdventureWorks DW2012 Database.

    Run command

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

  4. Drag and drop measures

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

  6. mdx query

  7. Copy the query and save it to a file named "mymdx.mdx" in the "d:\powershell2\" directory.
  8. To run the mdx script in PowerShell run this command:
  9. 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.

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

  11. xml result
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms