locked
How do I process a cube via T-SQL? RRS feed

  • General discussion

  • [This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]


    Answer:
    There are some methods like starting a SQL job to execute Analysis Services command or using xp_cmdshell to execute ascmd to process a cube. However these methods require additional RPC calls and do not look graceful.  A better way is to create a CLR stored procedure to process the cube.
     
    1. Alter your database to set it to Trustworthy
        ALTER DATABASE Test SET TRUSTWORTHY ON
     
    2. Run "CREATE ASSEMBLY" to load the ADOMD.NET assembly
    CREATE ASSEMBLY [AdomdClient]
    AUTHORIZATION [dbo]
    FROM 'C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll'
    WITH PERMISSION_SET = UNSAFE;
     
    3. Create a SQL Server Database project, add the reference AdomdClient under the SQL Server tab.
     
    4. Create a CLR stored procedure as following:
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    using Microsoft.AnalysisServices.AdomdClient;

    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void FullProcessCube(SqlString strServer, SqlString strDbName, SqlString strCubeName)
        {
            // Put your code here
            //Open a connection to the local server
            AdomdConnection conn = new AdomdConnection(String.Format("Data Source={0}",strServer));
            conn.Open();

            //Create a command, and assign it an XMLA command to process the cube.
            AdomdCommand cmd = conn.CreateCommand();
            cmd.CommandText = String.Format("<Process xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">\r\n" +
    @"<Object>
        <DatabaseID>{0}</DatabaseID>
        <CubeID>{1}</CubeID>
      </Object>
      <Type>ProcessFull</Type>
    </Process>",strDbName,strCubeName);

            //Execute the command
            int result = cmd.ExecuteNonQuery();

            //Close the connection
            conn.Close();

        }
    };
    5. Set the Project property "Permission Level" to Unsafe.
     
    6. Build the project and deploy it to your SQL Server database.
     
    7. Enable clr on your SQL Server by using sp_configuration
        sp_configure 'clr enabled',1
        go
        reconfigure with override
        go
     
    8. Execute the stored procedure:
        exec dbo.FullProcessCube 'localhost','ASDemo','DemoAdventure'
     
     
    For more information about ADOMD.NET, XMLA, CLR programming, you can refer to the following articles:
    Overview of CLR Integration
    http://msdn.microsoft.com/en-us/library/ms131045.aspx
    CREATE ASSEMBLY (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms189524.aspx
    ADOMD.NET Client Programming
    http://msdn.microsoft.com/en-us/library/ms123477.aspx
    XML for analysis (XMLA)
    http://msdn.microsoft.com/en-us/library/ms187178(SQL.90).aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Friday, June 25, 2010 3:44 AM

All replies

  • I think it is worth while to note that the Step 2 of the described process fails unless the Cumulative Update package 6 for SQL Server 2008 R2 is installed: http://support.microsoft.com/kb/2489376.

    If it is not installed, the following error is possible:

    Msg 10301, Level 16, State 1, Line 1
    Assembly 'Microsoft.AnalysisServices.AdomdClient' references assembly 'microsoft.sharepoint, version=14.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

    The problem is described here: http://support.microsoft.com/kb/2491439

    Thursday, February 24, 2011 6:26 PM