Automate SSAS Tabular Mannequin Processing

You might be engaged on a BI undertaking that entails SSAS Tabular mannequin. As you can’t course of the mannequin manually you might want to develop an answer to course of the mannequin robotically. There are two eventualities right here:

1. You have got an up and operating SQL Server Integration Companies (SSIS) occasion: On this case you may construct a SSIS bundle to course of the Tabular Mannequin.
2. You do not need an SSIS occasion prepared on the server: So you might want to create a SQL Server Agent Job.

On this publish I’m expressing straightforward methods to unravel the issue primarily based on the above eventualities.

Comply with the steps under:

1. Create a brand new SSIS undertaking utilizing (SQL Server Knowledge Instruments) SSDT
2. Proper click on on the “Connection Managers” space and choose “New Evaluation Companies Connection”

1. Click on “Edit”
2. Enter the SSAS Tabular server
3. Depart “Location ” BLANK! You may face the next error: “A connection can’t be made. Be sure that the server is operating.”
4. Click on “Use Home windows NT Built-in Safety”
5. Choose your SSAS Tabular catalog from the record. In my case it’s Journey Works
6. Click on OK
7. Put a “Evaluation Companies Processing Activity” on Management Movement and double click on on the duty
8. Go to Processing Settings
9. Choose the Evaluation Companies Connection
10. Click on “Add”
11. Tick the database and click on OK
12. It’s best to see one thing just like the picture under within the “Evaluation Companies Processing Activity Editor” window. Word that the article kind is “Database”.
13. Click on OK

Now you may run the bundle by urgent F5 and your SSAS Tabular mannequin might be processed.

Now you might want to deploy the SSIS undertaking to SSIS Catalog after which create a SQL Server Agent Job to run the SSIS bundle in a single day.

On this case you might want to use SSMS to generate the XMLA scripts for processing the Tabular mannequin. To take action comply with the under directions:

1. Open SSMS and hook up with a Tabular Evaluation Companies server and develop “Databases”
2. Proper click on on a desired database and choose “Course of Database”
3. In “Course of Database” window choose the processing mode from the dropdown record

1. Click on on the small rectangle of the “Script” button and choose “Script Motion to New Question Window”
2. In the event you do NOT must course of the database now click on on “Cancel” button
3. The wanted XMLA scripts is generated in SSMS
4. Copy the generated XMLA code
5. In SSMS hook up with the SQL Server occasion that you simply need to outline a brand new SQL Server Agent job for processing the Tabular database
6. Create a brand new job by proper clicking on “SQL Server Agent” and deciding on “New” after which “Job”
7. Kind a descriptive title for the brand new job and go to steps and click on New to create a brand new step
8. Kind a reputation for the step
9. In Kind part choose “SQL Server Evaluation Companies Command” from the dropdown record
10. Kind within the Tabular mannequin server handle within the “Server” part
11. Now paste the XMLA script that you simply’ve copied earlier than after which OK

All executed!

Now you may run the job and your Tabular mannequin might be processed.

NOTE: Don’t forget to outline a schedule for operating the job ceaselessly. As defining a brand new job is out of scope I didn’t clarify it intimately.