Sunday, November 20, 2022

How to Refresh Azure Analysis Service Model using Azure Data Factory

How to Schedule and Refresh an Azure Analysis Service Model using Azure Data Factory
1) Add Azure Data Factory as Managed Identity to the Azure Analysis Service:
First go the Azure Portal > Azure Analysis Service > Access Control(IAM).
Next click on add new Role Assignment and then choose Role > Contributor
Next go to Members > Assign access to > and choose Managed Identity and then Select Members.
Next from Select Managed Identities > Select the Azure Data Factory as a Managed Identity.

Note:
Please make sure you have created a ADF Service first to use above.


2) Generate the ADF Service Principal as per below:
app:<Managed identity Application ID>@<Managed identity Tenant>

Notes :
The details of <Managed identity Application ID> ; <Managed Identity Tenant> can accessed from the Azure Portal > Azure Data Factory service > Properties


3) Add the ADF Service Preincipal as an User to Azure Azure Analysis Service Model from SQL Server Management Studio(SSMS):
Go the SSMS > AAS Server>Properties>Security.
Next add the "ADF Service Principal" as an User.

4) Generate the AAS URL to use in ADF Pipeline for Refresh:
The Azure AAS url link be like below, which will be using in the Next step in ADF Pipeline > Web activity.
https://<region>.asazure.windows.net/servers/<AAS ServerName>/models/<AAS ModelName>/refreshes

Eg: Azure AS Server Name:
asazure:/<region>.asazure.windows.net/<AAS ServerName>
asazure:/centralus.asazure.windows.net/AAStestService


Eg: Azure AS URL/Link to Refresh:
https://centralus.asazure.windows.net/servers/AAStestService/models/AAStestModel/
refreshes

5) Create a Pipeline in ADF to Refresh AAS Model:
First go to ADF > Create a new Pipeline.
Next choose the Web activity from the Pipeline > Activities > Web.
Next go to the Web > Settings > URL and pass the URL like below of your AAS Model to refresh.

https://<region>.asazure.windows.net/servers/<AAS ServerName>/models/<AAS ModelName>/refreshes

Next choose the Method as "POST" in the same Web>Settings window.
Next in the body use the below Json Script to Process the AAS Model:

To full process the entire Model:

{
"Type":
"Full",
"CommitMode": "transactional",
"MaxParallelism": 2,
"RetryCount": 2,
"Objects": []
}

To full process specific Table/Partition of the Model:

{
"Type":
"Full",
"CommitMode": "transactional",
"MaxParallelism": 2,
"RetryCount": 2,

"Objects": [
{
"table": "DimProduct",
"partition": "CurrentYear"
},

{
"table": "DimDepartment"
}
]

}

Next choose the Integration Runtime as "AutoResolveIntegrationRuntime".
Next choose the Authentication as "Managed Identity".

Next in the Resource, you can pass the below link:
https://*.asazure.windows.net

Finally Save and Excecute(Debug) to run the Web task and process the Cube.

Notes:
We can use the "GET" method and run the Pipeline to see the Model refresh status.


6) Finally, Add a Trigger and Schedule the ADF Pipeline for Automatically Refresh AAS Model:
Go to ADF > Pipeline > Triggers , then add a Trigger to run the Pipeline at a specified Schedule(Every Minute/Hour/Week/Month).

Finally Save and Publish the ADF Service.

1 comment:

  1. Thanks for the tutorial. Can we run Dax Queries and retrieve data?

    ReplyDelete

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.