When creating an SSIS dimension or partition processing task it offers a
'parallel' option but then it seems to force a single transaction for the
entire processing block. I'd like to kick off parallel processing to utlilize
cpu/db resources better but in seperate transactions (so I don't get
roll-back of everything if one partition or dim fails - my processing takes 8
hours!). Anyone know if that's possible in an SSIS package or another way ?

To answer my own question I tried the following XMLA:


<Batch Transaction="false"
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>prod_01_Source</DatabaseID>
<DimensionID>Broker</DimensionID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>

It results in:


<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<results
xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
<Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />
<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
<Error ErrorCode="3240034378" Description="Errors in the OLAP
storage engine: Element Parallel is not supported in non-transactional
batch." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
</Messages>
</root>
</results>
</return>


... so it looks like non-transactional parallel processing is not supported.
Great.



"Steve G" wrote:

You can still process objects in parallel using separate transactions and
connections to the server.

E.g.
- In parallel:
- Application 1: Begin session, Begin transaction, Process dimension, Commit
transaction
- Application 2: Begin session, Begin transaction, Process dimension, Commit
transaction

- Wait till dimensions are processed

- In parallel:
- Application 3: Begin session, Begin transaction, Process partition, Commit
transaction
- Application 4: Begin session, Begin transaction, Process partition, Commit
transaction
- Application 5: Begin session, Begin transaction, Process partition, Commit
transaction

Be a little careful of locking issues, but I believe the above sequence
should be fine...

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Steve G" <SteveG (AT) discussions (DOT) microsoft.com> wrote