« | Main | »
July 21, 2010
Processing an OLAP cube with a T-SQL Stored ProcedureWhen processing cubes, you have a couple of different choices about how to process your cube. One method is use an SSIS package with Cube\Dimension Processing tasks.
Another method to process your cube is to use a stored procedure.
Below is a simple SQL Server stored procedure that you can call to process an OLAP cube using T-SQL.
Create Procedure ProcessCube
@Database varchar(100),
@Cube varchar(100),
@Partition varchar(100) = null, -- If NULL, process the entire Cube
@Server varchar(100) = 'localhost'
as
/* 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 by Steve Mann at July 21, 2010 7:38 AM
Twitter 
LinkedIn 
YouTube