ssasTools

0.1

ssasTools.psm1

# SSAS Tools and Library
# (v) Deepak, Prodata Feb 2020
# History
# 12/02/2020 First Version
#
#Install-Module -Name SqlServer
      

function Invoke-ssasProcess
{
    param(
        [Parameter(Mandatory=$True)]  [object]$Server ,                       #Analysis Services Server
        [Parameter(Mandatory=$True)]  [String]$DatabaseName,                  #Database name in Analysis Server
        [Parameter(Mandatory=$True)]  [String]$RefreshType,                   #Refresh type for cube processing
        [Parameter(Mandatory=$True)]  [String]$Workspace,                     #Workspace name requied for Process log
        [parameter(Mandatory=$false)] [switch]$MailOnFailure=$False,          #Email on Failure
        [parameter(Mandatory=$false)] [String]$SqlServer ="localhost" ,       #Sql Server to Log Results. Will Log if this is present
        [parameter(Mandatory=$false)] [String]$SqlDatabaseName  ,             #Sql Server to Log Results to. Will Log if this is present
        [parameter(Mandatory=$false)] [String]$SqlSchemaName ="dbo" ,         #Sql Server to Log Schema to. Will Log if this is present
        [parameter(Mandatory=$false)] [String]$SqlTableName ="ProcessView",   #Sql Table to Log Results to
        [parameter(Mandatory=$false)] [switch]$IgnoreProcessError,            #Raise Error if any Process Errors. Default is to Fail on any Error
        [parameter(Mandatory=$false)] [switch]$NoOutput                       #Output Status When Finished
    )
    $ErrorActionPreference = "Stop"
    $Error = ""
    Check-ssasModules
    try {
        [DateTime]$StartTime=(Get-Date -Format "yyyy-M-d HH:mm:ss") 
        Write-Host ("Process started at", $StartTime)      
        $xmla = Invoke-ProcessASDatabase -Server $Server -DatabaseName $DatabaseName -RefreshType $RefreshType           
    }    
    catch 
    {
        $Error =  $_.Exception.Message
        $RefreshStatus = "Failed"
        Write-Host $Error    
    }
    if($Error -eq "")
    {
        $RefreshStatus= "Completed"
    }
    
    [DateTime]$EndTime=(Get-Date -Format "yyyy-M-d HH:mm:ss")
    $DataSetList=@()
    $obj=$null
    $obj = New-Object System.Object  
    $obj | Add-Member -type NoteProperty -Name WorkSpace -Value $Workspace 
    $obj | Add-Member -type NoteProperty -Name DataSetId -Value $null
    $obj | Add-Member -type NoteProperty -Name DataSet -Value $DatabaseName
    $obj | Add-Member -type NoteProperty -Name ConfiguredBy $null
    $obj | Add-Member -type NoteProperty -Name IsOnPremGatewayRequired  1 
    $obj | Add-Member -type NoteProperty -Name refreshStatus $RefreshStatus
    $obj | Add-Member -type NoteProperty -Name refreshType "ViaApi"
    $obj | Add-Member -type NoteProperty -Name startTime $StartTime
    $obj | Add-Member -type NoteProperty -Name EndTime $EndTime
    $obj | Add-Member -type NoteProperty -Name Error $Error
    $obj | Add-Member -type NoteProperty -Name CreatedBy $(whoami)
    $obj | Add-Member -type NoteProperty -Name ProcessKey ""
    $obj | Add-Member -type NoteProperty -Name DatasetType "SSAS"
    $DataSetList+=$obj


    Write-Host "Process ended at "  $EndTime "with status:" $RefreshStatus
     
    if (($SqlServer) -and ($SqlDatabaseName)) {
    Write-Verbose -Message "Logging to $SqlServer.$SqlDatabaseName.$SqlSchemaName.$SqlTableName"
    $DataSetList | Select-Object -Property * | Write-SqlTableData -ServerInstance $SqlServer -DatabaseName $SqlDatabaseName -TableName $SqlTableName -SchemaName $SqlSchemaName
    
    }  
}


function Load-Module ($m) {

    # If module is imported say that and do nothing
    if (Get-Module | Where-Object {$_.Name -eq $m}) {
        return
    }
    else {

        # If module is not imported, but available on disk then import
        if (Get-Module -ListAvailable | Where-Object {$_.Name -eq $m}) {
            Import-Module $m 
        }
        else {

            # If module is not imported, not available on disk, but is in online gallery then install and import
            if (Find-Module -Name $m | Where-Object {$_.Name -eq $m}) {
                Install-Module -Name $m -Force -Verbose -Scope CurrentUser
                Import-Module $m 
            }
            else {

                # If module is not imported, not available and not in online gallery then abort
                write-host "Module $m not imported, not available and not in online gallery, exiting."
                Exit 1
            }
        }
    }
}


function Check-ssasModules {
    Load-Module("SqlServer")
}


function Get-LastProcessedDate
{
  param(
      [Parameter(Mandatory=$True)]  [object]$Server ,                       #Analysis Services Server
      [Parameter(Mandatory=$True)]  [String]$DatabaseName,                  #Database name in Analysis Server
  )
  Begin {
    $serverObj = New-Object Microsoft.AnalysisServices.Server
    $serverObj.Connect("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$DatabaseName;Data Source=$Server")
  }
  Process {
    Try {
    $DatabaseName = $serverObj.Databases.GetByName($DatabaseName)  
    
    return $DatabaseName.LastProcessed
    }
    Catch [System.Exception] {
      Write-Host $Error[0].Exception
    }
    Finally {
      if ($DatabaseName -ne $null) {
          $DatabaseName.Dispose()
      }    
    }
  }
  End {
    $serverObj.Dispose()
  }
}

export-modulemember -function Invoke-ssasProcess
export-modulemember -function Get-LastProcessedDate