ASCMD executing multiple MDX commands in one batch file

Has anyone got an example of ASCMD executing multiple MDX commands in one batch file? Or are we restricted to one MDX command only? We haven't been able to use multiple commands.

The doc at http://msdn2.microsoft.com/en-us/library/ms186692.aspx states that you can have multiple xmla commands.

<Command> <Batch Transaction="Boolean" ProcessAffectedObjects="Boolean"> <Bindings>...</Bindings> <DataSource>...</DataSource> <DataSourceView>...</DataSourceView> <ErrorConfiguration>...</ErrorConfiguration> <Parallel>...</Parallel> <!-- One or more XMLA commands --> </Batch> </Command>

I think ascmd is already sending through the <Command> element, so you only need to pass it the elements under that. If you put the following into a text file and pass it into ascmd, it will execute 2 create commands, creating two empty databases called Test Database 1 and Test Database 2.

<Batch Transaction="false" ProcessAffectedObjects="false" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Create>
<ObjectDefinition>
<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>Test Database 1</Name>
<Description>A test database.</Description>
</Database>
</ObjectDefinition>
</Create>
<Create>
<ObjectDefinition>
<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>Test Database 2</Name>
<Description>A test database.</Description>
</Database>
</ObjectDefinition>
</Create>
</Batch>

|||

You cannot have multiple MDX commands in your batch. Batch is intended for execution of several processing commands not MDX.

I believe next version of ASCMD will support GO statement. So you'd be able to create an input file with several MDX statements separated by the GO. Very much the same way it is done in SQL.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Sorry contexbi, Edward is correct BOL is inaccurate. I read "MDX Command", but I was thinking "XMLA Command" when I first answered your question.

As you may know MDX statements are sent in a "Statement" element and you cannot have more than 1 of these in an XMLA command/batch. Other commands (like my create example) can be batched up, but until BOL is fixed you can't really tell which ones until you try. I believe that the restriction on only allowing a single MDX statement is a security feature to prevent potential "MDX injection" attacks.

Management Studio does implement the "GO" statement to execute multiple MDX statements, but this is because it breaks up and sends the MDX separately but I don't think you can get SSMS to run unattended.

Your only approach at the moment would be to call ascmd multiple times from a batch file.

|||

Thanks for the update and clarification. The documentation was confusing.

Any ETA for the next version of ASCMD that supports multiple MDX statements in a batch? SP2?

|||

Hello,
Can we process the partitions and dimensions in one xmla file?
The reason I ask is that we will use the parallel tag to process the dimensions in parallel. But how do I ensure that the partition/ measure group is processed after the dimensions in a single XMLA script?

Rgds
HariK

|||

You can definitely have dimensions and partitions in a single batch command.

Analysis Server will analyzes every object involved in processing and builds a processing schedule the way dimensions are processed first.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.