Search this blog

Thursday, December 8, 2011

How To Create XMLA Script For Cube Process Using SSMS

Following steps will explain “how to generate a XMLA Script for Cube process” using SQL Server Management Studio (SSMS).
1.       Open SQL Server Management Studio by clicking on the SSMS shortcut icon which located in the Start --> All Programs --> Microsoft SQL Server 2008 R2 --> SQL Server Management Studio.
See the given exhibit for reference (select any version whichever you installed)
2.       Click on the “Connect” button, which is available at Object Explorer, Choose “Analysis Services…” as shown Below
3.       Enter the server detail/credential you want to connect
4.       After you connected to the with proper server details, you can see the list of databases available on the server in Object Explorer Windows as shown below
5.       In Object Explorer, Select the cube which you need to process --> Right Click on “Process”
6.       Upon click on process, Following model dialog window will appear  --> Click on Script link
7.       Script link helps you create a script in 3 ways as listed in the exhibit
8.       I’ve selected the option to generate the output script on “New Query Window”, so it generated the script on New XMLA Script window as mentioned below:
Sample XMLA Script:-
<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" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>Analysis Services Tutorial</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel> 
</Batch>
Note: system will generate the script as per the input you provided on Process window in step 6