3

With Azure Data Factory I have built a pipeline to orchestrate the processing of my Azure Analysis Services model trough a dedicated Logic App as explicated in this article, and it works properly.

Now, always using Azure Data Factory (through Logic App), I wish I could also update the list of the user in a specific roles.

In the article mentioned above, to process the Azure Analysis Services models, the Logic App calls a specific API that has the following format:

https:// <rollout>.asazure.windows.net/servers/<serverName>/models/<resource>/refreshes

but this API doesn't seem to work for update the model's roles.

Is there anyone who knows the correct method to be able to update model roles using a specific Logic App?

Thanks for any suggestions

Lorenzo Benassi
  • 621
  • 1
  • 8
  • 31
  • You can modify Roles via the details listed here: https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-database-users If you wanted to do this entirely within the Logic App, you would need to have it trigger one of those processes. – iamdave Jan 21 '20 at 11:22
  • @iamdave sorry I don't understand your comment. The link specify how to manage the roles manually. I am able to modify the database roles in Analysis Services manually, but how can this link help me? The question is how I can automate this process within the Logic App? – Lorenzo Benassi Jan 21 '20 at 12:09
  • You need to author a way to send the commands detailed in that article to your server, then call this service from your Logic App. As far as I am aware there is no code free solution to this. – iamdave Jan 21 '20 at 16:32

2 Answers2

0

If you don't necessarily need to use the logic app for this, I think it might be possible using Azure automation and the powershell cmdlets for managing azure analysis services:

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-refresh-azure-automation

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-powershell

https://learn.microsoft.com/en-us/powershell/module/sqlserver/Add-RoleMember?view=sqlserver-ps

Cedersved
  • 1,015
  • 1
  • 7
  • 21
0

One alternative approach might be to have fixed AD groups as members of the tabular model roles and add / remove members from those AD groups. Therefore the tabular model roles would not need to be refreshed, it would simply be a matter of adding or removing members from the AD groups as part of your governance process.

A second approach would be to use dynamic row-level security. Adding records to a Azure SQL DB table is perfectly possible with Logic Apps and could be used to drive security, depending on your requirements. You can then refresh your security dimension with the Logic App. See here for more details:

https://learn.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular

To answer your question however, the Azure Analysis Services REST API is useful but is not that fully featured, ie it does not contain all possible operations for tabular models or the service. One other missing example I found was backups, ie although it is possible to trigger a pause or resume of the service, it is not possible to trigger a backup of a tabular model via the REST API. I do not believe it is possible to alter role members or at least, the operation is not listed in the REST API, although happy to be corrected if I am wrong. To be more specific, Roles is not mentioned in the list of available objects which can be passed in to the Objects array using the POST / Refreshes eg here. table and partition are the only ones I'm aware of.

There are also no examples on the MS github site:

https://github.com/microsoft/Analysis-Services

Finally, consider calling TMSL via Powershell in an Azure Function, which you can call from Azure Data Factory.

HTH

wBob
  • 13,710
  • 3
  • 20
  • 37