Jo West's ProjectWise Blog

Using a MS SQL Server Procedure to Populate Project Properties

Jo West — Fri, 16 Dec 2011 16:18:00 GMT

If you read my blog about Cascading Attributes and found that useful, here’s another way to do something similar, but a bit more complicated.

Recently I was asked, “How do I create a SELECT statement to do this?”

I have two Project Properties:
@"PROJECT_Project_Number" and @"PROJECT_Billing_Type"

Here is the logic I want to use:
          If @"PROJECT_Project_Number" is a NULL then
                  @"PROJECT_Billing_Type" = Non-billable (pre-contract)
          Else If the first character of @"PROJECT_Project_Number" is a 'P' then
                  @"PROJECT_Billing_Type" = Non-billable (pre-W)
                  Else If the first character of @"PROJECT_Project_Number" is a 'W'
          then
                   @"PROJECT_Billing_Type" = Non-billable (pre-contract)
          Else
                   @"PROJECT_Billing_Type" = Billable (Contracted)
          End If

Well, I’m no SQL guru, but I do know that it would be tough to do this with just plain SQL, but pretty easy if you create and use a stored SQL Server procedure instead! (And of course, you can do something similar with Oracle.)

First setup the Billing_Type Project Property
Using the ProjectWise Administrator Client, create the necessary Project Type and add your Project Properties as usual, but you will need to provide a default value for Billing_Type as well as adding a “select” statement that actually calls a stored procedure. Here’s an example Project Type (Billing Example) with some Project Properties.

Take a good look at “Project_Number” and “Billing_Type”.

Now create the stored procedure in SQL Server
I used the tools inside of Microsoft SQL Server Management Studio, but no matter what tool you use, you want a stored procedure that looks like this:

Now test the stored procedure
Create a new Project in ProjectWise Explore and then go to the Project Properties page. Your “Billing_Type” should update based on what you enter for the “Project_Number”. Here’s how it should look like with some sample data.

Enjoy!

So How Much Programming Experience Do You Need?

Jo West — Fri, 16 Dec 2011 03:58:20 GMT

A common question I get asked about the ProjectWise V8i SDK training is:

“How much programming experience does someone really need to take the class?”

The course description has the following requirements and recommendations:”

Recently a ProjectWise Administrator asked me this familiar question, but with a new twist:

“Someone told me that they had ZERO programming experience, went to the PW SDK class and started developing numerous applications right away! How is that possible when I have heard that you need many years of C++ experience to even attempt to be productive using the PW SDK?”

Well, as my track coach use to say, “There’s no substitute for talent but you still have to work hard to accomplish anything worthwhile!”

Talent

I had a classmate back then that never ran track before, but beat me in the mile run in gym class! So I convinced him to go out for track and he set a school record in the mile run for sophomores that year. He clearly had natural talent because he did this without going to practice very much and he smoked cigarettes! Needless to say that eventually he got kicked off the team for his less than ideal work ethic despite his talent for running.

Obviously a certain amount of “talent” (i.e. ability) is required to create programs that are reasonably robust and useful, but for most of us, experience is what allows us to solve more complex problems in a reasonable amount of time.

Experience

For most of us with talents closer to the mean, “experience” helps make it possible for us to create better programs. And it is hard to measure experience in a way that can be compared. A person who has been doing something relatively easy for many years has experience, but someone else who has less time invested but has been doing much more difficult tasks and growing their skills can typically accomplish more in less time.

The kind of experience also is a factor. If your programming experience is “only” with VB, VBA or some other “scripting” language, you are definitely at a disadvantage when attempting to complete the exercises in the ProjectWise SDK course guide, but at least you understand assignments, if tests and looping. Users with “only” this kind of experience have taken the class and most of them do well enough. However, if you are truly new to programming, you really need to take some programming classes first or come with a partner who can help you with the exercises.

Difficulty

How quickly and how many different applications you can create using the ProjectWise SDK depends of course upon what exactly you are trying to accomplish as well as your abilities and experience. Creating an application that creates new users from a list of some sort is actually pretty easy to design and code, while trying to “integrate” ProjectWise with a system like SAP is much more complex.

So, how much experience do you need?

The real point behind the prerequisite for the ProjectWise V8i SDK class is that the course is about the ProjectWise SDK and not about ProjectWise and especially not about how to program. Since the SDK APIs are “C” based, the class is taught using Visual C++, as that was the development environment of choice when the SDK was first released and continues to be a viable development environment. The material covered in the class is not very complicated because the goal is to help users get a “running start” into using the SDK. It is probably more important that the users who take the ProjectWise SDK training have some experience, or at least exposure to ProjectWise than actual programming experience. It is hard to solve a problem that you don’t understand.

The class instructors do try to help each student as much as possible, including those struggling with the mechanics of Visual Studio as well as “programming” topics, but typically the amount of available time for this is pretty limited, especially if the class has more than 6 or so students. So, each student has to determine just how ready they are for taking this class. A simple measure is to take a look at the sample code provided with the SDK. Take a look at the code that compiles into applications, not custom modules as that requires some knowledge covered in the class. For example, take a look at the “vaultlist” project. If you can follow the flow of the program and understand most of the syntax of the code, then you probably can handle the programming aspects of the class. If you don’t understand what “vaults” are (AKA “projects” or “folders”) or what documents are (they are not files), then you probably should study up on ProjectWise a bit before taking the class.

The real value of the class comes from the probability that it will likely save a programmer at least two weeks of time in trying to understand how the SDK is organized, how to use the documentation, how to compile and examine the sample code as well as some “tips and tricks”. Some students have reported that taking the class saved them a lot more than 2 weeks of effort, but of course it depends upon the skill of the student as well as the effort they put behind it. Just like my track coach use to remind us...

Access Control in Preview Pane (SS3)

Jo West — Mon, 10 Oct 2011 14:02:51 GMT

With ProjectWise SelectSeries3 release, there is an easier way to set Project/Folder and document security. You no longer need to open the folder properties dialog to set its permissions.

As you select each folder, its’ permissions are displayed on the ‘Access Control’ tab of the preview pane. You can easily perform the following

Identify folder and document permissions (create subfolders, read, write and no access, etc.)
If the folder has its own security or inherited from its top level folder
Assign users and groups with appropriate permissions to selected folder and document
Apply current folder permissions to a different folder/document

Import/export permissions to an excel file.

What is the Restricted Administrator Group?

Jo West — Mon, 10 Oct 2011 13:59:36 GMT

When a user is a member of the Administrator group, he or she will have full access to every node under the datasource (Applications, States, Environments, Users, etc.).

Now with ProjectWise SELECTseries 3, each node has a “Granular Security” tab allowing the ProjectWise administrator to define who should have access only to a particular node. There is also a new group called “Restricted Adminstrator” that works along with “Granular Security”.

For example, if you want userA to have access only to Applications and Environments node, you need to make userA a member of the “Restricted Administrator” group first and then assign userA to the “Granular Security” tab of the Applications and Environments node. The end result will be that userA can only view these two nodes when he/she access the ProjectWise Administrator module.

Note: if userA is also a member of the Administrator group, then he/she will have access to all the nodes, not just Applications and Environments node.

Group and User List Membership

Jo West — Mon, 10 Oct 2011 13:45:59 GMT

Group and User List Membership

Groups and user lists are two different ways of grouping users together in the datasource.

Groups are typically created by the administrator as a way to group together users who need the same access rights to folders and documents. Once a group is created, you can then assign that group permission to a folder, rather than assigning each user permission to the folder, one at a time.

User lists provide a way to group together users, groups and also other user lists. There are two types of user lists: access lists and mailing lists. The administrator will create an access list to group together users, groups and other access lists that need the same access rights to folders and documents. The administrator will create a mailing list (also referred to as Global Address Books) to group together users with email addresses, groups, and other mailing lists, so users with email addresses can send and receive messages using Messaging Services in ProjectWise Explorer.

The membership of groups and user lists could previously only be managed from ProjectWise Administrator, but now can also be managed from ProjectWise Explorer, using the new User / Group Management dialog, which opens when you select Tools > User Management.

The user you are logged in as must be an owner of a group in order to manage its membership and ownership; however you are not required to be a member of the group to be an owner of it. For any group of which you are an owner, you can add users to the group, and you can also designate other users to be owners of the group (a group can have multiple owners). You can also add a group to a user list, provided that you are an owner of both the group and the user list.

Note:

The version of the ProjectWise Integration Server you are connecting to must be ProjectWise V8i (SELECTseries 3), in order to fully use the controls in the User / Group Management dialog.
If the user you are logged as is a member of the Administrator group and the version of ProjectWise Integration Server you are connecting to is ProjectWise V8i (SELECTseries 1) or (SELECTseries 2), then the controls in the dialog will be read-only.
If the user you are logged as is NOT a member of the Administrator group and the version of ProjectWise Integration Server you are connecting to is ProjectWise V8i (SELECTseries 1) or (SELECTseries 2), then the dialog will not open if you select Tools > User Management.
If an owner of a group is not also a member of the group, then that user will not be able to see any folders and projects to which the group is assigned, when using User/Group Management dialog.
The user you are logged in as must be the owner of a group, to manage it. Any group you select which you are not the owner of will display the text “Current user has no access to the list.” in the Available and Assigned lists.

PW Variables to Managed Workspaces

Jo West — Mon, 10 Oct 2011 13:42:22 GMT

Is there a way to use Project Properties conditionally in a MicroStation Workspace? There is a technique to do this, simply create a Workspace Variable at the Command Line using the –ws switch.

In ProjectWise Admin, edit the MicroStation Application.
Modify or create a new Association.
In the command line arguments, create a variable with the desired Project Property:

Example: -wsCountry=$PROJECT#PROJECT_Country$

The -ws switch creates a Workspace Configuration variable before any configuration file is read.

Hint: Click Add to get the correct syntax for any System Variable or Project Property.

Now, the workspace is able to use the variable. For instance, a conditional statement could load a cell library specific to the project based upon country:

%if $(Country)=="UK"

MSCELLLIST > ukcells.cel

%endif

The same technique can be used with System Variables and Windows Variables.

PW Database Requirements

Jo West — Mon, 10 Oct 2011 13:38:48 GMT

ProjectWise Database User requirements

ProjectWise can run on both Oracle and Microsoft SQL Servers, it uses a multi-user connection in which the ProjectWise Server has a connection with the database (through ODBC or SQL Native Client). Besides this, users access ProjectWise through their own authorization in ProjectWise.

For the Database, there is only one user which is really important and needs to be setup with according specifications. When you login via the ProjectWise Administrator, a table check is executed and if you have insufficient privileges, you will keep getting prompted with the create tables dialog. You will need sysadmin/dbo privileges for the “SP” to execute or the tables will not be found correctly.

In both cases the database to be used will have to be in the UniCode format.

General information for servers using 64-bit.

There is no real problem if you use a 64-bit server to install your ProjectWise server. However, since our software is 32-bit, the ODBC datasources will have to be created using the 32-bit version of the tools. In case of Oracle, make sure to install the 32-bit Oracle Client for your database according to the specification, including ODBC driver. The 32-bit version of the ODBC manager can be found in c:\windows\syswow64\odbcad32.exe

Oracle installations

Connections made to the database will have to go through the Oracle ODBC driver (NOT the Microsoft ODBC for Oracle).
The user in Oracle will need the following privileges

— CONNECT role
— CREATE PROCEDURE privilege
— CREATE SEQUENCE privilege
— CREATE TABLE privilege
— CREATE VIEW privilege
— UNLIMITED TABLESPACE privilege
— EXECUTE permission on the DBMS_LOB package

SQL Server installations

Connections made to the database can go through the SQL server Native client or the ODBC client. No real difference there:

Make sure the user we use for ProjectWise is a user who has in SQL-server the Dbowner privilege.

The easiest way to give the PWDB user the best rights without giving them full database rights is to make them the database owner. Simply go to the database, right-click on properties, select Files, and assign the user as the owner:

The results of this are shown in the user’s User Mapping windows.

Creating a PW Network Diagram

Jo West — Mon, 10 Oct 2011 13:31:00 GMT

In all of my ProjectWise deployments, one of the first things I develop is an architecture diagram depicting a structure of the system hardware and ProjectWise components that run on them, showing how the hardware and software components work together. In other words, an architecture diagram should include the hardware for your ProjectWise system; including key specifications such has CPUs, operating system and RAM(memory). The diagram should also show the software that is installed on that hardware, both the required Windows components for ProjectWise and the exact ProjectWise modules.

A good architecture diagram will also show applications that are deployed to several machines, for example the ProjectWise Orchestration Framework as well as the physical location of the servers, noting if any reside behind a corporate firewall or a DMZ (Demilitarized Zone) and the port(s) used.

Of course, before you can develop the architecture diagram, you need to determine the scope of your ProjectWise deployment. Some considerations include:

- Identify the size of the deployment. Will ProjectWise be used by select corporate stakeholders (departments, business units, etc.)? Or will it be used throughout your organization? Make sure your design considers long-range planning as many companies will scale the use of ProjectWise over time.

- Consider fundamental technical issues. Will any existing systems need to interact/integrate with ProjectWise? How robust does your system need to be (will there be redundant hardware to failover to)? What/who will need to connect to and/or interact with your system and how will they do it? What operating system and database will your system use? How secure does the system need to be? Do you need a DMZ? Will any ProjectWise components require a SSL certificate?

- Identify Responsibilities. Most ProjectWise deployments will involve multiple IT/System technicians. Consider who will be responsible for making server and network decisions, database configurations and, if using the Web component, does anyone possess IIS/XML experience. Further, who will administrate ProjectWise?

- Distribute software to nodes. Identify how ProjectWise will be deployed on each workstation and include critical information such as ProjectWise configurations, licensing information and make sure you consider all desktop applications (ProjectWise Explorer, InterPlot, Navigator, etc.)

Having a solid architecture diagram of your ProjectWise configuration will aide both your organization and Bentley during the installation, as well as troubleshooting any problems or with future upgrades.

Definition of a PW Datasource

Jo West — Mon, 10 Oct 2011 13:30:00 GMT

During ProjectWise training, I am often asked what is the definition of a “Datasource”. In general terms, the name datasource is commonly used when creating a query to the database. More specifically, I define a datasource as a structure that contains specific ProjectWise information, such as users, groups, environments, etc. which is stored in a database. Each PW datasource must be attached to its own database (SQL or Oracle) and contain at least one Storage Area.

SelectSeries 1 ReadMe Files

Jo West — Mon, 10 Oct 2011 13:28:12 GMT

Readme’s have come a long way since the readme.txt files of old. Over the years, table of contents and hyperlinks have been added. The format of the Select Series I readme has a hyperlinked table of contents on the left and the actual contents on the right. Some of the table of contents links are to on-line web pages. Be aware that you can no longer scroll from beginning to end with the format. Nor is there an obvious way to search the readme file.

8.11.7.xx - Dynamic Composition Service

Jo West — Mon, 10 Oct 2011 13:26:24 GMT

ProjectWise i-model Composer (SELECT Series 1) is a desktop application for generating i-models.

An i-model is a read-only container which contains 2D/3D geometry as well as business data.

You can publish an i-model which will produce separate i-models for master and reference files or you can publish a package which is a single file containing master and references.

With ProjectWise i-model Composer, it requires user intervention to generate i-models or packages.

A new extension called “Dynamic Composition Service” is now delivered with ProjectWise Select Series 1, along with ProjectWise Automation Service and ProjectWise i-model Composer to enable i-models and packages to be automatically generated and distributed into a ProjectWise datasource based on job definitions.

Clearing Distribution Service Jobs

Jo West — Mon, 10 Oct 2011 13:08:02 GMT

When working with the ProjectWise Distribution Service, each “job” creates a variety of different files, including a copy of all files selected by the Job (and their Reference Files), any renditions, job history and more. Over time, these files can take up quite a bit of disk space and should be cleaned periodically. If you read the ProjectWise Implementation Guide, it states “It is recommended that you never manually delete files from the working directory”, but stops short of telling you how you should delete these files.

The proper way to clean the working directories is through the Service Browser that contains your jobs. For example, if you have Processor running under the Distribution Services, connect to the Distribution Service Browser. Once connected, navigate under Configuration > Working Directory, then right click on the working directory folder and select “Disk Usage…” This will publish a list of all Processor jobs. Highlight each job and click the Clear Cache button. This should clear the Cache and Files in your working directory.

This process works similarly for all Automation Service Jobs.

Set Me Free

Jo West — Tue, 11 May 2010 03:38:44 GMT

If your user setting has been set to allow you to free documents, then traditionally you can only free your own checked out documents or the ones that have been exported by you. You can’t free documents that are checked out or exported out by other users. It is only possible if you are a member of the ProjectWise administrator group.

With the release of ProjectWise Select Series 1, there is a new setting in document permission called “Free”. Administrators can decide at a project level, folder or document level which user can have the ability to free documents that are checked out or exported out by other users.

It is important to note that you don’t need to be part of ProjectWise Administrator group anymore to free documents checked out/exported by other users. It does however as a minimum, require that your user setting has been set to allow you to free documents.

Auto Login for WEB Parts in Non-SharePoint Website and More

Jo West — Mon, 26 Apr 2010 12:50:00 GMT

I'd like to share a configuration available for default ASP.NET page, which is delivered together with ProjectWise Web Server.

For example: In order to Auto login into ProjectWiseServerV8i.bentley.com server Datasource using SSO you need to modify the Default.aspx to look like the following:


 <td rowspan="3" class="partBody" style='vertical-align: width: 25%'>

<pw:Navigation ID="navigation" ConnectionId="default" Title="Navigation" Height="700px" runat="server"

ProviderID="Bentley.ECOM.ProjectWiseProvider" Location="ProjectWiseServerV8i.bentley.com:Datasource" ConnectionInfo="">

</td>

There are several properties which can be changed to modify the default behavior of ProjectWise web parts in the default ASP.NET page.

Common properties for all web parts and LoginForm:

Note: automatic authentication works when credentials are specified for web parts. Specifying credentials for LoginForm will only show these values as default.

ConnectionId - identifies a set of controls which share same session and events. May be any alphanumerical value.
This property must be specified for all controls that need to be connected together.
Location - specifies repository name to use for automatic authentication. For ProjectWise, this setting should contain fully qualified domain name (FQDN).
This property should be specified only for the first control that shares the same ConnectionId. Required for automatic authentication.
ConnectionInfo - specifies the default credentials to be used when someone connects to a page. For ProjectWise logins this has format "username:;password:". To use SSO authentication automatically, set this property to an empty string - ConnectionInfo="".
This property should be specified only for the first control that shares the same ConnectionId. Required for automatic authentication.

Common properties for all web parts:

RootPath - specifies default root folder to display for all web parts using same ConnectionId. ProjectWise path can be specified by prefixing it with "DMSFolder\". For example, "DMSFolder\MicroStation V8i", where "MicroStation V8i" is a name of a folder in ProjectWise datasource hierarchy.
This property should be specified only for the first control that shares the same ConnectionId.

LoginForm properties:

AllowAuthentication - if set to false, this instructs LoginForm control to not render Login/Logout buttons. This is useful in custom authentication scenarios when authentication is executed manually, but data entry form is required.
AllowProviderChange - this property may be used in integration scenarios with other ECPlugin implementations.
AllowRepositoryChange - if set to false, LoginForm will not allow you to change Location.

ContentControl/PropertiesControl web part properties:

DisplayType - specifies the primary view for web part. Only FlexibleList, PropertyPane or Spatial are supported by ProjectWise Web Server.

PropertiesControl web part properties:

UseSessionKey - true or false; specifies whether the web part shows properties of currently displayed folder or shows them on demand (selecting Properties menu item). Default is false.

Search web part properties:

SearchMode - specifies the default mode of Search web part. Available modes are General and Advanced. Default is General.
LimitToRootPath - Specifies whether search results are limited to folders under RootPath. Has no effect if RootPath property is not specified for controls with same ConnectionId. Default is false.

Thumbnail View

Jo West — Mon, 19 Apr 2010 13:22:00 GMT

In the new release of ProjectWise Select Series 1 (Refresh update) - version 08.11.07.133, users can see document thumbnail in the document list window by enabling View=>Thumbnail. "Preview Pane" can be turned off to recover more screen space.

After document thumbnail has been extracted, users can see document thumbnails in ProjectWise Explorer client by enabling "Preview Pane". The "Preview Pane" is at the bottom of the screen, it does however occupy screen space.