Category: Useful Scripts

Michał PawlikowskiMultidimensional, SSAS, Useful Scripts

SSAS database info (db size, partition size, processing status etc.)

Source: http://www.ssas-info.com/analysis-services-scripts/1197-powershell-script-to-list-info-about-ssas-databases   slightly modified

PowerShell
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
param($ServerName = "localhost", $dbname = "db_name")
 
## Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
 
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
    Write-Output ("Server '{0}' not found" -f $ServerName)
    break
}
 
foreach ($d in $server.Databases ) {
    if ($d.Name -ne $dbname) { continue; }
 
    Write-Output ( "Database: {0}; Status: {1}; Size: {2}MB" -f $d.Name, $d.State, ($d.EstimatedSize / 1024 / 1024).ToString("#,##0") )
 
    foreach ($cube in $d.Cubes) {
        Write-Output ( " Cube: {0}" -f $Cube.Name )
        foreach ($mg in $cube.MeasureGroups) {
            Write-Output ( "  MG: {0}; Status: {1}; Size: {2}MB" -f $mg.Name.PadRight(25), $mg.State, ($mg.EstimatedSize / 1024 / 1024).tostring("#,##0"))
            foreach ($part in $mg.Partitions) {
                Write-Output ( "   Partition: {0}; Status: {1}; Size: {2}MB" -f $part.Name.PadRight(35), $part.State, ($part.EstimatedSize / 1024 / 1024).ToString("#,##0") )
            } # Partition
        } # Measure group
        foreach ($dim in $d.Dimensions) {
            Write-Output ( "Dimension: {0}" -f $dim.Name)
        } # Dimensions
 
    } # Cube
 
} # Databases

 

Michał PawlikowskiUseful Scripts

TSQL query for generating a report from SSISDB with SSIS package processed in execution

And the thanks goes to Michał Powaga for providing me that script below 😉

Transact-SQL
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
USE SSISDB;
GO
 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
DECLARE @execution_id BIGINT = 70660;
-- your package id
 
 
WITH msgs
AS ( SELECT event_message_id ,
            execution_path ,
            package_name ,
            package_path_full ,
            event_name ,
            message_source_name ,
            package_path
     FROM   internal.event_messages (NOLOCK)
     WHERE  event_name IN ( 'OnPreExecute', 'OnPostExecute' )
            AND operation_id = @execution_id ) ,
     running
AS ( SELECT *
     FROM   msgs o
     WHERE  o.event_name = 'OnPreExecute'
            AND NOT EXISTS (   SELECT *
                               FROM   msgs AS c
                               WHERE  c.event_name = 'OnPostExecute'
                                      AND c.execution_path = o.execution_path ))
SELECT   ex.execution_id ,
         ex.project_name ,
         e.executable_id ,
         e.executable_name ,
         e.package_name ,
         e.package_path ,
         CONVERT(DATETIME, es.start_time) AS start_time ,
         CONVERT(DATETIME, es.end_time) AS end_time ,
         CONVERT(VARCHAR, DATEADD(ms, es.execution_duration, 0), 108) AS 'duration_h:m:s' ,
         es.execution_duration AS 'execution_duration_ms' ,
         es.execution_result ,
         CASE es.execution_result
              WHEN 0 THEN 'Success'
              WHEN 1 THEN 'Failure'
              WHEN 2 THEN 'Completion'
              WHEN 3 THEN 'Cancelled'
         END AS execution_result_description ,
         es.execution_path ,
         r.*
FROM     catalog.executions ex (NOLOCK)
         JOIN catalog.executables e (NOLOCK) ON ex.execution_id = e.execution_id
         JOIN catalog.executable_statistics es (NOLOCK) ON e.executable_id = es.executable_id
                                                  AND e.execution_id = es.execution_id
         FULL OUTER JOIN running r (NOLOCK) ON es.execution_path = r.execution_path
WHERE    e.execution_id = @execution_id
ORDER BY es.execution_id DESC;
 
 
 
 
WITH msgs
AS ( SELECT event_message_id ,
            execution_path ,
            package_name ,
            package_path_full ,
            event_name ,
            message_source_name ,
            package_path
     FROM   internal.event_messages (NOLOCK)
     WHERE  event_name IN ( 'OnPreExecute', 'OnPostExecute' )
            AND operation_id = @execution_id ) ,
     running
AS ( SELECT *
     FROM   msgs o
     WHERE  o.event_name = 'OnPreExecute'
            AND NOT EXISTS (   SELECT *
                               FROM   msgs AS c
                               WHERE  c.event_name = 'OnPostExecute'
                                      AND c.execution_path = o.execution_path ))
SELECT *
FROM   running;