ProjectWise Performance Report

While testing an export to SQLLITE I did the following, just sharing might find it useful..

$scriptFolder = (([System.IO.Directory]::GetParent($MyInvocation.MyCommand.Path)).FullName)

$PWPerformanceReportVariables = @{
    Connection = "Office LAN";
    Location = "Melbourne, Victoria";
    ConnectingViaCache = "True";
    Datasource = "decide-pwce-aus.bentley.com:decide-pwce-aus-010";
    ProjectWiseUserName = "PWUserName";
    ProjectWisePassword = (ConvertTo-SecureString -String PWPassword -AsPlainText -Force);
    OutputType = "DataTable";
}

$PerformanceDataset = Get-PWPerformanceReportData @PWPerformanceReportVariables -Verbose

foreach ($Table in $PerformanceDataset.Tables)
{
    #create a new datatable to populate with the performance data
    $dt = New-Object System.Data.Datatable ($Table.TableName)
        
    #add the columns to the datatable. We can use the property names of the properties object as our column names
    # Get a list of the performance properties.
    $PWperformanceProperties = $Table | Get-Member -MemberType Property

    # Add columns to the datatable for each of the properties in $docProperties
    foreach ($property in $PWperformanceProperties){
        $dt.Columns.Add($property.Name) | Out-Null
    }

    #create a new datarow, loop through the performance data and populate the row, and then add the row to the datatable
    # Create New Datarow
    $dr = $dt.NewRow()
    # Populate Datarow with performance values
    foreach ($column in $dt.Columns) {
        $dr[$column.ColumnName] = $Table.$($column.ColumnName)
    }
    # Add new row to the datatable
    $dt.Rows.Add($dr)

    $SQLLITE = @{
        SourceDataTable = $dt;
        OutputFolder = $scriptFolder;
        OutputFileName = "PW_Performance.sqlite";
    }
    #Export dataset to a SQLLITE database            
    Export-DataTableToSQLiteDB @SQLLITE -Append
}

    

While testing an export to SQLLITE I did the following, just sharing might find it useful..

$scriptFolder = (([System.IO.Directory]::GetParent($MyInvocation.MyCommand.Path)).FullName)

$PWPerformanceReportVariables = @{
    Connection = "Office LAN";
    Location = "Melbourne, Victoria";
    ConnectingViaCache = "True";
    Datasource = "decide-pwce-aus.bentley.com:decide-pwce-aus-010";
    ProjectWiseUserName = "PWUserName";
    ProjectWisePassword = (ConvertTo-SecureString -String PWPassword -AsPlainText -Force);
    OutputType = "DataTable";
}

$PerformanceDataset = Get-PWPerformanceReportData @PWPerformanceReportVariables -Verbose

foreach ($Table in $PerformanceDataset.Tables)
{
    #create a new datatable to populate with the performance data
    $dt = New-Object System.Data.Datatable ($Table.TableName)
        
    #add the columns to the datatable. We can use the property names of the properties object as our column names
    # Get a list of the performance properties.
    $PWperformanceProperties = $Table | Get-Member -MemberType Property

    # Add columns to the datatable for each of the properties in $docProperties
    foreach ($property in $PWperformanceProperties){
        $dt.Columns.Add($property.Name) | Out-Null
    }

    #create a new datarow, loop through the performance data and populate the row, and then add the row to the datatable
    # Create New Datarow
    $dr = $dt.NewRow()
    # Populate Datarow with performance values
    foreach ($column in $dt.Columns) {
        $dr[$column.ColumnName] = $Table.$($column.ColumnName)
    }
    # Add new row to the datatable
    $dt.Rows.Add($dr)

    $SQLLITE = @{
        SourceDataTable = $dt;
        OutputFolder = $scriptFolder;
        OutputFileName = "PW_Performance.sqlite";
    }
    #Export dataset to a SQLLITE database            
    Export-DataTableToSQLiteDB @SQLLITE -Append
}

    

Thanks for sharing. 

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.