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