Azure Analysis Services - Refresh Tabular Model Partition in "Add Mode" from Azure Data Factory

Question

Azure Analysis Services - Refresh Tabular Model Partition in "Add Mode" from Azure Data Factory

Peter Rush 61

Hi, we are using Azure Data Factory to automate ETL and management of an Azure Analysis Services Tabular Model.
We want to do an incremental refresh of the Sales Fact table on a regular basis, and given its size we only want to process additional data, not a full refresh.

We are on the Basic AAS tier, so we cannot use Multiple Partitions per table.

I have found how to use ADF to call the AAS REST API to refresh the model, but it seems that the "Add" Refresh type is not supported (Not sure why).

Is there some other way I can execute "Add" mode processing of the partition from ADF?

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-10-03T19:46:51.82+00:00
Hello @Peter Rush ,

Thanks for the question and using MS Q&A platform.

When you say I have found how to use ADF to call the AAS REST API to refresh the model, but it seems that the "Add" Refresh type is not supported (Not sure why). - Do you mean you were not able to see an option to pass the refresh type to the API? If that is the case please follow below article by a community volunteer to pass the refreshType to the body of your API request by creating a pipeline parameter in your ADF. It has been described in the article.

Reference article: Azure Data Factory Refreshing Azure Analysis Services Model

Pass the parameter value in the body of your web activity request:

Hope this info helps. Please let me know if my understanding is different than what you are looking for.

Thank you
Peter Rush 61 Reputation points

2022-10-04T07:51:20.507+00:00

Hi, thanks for the reply.

I can pass in the refresh type into the REST API using the ADF web activity:

However, if I set the request type to "Add" from "Full", this generates an error: Request parameter RefreshType is not in correct format. On checking the documentation (this page: https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-async-refresh?source=recommendations), it specifcally says the "add" refresh type is not available:

I'm not sure why the Add mode is not supported by the REST API, but it leaves me looking for a workaround so I can process my partition from ADF via another approach.
Many thanks for your help.
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-10-17T20:06:08.93+00:00

Hello @Peter Rush ,

Thanks for getting back and sharing additional information. As there is no support for Add type using REST API, it is not possible to achieve your desired requirement using ADF or other service where you use REST API.

Lastly, I would recommend trying out refreshing using Azure Runbook and see if it supports Add type (I doubt if it supports but worth giving a try). In case if that supports then, you may utilize that Runbook in ADF as explained in the documentation: AAS Refresh with Azure Automation and ADF

Please let us know how it goes.
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-10-19T06:27:52.657+00:00

Hello @Peter Rush ,

Just checking in to see if you have got a chance to try the workaround suggested using Azure Runbooks. If so, please let us know how it goes.

Thank you
Peter Rush 61 Reputation points

2022-10-19T07:38:14.943+00:00

Hi thanks for the suggestion. I havent had a chance to try as yet, but I will give it a go and feed back.
Thanks
Peter
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-10-20T06:56:41.057+00:00

Hello @Peter Rush ,

Thanks for your response. Yes, please try it out and keep this thread posted how it goes. If that doesn't work, then I recommend to up-vote and comment on this existing feature request which would help increase the priority of it - REST API : Allow Process Type "Add"

Your answer

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-10-03T19:46:51.82+00:00

Hello @Peter Rush ,

Thanks for the question and using MS Q&A platform.

When you say I have found how to use ADF to call the AAS REST API to refresh the model, but it seems that the "Add" Refresh type is not supported (Not sure why). - Do you mean you were not able to see an option to pass the refresh type to the API? If that is the case please follow below article by a community volunteer to pass the refreshType to the body of your API request by creating a pipeline parameter in your ADF. It has been described in the article.

Reference article: Azure Data Factory Refreshing Azure Analysis Services Model

Pass the parameter value in the body of your web activity request:

Hope this info helps. Please let me know if my understanding is different than what you are looking for.

Thank you
Peter Rush 61 Reputation points

2022-10-04T07:51:20.507+00:00

Hi, thanks for the reply.

I can pass in the refresh type into the REST API using the ADF web activity:

However, if I set the request type to "Add" from "Full", this generates an error: Request parameter RefreshType is not in correct format. On checking the documentation (this page: https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-async-refresh?source=recommendations), it specifcally says the "add" refresh type is not available:

I'm not sure why the Add mode is not supported by the REST API, but it leaves me looking for a workaround so I can process my partition from ADF via another approach.
Many thanks for your help.
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-10-17T20:06:08.93+00:00

Hello @Peter Rush ,

Thanks for getting back and sharing additional information. As there is no support for Add type using REST API, it is not possible to achieve your desired requirement using ADF or other service where you use REST API.

Lastly, I would recommend trying out refreshing using Azure Runbook and see if it supports Add type (I doubt if it supports but worth giving a try). In case if that supports then, you may utilize that Runbook in ADF as explained in the documentation: AAS Refresh with Azure Automation and ADF

Please let us know how it goes.
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-10-19T06:27:52.657+00:00

Hello @Peter Rush ,

Just checking in to see if you have got a chance to try the workaround suggested using Azure Runbooks. If so, please let us know how it goes.

Thank you
Peter Rush 61 Reputation points

2022-10-19T07:38:14.943+00:00

Hi thanks for the suggestion. I havent had a chance to try as yet, but I will give it a go and feed back.
Thanks
Peter
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-10-20T06:56:41.057+00:00

Hello @Peter Rush ,

Thanks for your response. Yes, please try it out and keep this thread posted how it goes. If that doesn't work, then I recommend to up-vote and comment on this existing feature request which would help increase the priority of it - REST API : Allow Process Type "Add"

Share via

Azure Analysis Services - Refresh Tabular Model Partition in "Add Mode" from Azure Data Factory

Your answer