SQL Job – Check Cube Valid Data as Last Step

Running a SQL Agent job to do an ETL/Cube Processing, you might also want to check the status of the cube after you process it, just to make sure.

Create a job step that is a T-SQL type, and

image

DECLARE @forecast VARCHAR(10)

    SELECT  @forecast = CAST("[Measures].[Forecast-Part]" AS VARCHAR(10))
        FROM
    OPENROWSET(‘MSOLAP’, ‘Data Source=localhost;Initial Catalog=ComponentForecast;’,
        ‘SELECT { [Measures].[Forecast-Part] }  ON COLUMNS FROM [ComponentForecast]’)

IF @forecast = ‘0’ OR @forecast IS NULL
RAISERROR (‘Cube Data Not Loaded Correctly’, 17, 1)

 

Of course your MDX query in the OPENROWSET will need to be different depending on your cube. If you get more complicated, you can also just call a stored procedure and let your imagination run wild with what you can do.

* update – fixed sql code – changed from BIGINT to VARCHAR(10)

About these ads

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