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

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)