How to process a cube in a SQL Agent job

It can be done but it’s not well documented and it’s complicated by the fact that you can’t easily get the result of the process. Unless you actively check and manually raise an error if there’s been a problem, the job will report sucess no matter how screwed up the cube processing is. That part is all about the XML results that are output by the process.

Here’s how I do it (and I’d be very interested if you have any suggestions to improve it):

Connect to the Analysis Services server in SQL Server Management Studio.

Right click on the database and select Process.

Configure all the options and then use the Script button to grab the XML that defines the processing job. It will look something like this:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
        <KeyErrorLimit>-1</KeyErrorLimit>
        <KeyErrorLogFile>\\rep01\joblogs\ProcessASOnBI1KeyErrors.log</KeyErrorLogFile>
        <KeyNotFound>ReportAndContinue</KeyNotFound>
        <KeyErrorAction>ConvertToUnknown</KeyErrorAction>
        <KeyErrorLimitAction>StopProcessing</KeyErrorLimitAction>
        <KeyDuplicate>IgnoreError</KeyDuplicate>
        <NullKeyConvertedToUnknown>IgnoreError</NullKeyConvertedToUnknown>
        <NullKeyNotAllowed>ReportAndContinue</NullKeyNotAllowed>
    </ErrorConfiguration>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
    <Object>
            <DatabaseID>BI1</DatabaseID>
        </Object>
        <Type>ProcessFull</Type>
        <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
</Batch>

Create a new SQL Agent job.

Add a step to get rid of any old output file(s). Change the step type to CmdExec and paste in this code, modifying the file name:

if exist "\\rep01\joblogs\ProcessASOnBI1Log.xml" (del "\\rep01\joblogs\ProcessASOnBI1Log.xml")

Add a step for the processing. Change the step type to SQL Server Analysis Services Command and paste in the XML. Go to the Advanced page and give it an XML output file – with the same name you used in the previous step – to write its results to.

Add a step to check the output XML file to see whether the process was successful. Change the step type to T-SQL and paste in this code:

-- Check the result files from the cubes
 
set nocount on;
 
create table #File
(
        ResultFileName        varchar(255)
);

insert #File (ResultFileName) values ('\\rep01\joblogs\ProcessASOnBI1Log.xml');
insert #File (ResultFileName) values ('\\rep01\joblogs\ProcessASOnBI2Log.xml');

create table #FileContents 
(
        LineNumber        int identity
        , LineContents        nvarchar(4000)
);
 
declare @FileContents        nvarchar(4000)
        , @NewLine        char(2)
        , @FileName        varchar(255)
        , @CubesFailed        bit
        , @CmdLine        varchar(300)
 
set @NewLine = char(13) + char(10);
 
set @CubesFailed = 0;
 
-- Loop through result files
declare file_cursor cursor
local forward_only 
for 
select        ResultFileName
from        #File;
 
open file_cursor
 
        fetch next from file_cursor 
        into         @FileName
 
 
        while @@fetch_status = 0
        begin
 
                set @CmdLine = 'type ' + @FileName;
 
                insert #FileContents
                exec master.dbo.xp_cmdshell @CmdLine;
 
                select        @FileContents = isnull(@FileContents, '') + @NewLine + isnull(LineContents, '')
                from        #FileContents;
 
                select @FileName;
 
                select @FileContents;
 
                set nocount off;
 
                if @FileContents like '%error%'
                begin
                        set @CubesFailed = 1
                end
 
                delete #FileContents;
 
 
                -- Get the next cursor row
                fetch next from file_cursor 
                into         @FileName
 
        end
 
close file_cursor
deallocate file_cursor
 
 
drop table #FileContents;
drop table #File;
 
 
if @CubesFailed = 1
begin
        raiserror('Cubes failed to build.', 16, 1)
end

Modify it to add your own file name(s) into #File at the top. This step will raise an error if the processing failed. If you don’t do this, you won’t know whether the job has worked.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s