HowTo: Setup New Datasource

Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.

UPDATE: There was in issue in one of the functions I provided. Please download the attachment again to get the latest file.

In this post, we will be using two PowerShell functions (New-PWDSSetupReport and Use-PWDSSetupReport.) to add users, groups, userlists, workflows, states, etc. to a new datasource.  The functions are provided in a PSM1 file contained in a zip file which you can download via a link at the bottom of the page.

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.10.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Get-PWCurrentDatasource
• New-XLSXWorkbook
• Write-PWPSLog
• Get-TablesFromXLSXWorkbook
• New-PWUserSimple
• New-PWGroupByName
• New-PWUserListByName
• Add-PWMemberToGroup
• Add-PWMemberToUserList
• New-PWWorkflow
• New-PWState
• Add-PWWorkflowState
• New-PWInterface
• New-PWEnvironment
• Add-PWEnvironmentColumn
• Undo-PWLogin
• New-PWLogin
• Add-PWAttributeToInterface
• New-PWView
• Import-PWRichProjectDefinitions

Import Module (PSM1)

We need to import the FUNCTIONS_DataSourceSetup.psm1 file before we can do anything.

# Path to the local folder where you saved the psm1 file.
$Path = 'D:\PowerShell\!Blog Post Scripts\DatasourceSetup'
Import-Module -FullyQualifiedName "$Path\FUNCTIONS_DataSourceSetup.psm1" -Force

Get Command

Running the Get-Command with the module name specified will show the available functions.

# Show functions in the psm1 file.
Get-Command -Module FUNCTIONS_DataSourceSetup

Generate Report

Now we can use the New-PWDSSetupReport function to generate an Excel file with multiple worksheets corresponding to various elements within a ProjectWise datasource. Each worksheet will contain the required fields to create the corresponding ProjectWise element. For example, the Users worksheet contains six columns (UserName, Description, Email, SecurityProvider, Password, and IMSUser).

The idea is to generate this report, file out all of the relevant information and then run the Use-PWDSSetupReport function to import the data from the Excel file and create the ProjectWise elements.

New-PWDSSetupReport

First, we will use the New-PWDSSetupReport function to generate the Excel workbook (report). This function has only one required parameter which we need to provide to run the function. We need to specify the location and name of the Excel file to be generated.

There is an optional parameter to include the Default User Settings. See the help.

# Create new datasource setup report.
$OutputFileName = "d:\temp\export\NewDatasourceSetup.xlsx"
New-PWDSSetupReport -OutputFileName $OutputFileName -Verbose

The following shows the output Excel file.

Update Report

Now, for the time consuming part of the process. You will need to enter all of the relevant information into the spreadsheet. If you do not wish to create one of the ProjectWie elements, simply leave the worksheet empty, or delete it from the Excel workbook.

The following images show the contents of each of the worksheets (except the Default User Settings) contained in the Excel workbook after all of the relevant data has been updated. This will be the workbook used later.

Process (Use) Report

Finally, we will run the Use-PWDSSetupReport function. This function will import the specified report and process each worksheet. It is looking for the worksheets created using the New-PWDSSetupReport. If a worksheet does not match one of the designated names,  it will be ignored.

As the function processes each worksheet, the corresponding element(s) will be created within the ProjectWise datasource.

# Updated report. Contains users, groups, userlists, memberships, etc.
$InputFilePathName = 'd:\temp\export\NewDatasourceSetup_Updated.xlsx'
# Log file to be generated.
$LogFile = 'd:\temp\export\NewDatasourceSetup_Process.log'
$Splat_Process = @{
    InputFilePathName = $InputFilePathName 
    LogFile = $LogFile
}
Use-PWDSSetupReport @Splat_Process -Verbose

Prior to adding Attributes to an Interface, we will need to logout and back into the ProjectWise session. So, you may be prompted to log back in. You can update the function to automatically log you back in. This would require using BentleyIMS or providing a secure password file for the current ProjectWise user.

Results

So, we have run the Use-PWDSSetupReport function. Open the ProjectWise Administrator client to verify / validate that each of the expected elements have been created. Of course, you could use PowerShell to do this. For the purposes of this post, I am going to show each of the ProjectWise elements as seen in the Administrator.

Users:

The following shows the eight new User accounts. All beginning with ‘ps’.

Groups and Group Membership:

The following images show the six Groups were created and the membership of one of them. All beginning with ‘ps’.

UserLists and UserList Membership:

The following images show the six UserLists were created and the membership of one of them. All beginning with ‘ps’.

Workflows and States:

The following images show the three Workflows and six States were created. Also, shows that one of the workflows has four states added. All beginning with ‘ps’.

Environments and Columns:

The following shows the Environment was created with the specified attributes. All beginning with ‘ps’.

Interfaces:

The following shows the two new Interfaces. All beginning with ‘ps’.

Views:

The following shows two new Views were created and the columns of one of them. All beginning with ‘ps’.

WorkAreas:

The following shows the new Work Area was created with the specified properties.

Summary

Let’s summarize what we have done.

We used two functions (New-PWDSSetupReport and Use-PWDSSetupReport) to generate an Excel workbook (report) containing multiple worksheets which correspond to the various elements within a ProjectWise datasource.

Then we added data to each of the worksheets to include, users, groups, userlists, environment, etc.

The updated report was then imported into PowerShell and processed to create / add each of the ProjectWise datasource elements.

The intent is for this to provide an easy way to setup your datasource(s) using PowerShell and the PWPS_DAB module.

---

Experiment with it and have fun.

Link to a zip file containing Functions_DataSourceSetup.psm1, Calling.ps1, a sample Excel workbook and a sample log file.

HowToSetupNewDataSource

Hopefully, you find this useful. Please let me know if you have any questions or comments.  If you like this post, please click the Like button at the bottom of the page.