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 |