Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Process Dimentions and Cubes - SSAS Job Error

Answered Process Dimentions and Cubes - SSAS Job Error

  • Monday, March 18, 2013 10:26 AM
     
     
    Sign In to Vote
    0

    Hi,

    I am facing an issue in a SSAS job, It was failing contiuneously. Since I am not a SSAS developer, so I am unable to undersand and give solution to this issue.

    It has always failed since it was created on 3rd Jan(from the implementation time it seems). This is what is recorded in the log entry:

    Executed as user : Domain\usernaame.Microsoft.AnalysisService.Xmla.XmlaException: The 'Batch' element line 20, column 73

    ('http://schemas.microsoft.com/analysisservices/2003/engine' namespace) appears more than once under Envelope/Body/Execute/Command.)

    <Batch 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" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">     

    <Object>      

    <DatabaseID>Marks and Spencer</DatabaseID>

    <DimensionID>DATE</DimensionID>

    </Object>

    <Type>ProcessUpdate</Type>

    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

    </Process> 

    </Parallel>

    </Batch>

    Thanks & Regards    Vasanth Arivali

     

All Replies

  • Monday, March 18, 2013 10:59 AM
     
     
    Sign In to Vote
    0
    Do you know how this is error is being generated?  Is there a SQL Agent job executing an XMLA script to process the SSAS database?  Is it a SQL Agent job executing an SSIS package with an Analysis Services processing job?
     
  • Monday, March 18, 2013 12:52 PM
     
     
    Sign In to Vote
    0
    Yes It seems like that only, But i didn't get the SQL Agent Job right now. If it is executed by SQL Agent, how we fix that issue?
     
  • Monday, March 18, 2013 1:31 PM
     
     Answered Has Code
    Sign In to Vote
    0

    I'd start by figuring out what is triggering the error.  If it is a SQL Agent job that is executing an XMLA script, then remove SQL Agent from the scenario and take the script and execute it directly against the SSAS instance (via SSMS).

    I'd also check the log to see if there are any other errors.  The XMLA posted looks syntactically correct and executes fine on my dev box against the adventureworks cube:

    <Batch 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" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
			<Object>
				<DatabaseID>AdvWrksMultidimensional2012</DatabaseID>
				<DimensionID>Dim Time</DimensionID>
			</Object>
			<Type>ProcessUpdate</Type>
			<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
		</Process>
	</Parallel>
</Batch>

     
  • Monday, March 18, 2013 1:44 PM
     
      Has Code
    Sign In to Vote
    0

    Thanks Bill, I will try it and update the result, Kindly let me know is the below entire script is correct?(because I don't have SQL access in project and I don't know Analysis Service Script)

    <Batch 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" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>MYDB</DatabaseID>
        <DimensionID>BICOMPANYVIEW</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

<Batch 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" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>MYDB</DatabaseID>
        <DimensionID>DATE</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

<Batch 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" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>MYDB</DatabaseID>
        <DimensionID>SALESLINECUBE</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

<Batch 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" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>MYDB</DatabaseID>
        <DimensionID>INVENTTABLEEXPANDED</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

<Batch 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" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>MYDB</DatabaseID>
        <DimensionID>PROJTABLECUBE</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

<Batch 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" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>MYDB</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>


     
  • Friday, March 22, 2013 8:49 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Vasanth,

    Yeah! The XMLA code you posted are processing dimensions in a SQL Server Agent job. For more information, please see:
    Schedule SSAS Administrative Tasks with SQL Server Agent: http://technet.microsoft.com/en-us/library/ff929186.aspx

    Regards,

    Elvis Long
    TechNet Community Support

     
  • Friday, March 22, 2013 11:52 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi Vasanth,

    The script looks fine, but it does seem odd that first 5 blocks are processing individual dimensions, and then the 6th block comes along and processes (w/ ProcessFull) the entire SSAS database.  If the 6th block is going to fully process the SSAS database, then there is no need to process the dimensions individually...so those first 5 blocks can probably be removed.

    As far as the error, I'd first check that the objects reference by each code block exists.  For example, in the first code block:

    <Batch 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" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>MYDB</DatabaseID>
        <DimensionID>BICOMPANYVIEW</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

    Is there a BICOMPANYVIEW dimension?

    BI Developer and lover of data (Blog | Twitter)