Asynchronous Refresh of AAS Model using PowerShell

With the introduction of REST API to refresh the analysis services model, we can now perform asynchronous data refresh operations. This also includes synchronization of read-only replicas for query scale out.

Some of the pros of using this approach is:

  1. Since the requests are asynchronous, there is no need for long running HTTP connections
  2. In response we get a operation id which can be used to query the status of the job
  3. In-built functionality for auto-retries
  4. Multiple table/partition names can be passed so that only that part is refreshed
  5. Batch Processing is supported using the “CommitMode” parameter

More details of the API can be found here, which includes the permissions required to make the calls along with detailed info of all the parameters:

https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-async-refresh

As mentioned in above documentation, you need a valid access token to make this call. The PowerShell to get that token via certificate auth or via client id/secret can be found here. The code works perfectly, just make sure to use the correct scope which in this case is: https://*.asazure.windows.net/.default. Also the user or application making the call should be server admin on the AAS.  The code is quite simple afterwards (used azure functions for the below):

The $results includes an operationId which can be used to make a GET call to fetch status of the operation. The payload for the above API looks similar to:

{
“AnalysisServerName”: “analysisservername”,
“AnalysisServerLocation” : “northeurope”,
“ModelName”: “adventureworks”,
“AASObject”: {
“Type”: “Full”,
“CommitMode”: “transactional”,
“MaxParallelism”: 2,
“RetryCount”: 2,
“Objects”: [
{
“table”: “DimCustomer”,
“partition”: “DimCustomer”
},
{
“table”: “DimDate”
}]
}}

If no “Objects” are specified above, the whole model is refreshed.

Also note, that only one request is accepted per model. If there is already an operation running and another is submitted, server will return 409 Conflict HTTP Status Code. Therefore, the return result is very important as we can use the id to query the status of the already running operation.

If you want to sync the new data with replicas for query scale-out, you need to make a POST request to the /sync endpoint separately. The above code for all the operations remain the same except the different in $uri and request body.

Hope this helps!

Happy Coding!

Manage Licenses in O365 using Powershell

Few days back, I got stuck in a weird issue while creating users in O365. For some of the users, the services like SharePoint, Exchange etc. were displaying “setting up…” status. I checked and was informed that it might take up to 4 hours for services to set up but even after 2 days, the issue was not resolved. I tried to remove and assign the license again for one of the user and it started working. As I have lots of users, I can not do it manually so eventually I ended up using powershell.

This post will take you through the process I followed while trying to resolve this issue and also the powershell script.

  1. The first step was to install Microsoft Online Services Sign-In assistant and Windows Azure Active Directory (Azure AD) module for Windows Powershell in my system. Both are 64 bit versions and you can find more details about it here.
  2. After that launch the powershell and connect to the tenant which is facing the issue using the Connect-MsolService command. It will ask for your O365 credentials.
    Powershell Credential Window
  3. To get the AccountSkuId and included ServicePlans for your tenant, you can use the following command:
    Get-MsolAccountSku | Where-Object {$_.SkuPartNumber -eq “yourpackname“} | ForEach-Object{$_.ServiceStatus}
    Account Details
    As you can see in the above screenshot, we now have tenant’s AccountSkuId and all the service plans with their status. Similarly, we can get the status of service plan assigned to all the users. For faulty licence assignment, usually the provisioning status is “Pending Input”.
  4. Next step is to create a CSV of all the users for which this issue is present. Let’s say the name of CSV is UsersList.csv.
  5. Run the below powershell command to remove and assign licenses again:
    $allUsers = Get-Content "{PathofFile}\UsersList.csv"
    :Outer foreach ($user in $allUsers)
    {
      Write-Host($user) -foregroundcolor "magenta"
      $IsFaulty = "false";
      $O365User = Get-MsolUser -User $user
      $Licenses = $O365User.Licenses[0].ServiceStatus
      :Inner foreach($lic in $Licenses) {
         If ($lic.ServicePlan.ServiceName -eq "SHAREPOINTSTANDARD_EDU" -and $lic.ProvisioningStatus -ne "Success")
         {
           $IsFaulty = "true"
            break Inner
          }
     }
     If ($IsFaulty -eq "true")
     {
       Set-MsolUserLicense -User $user -RemoveLicenses {AccountSkuId:screenshot2}
       Set-MsolUserLicense -User $user -AddLicenses {AccountSkuId:screenshot2}
     }
    }
    

The above code first reads all the users from the CSV file. After that it checks their SharePoint service status and reassigns the licence if the status is not equal to “Success”. You can check for any other service which is applicable to you.

The name of services are different for different plans so make sure to execute step 3 for exact service names. One important thing to note here is, if you remove the license, users might lose all of their data. In my scenario, the users were newly created so that was not an issue, but if that is the case don’t run this script.

Hope this helps!

References: