Azure Analysis Services tabular model alerts

Question

Hello,

We refresh our tabular models on Azure from SSMS through PowerShell scripts. But apparently the jobs don’t fail if the script contains some errors. So sometimes we have cases when all is running OK but our models on AzureAS are not being refreshed.

Model refreshing part of PowerShell script:

Write-Output "Refreshing model $model"
$output = Invoke-ASCmd -Credential $credential -Database Database01 -Server asazure://northeurope.asazure.windows.net/server01 -Query "{'refresh': {'type': 'full','objects': [  {'database': 'Database01'}]}}"
Write-Output "$output"
if ($output -like '*Error returned*') {
  throw $output
}

Returned message:
Executed as user: DOMAIN\sql-service. Refreshing model       The job script encountered the following errors. These errors did not stop the script:  A job step received an error at line 7 in a PowerShell script. The corresponding line is '$output = Invoke-ASCmd -Credential $credential -Database Database01 -Server asazure://northeurope.asazure.windows.net/server01 -Query "{'refresh': {'type': 'full','objects': [  {'database': 'Database01'}]}}"   '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The server sent an unrecognizable response.  'Element' is an invalid XmlNodeType. Line 32, position 21.  '.  Process Exit Code 0.  The step succeeded.

According to Job status in Log file viewer the step with this PowerShell script in SQL succeeded, but in reality the models in Azure Analysis Services were not refreshed. So the error handling from PowerShell script is not done well.

What we need is to find a way for us to know if a tabular model has in fact been refreshed or not. And if not we need to be notified. Maybe Azure has some handling for this?

We would like to research the possibilities on alerts for this. We would like to get alerts if tabular models on AzureAS are not being refreshed.
Could someone help us out in researching the possibility of this?

This would greatly improve the quality of experience for our reports users as we will be able to pick up the issue before the users raise an alert.

Thank you in advance!

Answer 1

Hello MaratN-3573 ,

On reading the post again , I understand that the you are triggering the PS scripts which you posted(above) from SQL job . Is that correct ?

if thats the case the job will never fail as the return from the PS scripts is not returned to SQL job and no matter what the job does , it will always succeed . I have seen this before also .

This is what I suggest .

1. Create a audit table on a DB .
2. On failure ( in the powershell script ) insert a record in the table( from the PS script only ) .
3. The SQL job which triggers the PS scripts , add one more job step , which queries the table created in step#1 .
4. Query on the audit table and fail tthe step and it will fail the job also .

Lets create a audit table on SQL server on a DB named TESTDB

CREATE TABLE AuditTable  
(  
AuditId int identity (1,1)  
,AuditInsertDate datetime default getdate()  
,Status int   
,message varchar(2000)  
)  

Update the PS script to include a function named WriteToAuditTable . This function just inserts record in the SQL.
Status code of 200 is for success and 400 for failure .

 if ($output -like 'Error returned')  
    {  
      WriteToAuditTable 400 'Failure'  
      throw $output  
    }  
    else  
    {  
    WriteToAuditTable 200 'Success'  
    }  



function WriteToAuditTable {  
    param (  
        $status , $messaage   
    )  
  
$params = @{'server'='MININT-8930HD5';'Database'='TestDB'}  
  
$InsertResults = @"  
INSERT INTO AuditTable (Status,message) VALUES ('$status','$messaage')  
"@  
Invoke-sqlcmd @params -Query $InsertResults  
}  

Now everytime you call this PS script there should be an entry in the sql table .

Lets create a SQL job to monitoring the entry to the table .

I will use the below SQL query to check if we have any new failure in the last 60 min ( you have change this as you want )

if  (select count(*) from AuditTable where status = 400 and AuditInsertDate > DATEADD(MINUTE, -60, AuditInsertDate)) != 0    
BEGIN   
RAISERROR ('Fail!', 16, 1)  
END   

We create a SQL job and run the above query as one of the step . If there is any entry it will fail the job .
You can create some monitoring on the job and it should help you achieve what you want to .
May be the below animation helps you in creating the job .

Do let me know how it goes .

Thanks Himanshu

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

Please see this link
https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-refresh-azure-automation