Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Monday, October 27, 2008

Processing an OLAP cube with a T-SQL Stored Procedure

Here's a simple SQL Server stored procedure that you can call to process an OLAP cube using T-SQL.  The parameters should be self-explanatory.  To me, this is a little easier and more flexible than processing using DTS packages.

create procedure ProcessCube

    @Database varchar(100),

    @Cube varchar(100),

    @Partition varchar(100)  = null, -- If NULL, process the entire Cube

    @Server varchar(100) = 'localhost'

as

 

/*

    Author:        Jeff Smith

    Version:    10/27/2008

*/

 

/* variables used to store object handles */

declare @o_svr int, @o_db int, @o_cube int, @o_part int, @o_mds int

declare @hr int

 

/* different cube processing options. This SP uses "default" */

declare @PROCESS_DEFAULT int

declare @PROCESS_FULL int

declare @PROCESS_REFRESH_DATA int

 

set @PROCESS_DEFAULT = 0

set @PROCESS_FULL = 1

set @PROCESS_REFRESH_DATA = 2

 

-- create a DSO.Server object:

exec @hr = sp_OACreate 'DSO.Server', @o_svr out

if @hr <> 0

    begin

        print 'Error at create server:'

        exec sp_OAGetErrorInfo @o_svr

        goto cleanup

    end

 

-- Connect to the server:

exec @hr = sp_OAMethod @o_svr, 'Connect', null, @Server

if @hr <> 0

    begin

        print 'Error at connect to server:'

        exec sp_OAGetErrorInfo @o_svr

        goto cleanup

    end

 

-- Get the MDStores property from the Server:

exec @hr = sp_OAGetProperty @o_svr,'MDStores', @o_mds OUT

if @hr <> 0

    begin

        print 'Error at get getting Server MDStores:'

        exec sp_OAGetErrorInfo @o_svr

        goto cleanup

    end

 

-- Get the database from the MDStores:

exec @hr = sp_OAGetProperty @o_mds,'Item',@o_db OUT,@Database

if @hr <> 0

    begin

        print 'Error at get database:'

        exec sp_OAGetErrorInfo @o_mds

        goto cleanup

    end

 

-- get the MDStores property from the database:

exec sp_OADestroy @o_mds

exec @hr = sp_OAGetProperty @o_db,'MDStores', @o_mds OUT

if @hr <> 0

    begin

        print 'Error at get database MDStores:'

        exec sp_OAGetErrorInfo @o_db

        goto cleanup

    end

 

-- get the Cube from the MDStores

exec @hr = sp_OAGetProperty @o_mds,'Item',@o_cube OUT, @Cube

if @hr <> 0

    begin

        print 'Error at get Cube:'

        exec sp_OAGetErrorInfo @o_mds

        goto cleanup

    end

 

if @Partition is null -- Process the entire Cube, not just a single partition

    begin

        exec @hr = sp_OAMethod @o_cube, 'Process', null, @PROCESS_DEFAULT

        if @hr <> 0

            begin

                print 'Error at process Cube:'

                exec sp_OAGetErrorInfo @o_cube

                goto cleanup

            end

    end

 

else    -- just process the specified Partition

 

    begin

        -- Get the MDStores property of the Cube:

        exec sp_OADestroy @o_mds

        exec @hr = sp_OAGetProperty @o_cube,'MDStores', @o_mds OUT

        if @hr <> 0

            begin

                print 'Error at get Cube MDStores:'

                exec sp_OAGetErrorInfo @o_cube

                goto cleanup

            end

 

        -- Get the partition to process:

        exec @hr = sp_OAGetProperty @o_mds,'Item',@o_part OUT, @Partition

        if @hr <> 0

            begin

                print 'Error at get Parition:'

                exec sp_OAGetErrorInfo @o_mds

                goto cleanup

            end

 

        -- Process the partition:

        exec @hr = sp_OAMethod @o_part, 'Process', null, @PROCESS_DEFAULT

        if @hr <> 0

            begin

                print 'Error at process Partition:'

                exec sp_OAGetErrorInfo @o_part

                goto cleanup

            end

    end

 

-- And unlock all objects on the server:

exec @hr = sp_OAMethod @o_svr, 'UnlockAllObjects'

if @hr <> 0

    begin

        print 'Error at unlock all server objects:'

        exec sp_OAGetErrorInfo @o_svr

        goto cleanup

    end

 

cleanup:

 

if @o_mds is not null exec sp_OADestroy @o_mds

if @o_Part is not null exec sp_OADestroy @o_Part

if @o_cube is not null exec sp_OADestroy @o_cube

if @o_db is not null exec sp_OADestroy @o_db

if @o_svr is not null exec sp_OADestroy @o_svr

posted @ Monday, October 27, 2008 3:26 PM | Feedback (12) | Filed Under [ T-SQL OLAP ]

Powered by:
Powered By Subtext Powered By ASP.NET