Remote Processing (Analysis Services)
You can run scheduled or unattended processing on a remote Analysis Services instance, where the processing request originates from one computer but executes on another computer on the same network.
Prerequisites
If you are running different versions of SQL Server on each computer, the client libraries must match the version of the Analysis Services instance that is processing the model. For example, if processing is on a SQL Server 2012 Analysis Services (SSAS) instance, then the computer from which the request originates must have the client library corresponding to SQL Server 2012 Analysis Services (SSAS). See Client Libraries used for Analysis Services connections.
On the remote server, Allow remote connections to this computer must be enabled, and the account issuing the processing request must be listed as an allowed user.
Windows firewall rules must be configured to allow inbound connections to Analysis Services. Verify you can connect to the remote Analysis Services instance using SQL Server Management Studio. See Configure the Windows Firewall to Allow Analysis Services Access.
Resolve any existing local processing errors before attempting remote processing. Verify that when the processing request is local, data can be successfully retrieved from the external relational data source. See Set Impersonation Options (SSAS - Multidimensional) for instructions on specifying credentials used to retrieve data.
On-demand remote processing
Analysis Services accepts processing requests from user or application accounts that have Analysis Services administrator permissions. If you are an administrator, verify that you can connect to the remote instance and process the database manually over the remote connection.
On the computer that will be used to schedule processing, start SQL Server Management Studio and connect to the remote Analysis Services instance.
Right-click the database, select Process, point to Script and choose Script Action to New Query Window. Commands used to invoke processing will appear in the query window.
Click OK to begin processing.
Successful completion of this task provides an XMLA query that you can embed in a scheduled job. It also confirms there are no connection problems.
Schedule remote processing using SQL Server Agent Service
By default, SQL Server Agent service runs under a virtual account, with network connections made using the machine account. To avoid having to give a machine account administrative rights on the remote Analysis Services instance, you should change the SQL Server Agent service account to run as a least-privilege domain user account.
Be sure to grant all of the necessary permissions, including giving the account sysadmin rights on the Database Engine instance providing the service.
Use the following links to set permissions:
SQL Server Agent Components suggests alternative fixed server roles if granting sysadmin permissions is not possible.
After account permissions are configured, continue with these steps.
Grant the SQL Server Agent account administrator permission on SSAS
Using Management Studio, connect to the remote Analysis Services instance.
Right-click the server name, click Properties, and then click Security.
Click Add to add the SQL Server Agent account.
Create the Job
In Management Studio, connect to the local Database Engine instance. SQL Server Agent is the last item in Object Explorer. If necessary, start the service.
Right-click Jobs, click New Job and then enter a name.
In Steps, click New and then enter a name.
In Type, choose SQL Server Analysis Services Command.
In Server, enter the name of the remote Analysis Services instance.
In Command, paste the XMLA command for processing the database. This is the XMLA script that you generated in the verification step for on-demand remote processing. Click OK to save the job.
Start SQL Server Profiler
On the remote computer, start SQL Server Profiler. Connect to the Analysis Services instance, and click Run to start the trace using the default events.
You will use SQL Server Profiler to monitor the processing events as they occur.
Optionally, you can set trace properties to send the trace to a file or table in a database.
Run the job
On the computer used to run the job, verify that the job can perform the basic operation. In Object Explorer, under SQL Server Agent, expand Jobs, right-click the job you just created, and click Start Job at Step. The job starts immediately. You can monitor progress in SQL Server Profiler.
As a final step, modify the job to run on a schedule that you define, adding any alerts or notifications necessary to administer the job. You might also want to refine the processing script, or create multiple steps in the job to process objects independently.
See Also
Tasks
Schedule SSAS Administrative Tasks with SQL Server Agent
Batch Processing (Analysis Services)
Concepts
Multidimensional Model Object Processing