0

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.

  • 1
    Hi, welcome to the site. Sorry but your question is confusing. What duplication are we talking about when processing from SSIS? What are the settings for the SSIS process cube task? The code you added to an agent job only processes a cube but then you san you tried the "same" code to process dimensions, then measure group and then full process the cube. The code you posted doesn't do that. What is the output you receive when you run the code in SSMS? Also, the code doesn't look like MDX but XMLA, maybe that's a typo on your end? – Tom V - try topanswers.xyz Jul 25 '18 at 12:20
  • Hello Duplicates in a load table which is used to populate the cube. I don't have access to the SSIS package, all I know is that it brings in data from files and populates a table which is used to build the cube. All we want to do is mimic what a manual 'process' on the Cube does. I realise the code I posed doesn't do the other bits. All I'm trying to say is that this code does not seem to re-process the cube in the same way as when we do the process through the GUI. (posting code in follow up comment as there are too many characters – Mickey Jul 25 '18 at 13:12
  • <Batch xmlns="schemas.microsoft.com/analysisservices/2003/engine"> <Parallel> <Process xmlns:xsd="w3.org/2001/XMLSchema" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:ddl500_500="schemas.microsoft.com/analysisservices/2013/engine/500/500"> <Object> <DatabaseID>Footfall</DatabaseID> <CubeID>Footfall</CubeID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch> – Mickey Jul 25 '18 at 13:18
  • Hi, I now have access to the SSIS. It executes a masterpackage.dtsx which then processes the cube by running similar commands to the one posted above however there are 2. 1 processes the dimensions and other the measure group. The SSIS package is executed with a proxy account which is active to the following subsystems: Operating System (CmdExec) and SSIS package. Essentially the SSIS package needs to be completely re-written. For now, we just want to automate the processing of the cube and the code posted doesn't seem to do that. Is there any other way to process the Cube automatically? Thank – Mickey Jul 25 '18 at 14:13
2

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.

enter image description here

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

|improve this answer|||||
  • I've figured it out. When I open SSMS to connect to the SSAS instance, at the top of the window it defaults to another database. When I right-click the intended database > new query > xmla the SSMS window connection is correct. The code I used yesterday ran and updated the table SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES. Data is also correct now. Good News! Only thing is to mimic that now in SSIS. I don't think the way this currently being done by SQL Agent works because the 'default' connection is to another database and you can't specify this from the SQL Agent job. – Mickey Jul 26 '18 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.