By: Neil Barkhina

SQL Server 2012 is an amazing platform for reporting and business intelligence. In order to use some of its most powerful features such as PowerView, PowerPivot and PerformancePoint you will need to use SQL Server Analysis Services (SSAS) which stores its data in a specialized database called a cube. Cubes are designed to facilitate reporting on large volumes of data very quickly. This requires that the data gets imported from a data source on a regular schedule. While this concept is widely understood, I do frequently get asked how to set up a refresh schedule. This can be done using the SQL Server Agent.

The first step is to store the login in the Credentials section under Security:

Give the credentials a title and then type in the username and password.

Then go to the Proxy Section under the Sql Server agent and create a new Proxy.

Give the Proxy a name and check off SQL Server Analysis Services Command as the subsystem.

Now we are ready to create the Job that will run the Import. Right click on jobs and create a new job.

Give the Job a name and add a Step to the job

Give the step a name, the type should be a SQL Server Analysis Services Command. Also select your proxy that you created in the previous step.

The Command is  the following XML. Replace the text in red with the name of your cube:

<Process xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>

  <Object>

    <DatabaseID>[cube name]</DatabaseID>

  </Object>

  <Type>ProcessFull</Type>

  <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

</Process>

Now create a schedule, and choose the interval of which the job should run. In my case I set it to run every hour:

After creating the job, right click on it and run the job to test it. If you see the following then everything is ready to go.