Below I describe 2 methods of scheduling your cubes processing.
Be sure to test your security from the SQL Agent Service Account to the SSAS Cube, as it will require processing permissions.
Connect to your SSAS Instance where the cube resides that needs to be automated, Right Click on the Cube and select “Process”
Ensure the selected cube on the properties is correct:
If you have any changes to make on the Processing order, Transaction Mode, Dimension Error Handling, click on “Change Settings” in the bottom right corner, confirm your settings, and click Ok to view the Properties window again.
At the top of the Window, note the “Script” and “Help” items.
Click on “Script”
Select “Script Action to New Query Window” or use the Ctrl+Shift+N shortcut keys.
The XMLA code will be scripted to SQL Server Management Studio query Window as in image above:
Copy the code, create a new SQL Server Agent Job, Add a task with applicable name, and use the Type
“SQL Server Analysis Services Command”:
Paste the Copied Code into the SSAS Command Window:
Save the step properties, add a schedule as desired.
Open a new SQL Server Business Intelligence Development Studio Project, with SSIS Project as the type, provide a name to the project, and define your project location.
Add the Analysis Services Processing Task from the Control Flow Items options on the left:
Right click on the added task, and select “Edit” to specify processing properties, and connection manager properties.
Click on “New” to create a new connection:
Click on Edit to specify the initial catalog, or it will detect the first cube created by ID, for the example I will use AW2008 Cube.
Click OK to continue back to the SSAS Processing Window, and click “Add” to add objects to the processing task.
Drop down the Cube to view Measure and the Dimensions, make the selection based on what you want to include in the scheduled processing; for the demo, I will only select my Measures:
Once your selection has been made, click OK to continue back to the main SSAS Task Window.
The Object List will be populated in to the Object List:
In the Processing Column, drop down each Object to configure the Processing Type if required (Above).
Click on “Change Settings”:
Configure all processing options, key handlers:
Click OK to confirm and continue.
Once all steps have been confirmed, Click on File, Save a Copy of Package.dtsx As:
Select your Server, Authentication Type, Protection Level as desired. For the demo I use my SSIS Package Store, Local Server, and File System/Package as the options.
Once you package has been save in the SSIS Package Store, Create a new SQL Agent Job, new Task as
SQL Server Integration Services Package Type, Select your newly save package from Package at the bottom of the window:
Add a schedule to the new Job as required.
The newly created SQL Agent Job will be listed under the SQL Agent Jobs folder as with any other SSIS/maintenance job.