Hello everyone,
For those of you at the conference, this was the dashboard I showed in my presentation and for those not at the conference, this is a dashboard on performance monitoring inside of ProjectWise using PowerShell. Please find the attached documents to getting started with setting up performance monitoring on your system. There is a PowerShell script that needs to be run on some kind of scheduled task at the various locations you want to test, the template for the PowerBi report and a setup document to help you link the results from your script with PowerBi. For any questions or discussion please post to this thread. Enjoy!
Thanks,
Marty
UPDATE March 16, 2020
ProjectWise_Performance_Report_1.0.7zProjectWise_Performance_Report_Example_2.0.zip ProjectWise_Performance_Dashboard_1.0.zip
After adding the SQLite3 database via ODBC to PowerBI I tried remapping the data to each of the graphs as this is all lost from the given template when using SQLite.
After not seeing the data represented correctly after mapping some fields to values in the charts, I realised I had to go the the Data Modeling and change each duration field for every table to Data Type: Decimal Number. Once doing this the Power BI template worked well:
Thanks for sharing!
This is excellent. We are happy to see folks using it.
Can anyone tell me what's happening, or not happening and how to fix it?
New-SQLTable : Cannot validate argument on parameter 'Database'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.At line:14 char:62+ ... QLTable -SQLConnection $SQLConnection -Database $DatabaseName -DataTa ...+ ~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [New-SQLTable], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationError,New-SQLTable BulkCopy-SQLTable : Exception calling "WriteToServer" with "1" argument(s): "Cannot access destination table 'PWPerfGeneral'."At line:17 char:5+ BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $Table+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,BulkCopy-SQLTable Write-PWPSLog : Exception calling "WriteToServer" with "1" argument(s): "Cannot access destination table 'PWPerfGeneral'."At C:\Program Files\WindowsPowerShell\Modules\PWPS_DAB\1.12.10.0\HelperFunctions.psm1:1460 char:13+ Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Er ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-PWPSLog BulkCopy-SQLTable : Error bulk copying table.At line:17 char:5+ BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $Table+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,BulkCopy-SQLTable Write-PWPSLog : Error bulk copying table.At C:\Program Files\WindowsPowerShell\Modules\PWPS_DAB\1.12.10.0\HelperFunctions.psm1:1464 char:13+ Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Er ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-PWPSLog New-SQLTable : Cannot validate argument on parameter 'Database'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.At line:14 char:62+ ... QLTable -SQLConnection $SQLConnection -Database $DatabaseName -DataTa ...+ ~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [New-SQLTable], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationError,New-SQLTable BulkCopy-SQLTable : Exception calling "WriteToServer" with "1" argument(s): "Cannot access destination table 'PWPerfDocument'."At line:17 char:5+ BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $Table+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,BulkCopy-SQLTable Write-PWPSLog : Exception calling "WriteToServer" with "1" argument(s): "Cannot access destination table 'PWPerfDocument'."At C:\Program Files\WindowsPowerShell\Modules\PWPS_DAB\1.12.10.0\HelperFunctions.psm1:1460 char:13+ Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Er ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-PWPSLog BulkCopy-SQLTable : Error bulk copying table.At line:17 char:5+ BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $Table+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,BulkCopy-SQLTable Write-PWPSLog : Error bulk copying table.At C:\Program Files\WindowsPowerShell\Modules\PWPS_DAB\1.12.10.0\HelperFunctions.psm1:1464 char:13+ Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Er ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-PWPSLog New-SQLTable : Cannot validate argument on parameter 'Database'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.At line:14 char:62+ ... QLTable -SQLConnection $SQLConnection -Database $DatabaseName -DataTa ...+ ~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [New-SQLTable], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationError,New-SQLTable BulkCopy-SQLTable : Exception calling "WriteToServer" with "1" argument(s): "Cannot access destination table 'PWPerfFolder'."At line:17 char:5+ BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $Table+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,BulkCopy-SQLTable Write-PWPSLog : Exception calling "WriteToServer" with "1" argument(s): "Cannot access destination table 'PWPerfFolder'."At C:\Program Files\WindowsPowerShell\Modules\PWPS_DAB\1.12.10.0\HelperFunctions.psm1:1460 char:13+ Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Er ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-PWPSLog BulkCopy-SQLTable : Error bulk copying table.At line:17 char:5+ BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $Table+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,BulkCopy-SQLTable Write-PWPSLog : Error bulk copying table.At C:\Program Files\WindowsPowerShell\Modules\PWPS_DAB\1.12.10.0\HelperFunctions.psm1:1464 char:13+ Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Er ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-PWPSLog New-SQLTable : Cannot validate argument on parameter 'Database'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.At line:14 char:62+ ... QLTable -SQLConnection $SQLConnection -Database $DatabaseName -DataTa ...+ ~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [New-SQLTable], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationError,New-SQLTable BulkCopy-SQLTable : Exception calling "WriteToServer" with "1" argument(s): "Cannot access destination table 'PWPerfUser'."At line:17 char:5+ BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $Table+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,BulkCopy-SQLTable Write-PWPSLog : Exception calling "WriteToServer" with "1" argument(s): "Cannot access destination table 'PWPerfUser'."At C:\Program Files\WindowsPowerShell\Modules\PWPS_DAB\1.12.10.0\HelperFunctions.psm1:1460 char:13+ Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Er ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-PWPSLog BulkCopy-SQLTable : Error bulk copying table.At line:17 char:5+ BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $Table+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,BulkCopy-SQLTable Write-PWPSLog : Error bulk copying table.At C:\Program Files\WindowsPowerShell\Modules\PWPS_DAB\1.12.10.0\HelperFunctions.psm1:1464 char:13+ Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Er ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-PWPSLog
Hey Jeff,I've replied via email
For everyone else, this was because the Database parameter in New-SQLTable is specified as $DatabaseName in the example script, instead of $SQL.Database.Below is an updated copy of the example script with these changes added. (v1.1)
ProjectWise_Performance_Report_Example_1.1.txt
Perfect! Thanks! I have tables now.