title: Automate process SSAS tabular model using Azure
body: <p>I have process that uses Azure Data Factory for pulling data, processing and pumping data on Azure SQL database. At the end the data from Azure SQL is used by a SSAS tabular model. As of now the entire process till pumping data to Azure SQL is automated using Azure Data Factory v2 but I am unable to find a way to automatically process SSAS tabular model. Kindly let me know if there's a way to do the same.</p>
  
  <p>I want it to be triggered somehow by ADF v2 so that the tabular gets updated data as soon as new data is pumped into SQL. As I was not able to do it using ADF v2, I tried using Powershell. The roadblock that I am facing here is, I need to pass two credentials for processing tabular
  1.  SQL server authentication for Azure SQL
  2.  Windows credentials of one user for impersonation
  I am very new to Powershell and having issues in passing both these credentials</p>
  
  <p>Powershell script:-</p>
  
  <pre><code>param($ServerName="Your Server Name", $DBName="Your DB Name", $ProcessTypeDim="ProcessFull",$ProcessTypeMG="ProcessFull", 
  $Transactional="Y", $Parallel="Y",$MaxParallel=2,$MaxCmdPerBatch=1, $PrintCmd="N",
  $logFilePath="Your Path where you want to save the log file")
  ## Add the AMO namespace
  Write-Output "Process Starts for database $DBName :" | Out-File -FilePath $logFilePath
  $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
  if ($Transactional -eq "Y") {$TransactionalB=$true} else {$TransactionalB=$false}
  if ($Parallel -eq "Y") {$ParallelB=$true} else {$ParallelB=$false}
  $server = New-Object Microsoft.AnalysisServices.Server
  $server.connect($ServerName)
  if ($server.name -eq $null) {
   Write-Output ("Server '{0}' not found" -f $ServerName)
   Write-Output "Server '{0}' not found" -f $ServerName | Out-File -FilePath $logFilePath -Append
   break
  }
  $DB = $server.Databases.FindByName($DBName)
  if ($DB -eq $null) {
   Write-Output ("Database '{0}' not found" -f $DBName)
   Write-Output "Database '{0}' not found" -f $DBName | Out-File -FilePath $logFilePath -Append
   break
  }
  $date_Start=Get-Date
  Write-Output("Load start time {0}" -f (Get-Date -uformat "%H:%M:%S") )
  Write-Output "Load start time: "  (Get-Date -uformat "%H:%M:%S") | Out-File -FilePath $logFilePath -Append
  
  Write-Output("----------------------------------------------------------------")
  Write-Output "----------------------------------------------------------------"| Out-File -FilePath $logFilePath -Append
  Write-Output("Server  : {0}" -f $Server.Name)
  Write-Output "Server  :"   $Server.Name | Out-File -FilePath $logFilePath -Append
  Write-Output("Database: {0}" -f $DB.Name)
  Write-Output "Database: "  $DB.Name | Out-File -FilePath $logFilePath -Append
  Write-Output("DB State: {0}" -f $DB.State)
  Write-Output "DB State: "  $DB.State | Out-File -FilePath $logFilePath -Append
  Write-Output("DB Size : {0}MB" -f 
  
      ($DB.EstimatedSize/1024/1024).ToString("#,##0"))
      Write-Output "DB Size : "  ($DB.EstimatedSize/1024/1024).ToString("#,##0")| Out-File -FilePath $logFilePath -Append
      Write-Output("----------------------------------------------------------------")
      Write-Output "----------------------------------------------------------------"| Out-File -FilePath $logFilePath -Append
      Write-Output("DB processing started.   Time: {0}" -f (Get-Date -uformat "%H:%M:%S"))
      Write-Output "DB processing started.   Time: " (Get-Date -uformat "%H:%M:%S")| Out-File -FilePath $logFilePath -Append
      $server.CaptureXml=$TRUE # Just capture server statements, dont execute them
      #Process dimensions
      foreach ($dim in $DB.Dimensions) {
        $dim.Process($ProcessTypeDim)
      } # Dimensions
      #Process cubes
      foreach ($cube in $DB.Cubes) {
       foreach ($mg in $cube.MeasureGroups) {
        foreach ($part in $mg.Partitions) {
         $part.Process($ProcessTypeMG)
        }
       }
      }
      # Separate step to process all linked measure groups. Linke MG does not have partitions
      foreach ($cube in $DB.Cubes) {
       foreach ($mg in $cube.MeasureGroups) {
        if ($mg.IsLinked) {
         $mg.Process($ProcessTypeMG)
        }
       }
      }
  
      $server.CaptureXML = $FALSE # Finish capturing statements. All statements are in Server.CaptureLog
      $cmdBatch = @"
      &lt;Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"&gt;
        &lt;Parallel MaxParallel="##MaxParallel##"&gt;
      ##ProcessCmd##
        &lt;/Parallel&gt;
      &lt;/Batch&gt;
      "@
      $cmdBatch = $cmdBatch -replace("##MaxParallel##",$MaxParallel)
      #$ErrorActionPreference = "SilentlyContinue"
      $currentCmdNo=0; $currentCmdInBatchNo=0;
      $processCmd="";$currentBatchNo=0
      $TotalCmdCount = $Server.CaptureLog.Count
  
      foreach ($cmdLine in $Server.CaptureLog) {
       $currentCmdNo = $currentCmdNo + 1
       $processCmd = $processCmd + $cmdLine + "`n"
       $currentCmdInBatchNo=$currentCmdInBatchNo + 1
       if ($currentCmdInBatchNo -ge $MaxCmdPerBatch -or $currentCmdNo -eq $TotalCmdCount) { #MaxCmdPerBatch reached, execute commands
        $processCmd = $cmdBatch -replace("##ProcessCmd##", $processCmd) 
        if ($PrintCmd -eq "Y") { Write-Output($processCmd) }
        $currentBatchNo = $currentBatchNo + 1;
        Write-Output("=== Startining batch No {0}. Time: {1} ..." -f $currentBatchNo, (Get-Date -uformat "%H:%M:%S"))
        Write-Output "=== Startining batch No $currentBatchNo. Time: ..."  (Get-Date -uformat "%H:%M:%S")| Out-File -FilePath $logFilePath -Append
        $Result = $Server.Execute($processCmd)
        # Report errors and warnings
        foreach ($res in $Result) {
         foreach ($msg in $res.Messages) {
          if ($msg.Description -ne $null) {
                          Write-Output("{0}" -f $msg.Description)
                          Write-Output $msg.Description| Out-File -FilePath $logFilePath -Append
                          } 
         }
        }
        # Reset temp values
        $processCmd = ""; $currentCmdInBatchNo=0;
       }
      }#foreach 
  
      Write-Output("-------------------------Cube status----------------------------")
      Write-Output("-------------------------Cube status----------------------------")| Out-File -FilePath $logFilePath -Append
  
      $data=$DB.Cubes|select name,state,lastprocessed
      Write-Output($data)
      Write-Output $data| Out-File -FilePath $logFilePath -Append 
  
      Write-Output("--------------------Dimension status----------------------------")
      Write-Output("--------------------Dimension status----------------------------")| Out-File -FilePath $logFilePath -Append
  
      $data=$DB.Dimensions|select name,state,lastprocessed
      Write-Output($data)
      Write-Output $data| Out-File -FilePath $logFilePath -Append 
  
      Write-Output("-----------------Dimension related to cubes---------------------")
      Write-Output("-----------------Dimension related to cubes---------------------")| Out-File -FilePath $logFilePath -Append
  
      foreach ($cube in $DB.Cubes) {
          Write-Output("Cube Name: $cube")
          Write-Output "Cube Name: $cube"| Out-File -FilePath $logFilePath -Append
              foreach ($dim in $DB.Dimensions) {
              Write-Output("                         $dim")
              Write-Output "                         $dim"| Out-File -FilePath $logFilePath -Append
  
          } 
      }
  
      Write-Output("----------------------------------------------------------------")
      Write-Output("----------------------------------------------------------------")| Out-File -FilePath $logFilePath -Append 
      $date_End=Get-Date
      Write-Output("Load End Time: {0}" -f (Get-Date -uformat "%H:%M:%S"))
      Write-Output "Load End Time: "  (Get-Date -uformat "%H:%M:%S")| Out-File -FilePath $logFilePath -Append
  
      $ptime="Total Processing Time :"+($date_End-$date_Start).Hours+" Hours, "+($date_End-$date_Start).Minutes+" Mins, "+($date_End-$date_Start).Seconds+" Secs "
      Write-Output $ptime
      Write-Output $ptime | Out-File -FilePath $logFilePath -Append 
  </code></pre>
  
  <p>Error message -</p>
  
  <blockquote>
    <p>OLE DB or ODBC error: Login timeout expired; HYT00; A network-related
    or instance-specific error has occurred while establishing a
    connection to SQL Server. Server is not found or not accessible. Check
    if instance name is correct and if SQL Server is configured to allow
    remote connections. For more information see SQL Server Books Online.;
    08001; Named Pipes Provider: Could not open a connection to SQL Server
    [5]. ; 08001.</p>
  </blockquote>