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.
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
Powershell script:-
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 = @"
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel MaxParallel="##MaxParallel##">
##ProcessCmd##
</Parallel>
</Batch>
"@
$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
Error message -
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.
$server.connect($ServerName). You've put in the correct server name right? (passed in as a parameter). A good way to check is just put$ServerNameon the line right before this and it will echo it. – Nick.McDermaid