Today, we are going to learn how to refresh an Azure Analysis Service Models from the Azure Synapse pipeline. There may be scenarios where you want to refresh the AAS model dependent on the Azure ETL pipeline runs.
It is crucial to initiate the refresh as soon as the data load is completed to reduce delays and monitoring efforts associated with manual refresh and data load completion checks.
Scenario: I am taking the scenario where my AAS model need to refresh the moment the Azure ETL pipeline gets completed. I want to trigger the refresh at the end of the pipeline by using AAS model refresh API.
What do we need to achieve it:
- You need to have Azure Analysis Service.
- You should have permissions to give access in AAS.
- You should have the tenant id and the application id of the workspace.
First, create an AAS service and once it is deployed, create the models that you want to refresh.
Note: Make sure you do not have the space in the model name. AAS refresh does not recognise the model if it has space in between.
Next, create the service principle and grant access to the Azure Analysis service.
The user would be in the format : app:<Synapse APP id>@<tenant id>
You can use the below PowerShell command to obtain the App ID by using the synapse managed object ID.
Get-AzADServicePrincipal -ObjectID <paste the managed identity Object Id from synapse workspace properties>
This command will return the App ID, which should be used along with the tenant ID separated by ‘@’.
Afterwards, Login to your Azure Analysis service in your SSMS (SQL Server Management Studio) and add this service principle through manual entry in the Role assignment as an admin on the AAS server.
Once you have completed the above process, you need to add the synapse workspace as a contributor in the Azure Analysis Services IAM (Identity and Access Management).
To refresh the models, we will be using the API. Here is the API to trigger the refresh:
https://<region>.asazure.windows.net/servers/<AAS ServerName>/models/<AAS ModelName>/refreshes
you need to fill in the fields and form your URL. The highlighted field can be taken from the AAS server name.
We will use the POST method to trigger the refresh. You need to use the below JSON body to pass the parameters.
To Process the full Model:
{
“refreshType”: “Full”,
“CommitMode”:”Transactional”
“maxparallelism”:”2”,
“RetryCount”:”1”
“objects”:
[
{
“database”: “DatabaseName”,
“table”: “TableName”,
“partition”: “PartitionName”
}
]
}
Since the Objects accepts a list, we can use to refresh a particular table or even a partition within the Model.
{
“Type”:”Full”,
“CommitMode”:”transactional”
“maxparallelism”:”2”,
“RetryCount”:”1”
“Objects”:
[
{
“table”:”product”,
“partition”:”year”
}
]
}
If you only want to refresh the entire table and not a specific partition, then you can remove the “partition” argument from the JSON body.
Now, you need to provide the resource. You can use the link provided below:
https://*.asazure.windows.net
The final web activity would look like:
You can click on “Debug” to process the result. If you want to view the result of the refresh, you can change the API method to GET and access the details.
I hope this blog has provided you with some insights on how to refresh the model from the Synapse pipeline.
