PDXpert PLM Software
Application Notes
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.
Your PDXpert Server may use SQL Server Express LocalDB, which improves database security byblocking all remote connections. You can confirm whether PDXpert uses SQL Server Express LocalDB by looking on the PDXpert Server's Information tab, in theSQL 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 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 toContents > How to report, import & export >View & export via ODBC in the PDXpert help guide.
Before connecting Altium Designer to the PDXpert database, you'll first need to define custom attributes to support Altium Designer's libraries.
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 theSources 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 yourResistor and Capacitor part types both have a custom attribute namedValue, 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.
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 PDXpertPart 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 guideContents > How to report, import & export >Using ODBC for export >ItemMasterView column reference and > SourceItemMasterView column reference topics.
The PDXpert database contains many objects, but only public views (with aView 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 publicSourceItemMasterView andSourcePairMasterView, 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 theBatch Importer tutorial) select all part records, but we'll be filtering out much of the data:
SELECT *
FROM [PDXpertDB].[viewer].[SourcePairMasterView]
WHERE [Class] = 1
SELECT *
FROM [PDXpertDB].[viewer].[SourceItemMasterView]
WHERE [Class] = 1
Do not use SELECT * in your actual queries. Always specify the exact columns required.
This first example relies on a single Component part type for all CAD-related parts (e.g., resistors and capacitors), and anAltium 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 ownComponentLink 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 | StringResistor-Std | StringCapacitor-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:
SELECT CAST([Number] AS nvarchar(250)) AS [Number]
,CAST([Description] AS nvarchar(250)) AS [Description]
,CAST([Altium Category_Member] AS nvarchar(250)) AS [Category]
,CAST([Footprint Path] AS nvarchar(250)) AS [Footprint Path]
,CAST([Footprint Ref] AS nvarchar(250)) AS [Footprint Ref]
,CAST([Library Path] AS nvarchar(250)) AS [Library Path]
,CAST([Library Ref] AS nvarchar(250)) AS [Library Ref]
,CAST([OwnerShort_Child] AS nvarchar(250)) AS [Manufacturer]
,CAST([Number_Child] AS nvarchar(250)) AS [Manufacturer P/N]
,CAST([Datasheet_Child] AS nvarchar(250)) AS [ComponentLink1Description]
,CAST([Datasheet URL_Child] AS nvarchar(250)) AS [ComponentLink1URL]
FROM [PDXpertDB].[viewer].[SourcePairMasterView]
WHERE [Altium Category_Member] IS NOT NULL
Or you can create a PDXpert Views member (e.g., a MyResistors view) for each Altium category just by changing the SQL WHERE clause:
...
WHERE [Altium Category_Member] = 'Resistor'
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.
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.
Again, all supplier parts share a common Source part type with its own datasheet link that becomes the AltiumComponentLink 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 anItem 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.
After all part types have been defined, create theViews collection members for Altium.
In the following example, a user-defined view (MyResistors) uses the PDXpert public view SourceItemMasterView (to get thePrt 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 withType='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.
SELECT
CAST(Prt.[Number] AS nvarchar(250)) AS [Number]
,CAST(Prt.[Description] AS nvarchar(250)) AS [Description]
,CAST(Prt.[Value] AS nvarchar(250)) AS [Value]
,CAST(Prt.[Tolerance] AS nvarchar(250)) AS [Tolerance]
,CAST(Prt.[CostPerUnit] AS nvarchar(250)) AS [Price]
,CAST(Prt.[Footprint Path] AS nvarchar(250)) AS [Footprint Path]
,CAST(Prt.[Footprint Ref] AS nvarchar(250)) AS [Footprint Ref]
,CAST(Prt.[Library Path] AS nvarchar(250)) AS [Library Path]
,CAST(Prt.[Library Ref] AS nvarchar(250)) AS [Library Ref]
,CAST([OwnerShort_1] AS nvarchar(250)) AS [Manufacturer]
,CAST([Number_1] AS nvarchar(250)) AS [Manufacturer P/N]
,CAST(Src1.[Datasheet] AS nvarchar(250)) AS [ComponentLink1Description]
,CAST(Src1.[Datasheet URL] AS nvarchar(250)) AS [ComponentLink1URL]
,CAST([OwnerShort_2] AS nvarchar(250)) AS [Manufacturer2]
,CAST([Number_2] AS nvarchar(250)) AS [Manufacturer2 P/N]
,CAST(Src2.[Datasheet] AS nvarchar(250)) AS [ComponentLink2Description]
,CAST(Src2.[Datasheet URL] AS nvarchar(250)) AS [ComponentLink2URL]
FROM
[PDXpertDB].[viewer].[SourceItemMasterView] Prt
LEFT JOIN [PDXpertDB].[viewer].[ItemMasterView] Src1 ON [ItemId_1] = [Src1].[ItemId]
LEFT JOIN [PDXpertDB].[viewer].[ItemMasterView] Src2 ON [ItemId_2] = [Src1].[ItemId]
WHERE
Prt.[Type] = 'Resistor'
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 asMyCapacitors 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, theORDER BY clause is not valid in views unless the TOP clause is also specified.
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 theField 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.
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.
To display and place parts from the part list source, Altium
- presents the results of your part search,
- displays the part detail each time you select a row, and
- 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 toSources 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.
A materialized view is a database table that contains a snapshot of a view, after all joins have been performed.
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!
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 aPDXpert 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 thePDXpertDBStatic.mdf/.ldf database files within a \\Static\\ folder parallel to the \\Data\\ folder.
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 theCreating 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.
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 theUse 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.
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.