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!!!

Follow

Get every new post delivered to your Inbox.

Join 50 other followers