#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

HowTo: Generate Attribute Usage Report

Have you ever wondered which attributes (columns) within each of your ProjectWise Environments are actually being used?  I have seen many users with multiple environments, each containing hundreds of attributes. The problem is that only 20 to 25% of the attributes (columns) may contain any values. It takes a lot of time and energy to develop your environments only to have them under-utilized.

I have created a PowerShell function (New-PWAttributeUsageReport) to generate a report listing all attributes (columns) within each environment and the number of times the attribute is used (has a value entered).  In this post, I am going to show you what I have developed. 

We will be using the following cmdlets to accomplish this task. All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.21.12.0. (2112, great album by Rush)  Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

  • Get-PWEnvironments
  • Get-PWDocumentsBySearch
  • Get-PWEnvironmentColumns
  • Select-PWSQL
  • New-XLSXWorkbook
  • Test-Path
  • Split-Path
  • Get-Date
  • Write-Host
  • Write-Progress
  • Measure-Object
  • Select-Object
  • Write-Warning

FUNCTION Definition

The following is the function wrapper which will contain the remainder of the code.

FUNCTION New-PWAttributeUsageReport {
    <#
            .SYNOPSIS
Generates a report containing attribute usage data.
.DESCRIPTION
Generates an Excel workbook containing a worksheet for each environment in the current datasource.
.EXAMPLE
# The following with generate an attribute usage report.
$Splat_New = @{
ReportPathName = 'd:\temp\export\AttributeReport.xlsx'
EnvironmentsToSkip = @('zLookupTables')
}
New-PWAttributeUsageReport @Splat_New -Verbose #> [CmdletBinding()] param (...) # end param... BEGIN {...} # end BEGIN... PROCESS {...} # end PROCESS... END{...} # end END... } # end FUNCTION New-PWAttributeUsageReport... Export-ModuleMember -Function New-PWAttributeUsageReport

Parameters

First thing we need to do is create our parameter and value pairs. The help messages will explain the purpose for each parameter. 

You will see that I have incorporated “ValidateScript” for the ReportPathName parameter. This way I do not have do any additional checks to determine if the folder specified exists or not.

[CmdletBinding()]
param ( 
    # Specifies the folder path and name of report to create.
    [ValidateNotNullOrEmpty()] 
    [ValidateScript({ Test-Path -Path (Split-Path $_ -Parent) })]
    [Parameter(
        HelpMessage = "Path and name of report to create.",
        Mandatory = $true,
        Position = 0)]
    [string] $ReportPathName,

    # Specifies the name of any environments to be ignored.
    [ValidateNotNullOrEmpty()]
    [Parameter(
        HelpMessage = "Names of environments to NOT report on.",
        Position = 1)]
    [string[]] $EnvironmentsToSkip
) # end param...

Begin

I want to take a second and point out a couple of conventions that I use in my scripts. First, you may notice that I put a comment at the end of many of the blocks of code. Doing this makes it easier for me to determine where a block of code starts and ends. Comes in handy when debugging a script.  I also add text ([BEGIN], [PROCESS], [END]) in the output messages (Write-Verbose, Write-Warning, Write-Error, Write-Host) corresponding to the section of the script I am in. Again, this makes it obvious where something occurs within the script.

Here we are getting a list of all Environments contained in the current ProjectWise datasource. This will determine the number of environments we will be reporting on.

BEGIN {  
    $CmdletName = $MyInvocation.MyCommand.Name
    $StartTime = Get-Date
    Write-Host "[BEGIN] $Start - Entering '$CmdletName' Function..." -ForegroundColor Cyan 

# Get folder count prior to removing empty folders.
$environments = Get-PWEnvironments

# Determine number of environments to report on.
$envCount = $environments.Count
if( -not ([string]::IsNullOrEmpty($EnvironmentsToSkip))){
$envCount = $environments.Count - $EnvironmentsToSkip.Count
}

Write-Host "[BEGIN] Reporting on $($envCount) environments." -ForegroundColor DarkGreen

} # end BEGIN...

PROCESS

Now, we will proceed to the PROCESS code block. Here we will look at each of the ProjectWise Environments to report on.

In this first section we start looping through each of the environments in the current datasource.  We will get a document count for all documents residing in folders which have the specified environment associated with it. We will then, create a datatable and add the document count information to the first row.

PROCESS {

# Arraylist to contain datatables to be exported.
$arDataTables = [Collections.ArrayList]::New()

# The following is a counter for the progress bar.
$Counter = 1
$itemCount = $envCount
# Loop through each environment within the current datasource.
foreach($env in $environments){

#region PROGRESS SECTION

$Progress = @{
Activity = "Getting environment information for '$($env.Name)'."
Status = "Processing $Counter of $ItemCount"
PercentComplete = $([math]::Round($(($Counter / $ItemCount) * 100 ), 2))
}
Write-Progress @Progress -Id 1

#endregion PROGRESS SECTION

# Skip any environments contained in the $environmentsToSkip array.
if($environmentsToSkip.Contains($env.Name)){
continue
}

# Create new datatable for the current Environment.
$dt = [Data.Datatable]::New($Env.Name)
$dt.Columns.Add('ColumnName', [string]) | Out-Null
$dt.Columns.Add('Count', [int]) | Out-Null
$dt.Columns.Add('Usage', [double]) | Out-Null

# Get count of documents for current Environment. This will be placed in the first row.
$docCount = Get-PWDocumentsBySearch -Environment $env.Name |
Measure-Object | Select-Object -ExpandProperty Count

$dr = $dt.NewRow()
$dr.ColumnName = "DocumentCount"
$dr.Count = [int]$docCount
$dt.Rows.Add($dr)

In the next section we will get a list of the current environment attributes (columns) and loop through them to get the number of entries for each. Finally, we will calculate the percentage used, and then add the data to a datatable.

 # Get list of attributes (columns) of the current Environment.
$envColumns = Get-PWEnvironmentColumns -EnvironmentName $env.Name

# Add columns and metadata count for each column to the new datatable.
# Skip the a_attrno column.

# The following is a counter for the progress bar.
$Counter_Cols = 1
$ItemCount_Cols = $envColumns.count
foreach($col in $envColumns){

#region PROGRESS SECTION

$Progress_Cols = @{
Activity = "Getting column information for '$($col.Name)'."
Status = "Processing $Counter_Cols of $ItemCount_Cols"
PercentComplete = $([math]::Round($(($Counter_Cols / $ItemCount_Cols) * 100 ), 2))
}
Write-Progress @Progress_Cols -Id 2 -ParentId 1

# Increment the counter.
$Counter_Cols++

#endregion PROGRESS SECTION

if($col.Name.Equals('a_attrno')){
continue
}

$results = Select-PWSQL -SQLSelectStatement "SELECT COUNT($($col.Name)) AS COUNT FROM $($env.TableName)"
Write-Host "ColumnName: $($col.Name); COUNT: $($results.Count)" -ForegroundColor DarkGreen

# Create and populate new row.
$dr = $dt.NewRow()
$dr.ColumnName = $col.Name
$dr.Count = $results.Count
try{
$dr.Usage = [math]::Round( ($results.Count / $docCount * 100), 2 )
} catch {
# Deliberately left empty.
}
# Add row to datatable.
$dt.Rows.Add($dr)

} # end foreach($col in $envColumns...

Finally, we will finish out the parent loop, add the current datatable to the arraylist and end the process block.

        Write-Progress -Activity "Completed." -Status "Completed" -Id 2 -ParentId 1 -Completed

Write-Host "[PROCESS] Adding datatable '$($dt.TableName)' to arraylist. ($Counter of $envCount)" -ForegroundColor DarkGreen
$null = $arDataTables.Add($dt)

# Increment the counter.
$Counter++
} # foreach($env in $environments...

} # end PROCESS...

The following shows the script running. Notice the multiple progress bars being displayed. One lists the information for the environments. The other lists the attribute (column) information.  Also, you can see each attribute (column() is being returned with the number of times it is used and then the data is added to the datatable. Lastly, the datatable is added to the arraylist of datatables to be used in the report.

scriptrunning


END

Lastly, we will proceed to the END block of code.  If any datatables were created in the Process block, we will generate our report.

END {
if($arDataTables.Count -gt 0){
Write-Host "[END] Exporting $($arDataTables.Count) datatables." -ForegroundColor DarkGreen
try{
New-XLSXWorkbook -InputTables $arDataTables -OutputFileName $ReportPathName -ErrorAction Stop
} catch {
Write-Warning -Message "[END] Error occurred while generating report."
}
}

$EndTime = Get-Date
Write-Host "[END] It took $($EndTime - $StartTime) to complete the process." -ForegroundColor Cyan
Write-Host "[END] $EndTime - Exiting '$CmdletName' Function..." -ForegroundColor Cyan
} # end END...

The following shows a sample of the output. Notice the usage. This is the percentage of usage for the individual attribute (column) based on the number of documents returned.

exampleoutput


Experiment with it and have fun. Hopefully, you find this useful. Please let me know if you have any questions or comments. 

The following is a link to the full PowerShell script. 

FUNCTION-NewAttributeUsageReport

 If you like this post, please click the Like button at the bottom of the page.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.