Yet another thread about Parallel Process

Question

Yet another thread about Parallel Process

Default port

Points: 1403

January 12, 2007 at 10:21 am

Hi,

In AS2000, with Parallel Process it is possible to process paritions in parallel each partition within a transaction.

In AS2005, it is possible to process partitions in parallel but within a unique transaction. For us this a major problem since processing the other partitions again can result in unnecessary hours reprocessing.

Futhermore, how do you process partitions in parallel from a command line?

Is it possible thru DSO (installing the backward compability) to process a AS2005 cubo using the Parallel Process?

If so, how it can be done?

Thanks in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Answering your last question first, i don't think you can run Parallel Process (the app) on AS2k5 cubes, but could be wrong. I don't think you'd *want* to run it on AS2K5 cubes .

I think what you're looking for is the 'Batch' element in an XMLA command to the server. You can see the Batch element (here).

i) the 'Transaction' property, a boolean indicating whether all commands included should be run in a single transaction or their own transactions (set it to false to run them in individual transactions);

ii) the 'Parallel' element. This contains a proeprty that you set indicating how many commands should be run in parallel. It is also where you specify the 'Process' commands for each of your partitions.

The only problem that I have found with the above is that setting Transaction="false" is not a valid option with a batch element with Parallel child elements. So it looks like this may not be supported at this time. This is detailed here, scroll down to the section on 'Using Serial and Parallel Execution' and look at the note below the paragraph.

To see how one of these command files is put together, go through SQL Server Management Studio, right click on one of your cubes and select 'process'. Then click the 'Change Settings' button. On the Change Settings dialog, change the level of parallelism (if desired). Click OK and then click the 'Script' button (top left of the dialog). By default this scripts the command(s) to a new XMLA file. Remeber to cancel out of the dialog if you don't want to actually do a processing run.

To answer your question of 'running from a command line', do a search on microsoft.com for ASCMD, this is the AS equivalent of SQLCMD, in that it lets you connect to servers and pass command files (like the one we've discussed above) to the server for processing. You could, of course, make a cmdline call to this commandline application via SQLAgent as part of a nightly/weekly processing run.

SO, to get them to run in parallel, you need them in a single transaction. You could run them serially in individual transactions but that's probably not what you're after. You could try sending several process commands (ie one per partition that you want to process) to the server at a single time, this you control the parallelism (by sending however many you want to process) and each isin a single transaction. The issue will be determining how many process commands the server can/will handle at a single time. Although running in a single transaction, we had a 4-way server with 4gb of memory that could easily process between 2 and 4 partitions in parallel (~100mill fact rows per partition).

HTH,

Steve.

Tiago Rente Default port Points: 1403 More actions · Answer 2

Thanks Steve for you feedback.

We already use Parallel Process in AS2000, so using it in AS2005 would be a plus.

We are trying to create a IS package that creates a dynamic package that creates and executes n calls to "Analysis Services Execute DDL Task" each for each dimension/partition. This dynamic package sets the number of tasks to be executed in parallel. However, we are not able to grab the log of each task and sometimes the tasks fail without any apparent reason.

Now, we are changing a little the strategy to call ascmd instead the "Analysis Services Execute DDL Task".

Thanks.