Using Dynamic Values in XMLA

A question was raised on Twitter today via the #sqlhelp hash tag about passing parameter values to XMLA for a backup script. The popular answer at the time I saw it was to dynamiclly create and drop a SQL job to run the script every time you need it to run. I proposed a different solution,

The Question

The following request was sent in a series of tweets:

Is there a way to pass variables between tsql and mdx queries within a sql job to automate #SSAS db backups. #sqlhelp

Correction: Is there a way to pass variables between tsql and XMLA queries within a sql job to automate #SSAS db backups. #sqlhelp

I have the tsql part working which creates the backup syntax, now I need it to execute the XMLA to perform the backups. #sqlhelp

My Proposed Solution

As I stated above, the popular solution was to use dynamic SQL to have it create a job that runs the backup and then delete the job. In my session A DBAs Guide to Administering BI Systems at the PASS Summit 2011, I talked about executing MDX queries via T-SQL across a linked server. This approach works with XMLA as well. I simply need to use dynamic SQL to build the XMLA command and then execute it across the linked server. I set up a test to demonstrate how to do it before recommending it.

I started by using the SSMS GUI to script out the backup command for the Adventure Works DW 2008R2 database in my local SSAS instance. I suspect that adding a date serial to the backup name is likely to be the reason for using this, that is the scenario I used. I then use the EXEC() AT command to execute the string across a linked server (named SSAS in my sample code).

Declare @XMLA nvarchar(1000),
    @DateSerial nvarchar(35);

-- Change date to format YYYYMMDDHHMMSS
Set @DateSerial = Convert(nvarchar, getdate(), 112) +
        Replace(Convert(nvarchar, getdate(), 108), ':', '');

-- Create the XMLA string
Set @XMLA = N'<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW 2008R2</DatabaseID>
  </Object>
  <File>c:bakAdventure Works DW 2008R2_' + @DateSerial + '.abf</File>
</Backup>';

-- Execute the string across the linked server (SSAS)
Exec (@XMLA) At SSAS;