SQL Server Agent: Automatic Refresh of SSAS Cube

Unless you're dealing with completely static data, you're going to want to set up an automatic refresh of your SSAS cube on some sort of schedule. Here's how you can get a full refresh going on a regular basis:

1. Make sure that permissions to your SSAS server are set up properly to allow the agent to trigger the refresh. Your service account should be added as a server administrator.
2. Create a new SQL Server Agent job.
3. Add one or more schedules on the Schedules page.
4. Add a step of type 'SQL Server Analysis Services Command'.
5. Choose your service account under 'Run as'.
6. Enter the URL for your SSAS server under 'Server'.
7. Enter the following as the command, making sure to edit the "database" value to match the name of your SSAS cube:

{

  "refresh": {

    "type": "full",

    "objects": [

      {

        "database": "NameOfYourSSASCube"

      }

    ]

  }

}

Categories: SQL Server, SQL Server Agent, SQL Server Analysis Services, SSAS