Azurearch Tech

Azure Analysis Cube verification

admin

This power Shell scripts gives each table processing date on mail .

This power Shell Scripts will fetch the data from Azure analysis services and send email to email id with each cube table process date like below :-

#Install Join Module

#connectionstring = "Provider=MSOLAP;Data Source=<Servername>;Initial Catalog=cube name;User ID=app:XXX;Password=<>.;Persist Security Info=True;Impersonation Level=Impersonate"

$cred = Get-AutomationPSCredential -Name 'aasautomationcred'
$ServerName = "servername"
#$DatabaseNames = "cubename"
$TenantId = "XXX"

$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 = "second server name if you have "
$DatabaseNames = "cubename"
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
#,"emailid","emailid2","emailid3"

$Parameters = @{
    FromAddress = "donotreply@notreply.com"
    ToAddress   = "email address"
    Subject     = "Important : Production Cube Processing Status"
    BodyAsHTML  = $htmltext
    Token       = "SG.XXX"
    FromName    = "Monitoring"
}
Send-PSSendGridMail @Parameters
Exit mobile version