question

surya1509 avatar image
0 Votes"
surya1509 asked surya1509 commented

XMLA drop database if exists and recreate

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.

sql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @surya1509, we have not get a reply from you. Could the answer from dgosbell help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·

1 Answer

dgosbell avatar image
0 Votes"
dgosbell answered surya1509 commented

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://docs.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://docs.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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@dgosbell ,
I am scripting out the complete cube from on-prem SSAS database , i dont see any option saying Allowoverwrite being scripted out , if the script the complete cube . Please suggest or guide me if i am missing anything.
In a one liner , i need a kind of if exists clause before the cube XMLA script to verify if the cube already exists then do not run the create cube XMLA script else run the script.

0 Votes 0 ·