Create XMLA file for OLAP Cubes

In order to refresh your OLAP Cubes (SQL Server Analysis Services), you need to create XMLA files. These XMLA files can be used in a SQL Server Agent Job, so that the Cubes are refreshed and re-processed. To accomplish this, you need to follow the next steps:

1) Deploy your Cube to your SQL Server
2) Right click on the database, and choose “Script database as” –> “Alter To”
3) Add the following node to the top of the XML:

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

4) Change the “Alter” node into the following node:

<Alter AllowCreate="true" ObjectExpansion="ExpandFull">

5) Add the following nodes to the bottom of the XML:

<Parallel>
  <Process>
   <Type>ProcessFull</Type>
   <Object>
    <DatabaseID>SandBox</DatabaseID>
   </Object>
  </Process>
 </Parallel>
</Batch>

6) Change the “DatabaseID” in the nodes of Step 5 to the Cube name you deployed
7) In your SQL Job, choose the “Type” of task “SQL Server Integration Services Package”
 
So from now on, your SQL Job will re-create the SSAS Cube, and process it. But be aware, every time you change your Cube, change your XMLA file as well!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: