SSAS Cube Process Manually works but MDX query doesnt work

Question

I am new to SSAS.

Here's a cube which is rebuilt by a SSIS package everyday but it seems to cause duplication. We manually 'Process' the cube using the GUI, it then updates this table with the correct date and time and the data is correct after we do this:

SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES

However, we have been trying to automate the 'Process' at cube level and have added this code to a SQL Agent Job which runs as type = SQL Server Analysis Services Command and as the SQL Server Agent Service Account:

<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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
      <Object>
        <DatabaseID>DBNAME</DatabaseID>
        <CubeID>CUBENAME</CubeID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

This does NOT fix the cube data and also doesn't update the data in table:

SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES

Is there anything else I can check or do to get the MDX query to work? I've also used the same code in an SSMS window to process the dimensions, then the measure group and then the ProcessFull on the Cube. To no avail.

Any help will be appreciated.

Answer 1

If you want to make sure the code you are running is exactly the code run when you process manually you should click the script button in SSMS and use that XMLA script. If you execute a series of steps you should script each step.

This will generate the scripts as XML so you can execute in SSMS and that will 100% do the same as clicking the OK button on the dialog.

If that works you should get output like this (empty result)

<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" />
  </results>
</return>

If that doesn't work you should get a response indicating an error like this

<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="3239313412" Description="Errors in the metadata manager. Either the database with the ID of 'testA' does not exist in the server with the ID of 'MES-TVG-80-U15', or the user does not have permissions to access the object." Source="Microsoft SQL Server 2016 Analysis Services" HelpFile="" />
      </Messages>
    </root>
  </results>
</return>

Once you have the correct script you can paste that in your agent job. If that doesn't work you should be looking for either errors in your agent job, or checking to see if the agent job is connecting to the right server, processing the right database, using the correct credentials etc.

Side note: If you do have multiple steps, and as such multiple XMLA scripts you can combine them using a tag like I show here: how to delete multiple ssas database using xmla script