XMLA drop database if exists and recreate

Question

Hi,
I have scripted out existing ssas database/cube using xmla format, now i wanted to make this script idempotent(run n number of times), do we have the capability to use if exists if not exists clause in SSAS xmla scripts? I dont want to execute this process using SSIS , if possible i want to automate it using powershell.
Please let me know if this is feasible.

Answer 1

The script command in SSMS can generate either XMLA or TMSL

If you are using actual XMLA (so for a multi-dim project or a compat level under 1200) then the CREATE element has an AllowOverwrite option see https://learn.microsoft.com/en-us/analysis-services/xmla/xml-elements-commands/create-element-xmla?view=asallproducts-allversions ) which will achieve the same effect

If the script is TMSL (a json format) then you can use the createOrReplace command https://learn.microsoft.com/en-us/analysis-services/tmsl/createorreplace-command-tmsl?view=asallproducts-allversions

You could also wrap a DROP and CREATE command in a BATCH in XMLA, but I'm not sure if that will run without error if the database does not already exist where as the above two options will. In TMSL you could do a similar think with a delete and a createOrReplace wrapped in a sequence.