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

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

Print | posted on Monday, October 27, 2008 3:26 PM | Filed Under [ T-SQL OLAP ]

Feedback

Gravatar

# 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

10/28/2008 7:34 PM | Sreekanth
Gravatar

# 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
10/30/2008 8:47 AM | Fabiano
Gravatar

# 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
12/9/2008 10:55 AM | Jiang
Gravatar

# 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
1/12/2009 2:30 AM | liyasker
Gravatar

# re: Processing an OLAP cube with a T-SQL Stored Procedure

What IS the alternate query for SSAS 2005 Database?
4/2/2009 10:15 AM | Jim Hoffman
Gravatar

# 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

8/12/2009 11:13 PM | Mariano
Gravatar

# 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
8/31/2009 7:08 PM | Jacob
Gravatar

# 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/
12/19/2009 6:22 AM | sam
Gravatar

# re: Processing an OLAP cube with a T-SQL Stored Procedure

That's what i am looking for,thanks
1/6/2010 3:12 AM | Vampal
Gravatar

# re: Processing an OLAP cube with a T-SQL Stored Procedure

hi,

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

thanks.
2/18/2010 12:45 PM | Andreu
Gravatar

# 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'
2/18/2010 5:43 PM | Alphapapalima
Gravatar

# 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
6/23/2010 3:04 PM | Terry
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET