#Requires -Modules SqlServer
#Requires -Modules ImportExcel
<# The AdventureWorksDW2017 only has 29 tables and they're all under 1 million rows.#>
cd SQLSERVER:\SQL\LocalHost\SQL2017\Databases\AdventureWorksDW2017\Tables

<# Scenario #1 A) all Dimensions in a single file,
    and B) each Fact table in their own file. #>

<# A) Every Dimension table in a worksheet named after the table, the same Excel file #>
dir | WHERE { $_.name -like 'dim*' } |
foreach {
"$($_.Name)"
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows |
Export-Excel -Path "c:\temp\AW\AdventureWorksDW2017_Dims.xlsx" -WorksheetName $_.Name -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors
}

<# B) Each Fact-table in it's own Excel file, named after the table. #>
dir | WHERE { $_.name -like 'fact*' } |
foreach {
"$($_.Name)"
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows |
Export-Excel -Path "c:\temp\AW\$($_.Name).xlsx" -WorksheetName $_.Name -ExcludeProperty IsReadOnly,IsFixedSize,IsSynchronized,SyncRoot,Count
}


<# Scenario #2 Each table in it's own Excel file, named after the table. #>
dir |
foreach {
"$($_.Name)"
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows |
Export-Excel -Path "c:\temp\AW\$($_.Name).xlsx" -WorksheetName $_.Name -ExcludeProperty IsReadOnly,IsFixedSize,IsSynchronized,SyncRoot,Count
}

cd SQLSERVER:\SQL\LocalHost\SQL2017\Databases\AdventureWorksDW2017\Tables
<# Scenario #3 Every table in a worksheet named after the table, all in the same Excel file #>
dir |
foreach {
"$($_.Name)"
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows |
Export-Excel -Path "c:\temp\AW\AdventureWorksDW2017.xlsx" -WorksheetName $_.Name -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors
}