Using XMLA Script-Deploy and Schedule SSAS Project

SSAS supports several options to meet different deployment needs. In below table describes outlines these options and a scenario in which using the option is recommended.      

Deployment Options:

Option	Recommended Use
BIDS	Deploying the latest changes to your local server for testing
Deployment Wizard	Deploy to a test or production environment when you need more granular control
XMLA script	Scheduling a deployment task
Synchronize Database Wizard	Synchronizing two cubes, such as a staging cube and a production cube
Backup and restore	Moving a cube from one server to another
AMO	Handling deployment programmatically

When running the Analysis Deployment Wizard,at the final step confirm deployment page you can view select create deployment script checkbox option,If you select  or enable the option the wizard generates an XMLA file named project name scritp.xmla and saves it in the specified location.

Understanding the Deployment Script:

The deployment script consists of two sections.

The first section starts with the Alter XMLA command followed by definitions of all objects in the database.

The second section starts with a Process XMLA command. It instructs the server to process the database by using the processing mode you specified in the Select Processing Options like

Option	Setting	Description
Processing Option	Default	Applies the minimum processing tasks to bring the cube to a ready state
	Full	Full processes the database
	Do not process	Deploys the changes only

For example:

Section-1:

<Alter Allow Create="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object>

    <DatabaseID>SSAS ProjectDatabaseID>

  Object>

  <ObjectDefinition>

    <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">

      <ID>SSAS ProjectID>

      <Name>SSAS ProjectName>

      <Language>1033Language>

      <Collation>Danish_Norwegian_CI_ASCollation>

      <DataSourceImpersonationInfo>

        <ImpersonationMode>DefaultImpersonationMode>

      DataSourceImpersonationInfo>

    Database>

  ObjectDefinition>

Alter>

After the database is fully created on the SSAS Server but not yet processes. In order to process the database we also need a process XMLA script. Like above we simply create this by grabbing it from SSMS. The script looks like this:

Section-2:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

   <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">

      <Type>ProcessFullType>    

      <Object>

        <DatabaseID>SSAS 7 ProjectDatabaseID>

      Object>     

    Process>

Batch>

  

Because the script is described in XMLA, you can modify it manually or you can run the Deployment Wizard again and overwrite the existing script file.

Running the Deployment Script:

When the deployment script is ready, you have several options for executing it. You can execute it manually in SQL Server Management Studio (SSMS), you can schedule the script execution with the SQL Server Agent services.

To execute manually, simply open the script file in SSMS, connect to the target server when prompted, and click the Exclamation toolbar button !Execute to run the script or press CTRL+E.

You can schedule the script execution with the SQL Server Agent Service, which is a component of SQL Server. SQL Server Agent supports an SQL Server Analysis Services Command task, which can use to execute any XMLA script.

Let see, step by step process for scheduling the XMLA script in SSMS. 

1.  Go to SSMS -> click SQL Server Agent -> Jobs Right click and select new job -> Write name

2. Click on Steps option in the left side -> Click New button -> Fill the details like below

Copy and paste the XMLA code which Analysis Deployment wizard generated.

In additional, you can mention some advanced options. 

3.  Click on Schedules options -> Click on new button and fill the details as per the requirement. Below are the sample schedule job timings.

4. Still you can mention Alerts,Notofications,Targets options in order to extend your functionality for the     job.

5.SQL Server Agent will automatically run the Schedule SSAS Cube job and you can check the history of the job by checking the view history options on the properties of the job.