How to automatically refresh sql server olap cube

Question

I have built a cube in visual studio and deployed to SSAS. How can I get the data refreshed using a nightly sql batch job or some other means? TIA

Answer 1

A few options:

1. Put a Process Analysis Services task in an SSIS package, and schedule the package with SQL Agent (great if you already have an etl package).
2. Using SSMS, go to the Process dialog box but hit "Script" at the start to get the XMLA command. Then put this in a SQL Agent job.

Answer 2

<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>CUBEOLAP</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>