ssasTools.psm1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
# 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