Using Pulsonix EDA software with the PDXpert server

Last update 2018-02-15

PDXpert provides a centralized repository of your part and document data, including management of approved supplier sources, bills of materials, revisions, and engineering changes. A "live view" of your part and supplier data is available from the PDXpert database via an ODBC connection.

This note links PDXpert PLM software's server-side public views to Pulsonix™ EDA component libraries.

Topic contents

Applies to PDXpert PLM server software release 11.0 and later.

• PDXpert PLM software support for printed circuit board (PCB) design
• Deciding how to connect Pulsonix EDA software to the PDXpert database
• Creating custom attributes for Pulsonix EDA software
• Exploring PDXpert software's public database views
• Creating user-defined views using the public views
• Connecting Pulsonix EDA software to the PDXpert database
• Final comments

PDXpert PLM software support for printed circuit board (PCB) design

PDXpert PLM software is built on the Microsoft SQL Server database engine, which provides powerful, scalable data management capabilities. Data records within the PDXpert database can be accessed using a stable database view using an ODBC connection. In addition to basic part and document attributes, PDXpert offers published read-only views that include custom attributes, qualified component sources and even bill of materials parent/child lists.

Product data is contained in the server machine's Microsoft SQL Server database, and a subset can be enabled on the client workstation. Server-side views provide a richer and more flexible view of your product data than client-side views.

Advantages of using SQL Server on the server computer:

• All queries are processed by SQL Server, which can use multiple concurrent processes.
• Data obtained from the server has no refresh delay.
• All public views (not just the SQLite-compatible subset) can be used in queries.
• Queries can use SQL Server's full range of built-in data manipulation functions.
• Table column names are somewhat more flexible for Custom Attribute names.
• The local workstation doesn't require an active PDXpert client to obtain current data.
• SQL Server offers powerful development tools with better documentation, support and complementary products.

By assigning appropriate custom attributes to PDXpert part type templates, components managed within PDXpert can support external computer systems. In particular, attributes required by Pulsonix EDA software—such as component names, search attributes, and source part numbers—can be created and managed within the PLM system.

For more information about PDXpert's ODBC views, refer to Contents > How to report, import & export > Using ODBC for export in the PDXpert help guide.

Creating custom attributes for Pulsonix EDA software

Before connecting Pulsonix EDA to the PDXpert database, you'll first need to define custom attributes on PDXpert parts to link to the Pulsonix libraries.

Ensuring useful and valid custom attributes

Parts in the PDXpert database have many standard attributes like part owner, type, number, description, unit of measure, and cost; these should not be duplicated in the custom attributes.

Each PDXpert part can include an approved set of supplier parts on the Sources tab. These sources are brought into the SourceItemMasterView (or can be derived from other public views), so you should not create custom attributes that duplicate the part's source data.

Where different part types have identically-named custom attributes, these will be merged into a single column of the SQL query results. For example, if your Resistor and Capacitor part types both have a custom attribute named Value, the resistor value (say, 22K) and capacitor value (say, 10nF) are merged into the view's single Value column.

Read the PDXpert help topic Contents > Collections reference > Custom attributes to learn about naming and managing custom attributes.

Designing PDXpert part types for Pulsonix search

You have some choices in how you define new part types:

• If you prefer a small set of custom attributes that are shared across all items (e.g., Category, Value, Tolerance, Rating, Package, PulsonixName), then these can be created within a single PDXpert Part Types collection member, say Component. The Category can be the Look In: selector for your Pulsonix EDA searches. This option is easy to manage, as well as being a useful complement to non-significant part numbering.
• You can also create a series of PDXpert part types, each of which can have both common attribute names (like Value, PulsonixName) as well as unique attributes (Rating, Tolerance, Dissipation, Gate Count, Forward Drop, etc.). If you're using category-based part number prefixes, then you may already have many of the types defined.

Define each part type and its custom attributes

In this application note, we'll create separate Part Types collection members for Capacitor, Connector, Resistor, etc. By defining distinct PDXpert part types, you can include custom attributes that are specific to each part type. In addition, there's no need to create a custom category attribute, since the Pulsonix Look In: selector is simply the part type's name.

A supplier part is created as a Manufacturer Source part type with its Item Files list containing an external link to its datasheet. This link is shown in the Pulsonix search results, and opened with the context menu Execute Link command.

The following example defines the Pulsonix data columns for resistors and capacitors (other part types would be similar).

Pulsonix column1	Type: Resistor	Type: Capacitor
Type	Standard part Type	Standard part Type
Number	Standard part Number	Standard part Number
Description	Standard part Part name (description)	Standard part Part name (description)
PulsonixName2	Custom attribute as Data:String	Custom attribute as Data:String
Value3,4	Custom attribute as UOM (Resistance: R, K, M  or  ohm, kohm, Mohm5)	Custom attribute as UOM (Capacitance: pF, nF, uF, mF, F)
Tolerance	Custom attribute as UOM (Proportion: %)	–
Power	Custom attribute as UOM (Power: uW, mW, W, kW)	–
Package	Custom attribute as Custom:Packages (collection)	Custom attribute as Custom:Packages (collection)
Voltage	–	Custom attribute as UOM (Voltage: uV, mV, V, kV)
Manufacturer	Manufacturer Source part Owner	Manufacturer Source part Owner
ManufacturerPN	Manufacturer Source part Number	Manufacturer Source part Number
Datasheet	Manufacturer Source part Item Files external link	Manufacturer Source part Item Files external link
Part name template6	Resistor, {Value}{Value++} {Tolerance}{Tolerance++} {Power}{Power++} {Package++}	Capacitor, {Value}{Value++}, {Voltage}{Voltage++}, {Package++}

Notes:

1. The names, schemes, default values and Part Types' Part name template can be different from this example. For instance, you may want to include the part's release state or a custom Capacitor categories collection with members like Ceramic-X7R, Electrolytic-aluminum, etc.

2. The Pulsonix part library's Part Name combines a schematic symbol and PCB layout footprint with other attributes. This Part Name is matched in the PDXpert database with the PulsonixName custom attribute. However, you can omit this custom attribute if the Pulsonix look-up value uses the PDXpert part number, the Global Number, or some other unique part value.

3. If you intend to use the Pulsonix Interpret As settings, then units of measure should conform to the requirements in the Pulsonix help file topic: Database Connection Setup > 'Interpreting As' Values (search help for Interpret As).

4. Although PDXpert can support Unicode characters (like Ω and µ), searching is much easier if users need only standard keyboard characters. Some ODBC drivers and CAD clients may be limited to ANSI alphanumeric characters.
5. Units of measure are case-insensitive, so the milliohms abbreviation cannot also be used: mohm is equivalent to Mohm.
6. If you want to merge the custom attribute values into the part name, enter the part name template text on the Part Types collection member's Attributes tab.

Your part type for the Resistor collection member might look like the following:

Create a few components and their sources for testing. The Pulsonix example library contains a resistor definition like this (this record is changed from the original's R 0.400 footprint):

In the following image, PDXpert home part 200480 is sourced from partner Rohm. The part is linked to the Pulsonix library part above using the PulsonixName attribute.

Deciding how to connect Pulsonix to the PDXpert server database

Your PDXpert Server may use SQL Server Express LocalDB, which improves database security by blocking all remote connections. You can confirm whether PDXpert uses SQL Server Express LocalDB by looking on the PDXpert Server's Information tab, in the SQL Server textbox, for the word LocalDB.

To allow your CAD tool to read the database, install SQL Server Express (or higher). After installation, use the PDXpert Application Server's Change Database Server button to move the existing database into the new instance.

SQL Server's TCP/IP protocol must be enabled, and the firewall and antivirus on both client and server computers must allow connections to SQL Server.

There are several alternatives for attaching the Pulsonix EDA electronic CAD application to PDXpert's SQL Server database.

• Connect Pulsonix EDA directly to the PDXpert public views, such as SourceItemMasterView. This is the simplest connection to create and manage. Rows that aren't useful to your electronic design (such as assemblies and mechanical parts) can be hidden from Pulsonix when the PulsonixName is empty. However, a public view will have many columns that you must hide during the Database Setup. More important, a single public view may not include every useful column; for example, the source part's Item Files datasheet link is available only if you use a SQL JOIN.

• Create PDXpert Views collection member(s), which overlay the PDXpert public database views, using SQL Server Management Studio (SSMS). The views appear as normal tables to Pulsonix EDA.

  If you're comfortable using SSMS to access a SQL Server database, this approach is powerful and flexible. SSMS can be downloaded from the Microsoft website.

  

This application note describes the second option using SQL Server Management Studio.

Exploring PDXpert software's public database views

The PDXpert database contains many objects, but only public views (with a View suffix) and user-defined views  (with a My prefix) typically remain stable during PDXpert upgrades. Queries should avoid database tables and private views (which have a _View suffix).

Custom attributes appear as dynamic columns within the public PDXpert views, alongside the standard columns like Owner, Number, Type and Description.

Use SQL Server Management Studio to browse the public views. In particular, examine the public SourceItemMasterView, which is derived from the more basic public ItemMasterView part & document list. The SourceItemMasterView is a side-by-side grouping of your company-defined parts (and documents), along with up to 3 qualified sources. All items include columns for the custom attributes.

Public views in the PDXpert database have a comprehensive set of columns. For CAD applications, you'll only need to include columns that are related to part identification, CAD application support, and possibly supplier data. The test parts can be viewed using this query:

See SQL Server Query Fundamentals for more details on how to construct your queries.

Creating user-defined views based on the public views

Never attempt to create new records, or update or delete existing data, with user-defined views. Use only SQL SELECT statements to read data.

Do not use SELECT * in your actual queries. Always specify the exact columns required so that database updates don't add unexpected new columns.

Define a PDXpert view for each Pulsonix part type

You can split the parts into separate tables by creating multiple views (MyResistors, MyCapacitors, etc.) using SQL filters like WHERE Category_Member='Resistor'.

You can build your user-defined views on the test SQL SELECT query (above).

After all part types have been defined, create the Views collection members. If you're saving SQL Server views within the PDXpert database, you'll create a new member of the Views collection within the PDXpert Collection Explorer, and name your view beginning with the characters My (as in MyResistors).

In the following example, a user-defined view (MyResistors) uses the PDXpert public view viewer.SourceItemMasterView to get the Prt and its first source. The source is left-joined with viewer.FilePairMasterView to get the datasheet link, if any. Identity columns from home part records are selected with Type='Resistor'. All data is CAST( ) to ensure it's usable text for the Pulsonix ODBC connection. Column names assigned within PDXpert (for example, [FileName]) can be relabeled within Pulsonix (AS [Datasheet]), using SQL's syntax [PDXpert column] AS [Pulsonix label]. Columns can be moved as needed. 

After you've constructed the first view, the views for other Pulsonix libraries, such as MyCapacitors for the capacitors library, are quite similar. Specifically, use the appropriate part type custom attributes for each library (such as [Voltage] instead of [Tolerance]) and apply the correct part type filter (e.g., WHERE Prt.[Type]='Capacitor').

For information about PDXpert software's ODBC views, refer to the help guide Contents > How to report, import & export > Using ODBC for export > SourceItemMasterView column reference and FilePairMasterView column reference topics.

Note the CAST(... AS nvarchar(250)). Some text fields in the PDXpert database can be larger than an ODBC client can handle. If you see a column result with a warning WIDEMEMO or MEMO, or an empty column where you expect text, then use the CAST( ) statement to constrain the field to a compatible size. Or, simply CAST( ) every string. If your ODBC client is Access, you can convert from within Access using the CStr( ) function.

Do not use an ORDER BY clause; your ODBC client should sort data search results. According to Microsoft, the ORDER BY clause is not valid in views unless the TOP clause is also specified.

If the part type Resistor is not always a home part, then change the filter to include WHERE [IsHomePart] = 1.

Connecting Pulsonix EDA software to the PDXpert database

You've created your part types and defined their custom attributes, and designed the queries to extract your component data. Now connect Pulsonix to the PDXpert server's SQL database.

The PDXpert ODBC connection is described in the Using the server-side SQL Server with Microsoft's ODBC driver help topic. We recommend using the Microsoft ODBC Driver 13.1 for SQL Server, or higher.

The Pulsonix Database Connection Users Guide describes the database connection setup procedure. Check for possible changes since this application note was written.

Create a "File DSN" if you want to share an ODBC Data Source configuration with other users who have the same driver installed.

An example database connection setup, which links the Pulsonix example resistors to parts in the PDXpert database:

The database search within Pulsonix may look like:

Final comments

Exit the Pulsonix software or disconnect the ODBC connection before editing your SQL views. Your queries will need to be reviewed, and possibly repaired, if you make changes within the PDXpert collections. For instance, the example SQL statement will fail if you remove or rename a custom attribute, or rename the Resistor part type. Make changes to your PDXpert collections cautiously to minimize reworking your user-defined views.

Finally, it bears repeating that your Pulsonix SQL queries must only extract data using SELECT statements.

Access, Excel and Microsoft are trademarks or registered trademarks of Microsoft Corp. Pulsonix is a trademark of WestDev Ltd.

This application note was relevant to the PDXpert software release that was current at time of publication. Product changes since that time may affect its utility. We'd be happy to assist you in assessing the applicability of this note to your situation.