Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Use PowerShell to extract Excel Worksheey Schema and Data to create Power BI DataSet
<# Literally one of the oldest SQL PowerShell examples, and it now works on PSCore.
I'm only doing this step to generate data to place in an Excel spreadsheet.
IMPORTANT: If you already have an Excel spreadsheet, skip this section! #>
ls 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse |
WHERE {$_.Mode -ne 'd' } |
foreach {
Invoke-Sqlcmd -ServerInstance $_.Name -Database master -OutputAs DataTables -Query "
SELECT @@ServerName AS 'ServerName',
DB_NAME(dbid) AS 'DatabaseName',
name AS 'LogicalName',
GETDATE() AS 'CheckDate',
CONVERT(BIGINT, size) * 8 AS 'SizeInKB',
filename AS 'DBFileName',
SYSDATETIMEOFFSET() 'DiscoveryOccured'
FROM master..sysaltfiles
WHERE dbid != 32767" |
Write-SqlTableData -ServerInstance Localhost\SQL2017 -DatabaseName BlankDB -SchemaName dbo -TableName DatabaseSizeHistory -Force;
}
<# This code build the Excel file.
Obviously, you can modify the Power BI section
to pull the data directly from the DatabaseSizeHistory table. #>
Read-SqlTableData -ServerInstance Localhost\SQL2017 -DatabaseName BlankDB -SchemaName dbo -TableName DatabaseSizeHistory |
Export-Excel -Path c:\temp\dbSizes.xlsx -WorksheetName dbSizeHistory -Show
<# ##############################################################################
OK, this is the Power BI portion.
You may not even need the InferData.ps1, but it's in the ImportExcel module.
############################################################################## #>
. .\InferData.ps1
$result = Import-Excel .\dbSizes.xlsx -WorksheetName 'dbSizeHistory'
$names = $result[0].psobject.properties.name
<# This code will generate the statement below which #>
$variables = @()
$Columns = $(for ($idx = 0; $idx -lt 1; $idx++) {
$colVar=0
foreach($name in $names) {
$variables += "`$col{0}" -f ($colVar++)
"{0} = New-PowerBIColumn -Name {1} -DataType {2}`r`n" -f $variables[-1],$name, (Invoke-AllTests $result[$idx].$name -OnlyPassing -FirstOne).DataType
}
})
@"
$Columns
`$table1 = New-PowerBITable -Name dbFileSizeHistory -Columns $($variables -join ", ")
"@
<# This code is all generated for you by the code above, just execute it.
Don not edit the code below, it's just here for illustrative purposes #>
# $col0 = New-PowerBIColumn -Name ServerName -DataType string
# $col1 = New-PowerBIColumn -Name DatabaseName -DataType string
# $col2 = New-PowerBIColumn -Name LogicalName -DataType string
# $col3 = New-PowerBIColumn -Name CheckDate -DataType datetime
# $col4 = New-PowerBIColumn -Name SizeInKB -DataType int
# $col5 = New-PowerBIColumn -Name DBFileName -DataType string
# $col6 = New-PowerBIColumn -Name DiscoveryOccured -DataType datetime
# $col7 = New-PowerBIColumn -Name RowError -DataType string
# $col8 = New-PowerBIColumn -Name RowState -DataType string
# $col9 = New-PowerBIColumn -Name Table -DataType string
# $col10 = New-PowerBIColumn -Name ItemArray -DataType string
# $col11 = New-PowerBIColumn -Name HasErrors -DataType bool
# $table1 = New-PowerBITable -Name dbFileSizeHistory -Columns $col0, $col1, $col2, $col3, $col4, $col5, $col6, $col7, $col8, $col9, $col10, $col11
<# /This code is all generated for you by the code above, just execute it. #>
$dataset = New-PowerBIDataSet -Name DougFinke_IsAWESOME! -Tables $table1
Login-PowerBIServiceAccount -Environment Public -Credential (Get-Credential EMAIL@ADDRESS-YouUseToLoginTo-PowerBI.com)
Add-PowerBIDataSet -DataSet $dataset
<# As of this writing, Add-PowerBIRow only accepts data via .CSV >#>
Import-Excel .\dbSizes.xlsx -WorksheetName 'dbSizeHistory' |Export-Csv .\data.csv -NoTypeInformation
Add-PowerBIRow -DataSetId f92973ba-b05c-4122-9deb-c339b27fbb6a -TableName SampleTable1 -Rows (Import-Csv -Path ".\data.csv")
0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.