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>
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.
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"
}
]
{
"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.
Thanks for the tutorial. Can we run Dax Queries and retrieve data?
ReplyDelete