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
<#
.SYNOPSIS
    PowerShell script for collecting and storing Power BI refresh 
    Performance data into a SQL Server database.

.DESCRIPTION
    This script connects to the Power BI service, retrieves workspace, 
    report, and dataset information, and captures refresh history details.
    The collected data is then stored in a SQL Server database table 
    for analysis and reporting.

#>

param (
    [string]$sqlUsername = "SuperAdmin",            # SQL Server username
    [string]$sqlPassword = "**********",            # SQL Server password
    [string]$serverName = "DESKTOP-*******",       # SQL Server instance 46A7LA5
    [string]$databaseName = "PBI_Inventory",       # Database name
    [bool]$clearOldData = $false                    # Flag to determine whether to clear old data
)

# Function to install or update a PowerShell module
function Install-OrUpdate-Module([string]$ModuleName) {
    $module = Get-Module $ModuleName -ListAvailable -ErrorAction SilentlyContinue

    if (!$module -or ($module.Version -ne '1.0.0' -and $module.Version -le '1.0.410')) {
        $action = if (!$module) { "Installing" } else { "Updating" }
        Write-Host "$action module $ModuleName ..."
        Install-Module -Name $ModuleName -Force -Scope CurrentUser
        Write-Host "Module $ModuleName $action complete"
    }
}

# Function to execute a SQL command with parameters
function Execute-DB-Command-With-Params([System.Data.SqlClient.SqlConnection]$connection, [String]$command, [Hashtable]$parameters) {
    $cmd = $connection.CreateCommand()
    $cmd.CommandText = $command
    foreach ($key in $parameters.Keys) {
        $cmd.Parameters.AddWithValue($key, $parameters[$key]) | Out-Null
    }
    $cmd.ExecuteNonQuery()
}

# Function to add a table to the database
function Add-Table([System.Data.SqlClient.SqlConnection]$connection, [String]$tableName, [String[]]$feature) {
    $createTableScript = @"
        IF OBJECT_ID('$tableName', 'U') IS NOT NULL
            DROP TABLE $tableName;
        CREATE TABLE $tableName (
            $($feature -join " VARCHAR(MAX),`n")
            VARCHAR(MAX)
        );
        ALTER TABLE $tableName
            ADD InsertDataTime VARCHAR(MAX);
"@
    Execute-DB-Command-With-Params $connection $createTableScript @{}
}

# Function to insert data into a table
function Insert-Data([System.Data.SqlClient.SqlConnection]$connection, [String]$tableName, [String[]]$feature, [String[]]$insertData) {
    $today = Get-Date -Format "yyyy-MM-dd_HH:mm:ss"
    $columns = $feature -join " VARCHAR(MAX),`n"

    $createQuery = @"
    IF OBJECT_ID('$tableName', 'U') IS NULL
    BEGIN
        CREATE TABLE $tableName (
            $columns VARCHAR(MAX),
            InsertDataTime VARCHAR(MAX)
        );
    END
"@

    $insertQuery = @"
    INSERT INTO $tableName ($($feature -join ', '), InsertDataTime)
    VALUES ('$($insertData -join "', '")', '$($today)');
"@

    $combinedScript = $createQuery + $insertQuery
    Execute-DB-Command-With-Params $connection $combinedScript @{}
}

# Install or update the required PowerShell module for Power BI management
Install-OrUpdate-Module -ModuleName "MicrosoftPowerBIMgmt"
Connect-PowerBIServiceAccount

# Set up the SQL Server connection
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Data Source=$serverName;Initial Catalog=$databaseName;User Id=$sqlUsername;Password=$sqlPassword;"
$connection.Open()

# Define Power BI table and features
$tableName_PBI_Performance = "dbo.PBI_Performance"
$feature_PBI_Performance = @("Workspace", "Report", "Dataset", "StartTime", "EndTime", "Status", "WorkspaceId", "ReportId", "DatasetId", "CreationTime","LastTimeRefresh","RefreshCostTime")

# Create table if clearOldData flag is set
if ($clearOldData) {
    Add-Table $connection $tableName_PBI_Performance $feature_PBI_Performance
}

# Process Power BI workspaces and refresh history
ForEach ($workspace in (Get-PowerBIWorkspace)) {
    $workspaceId = $workspace.Id
    $WorkspaceName = $workspace.Name

    foreach ($report in (Get-PowerBIReport -WorkspaceId $workspaceId)) {
        $ReportId = $report.Id
        $ReportName = $report.Name

        foreach ($dataset in (Get-PowerBIDataset -Id $report.DatasetId -WorkspaceId $workspaceId)) {
            $DatasetName = $dataset.Name
            Write-Host $DatasetName

            $DatasetId = $dataset.Id
            $refreshUri = "groups/$workspaceId/datasets/$($dataset.Id)/refreshes"
            $refreshHistory = Invoke-PowerBIRestMethod -Url $refreshUri -Method Get | ConvertFrom-Json
            $CreateTime = $refreshHistory.value[0].StartTime
            $LastTimeRefresh = $refreshHistory.value[$refreshHistory.value.Length-1].EndTime

            foreach ($refreshEntry in $refreshHistory.value) {
                $Status = $refreshEntry.Status
                $startTime = [DateTime]::Parse($refreshEntry.StartTime)
                $endTime = [DateTime]::Parse($refreshEntry.EndTime)
                $timeDifferenceInSeconds = [math]::Round(($endTime - $startTime).TotalSeconds)
                $formattedTimeDifference = "$($timeDifferenceInSeconds)s"
                
                $data_PBI_Performance = @($WorkspaceName, $ReportName, $DatasetName, $refreshEntry.StartTime, $refreshEntry.EndTime, $Status, $workspaceId, $ReportId, $DatasetId, $CreateTime, $LastTimeRefresh, $formattedTimeDifference)
                Insert-Data $connection $tableName_PBI_Performance $feature_PBI_Performance $data_PBI_Performance
            }
        }
    }
}

# Close the database connection
$connection.Close()

Write-Host "Completed"