Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Process a SQL Server Analysis Services Cube Using an XMLA Query


By:   |   Last Updated: 2011-01-20   |   Comments (4)   |   Related Tips: > Analysis Services Administration

Problem

One of the Junior DBAs in my company wanted to know how to process a cube using an XMLA Query. In this tip we will go through the steps a database administrator needs to follow to process an Analysis Services cube using an XMLA command.  

Solution

Let us go through the steps a database administrator needs to follow to process an Analysis Services cube using XMLA.

1. Connect to a SQL Server Analysis Services Instance using SQL Server Management Studio.

2. In the Object Explorer Pane, expand the Analysis Services Instance, expand Databases, expand Analysis Services database, and then right click the Analysis Services cube which you want to process and then choose New Query -> XMLA from the drop down menu to open a new XMLA Query window.

3. In the XMLA Query window copy and paste the below XML. You need to change the DatabaseID and CubeID appropriately as per your environment and then to do a Full Processing of the cube execute the XMLA Query.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 
<
Parallel>
    <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">
      <Object>
        <DatabaseID>Adventure Works DW</DatabaseID>
        <CubeID>Adventure Works DW</CubeID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</
Batch>

4. You can also generate the XMLA Query to process the cube by selecting Script Action to New Query Window option under the Process Cube GUI as shown in the below snippet.  Refer to this tip for more information on how to get this this screen.

Note

When you run the XMLA query as shown in Step 3 this will process the cube with the default options. If you need to specify other options as shown in this tip,, you can select all of the options using the SSMS and then use the "Script Action to New Query Window" to see how all of these addtional options are configured in XML.

Next Steps


Last Updated: 2011-01-20


next webcast button


next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, July 16, 2015 - 6:47:47 PM - Dallas Snider Back To Top

Does the "Processing" actually start?  

Are you receiving any error messages?  

Can you process the cube object from SQL Server Management Studio?

Can you process each of the dimensions?


Thursday, July 16, 2015 - 9:29:47 AM - Morris Mntoninzi Back To Top

I am trying to process a cube which was developed from my database I created, not AdventureWorks. The Cube cannot be processed although it was deployed. This results in cube that cannot be BROWSED. not browsed. The system does not show the screen with "Change Setting" tab. How are Adventure Works DW differs from ordinary database created on SSMS. Is my problem in type of database I used or what? I have been using SQL Server Enterprise 2012 evaluation edition. Please post your response on my e-mail


Thursday, July 16, 2015 - 8:27:35 AM - Morris Mntoninzi Back To Top

I have been trying to develop a project on SSAS on BIDS but I fail to process the project although it does Deploy. I therefore cannot Browse the Cube. Is it because I am using my own developed databases instead of those you use in your examples? Do I have to have my databases in warehouse or how to built a warehouse from my databases?


Thursday, July 09, 2015 - 10:40:36 AM - Reza Back To Top

In this practice, also we need to focus in "Change Settings/ Dimension Key Errors"


Learn more about SQL Server tools