Expected Annual Sales (MDX- Analysis Service)

Recently I got a query on to show annual sales.

For example in Adventure works for year 2004 there is sales upto July 31st.

How to backup multiple database using XMLA in one batch

Recently I got a question on how to backup multiple database using XMLA in one batch.

&sp;Lets see the problem

Here are two XMLA scripts for backup databases




  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Adventure Works DW 2008</DatabaseID>
    </Object>
    <File>adv1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Mining Test</DatabaseID>
    </Object>
    <File>mine1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>


Here when you run this query in XMLA query window it will give error

Executing the query ...
The 'Backup' element at line 15, column 76 ('http://schemas.microsoft.com/analysisservices/2003/engine' namespace) appears more than once under Envelope/Body/Execute/Command.
Execution complete


so we need to run it in batch. In batch we can run multiple XMLA query. But since this is backup we can not run it in transaction. So we have to specify transaction as false in the query

So the new query will be


<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="false" >

  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Adventure Works DW 2008</DatabaseID>
    </Object>
    <File>adv1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Mining Test</DatabaseID>
    </Object>
    <File>mine1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

</Batch>

Cheers
Amish Shah