Geeks With Blogs

News
DevJef's Mumbo-Jumbo «There's a bit of SQL in all of us»

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

Posted on Tuesday, October 25, 2011 2:13 PM SQL Server , SSRS | Back to top


Comments on this post: Create XMLA file for OLAP Cubes

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © DevJef | Powered by: GeeksWithBlogs.net