Guide to the Petro-SIM Database for the DBA
Petro-SIM is a process simulation product from KBC used for designing, simulating and optimising upstream, midstream and downstream process facilities. The product is a conventional program running on a Microsoft Windows desktop supporting two methods for data storage. One is a conventional file system model where Petro-SIM case files can be thought of in the same way as Microsoft Office documents like .docx and .xlsx files: Petro-SIM primarily uses a proprietary binary file type with a ksc extension. The second method using relational database for case and data storage is the subject of these pages. We have written this content with the database administrator in mind and the aim of giving you the DBA enough information that you can set-up, understand and administer Petro-SIM Databases.
Supported Platforms
Petro-SIM supports two main multi-user database platforms: Microsoft SQL Server version 2005 upwards and Oracle version 9g upwards. The program should also support the Express editions of these databases although we do not extensively validate against them. We make use of the following drivers:
SQL Server
Petro-SIM accesses SQL Server through the Microsoft SQLNCLI OLEDB driver in both 32-bit and 64-bit versions, installing it where missing on the client machine. The program supports authentication through native SQL Server logins and Windows authentication, often calling this SSPI. We typically recommend using Windows authentication for most users, reserving SQL Server logins for read-only or DBOwner roles.
Oracle
To be added
Data Model Overview
The database structure follows a recursive object-relational model with relatively few primary tables containing the bulk of the data. It uses a number of standard views to aid reporting, alongside a set of supporting reference data tables. Relationships between the primary tables are shown below:
We do add fields to tables from time to time so please be aware that the structure of your database may differ slightly from what is shown in this diagram.
Datatypes
The database uses integer IDs for most of the primary and foreign keys used in tables, mapping these to the 64-bit integer type supported by the database engine. Various other integer columns are used that use the standard 32-bit integer type of the engine.
Dates are held as strings in ODBC Canonical format.
Character Set
The Petro-SIM database supports both the standard ANSI/ISO 8859-1 character set as well as full Unicode. In general if you need to support characters outside the ANSI set, you will need to create the Petro-SIM database with Unicode support:
- On Oracle you can use UTF-8 encoding which very efficiently stores Unicode characters without much increase in disk space
- On SQL Server you will need to use NVARCHAR, meaning that strings consume roughly twice as much space than without (VARCHAR). As such, if all users will be using the same Western European language or languages that only use characters in the standard ANSI set, you can usually create the database without Unicode support to conserve disk space. If you are using SQL Server Enterprise however, we recommend creating the database with page compression and Unicode enabled, since its compression algorithm very efficiently handles Unicode strings.
Depending on your encoding, strings are either Varchars of predefined length (typically 50, 150 or 255 characters) or NVarchars of equivalent length where Unicode support is needed.
PetroSIM_Collections Table
The highest level item in the database is called a Collection. A Collection is a group of one or more objects that together form an entity Petro-SIM understands. Collections can be entire cases, assays, LP Utility objects, or other objects exported from Petro-SIM.
Every collection has a CollectionType which Petro-SIM uses to decide what to do if you import the collection into Petro-SIM, and a CollectionID which can be used to find other objects associated with that collection. Depending on the database engine, values for the IDs are auto-incremented or program assigned and are unique across the entire database. Collections are associated with database Objects.
| Field | Data Type | Meaning | Introduced in |
| CollectionID | Big Integer | Unique ID assigned to each collection. | V3 |
| ParentCollectionID | Big Integer | ID of any parent collection – will be 0 if no parent. | V3 |
| CollectionType | Varchar (50) | Indicates type of collection. Recognized strings are: Petro-SIM Full SimCase, Petro-SIM Group of Objects, Petro-SIM <objecttype> Object | V3 |
| Name | Varchar (150) | Collection name. | V3 |
| Path | Varchar (255) | For full cases, full path name of any Petro-SIM case file prior to save in the database. | V3 |
| BuildNumber | Varchar (50) | Build number of the Petro-SIM build used to save the collection | V3 |
| BuildType | Varchar (50) | Indicates whether saved from a Product or Development version of Petro-SIM | V3 |
| GenerationDate | Varchar (50) | When saved. | V3 |
| Author | Varchar (50) | Petro-SIM. | V3 |
| Description | Varchar (255) | Collection description string. | V3 |
| Revision | Integer | Revision number for collection, counting up from 0. | V4 |
| SampleDate | Varchar(51) | V4 | |
| Tag | Varchar(51) | Security tag used in revision locking | V4 |
| LockedBy | Varchar(50) | User name of an individual who has checked out a collection. Maybe null. | V4 |
| Dirty | Integer | 1 if collection data is dirty and needs to be recalculated by Petro-SIM. Used primarily on assay collections. | V4 |
| SecurityTag | Varchar(50) | Unused. Intended as tag that can be used by sites to implement additional row level security | V4 |
| CollectionStatus | Integer | Used to implement a pending delete mechanism where it takes 4 states:-1 – collection being written to the database; 0 – ok; 1 – marked for pending delete; 2 – marked for delete. The collection record is the first item written to the database for each new collection, with the CollectionStatus starting out at minus one. The value is updated to zero at the end of the write process, meaning this field can be used to check whether collection writes are complete. | V4 |
| RevisionLabel | Varchar(255) | Not used | V4 |
PetroSIM_Objects Table
The PetroSIM_Objects table holds details on individual objects and their relationships. Each object typically represents a unit operation inside Petro-SIM or a data group inside a unit operation. Data about the object is held in the PetroSIM_ObjectData table.
Each object has a unique ObjectID which can be used to locate its data. Database objects are hierarchical, with the basic hierarchy determined by the flowsheet and flowsheet object structure of Petro-SIM. Objects owned by the Collection have an OwnerObject of 0 (zero), but all other objects generally have an owner that may be a flowsheet or a flowsheet object. Objects also have parents. For direct descendants, the parent and owner objects are the same, but grandchild and deeper objects may have a common owner but different parents.
Direct parent-child relationships are built using the ParentObject attribute. All child objects of a database object can be found using the OwnerObject
| Field | Data Type | Meaning | Introduced in |
|---|---|---|---|
| CollectionID | Big integer | CollectionID of the owner of this object. | V3 |
| ObjectID | Big integer | Unique ID assigned for this object. | V3 |
| Revision | Integer | Counts from 1 upwards and indicates the revision number of this object. Objects of the same name can have multiple revisions, where this is used in linking cases. | V3 |
| FlowsheetName | Varchar (150) | Flowsheet owning this object. | V3 |
| ObjectName | Varchar (150) | Name of this object: will be unique within the Flowsheet. | V3 |
| TaggedName | Varchar (255) | Unique name within the collection of this object formed from object name @Flowsheet. | V3 |
| DBSourceTaggedName | Varchar (255) | Unique name of any linked object – reference to an item in another collection. | V3 |
| Type | Varchar (255) | Internal object type. | V3 |
| ParentObject | Big integer | ID of this object’s parent (will be 0 where null). | V3 |
| FluidPackage | Varchar (150) | Name of the Fluid Package associated with this object. | V3 |
| SubFlowsheet | Varchar (150) | Name of any sub-flowsheet associated with this object. Applies to ColumnOpObject and TemplateOpObject objects. | V3 |
| OwnerObject | Big integer | ID of this object’s owner, which will be a flowsheet level object. Will be 0 where null. | V3 |
| Description | Varchar (150) | V3 | |
| ObjectStatus | Integer | Used to implement a pending delete mechanism where it takes 3 states:0 – OK; 1 – marked for pending delete; 2 – marked for delete. Field is not used by Petro-SIM and is intended for use with applications that directly interact with the database. | V4 |
PetroSIM_ObjectData Table
The PetroSIM_ObjectData table holds individual attributes of objects. Attributes represent data values, including real, text, and attachment details, or connections of the object.
| Field | Data Type | Meaning | Introduced in |
|---|---|---|---|
| CollectionID | Big integer | CollectionID of the owner of this object. | V3 |
| ObjectID | Big integer | Unique ID assigned for this object. | V3 |
| Attribute | Varchar (255) | Attribute name. Forms the unique key along with the ObjectID. | V3 |
| AttributeType | Varchar (50) | Type of the Attribute: Attachment and Value.where Attachments are things like feed and product stream connections.Values are floating point or text. | V3 |
| AttributeValue | Float | Floating point value of the attribute. Will be -32767 when empty. | V3 |
| AttributeTextValue | Varchar (255) | Text value of the attribute. Will be null when a floating point value is available instead. | V3 |
| AttributeUOM | Varchar (50) | Units of measure string for floating point values. May be null where the value is empty or dimensionless. The set of possible strings are those recognized by Petro-SIM. | V3 |
| AttributeStatus | Varchar (50) | Describes the calculation status of the attribute. May be null or one of Calculated, Adjustable, Specified for Value AttributeTypes and null, or one of Feed or Product for Attachment types. | V3 |
| AttachmentName | Varchar (255) | Name of the attached object: will exist within the same flowsheet as the OwnerObject of this attribute. | V3 |
| AttachmentType | Varchar (50) | Object Type of the attachment. | V3 |
| OwnerObject | Big integer | ObjectID of the owning flowsheet object. | V3 |
Creating new databases
There are several steps to creating new Petro-SIM databases for SQL Server or Oracle.
- Create the new database or tablespace reserving appropriate space. Use a database management tool to do this such as SQL Management Studio for SQL Server or Oracle's Java interface <to be updated>. Petro-SIM databases can get large (we have seen sizes from 1 to 100 Gb) so please plan for this.
- Create the table structure in one of two ways:
- Let Petro-SIM create the structure for you, which it will offer to do on connecting to an empty database. Petro-SIM will also install reference data to the database.
- Let Petro-SIM generate SQL for creating the structure instead, with you applying this SQL through your management tool. Once done, re-connect with Petro-SIM and have it load the reference data.
- Create the database users
We recommend you consult with your Petro-SIM user community about the use of the database and hence its likely size: we are happy to provide advice.
Before you start
The Petro-SIM database holds engineering data where units of measure (C, F, ft, in, m and so on) are important. The database records units of measure for all numerical values and the setup process asks you to select the set you want to use. We recommend you consult with your Petro-SIM community about this before you embark on the setup.
SQL Server Considerations
We recommend you set the database up with an initial size of 1 Gbyte, allowing that size to increase automatically as needed. You will need also to define a user with db_owner permissions for the database, where we recommend this be a native SQL Server login with a name like PSDBO, giving that id a suitable password and granting them DB Owner rights over your newly created database.
Use Petro-SIM to connect to the database as follows:
- Install the program if necessary
- Start Petro-SIM from the shortcut on your Start menu
- Click Connect on the Data tab or click the Database Connection button on the right-hand side of the menu bar to bring up the Database Connection view
- Select SQL Server as the Provider
- Fill in the Connection Parameters where
- Provider is the OLEDB driver used to connect - this should default to SQLNCLI for SQL Server
- Data Source is the database server machine name
- Initial Catalog is the Database instance name on the server
- User Name and Password are for the native SQL Server login account you want to use
- Command Timeout is the SQL Command timeout in seconds. 0 means infinite and -1 means the field is not used and the connection timeout will default to the database standard (typically 30 seconds)
- Use Windows authentication checkbox controls the authentication type
- Use foreign keys causes the database to be setup with relational integrity checks between tables. We recommend this for normal use but you should consider unchecking this where high performance is a consideration.
- Unicode support causes the database to be setup with NVarchar strings that support the full Unicode character set. Note that Unicode strings are more expensive than standard ANSI or Varchar strings.
- Use row compression makes use of the row compression feature of SQL Server 2008 R2 Enterprise Edition or higher to minimise database size. This option will be ignored where it is not supported by the underlying database engine version.
- Click the Connect button to connect to the database. Petro-SIM will establish a connection and detect the database needs building, popping up a view seeking confirmation first:
You have two choices:
- Click Upgrade to have Petro-SIM build the database tables and indexes and populate with reference data.
- Check the Show Upgrade SQL Commands box to see the SQL DDL commands that will be used. You can right-click in the text window to save these to file so that you can inspect first or apply manually. If you build the structure manually, Petro-SIM will simply load the reference data when it next connects.