Using Altium Designer EDA software with the PDXpert server

Last update 2016-11-14

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 outlines the method for using PDXpert PLM software's server-side public views to support Altium Designer's component libraries.

Topic contents

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. Be sure that your server's firewall and client's firewall allow communication between the CAD application and SQL Server.

Applies to PDXpert PLM software releases 7.0 or later. Custom collections are available in PDXpert release 8.1 and later.

This demonstrates Altium's use of the Microsoft SQL Server database on the server computer. You can also connect Altium to the client-side database using Microsoft SQL Server LocalDB; see the Altium client-side application note.

• PDXpert PLM software support for printed circuit board (PCB) design
• Designing custom attributes for Altium Designer
• Deciding how to connect Altium Designer to the SQL Server database
• Optimizing Altium Designer's data access
• Connecting Altium Designer to the SQL Server 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 virtually no refresh delay.
• All public views (not just the client-side subset) can be used in queries.
• The local workstation doesn't require an active PDXpert client to obtain current data.

By assigning appropriate custom attributes to PDXpert part type templates, components managed within PDXpert can support external computer systems. In particular, attributes required by Altium Designer — such as component values, schematic symbols, layout footprints 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 > View & export via ODBC in the PDXpert help guide.

Designing custom attributes for Altium Designer §

Before connecting Altium Designer to the PDXpert database, you'll first need to define custom attributes to support Altium Designer's 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. 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. This is useful because SQL Server has a limit on the total number of columns that can be presented in a view.

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

Defining your set of part types & their custom attributes §

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

• If you want a small set of attributes that are shared across all items (e.g., Category, Value, Tolerance, Package, Library, Footprint), then these can be created within a single PDXpert Part Types collection member, say Purchase. The Category is the selector for categorizing your Altium libraries. This is a very simple system to maintain, and a useful complement to non-significant part numbering.
• You can also create a series of part types, each of which can have both common attribute names (like Value, Library Ref, Footprint Ref) as well as unique attributes (WVDC, Tolerance, Tempco, Gate Count, Forward Drop, Package, etc.). The part type is the selector for your Altium libraries. If you're using category-based part number prefixes, then you may already have many of the types defined.
• Your supplier part types can be matched exactly to your own parts' types (supplier resistors use the same Resistor part type as your own resistors). However, the source's custom attributes wouldn't have much value, since they'd mirror your own part's custom attributes. More usefully, your supplier parts are created from a common Source part type.

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

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) remain stable from one release to the next.

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

Public views in the PDXpert database provide a comprehensive set of data columns. For CAD applications, you'll only need columns that are related to part identification, CAD application support, and possibly supplier data.

Use SQL Server Management Studio, Microsoft Access, or any ODBC client to browse the public views. In particular, examine the public SourceItemMasterView and SourcePairMasterView, which are derived from the more basic public ItemMasterView part & document list. Both views present a side-by-side listing of your company-defined parts (and documents) with one or more qualified sources. Home items include their custom attributes; these views trade off one supplier with custom attributes for three suppliers without.

These test queries (based on the Batch Importer tutorial) select all part records, but we'll be filtering out much of the data:

Do not use SELECT * in your actual queries. Always specify the exact columns required.

Designing one PDXpert part type to contain all Altium parts §

This first example relies on a single Component part type for all CAD-related parts (e.g., resistors and capacitors), and an Altium Category attribute to group the parts within Altium. Although this part type will usually represent purchased parts, it may include custom in-house items (e.g., custom-wound chokes).

Supplier parts share a common Source part type, and each source part points to its own ComponentLink details. The PDXpert SourcePairMasterView view can display one source with custom attributes, or SourcePairMasterView displays up to 3 sources for each part.

Design each PDXpert view to select a specific category (and exclude parts with an empty category value). Altium treats each view as a .DbLib library table. Since all part types share the same custom attributes, the default values would all be empty at the Part Types collection, and these would be example values applied to each Component part.

Custom attribute name	Type: Component	Type: Component	Type: Source
Footprint Ref	String Resistor-Std	String Capacitor-Ceramic	–
Footprint Path	String ./Discretes.PcbLib	String ./Discretes.PcbLib	–
Library Ref	String Resistor-Std	String Capacitor-Ceramic	–
Library Path	String ./Discretes.SchLib	String ./Discretes.SchLib	–
Altium Category	Custom collection Part Categories Resistor member	Custom collection Part Categories Capacitor member	–
Datasheet	–	–	String
DatasheetURL	–	–	URI

If you create a single PDXpert view (say, MyComponents) that selects all Altium-related parts, it's easy to group parts within Altium using the Category column header:

Or you can create a PDXpert Views member (e.g., a MyResistors view) for each Altium category just by changing the SQL WHERE clause:

The custom attribute labels assigned within PDXpert don't need to conform to Altium's requirements; we use SQL's syntax [PDXpert column] AS [Altium label]. Columns can be re-ordered as needed. Also note that CAST([Datasheet URL_Child] AS nvarchar(250)) limits the length of the ComponentLink1URL link.

Designing one PDXpert part type for each Altium library §

This alternative creates one Part Types collection member (Capacitor, IC, Resistor, etc.) for each Altium part library. With a more detailed set of PDXpert part types, you can include custom attributes that are specific to each part type. In addition, there's no need to create a category custom attribute, since we'll be selecting records based on the part type's name.

Define each part type and its custom attributes

Again, all supplier parts share a common Source part type with its own datasheet link that becomes the Altium ComponentLink data.

This example demonstrates that the source part can also have an independent set of custom attributes. However, simply adding the source's datasheet URL as an Item Files external link is usually better practice.

Custom attribute name	Type: Resistor	Type: Capacitor	Type: Source
Footprint Ref	String; default value Resistor-Std	String; default value Capacitor-NonPolarized	–
Footprint Path	String; default value ./Discretes.PcbLib	String; default value ./Discretes.PcbLib	–
Library Ref	String; default value Resistor-Std	String; default value Capacitor-NonPolarized	–
Library Path	String; default value ./Discretes.SchLib	String; default value ./Discretes.SchLib	–
Value	Component resistance as String, UOM or Float	Component capacitance as String, UOM or Float	–
Tolerance	String (%)	–	–
Voltage	–	String (WVDC)	–
Datasheet	–	–	String
DatasheetURL	–	–	URI

Create a few components for testing; in the following diagram, home part 100043 is sourced from partner Rohm.

Define a PDXpert view for each Altium part library

After all part types have been defined, create the Views collection members for Altium.

In the following example, a user-defined view (MyResistors) uses the PDXpert public view SourceItemMasterView (to get the Prt and its sources). It's joined with an ItemMasterView for Src1 and Src2 (to get the two sources' custom datasheet details). A few columns are selected from part records with Type='Resistor' (this assumes the type is only applied to your own organization's parts). All data is CAST() to ensure it's usable by the Altium ODBC connector.

Again note that custom attribute labels assigned within PDXpert don't need to conform to Altium's requirements; we use SQL's syntax [PDXpert column] AS [Altium label]. Columns can be re-ordered as needed. And, CAST(SrcN.[Datasheet URL] AS nvarchar(250)) limits the length of the ComponentLinkNURL link.

After you've constructed the first view, the views for other Altium 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').

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.

Deciding how to connect Altium Designer to the SQL Server database §

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 Altium Designer electronic CAD application to the PDXpert database.

• The simplest, although more limited, method is to connect Altium Designer directly to the PDXpert public views. All data would be selected using the Field Settings to define the matching criteria.

• From within Microsoft Access (or similar tool), link the PDXpert public view(s) as an ODBC external data source. Create the queries required by Altium Designer and then attach Altium to these derived library views.

  

• 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 Altium.

  If you're comfortable using SSMS to access a SQL Server database, this approach is powerful, flexible and simple to maintain. SSMS is included with higher-end versions of SQL Server, and a free Express version can be downloaded from the Microsoft website.

  

Optimizing Altium Designer's data access §

Test your Altium performance before using these techniques. PDXpert 9.0 (and later) materializes public views, which reduces the time required to execute user-defined views. Altium may also change their code in later releases to eliminate time-sensitive queries.

Altium has some quirks related to data search and retrieval, and you may need to optimize data access.

Problem: Altium/SQL Server response time §

To display and place parts from the part list source, Altium

1. presents the results of your part search,
2. displays the part detail each time you select a row, and
3. lets you drag a selected part from the results list.

Unfortunately, Altium appears to make redundant queries for select and drag. That is, rather than using the results from the initial search, on row selection the database is re-queried, and at the start of the drag re-queried again. While the initial search (step 1) usually offers acceptable performance, steps 2 & 3 can have annoying delays.

A query to SQL Server is expensive: each part row is a join of the item, custom attributes, current revision with a link to Sources list, and collection tables (such as owning organization, type, lifecycle, UOM, etc.). This is presented in a public view, then filtered by your user-defined part list view, and finally delivered across the network. Depending on the amount and complexity of your data, this can take many hundreds of milliseconds — perfectly acceptable for search results but unacceptable for real-time drag-and-drop.

Altium has confirmed that this problem is common to any data store that's not a simple Access/Excel table on the local machine. Throwing hardware or high-end databases at the problem isn't going to solve it: for remote database sources, Altium advises users to create a static table that's refreshed periodically.

Solution 1: Using materialized views (static tables) §

A materialized view is a database table that contains a snapshot of a view, after all joins have been performed.

1A: Using PDXpert's built-in materialized item view

In PDXpert 9.0 and later, all public views are materialized. Item joins to lifecycle, owning organization, trustee, unit of measure and other collection tables are resolved and saved in the materialized view. User-defined views remain dynamic.

Depending on your SQL Server edition, system load and network latency, this approach may provide near real-time performance.

Connect the ODBC client to the PDXpertDB database, as described in Contents > How to report, import & export > View & export via ODBC > Create an ODBC connection help topic.

If the mouse select-and-drag performance is acceptable, you're done!

1B: Creating your own materialized administrator-defined views §

This procedure must be assessed and possibly modified by an IT specialist to accommodate your specific hardware and software environment. Code examples are provided for use at your sole risk, may require modifications and should be thoroughly tested before placing into production.

If SQL Server's query performance is inadequate for real-time response, then create a PowerShell script to transform each dynamic administrator-defined (My...) view in PDXpertDB into an equivalent static table.

These materialized views must be saved in a new PDXpertDBStatic database. Schedule a Windows task to run this script (Copy-PDXpertMyViews-Static.ps1) every, say, 10 minutes. A second script (Run-CopyPDXpertMyViews.ps1) may be used to kick off the scheduled task asynchronously, to force an immediate refresh. The layered permissions of Windows, the PowerShell executive and SQL Server may require some user account tweaks.

We've written two prototype PowerShell scripts along these lines, which you're free to modify and use.

These Windows Task Scheduler properties may provide a useful starting point:

• Name: Copy PDXpert Views located within a PDXpert folder

• Security options: Run with highest privileges

• Trigger: Begin on a schedule, run once, repeat every 10 minutes indefinitely

• Action: Start a program as

  powershell.exe -nologo -noninteractive -command & ("{C:\path\Copy-PDXpertMyViews-Static.ps1}")

• Settings: Allow task to be run on demand. If the task is already running, do not start a new instance.

Any time you upgrade PDXpert, you should disable the scheduled task so that it can't run during the upgrade.

Based on initial testing, Altium's response on part row selection becomes quite acceptable, under 500 ms, even across a network. For maximum efficiency and to minimize the table refresh period, you may wish to use a single user-defined view for all part data, and filter the results within Altium using a custom attribute for part category.

Although using static tables is Altium's recommended approach, it's not perfect. There is latency from when a part record is created/changed in PDXpert to when it appears in the static table. There are also times when the data is unavailable — the brief period when a static table is dropped and replaced by the updated table. More frequent refreshes increase the load on SQL Server and the chance of an Altium search hitting the replacement period. We suggest starting with 10 minute refreshes, which yields an average 5 minutes latency and < 0.2% data unavailability.

PDXpert server relies on total ownership of the \Data\ folder, and therefore the script creates  the PDXpertDBStatic.mdf/.ldf database files within a \Static\ folder parallel to the \Data\ folder.

Solution 2: Using a static parts list file §

The previous solution has several advantages: the parts list is updated in near real-time and all Altium users are looking at the same data. However, the solution requires some experimentation with SQL Server and PowerShell. If you only have a few Altium seats, it may be simpler to periodically export the parts list as a CSV file, and distribute that to Altium users for use on their own machines.

After creating the query for your user-defined view, create a new Transforms member and copy the SQL statement into the <sql></sql> tag, as described in the Creating a custom Transform help topic.

Whenever an updated parts list is needed, run the Report/Export Wizard to export your data into a CSV file.

Connecting Altium Designer to the SQL Server database §

To connect Altium Designer to SQL Server using ODBC, refer to Altium's help topics (for example, Using Components Directly from Your Company Database) or contact Altium for technical support. The following ideas are provided as a useful starting point.

You've created your part types and defined their custom attributes, and designed the queries to extract your component data.

Now connect Altium Designer to the selected ODBC data source using a Database Library (*.DbLib) file. If you're connecting directly to a PDXpert database view, you'll likely select the Use Connection String option.

Altium appears to work well using the OLE DB Provider for SQL Server since this apparently excludes the schema from the library name.

Connection string:

Provider=SQLOLEDB.1;Data Source=MACHINE,PORTNUM;Initial Catalog=DBNAME;Persist Security Info=True;User ID=USERNAME;Password=USERPASSWORD

where:

• MACHINE is server machine name

• ,PORTNUM is SQL Server instance port number (can omit if there's only a single instance on the server [default value is 1433])

  • SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for PDXPERT > TCP/IP > Properties > IPALL > TCP Dynamic Ports > PORTNUM or
  • SQL Server Management Studio > MACHINE\INSTANCE > Management > SQL Server Logs > Current > Server is listening on [... <ipv6> PORTNUM]

• DBNAME is the destination database, usually PDXpertDB (Option 1A, above) or possibly PDXpertDBStatic (Option 1B, above)

• USERNAME is viewer log-in name, usually PDXpertViewer

• USERPASS is viewer password, usually By2Go4Me8

For example (actual parameters may be in a different order):

Provider=SQLOLEDB.1;Data Source=PDXPERTSRVR\PDXPERT;Initial Catalog=PDXpertDB;Persist Security Info=True;User ID=PDXpertViewer;Password=By2Go4Me8

After the connection has been established, specify the appropriate matching criteria as described in the Altium Designer help topic.

Final comments §

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 SQL queries must only extract data using SELECT statements. Do not attempt to update or delete data within the PDXpert database.

Access, Excel and Microsoft are trademarks or registered trademarks of Microsoft Corp. Altium and Altium Designer are trademarks or registered trademarks of Altium Limited or its subsidiaries.

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.