As per current scenario ,we need to do manual intervention to find the last processing date for each table .now we have created power shell scripts to find the last processing date for each table for the cube for analysis server .
In Analysis Services, only the last process date for SSAS database are stored in Dynamic Management Views (DMV). There’s no last processed date stored in MDSCHEMA_DIMENSIONS . So we can’t directly use XMLA or DMV get last processed date for dimensions.
In this scenario, we can use either AMO or Powershell to get the LastProcessed property of a dimension. Please refer to the comments in the blog that you have pasted the link. When you get the LastProcessed DateTime, you can put the date, dimension name and SSAS database into a table. This is the fact table for your measure and you can use it in your model.
please find the PowerShell scripts for below :-
#Install Join Module
$cred = Get-AutomationPSCredential -Name 'aasautomationcred'
$ServerName = "<Server name>"
$DatabaseNames = "DatabsenameS","Dbname"
#$DatabaseNames =""
$TenantId = "Tenentnameid"
$FIRSTDAYOFMONTH=GET-DATE -Format o -Day 1 -Hour 0 -Minute 0 -Second 0 | ForEach-Object { $_ -replace “:”, “.” }
$FIRSTDAYOFMONTH
$table = ''
$tableCubeStruct = ''
$htmlBody = "
<html>
<style>
table, th, td {
border:1px solid black;
}
</style>
<body>
<h1>Monitor Current Status of Cube Processing.<br><br></h1>"
$tablebody = "<table style=`"width:50%`">"
$tableCaption = ''
$tableHeader = "<tr>
<th>TableName</th>
<th>PartitionName</th>
<th>RefreshDate</th>
</tr>"
#$tableNameAndCount = ''
$htmlfooter = "
</body>
</html>"
foreach ($DatabaseName in $DatabaseNames)
{
Write-Output $DatabaseNam
#Read all Tables in the Cubes
[xml]$TableList = Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query "select [ID],[Name] AS TableName from `$SYSTEM.TMSCHEMA_Tables"
$lookupList = [PSCustomObject]$TableList.return.root.row | Select-Object -Property ID, TableName
#Read all partition in the Cube
[xml]$XmlPartition = Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query "SELECT [ID] AS [PartID],[Name],tableID,ModifiedTime,RefreshedTime FROM `$SYSTEM.TMSCHEMA_PARTITIONS"
$partitionlist = [PSCustomObject]$XmlPartition.return.root.row | Select-Object -Property PartID,Name,tableID,ModifiedTime,RefreshedTime
$finallist = $partitionlist | Group-Object -Property tableID |ForEach-Object{$_.Group | Sort-Object ModifiedTime | Select-Object -Last 1} | Select-Object -Property PartID,Name,tableID,ModifiedTime,RefreshedTime
$TableCount = @($finallist).Count
$tableBodytag = ''
$tableCaption = "<caption style=`"text-align:left`"><b>$($DatabaseName) : $($TableCount)</b></caption>"
$tableBodytag = $tablebody + $tableCaption + $tableHeader
$tableOutput = Join-Object -Left $lookupList -Right $finallist -LeftJoinProperty 'ID' -RightJoinProperty 'tableID' -Type 'OnlyIfInBoth' | Select-Object -Property PartID,Name,ModifiedTime,RefreshedTime,TableName
$tableOutput | ForEach-Object -Process {
if ($_.RefreshedTime -lt $FIRSTDAYOFMONTH){
$table = $table + " <tr>
<td>$($_.TableName)</td>
<td>$($_.Name)</td>
<td bgcolor=`"red`">$($_.RefreshedTime)</td>
</tr>"
}
else{
$table = $table + " <tr>
<td>$($_.TableName)</td>
<td>$($_.Name)</td>
<td bgcolor=`"green`">$($_.RefreshedTime)</td>
</tr>"
} }
$tableCubeStruct = $tableCubeStruct + $tableBodytag + $table +"</table> <br><br>"
$table = ''
}
<#
$ServerName = "<Server name>"
$DatabaseNames = "dbname","dbname"
foreach ($DatabaseName in $DatabaseNames)
{
Write-Output $DatabaseNam
#Read all Tables in the Cubes
[xml]$TableList = Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query "select [ID],[Name] AS TableName from `$SYSTEM.TMSCHEMA_Tables"
$lookupList = [PSCustomObject]$TableList.return.root.row | Select-Object -Property ID, TableName
#Read all partition in the Cube
[xml]$XmlPartition = Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query "SELECT [ID] AS [PartID],[Name],tableID,ModifiedTime,RefreshedTime FROM `$SYSTEM.TMSCHEMA_PARTITIONS"
$partitionlist = [PSCustomObject]$XmlPartition.return.root.row | Select-Object -Property PartID,Name,tableID,ModifiedTime,RefreshedTime
$finallist = $partitionlist | Group-Object -Property tableID |ForEach-Object{$_.Group | Sort-Object ModifiedTime | Select-Object -Last 1} | Select-Object -Property PartID,Name,tableID,ModifiedTime,RefreshedTime
$TableCount = @($finallist).Count
$tableBodytag = ''
$tableCaption = "<caption style=`"text-align:left`"><b>$($DatabaseName) : $($TableCount)</b></caption>"
$tableBodytag = $tablebody + $tableCaption + $tableHeader
$tableOutput = Join-Object -Left $lookupList -Right $finallist -LeftJoinProperty 'ID' -RightJoinProperty 'tableID' -Type 'OnlyIfInBoth' | Select-Object -Property PartID,Name,ModifiedTime,RefreshedTime,TableName
$tableOutput | ForEach-Object -Process {
if ($_.RefreshedTime -lt $FIRSTDAYOFMONTH){
$table = $table + " <tr>
<td>$($_.TableName)</td>
<td>$($_.Name)</td>
<td bgcolor=`"red`">$($_.RefreshedTime)</td>
</tr>"
}
else{
$table = $table + " <tr>
<td>$($_.TableName)</td>
<td>$($_.Name)</td>
<td bgcolor=`"green`">$($_.RefreshedTime)</td>
</tr>"
} }
$tableCubeStruct = $tableCubeStruct + $tableBodytag + $table +"</table> <br><br>"
$table = ''
}
#>
$htmltext = $($htmlBody) + $($tableCubeStruct) + $($htmlfooter)
#Write-Output $htmltext
#,"userid@domain.com","##","##"
$Parameters = @{
FromAddress = "from email id"
ToAddress = "user1","user2","user3","user4"
Subject = "Important : Production Cube Processing Status"
BodyAsHTML = $htmltext
Token = "token"
FromName = "DatalakeMonitoring"
}
Send-PSSendGridMail @Parameters
Please used automation account to run this PowerShell scripts ,we can utilized the scripts for individual table last processing status for one cube or all cube present into tableprivate
