How to automate SSAS Tabular Model processing

You are working on a BI project that involves SSAS Tabular model. As you cannot process the model manually you need to develop a solution to process the model automatically. There are two scenarios here:

1. You have an up and running SQL Server Integration Services (SSIS) instance: In this case you can build a SSIS package to process the Tabular Model.
2. You do not have an SSIS instance ready on the server: So you need to create a SQL Server Agent Job.

In this post I’m expressing easy ways to solve the problem based on the above scenarios.

 

Using SQL Server Integration Services to Process SSAS Tabular Model

Follow the steps below:

1. Create a new SSIS project using (SQL Server Data Tools) SSDT
2. Right click on the “Connection Managers” area and select “New Analysis Services Connection
3. 
4. ”Click “Edit”
5. Enter the SSAS Tabular server
6. Leave “Location ” BLANK! You might face the following error: “A connection cannot be made. Ensure that the server is running.”
7. 
8. Click “Use Windows NT Integrated Security”
9. Select your SSAS Tabular catalog from the list. In my case it is Adventure Works
10. Click OK
11. 
12. Put a “Analysis Services Processing Task” on Control Flow and double click on the task
13. Go to Processing Settings
14. Select the Analysis Services Connection
15. Click “Add”
16. Tick the database and click OK
17. 
18. You should see something like the image below in the “Analysis Services Processing Task Editor” window. Note that the object type is “Database”.
19. 
20. Click OK

Now you can run the package by pressing F5 and your SSAS Tabular model will be processed.

 

Now you need to deploy the SSIS project to SSIS Catalog and then create a SQL Server Agent Job to run the SSIS package overnight.

Setup a SQL Server job agent to process the Tabular Model

In this case you need to use SSMS to generate the XMLA scripts for processing the Tabular model. To do so follow the below instructions:

1. Open SSMS and connect to a Tabular Analysis Services server and expand “Databases”
2. Right click on a desired database and select “Process Database”
3. 
4. In “Process Database” window select the processing mode from the dropdown list
5. 
6. Click on the small rectangle of the “Script” button and select “Script Action to New Query Window”
7. 
8. If you do NOT need to process the database now click on “Cancel” button
9. The needed XMLA scripts is generated in SSMS
10. 
11. Copy the generated XMLA code
12. In SSMS connect to the SQL Server instance that you want to define a new SQL Server Agent job for processing the Tabular database
13. Create a new job by right clicking on “SQL Server Agent” and selecting “New” and then “Job”
14. 
15. Type a descriptive name for the new job and go to steps and click New to create a new step
16. Type a name for the step
17. In Type section select “SQL Server Analysis Services Command” from the dropdown list
18. Type in the Tabular model server address in the “Server” section
19. Now paste the XMLA script that you’ve copied before and then OK

All done!

Now you can run the job and your Tabular model will be processed.

NOTE: Do not forget to define a schedule for running the job frequently. As defining a new job is out of scope I didn’t explain it in detail

 

Reference : http://biinsight.com/how-to-automate-ssas-tabular-model-processing/