Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

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

Legacy Comments


Sreekanth
2008-10-28
re: Processing an OLAP cube with a T-SQL Stored Procedure
Hi Jeff, on totally a different note, I have a question.

Do you know if query plans depend on database size? I mean for the same database schema, can query plans change for different database sizes?

Thanks
Sreekanth


Fabiano
2008-10-30
re: Processing an OLAP cube with a T-SQL Stored Procedure
Sreekanh: yes, query plans depend on database size. The number of rows is one of the most important variables.
Jeff: I've never followed that way. I did always with DTS. I’ll study in the next case. Thanks.
Fabiano

Jiang
2008-12-09
re: Processing an OLAP cube with a T-SQL Stored Procedure
Hi, Jeff, thanks for the posting.
I am using your script, but it erred out at connecting to my Analysis server.

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

I understand that it's using Windows authentication. I logged into my SQL server using an account that can process cube in my Analysis services.

Where should I look?

Thanks,

Jiang

liyasker
2009-01-12
re: Processing an OLAP cube with a T-SQL Stored Procedure
This code dosent work againest SSAS 2005 DataBase. I am getting
This Error
"
Error at get getting Server MDStores:"


What is the alternate query for SSAS 2005 Database?



Thanks
Sam K

Jim Hoffman
2009-04-02
re: Processing an OLAP cube with a T-SQL Stored Procedure
What IS the alternate query for SSAS 2005 Database?

Mariano
2009-08-12
re: Processing an OLAP cube with a T-SQL Stored Procedure
Hello, the code it was very useful. Now, I have a doubt as to how can i processes a dimension?

Thanks

Mariano Malomo


Jacob
2009-08-31
re: Processing an OLAP cube with a T-SQL Stored Procedure
I'm having a lot of issues connecting to the OLAP server:

"sp_OAMethod @o_svr, 'Connect', null, @Server" returns

-2146232576:

Error Source Description HelpFile HelpID
0x80131700 DSO Automation error NULL 1000440

I've been searching for hours trying to find an explanation and nothing seems to be leading me in the right direction. Any ideas for how I might diagnose this issue?

Thanks for your help!!

-Jacob

sam
2009-12-19
re: Processing an OLAP cube with a T-SQL Stored Procedure
http://www.sumerkent.com/index.php/2009/12/05/how-to-process-sql-server-olap-cubes-with-t-sql-stored-procedure/

Vampal
2010-01-06
re: Processing an OLAP cube with a T-SQL Stored Procedure
That's what i am looking for,thanks

Andreu
2010-02-18
re: Processing an OLAP cube with a T-SQL Stored Procedure
hi,

i have de same problem that jacob, do you solve this?

thanks.

Alphapapalima
2010-02-18
re: Processing an OLAP cube with a T-SQL Stored Procedure
Hi having the same problem...
0x80131700 DSO Automation error NULL 1000440

Erroring here, please help.
EXEC @hr = sp_OAMethod @o_svr, 'Connect', NULL, 'devdb\devdb5'

Terry
2010-06-23
re: Processing an OLAP cube with a T-SQL Stored Procedure
Hello,

Thanks for the code, I tried this and it worked great in a test.

Was wondering if I wanted to do a incremental update, how that could be done.

Thanks
Terry