How to Link to a P&ID Database
Instrument Manager provides the ability to "link" to external database tables, for example P&ID data tables.
Linked data can be viewed from inside Instrument Manager but the data remains in the external linked database.
Our current policy is to maintain a one-way link from the P&ID to Instrument Manager. Changes to the instrumentation in the P&ID will be seen in Instrument Manager but Instrument Manager cannot make changes to the P&ID.
When an external P&ID database table is linked to Instrument Manager, a component is created in Instrument Manager for each record in the linked P&ID table. The component in Instrument Manager then has both data in the Instrument Manager database and maintains a link to its record in the external P&ID database.
Requirements for a P&ID table to be linked
P&ID database tables linked to the Instrument Manager project have certain format requirements:
-
Each record in the table to be linked with must represent a single P&ID component. This follows the structure in which Instrument Manager stores components in the project database and is a basic requirement of the system.
-
The linked data set must contain the tag or name of the components in one or more of its columns. The component tag or parts of the tag cannot be in a different data set.
If the data for your components is divided between different tables in the P&ID database then you must use a link map so that you can link to a view or query; you cannot create a simple link via wizard choices as that can only link to a single table.
-
The linked table must contain a column that is of the SQL Server 'integer identity' type or Access 'AutoNumber' type. The data in this column is used by Instrument Manager to uniquely identify records on the linked table. If the database table to be linked does not contain a column of this type, then it must be added using a database package. The data in this column is automatically assigned and does not need to be maintained by Instrument Manager or any other database client software.
-
If the P&ID database is a SQL Server database, then you will need to enable the Instrument Manager user the permission to "View Server State" on the server hosting the P&ID database. This is required for Instrument Manager to detect changes to the linked P&ID data. If you do not have this permission then you will get error messages stating that you do not have the permission to read the system view [sys].[dm_db_index_usage_stats].
Considerations for AutoCAD P&ID/Plant 3D projects hosted in the Autodesk Cloud (eg BIM 360)
Autodesk does not make their cloud-based P&ID databases directly accessible. Instrument Manager instead relies on connecting to a local SQLite cache of the P&ID database, typically stored in a location such as C:\Users\username\AppData\Local\Autodesk\AutoCAD Plant 3D\CollaborationCache\projectname\ProcessPower.dcf. This local cache is only kept up to date while AutoCAD Plant 3D is running and has the applicable project loaded.
As such, when using Instrument Manager with a project connected to a cloud-based P&ID database:
- It is important that users have Plant 3D running and the Plant 3D project open, in order to show and use up-to-date P&ID data in the interface and output diagrams/reports.
- P&ID data changes by other users may still be some minutes out of date, as Plant 3D has its own time-interval based sychronisation with the data in the cloud.
- While P&ID data fetching can remain enabled (as it simple shows data fetched from the P&ID database), P&ID synchronisation should be disabled during multi-user use, as the synchronisation can make changes to the Instrument Manager database (such as creating components), and this may cause issues if the local SQLite cache of the P&ID database differs between users.
Instead, P&ID synchronisation should only be enabled when necessary while only one user is using Instrument Manager, then disabled again when complete.
These considerations are not applicable if the Plant 3D project database is hosted on a local network SQL Server instead of in the Autodesk Cloud, as Instrument Manager can directly connect to a database in SQL Server.
Changes from previous versions
The structure of the linking table in the Instrument Manager database was changed in version 7.7.2 and does not support links made in earlier versions.
EDS version 8.0 introduces the ability to use link map files and has structural changes to the link table, but remains compatible with links made in EDS 7.7.2 and later.
Procedure to Link to a P&ID Database
-
Select from the menu in Instrument Manager. This will display the PID linking wizard.
-
Select the database to be linked. This can be a Microsoft Access database, SQL Server database, or SQLite database.
-
Click the button to continue.
-
Select a P&ID type, or a method by which the P&ID should be linked.
The links for P&ID types that are pre-defined use link map files that are supplied with Instrument Manager. These map files will link all of the component types known to be useful at the time. The names of the link map files are shown in the option. You may use these link map files as a reference if you are making a user-defined link map. You may modify these link map files to add further capabilities to the linking that is supplied for those P&ID types. More information is provided in Link Methods below.
-
If you choose the option , then you can click the button to pick your map file, or enter the file name directly in the box.
-
If you have chosen either of the pre-defined links or you have chosen , click the button to complete the procedure and skip to the bottom of this procedure. When a link map is used all of the remaining information about the link is specified in the map file, including the list of tables or views to be linked.
-
If you have chosen , click the button to continue.
-
-
Select the table that is to be linked from the chosen P&ID database.
The table that you choose must contain the name or TAG of the components in one or more of the columns of the table.
-
Click the button to continue.
-
Configure the link to the chosen P&ID database table.
-
You must choose a primary key field, which is the required integer identity or autonumber column.
-
Select the Instrument Manager component type for the components that are created from the records in the P&ID database table. The component types in the list are a selection of potentially compatible types from within Instrument Manager.
You can also choose to use a lookup mapping (the entry "<Lookup...>") to determine individually the appropriate Instrument Manager component type for each linked record from the value in the chosen "Type" column of the P&ID table.
-
Select the rating table to use for the components that are created from the records in the P&ID database table. The rating tables available in the list depends on the choice that was made for the component type. If the component type is a lookup mapping then the only option for the rating table is also a lookup mapping.
For instruments, a lookup mapping (the entry "<Lookup...>") is the most common choice for determining the rating table to use for the linked components. This choice will use a lookup mapping table to provide a rating table that corresponds to the instrument type in the chosen "Type" column of the P&ID table.
-
If either the component type or the rating table are using a lookup mapping (the entry "<Lookup...>") then you must choose a "Type" column and the lookup map file. The lookup mapping is always used in such a way that the same lookup map file can be used to determine both the component type and the rating table from the same lookup map file. More information is provided in Lookup Mapping below.
-
In the example, the primary key column in the P&ID database table is "PnPID", the linked components will all be "Devices" in Instrument Manager, and the rating table for those devices will be determined by a Lookup mapping from a column named "Type" in the P&ID table referenced against a lookup table named "LookupForAP&IDEquipment.dbf".
-
-
Click the button to continue.
-
Configure the tagname formula for components in the linked table.
If the data in the column identified as containing the component name is made up of distinct parts, including parts from other columns in the record, a complex tagname formula must be used. This will associate the columns that make up the parts of the tagname with the column that contains the full tagname. When properly configured, Instrument Manager can ensure that the data stays consistent.
e.g. If the tagname of each component in the linked table consists of: a type, a hyphen and the id number of the component, then the tag formula could be configured as follows.
In the example, R2:Type is the column that contains the type of the component, and R2:Number contains the id number of the component. The two linked columns are separated by a hyphen as a delimiter. If the component were a pump with the Type column containing "P" and the Number column containing "201A", then the tag for the component in Instrument Manager would be set to "P-201A".
For more information on configuring the tagname, see Folder Tagname Formula. The procedure is identical.
-
Click the button to link the P&ID table to the project database.
-
Instrument Manager will then create a component from each record of the chosen P&ID database table.
If there are existing components in Instrument Manager with the same tags as those in the P&ID database table then those existing Instrument Manager components are linked to the P&ID table.
Link Methods
There are three methods to link to a P&ID database in the procedure above:
-
Pre-defined link for a P&ID type
This option results in the most linked information and is the easiest to use.
-
User-defined link map file
This option results in the most linked information but requires map file creation.
-
Wizard-generated link
This option results in the least linked information but guided via a wizard and works for any P&ID database.
If your P&ID is one of the types for which a pre-defined link is provided, then the link will result in the most information and yet is the easiest to use. For other types of P&ID, the wizard-generated link will allow you to make assisted choices with information in the dialog, however it can create only a simple type of link. If you need more capabilities for linking to a P&ID that is not pre-defined then you can create a map file. P&ID linking allows many of the features of importing to be used if a map file is used to make the link. The pre-defined links are simply links to types of P&ID for which a link map file has been pre-defined. They offer the same capabilities as using a user-defined link map file.
If a pre-defined or user-defined link map is used then the link can be made to a view or query from the P&ID database, rather than only from a table. Using a link map to link to a view or query is the only option if the data about your components is divided between different tables in the P&ID database.
Link map files are essentially the same as import map files. For more information see Import Map Files.
Lookup Mapping
The configuration for linking components from an external P&ID database table can use a lookup mapping to determine the type and/or rating table for the component that is created in Instrument Manager. A lookup mapping enables the type or rating table to be determined individually from each record that is linked.
Instrument Manager has a fixed configuration for a lookup mapping used for linking to a P&ID database table. The values from the chosen "Type" column from the P&ID database table will be compared to only a "CODES" column in the lookup map file. The component type if mapped must be in a "COMPTYPE" column in the lookup map file. The rating table name must be in a "RATING" column in the lookup map file.
For further information on lookup mappings, see Import Map Files.
Procedure to Unlink a P&ID Database
You can remove the link to a P&ID database table. During this procedure you can elect to either remove or retain the components that were previously linked.
-
Select "Unlink PID Database" from the "File" menu in Instrument Manager. This will display a list of the P&ID database tables that are currently linked to Instrument Manager.
-
Select one or more database tables to be unlinked.
-
Click .
-
You will be asked if you wish to remove the components that were previously linked to the P&ID tables that are being unlinked.
If you choose to not remove them then they will remain in the Instrument Manager project but will simply have their link to the P&ID database table removed. Their functionality in Instrument Manager and their Instrument Manager database data will remain unchanged, e.g. ratings and electrical connections are unchanged.
If you link to that P&ID database table again in the future then these same components will be re-linked.
You can link and unlink components freely without damage to the component data.
Viewing P&ID Component Data
After linking a P&ID database table, the components will appear in the Instrument Manager tree. The icon in the tree or list for a linked component will show the letters "PID" vertically beside it.
Instrument Manager can access data from the record from the P&ID table linked to the component using an R2: relationship. These R2: relationships can be used directly on output diagrams. To see the data as columns in the list or edit view of Instrument Manager you will need a relational alias for each column. See also Relational Columns.
Synchronising with Changes in the P&ID Database Tables
Instrument Manager will periodically check if there have been changes made to the external P&ID database tables.
If there have been changes to the data of the existing P&ID database table records that are already linked to components in Instrument Manager, then Instrument Manager will automatically show those changes if the columns are visible in the current view.
If there have been records added to the P&ID database tables that are already linked to Instrument Manager, then Instrument Manager will automatically create new components that are linked to the new P&ID database table records.
If there have been records deleted from the P&ID database tables that are already linked to components in Instrument Manager, then Instrument Manager will remove the link from the Instrument Manager component but it will not remove the component from the Instrument Manager database. If in future the same component name is restored to the P&ID database then it will be re-linked to the same component in the Instrument Manager database.
Disabling Synchronisation
Synchronising with changes in the P&ID database tables can be disabled, to prevent excessive changes in Instrument Manager while P&ID users are regularly updating the P&ID project and drawings.
Only one user is required to disable the synchronisation, because the change is applied to the database and thus all connected users will also have synchronisation disabled. Any user can re-enable the synchronisation for everyone at any time.
Disabling and enabling synchronisation is performed using the toggle option in the menu, or by using the toggle button on the toolbar: 
.
Disabling Fetching Data from the P&ID
You can disable fetching all data from the P&ID, including synchronising, for yourself. You would usually do this when you are temporarily unable to connect to the P&ID database, for example when you have a laptop containing a local Instrument Manager project but you are not connected to your company network. This setting does not affect other users, unlike the option to turn off synchronisation with P&ID data (as described above), as it relates to only your ability to connect to the P&ID database.
While fetching data from the P&ID is disabled:
Columns in the Instrument Manager view that show P&ID column aliases (see below) will remain empty.
Relational columns that fetch P&ID data on datasheets and other output diagrams that are generated during this time will remain empty.
Synchronisation with the P&ID database tables will be disabled implicitly.
Disabling and enabling fetching of P&ID data is performed using from the menu, or by using the toggle button on the toolbar: 
. Enable fetching P&ID data by ticking the menu entry, or by pressing the button in.
If Instrument Manager is unable to connect to the linked external P&ID database, it will automatically ask if you want to disable fetching external table data:
When you are once again able to connect to the P&ID database you must re-enable fetching external table data manually.
SQL Error referring to [sys].[dm_db_index_usage_stats]
If you get an error message from Instrument Manager that it failed to read from [sys].[dm_db_index_usage_stats] then you have not been granted the server permission "View Server State" that is mentioned in the requirements at the top of this page. You can find this permission in your SQL Server management tool under Server > Properties > Permissions, select a user and you will find "View Server State" at the bottom of the permissions list. This is required on the SQL Server hosting the P&ID database, which is not necessarily the same as the server hosting the Instrument Manager database.
P&ID Column Aliases
When one of the pre-defined links is made, Instrument Manager will also enable the use of a secondary table of relational-column aliases for the columns and tables in that type of P&ID database. You can edit these P&ID-specific column aliases via the menu entry: > .