Power Line Systems, Inc. 610 N. Whitney Way, Suite 160
Madison, WI 53705, U.S.A.
Phone: (608) 238-2171, Fax: (608) 238-9241
Email: info@powline.com
Home Search News Products

Integrating PLS-CADD with a Database

PLS-CADD can easily handle your material and labor accounting needs. Additionally, it can integrate with your existing databases to extract material, labor, and equipment units, as well as assemblies and "super" assemblies (pole top assemblies, compatible units, etc.). This TechNote will illustrate the methods used to link PLS-CADD to an Access database and will enable you to use the same techniques to integrate PLS-CADD with your database.

PLS-CADD uses Open Database Connectivity (ODBC) to integrate with your database. ODBC is a widely accepted application programming interface (API) for database access. ODBC allows users to "connect" into their existing databases such as Microsoft Access, Oracle, IBM DB2, Informix, Sybase and most other database software written in the last decade. We won't delve too deeply into ODBC in this document, but if you would like to learn more, visit Microsoft's ODBC home page at www.microsoft.com/data/odbc/. This capability allows users to integrate PLS-CADD with existing databases for materials and inventory management, labor and work order generation, construction equipment requirements and scheduling. ODBC even allows most common Geographical Information Systems (GIS) to instantly acquire project information in accordance with the latest design that has been developed in PLS-CADD. By using ODBC, there is no need to reinvent the wheel at your utility by rebuilding a database that you have probably already spent thousands of hours developing, populating and verifying. One very large utility in the Southeastern U.S. recently spent months (and many dollars) trying to reinvent such a wheel with another program and finally gave up. After discovering PLS-CADD's ODBC capability, they were able to implement the exchange of information in one afternoon. So, before you spend hundreds of thousands of dollars letting another company try to reinvent your existing database, why not spend a few minutes browsing this page and discover how you can implement this in your utility today at very low cost both in terms of time and money.

PLS-CADD Materials and Assemblies Functions

PLS-CADD has featured material and labor accounting for nearly 10 years now. If you are unfamiliar with these capabilities, or would like to just review what they are, please visit our PLS-CADD Materials TechNote page.

Existing Materials and Labor Database

The second part of the equation required to integrate PLS-CADD with a database is the database itself. For this TechNote, we have developed an example database with Microsoft Access that demonstrates many useful concepts. You may download this example database and make free use of it. Please note that our example requires Access 2000 and will not work on earlier versions. In it's present form, it can be used on a project by project basis or it could be modified to become part of a larger more sophisticated multi-project database. Please keep in mind that our intent is to demonstrate the simplest possible database and so we cannot promise that this is the best possible database design. This example is intended as an exercise to spur further development on your part and to show what a regular engineer can achieve when armed with a "Access for Dummies" book and a weeks worth of time. If you would like to download that database for reference, or would like to understand what is involved in this database before moving on, please visit our Example Database TechNote page.

ODBC Operations

Now that we have an understanding of how PLS-CADD handles material, labor and assemblies, and how that data can be presented, let's discuss how to automatically populate and maintain PLS-CADD with the various materials, labor, equipment units, and assemblies from our example database. The simplest way to do this is to obtain a spreadsheet or database table with the data presented in the columnar format that you have setup in PLS-CADD and then simply Copy/Paste that data into PLS-CADD. This is a great way to populate the tables on a one-time basis. However, prices change, manufacturers change, catalog numbers change, etc., which makes an automated electronic link between PLS-CADD and your spreadsheet or database program more desirable than a one-time Copy/Paste function. ODBC technology provides the link that makes it easy to have any changes made in your existing materials program reflected in your PLS-CADD projects. The ODBC functions are all reached under the Structures/Material menu list in PLS-CADD. There are four menu items that are applicable here: "Configure Parts Database", "Download Parts List from Database", "Upload Assemblies to Database", and "Download Assemblies from Database".

The first thing we need to do is configure the link between PLS-CADD and your desired database. When selecting the "Configure Parts Database" menu item, you will be prompted with a dialog box similar the following one.

As the dialog box above is actually displayed by your ODBC drivers, it may vary depending on which operating system and ODBC drivers you have installed on your computer. If you have difficulty at this stage, please see your computer services department for help. Now, select the proper data source for your database. You may need to install a driver for your desired database or spreadsheet program. Again, please see your computer services department for assistance.

Once the database source format has been selected, you will now need to locate the actual database. In this example, the database was created in Access and resides on a local hard drive under the Projects directory. This could just as easily be an Oracle database on your network - that is the beauty of ODBC - you are not locked into any one database or vendor.

Once the database is found and selected, you will be prompted with a dialog box similar to the one that follows. Find the tables in your database that contain the information you want to link into PLS-CADD, and then simply map the various columns of that table to the appropriate ones in your PLS-CADD project. Note the option to store your password so that this linkage can be invoked automatically in the future. If you want to prevent unauthorized users from making modifications to your project, you can simply choose not to have the password stored.

The mapping of PLS-CADD to your database is stored with your project once you have completed it. To synchronize PLS-CADD with your database for the first time or at anytime in the future, simply choose the Structures/Material/Download Parts from Database menu item and quicker than you think (usually in less than a second), it is complete. Now look at your Structures/Material/Edit Parts List to see what data was exchanged.

In addition to synchronizing parts, many users already have assemblies (compatible units) built in their database or spreadsheet program that they wish to synchronize with. Upon selecting the Structures/Material/Download Assemblies from Database menu item, you will be prompted to define the database type and find the database similar to configuring the parts linking above. Unlike a part list, assemblies must be stored in the Power Line Systems format documented below if they are to be imported with the built in Structures/Material/Download Assemblies from Database command. This format is also used when the Structures/Material/Upload Assemblies to Database command is invoked to write assemblies into the database. The PLS format "flattens" assemblies into four columns as shown in the Microsoft Access table below. Data in each column are also described below:

A row contains a single part for an assembly. An assembly will have as many rows as it has parts. In each row the PLS ASSEMBLY STOCKNO and PLS ASSEMBLY DESC fields will be duplicated and must be the same for all parts in the same assembly. This table format can easily be created in your database program with a simple query table. The appropriate table from our example database follows:

By setting up your ODBC linkage properly as described above, you will have created a circular reference between PLS-CADD and your database. Any changes made in your database to parts or assemblies will be modified in your PLS-CADD project if the user selects the options to Download Parts List and Download Assemblies. If changes are made in PLS-CADD to either the parts or assemblies, the user can update the database by simply selecting Database Export from the Parts Table and Upload Assemblies from the Structures/Material menu.

Many utilities structure their work flow such that transmission designers are not allowed to directly access the corporate database. For this reason, we have provided you with a separate program, PLS-DB, which handles all the database integration functions independent of the full PLS-CADD program. PLS-DB is a stand alone executable that does not require a hardware key. You are free to copy this program on to as many computers as you like or on to your network. This program ships with every version of PLS-CADD, so if you have PLS-CADD on your machine, you already have PLS-DB. PLS-DB allows your database department to perform all of the database functions independent of PLS-CADD and thereby maintain any security level that they desire.

What's Next?

Once we have the linkage of parts and assemblies between PLS-CADD and a database, what's next? First of all, by linking to an existing database, we are guaranteed that our pricing and other important information are as current as our material database is. Any changes made by material coordinators or others to the database will automatically be reflected in PLS-CADD the next time you choose the Structures/Material/Download Parts from Database menu item. Second, any table generated by PLS-CADD can be exported directly into a database (or spreadsheet) program. This is where the fun really begins.

Referring to our earlier generated Staking Material Table (Lines/Reports/Staking Material Table), clicking in the upper left hand corner opens the dialog box referenced earlier. Selecting the last option, Database Export, you will be presented with the select database source and the database open dialog boxes.

Once the table has been exported, the possible uses of this data are infinite. Work orders, material requisitions, equipment requirements, FERC accounting reports, and even REA/RUS design forms can be generated automatically generated.

In addition to the Staking Material Table, the Staking Table (Lines/Reports/Staking Table) can also be exported to your database for a project. This table contains all the information required to locate structures in your coordinate system (assuming that you use the same coordinate system in your PLS-CADD project as your database system is based on). Many popular GIS programs such as ESRI offer ODBC (or SQL) support and those programs can be set up to link into your database to populate your GIS base maps with your PLS-CADD designs. If you query your Staking Table and Staking Material Tables, you can even obtain a material listing at each structure location inside your GIS program.

Finally, using PLS-CADD's survey data import and graphical support functions (see USGS in PLS-CADD TechNote, for example), any data that you have in your GIS system can be imported into PLS-CADD as well. Again, the possibilities are endless as to what can be done. The only limit to what you can achieve is your desire to integrate PLS-CADD with your GIS and database systems.

In conclusion, we hope that we have helped you to understand how simple it is to integrate PLS-CADD with your existing GIS and database systems. Numerous utilities have already done this successfully and they have been amazed at how easy it was. So, before you go out and spend many hundreds of thousands of dollars letting someone else reinvent your wheel (again), why don't you give PLS's integration techniques a try first and tell the boss you want half the savings you've created for your next years bonus.

© 2000 Power Line Systems, Inc. All Rights Reserved.