ON THIS PAGE:
The key to a successful import in P2 Server is to make sure that the import spreadsheet is in the correct format and that everything in the spreadsheet is filled in correctly. Here are some tips that should help.
Tip 1. Know the spreadsheet structure
A. Object Types
The spreadsheet contains several tabs, which should all be completed correctly for a fully-formed asset model. Each tab represents an Object Type, which relates to an element of the Data Dictionary and determines its structure. Tab names do not matter, but Object Type does. There must always be an Object Type in cell A1.
If a worksheet has more than 1 section, further Object Types may be present in row 1.
The supported Object Types are:
- System
- Unit
- Datasource
- Datasource Parameter
- Digital State Set
- Digital State
- Entity
- Tag
- Tag Property
- Dataset
- Dataset Parameter
- Dataset Parameter Value
- Calculation Tag
- Hierarchy
- Hierarchy Relationship
- Link Group
- Link
- Image Group
- Image
- Template Group
- Template Definition
- Template Instance
- Attribute Group
- Attribute Definition
- Attribute Value
B. Formatting
Column headers are formatted to guide you on what to expect. Required parameters are in bold text and optional parameters are in plain text. The red icon indicates that descriptive help text is available.
C. Sections and Repeating Instances
Some worksheets have multiple sections. A section refers to a grouping of columns with respect to an Object Type. The second section, if it exists, may contain one or more repeating instances.
Tip 2. Know the terminology
These are the key terms you need to know to understand how P2 Server models data.
| Term | Explanation and example |
| System |
An external system which contains additional information about the assets modelled in the Data Dictionary. Example:
|
| Unit |
Unit of measurement. A unit can be associated with a tag to define the unit of values fetched from a historian. Example:
|
| Datasource |
Defines a connection that allows P2 Server to retrieve data from an external system. Example:
|
| Datasource Parameter |
The configuration for the datasource parameters as defined by its adaptor. You can find the parameters in P2 Server Management under the "Datasources" menu option. Example:
|
| Entity |
An object in a hierarchy (e.g. a field, a well, or a pump). An entity is a model of a real-world asset. Entities contain attributes that can be used to retrieve data. The entity itself does not have any data directly associated to it and it can not be 'fetched' directly. P2 Server supports duplicate entity names through the use of display names (Display Name column) and internal names (Name column). This allows P2 Server to refer to existing entities from another system such as Enterprise Upstream (System column) using the same names that they are known by in that system (Identifier column). Match up entities in P2 server with the corresponding entities in the external system, using a combination of the system ID and the entity ID from that system. Example:
|
| Tag |
A tag in P2 Server is an item that returns data. These tags are assigned to attributes of an entity. In this sheet, tag refers specifically to a historian tag that returns time-series data.
|
| Tag Property |
Additional, custom properties of a tag. This tab caters for additional metadata that historians send back with tags, that are not included elsewhere in this spreadsheet. Example:
|
| Dataset |
A set of data (from a datasource) in matrix form. E.g. An SQL query that returns rows and columns of data. Example:
|
| Dataset Parameter |
The parameters used when fetching a dataset. E.g. The parameters specified by the PARAM and PARAMS keywords.
|
| Dataset Parameter Value |
The default values for the parameters declared in the Dataset Parameter tab.
|
| Calculation Tag |
A tag that returns calculated data. Example:
|
| Hierarchy |
A structure containing items (e.g. entities, tags, datasets, calculations) that are organised into parent-child relationships. This sheet represents the information about each hierarchy defined in the system. It does not specify the contents of the hierarchy, which is defined on the 'Hierarchy Relationship' sheet. Example:
|
| Hierarchy Relationship |
The contents of the hierarchies defined on the 'Hierarchy' sheet i.e. the relationships that make up the hierarchies. Note that hierarchy relationships are time aware, meaning that they can change over time, denoted by the start and end times. Example:
|
| Link Group |
Provides a grouping for links, used for display purposes in consuming applications.
|
| Link |
The URI for links which can be assigned to templates.
|
| Image Group |
Provides a way of grouping images. Other applications can use image groups to filter images and icons. There are two groups available:
|
| Image |
Images and icons stored in P2 Server which can be used within Server and other P2 applications.
|
| Template Group |
Provides a grouping for templates, mainly for display purposes. A template (or object type) defines a real world object in the system, such as a Well. It also defines what attributes are able to be assigned to this type of object, such as tubing head temperature or flow rate. In P2 Server, the templates are time aware. This means that a Well could go from an Oil Producing Well to a Water Injection Well over its lifetime. This can be modelled by specifying a Primary Use Template which has a time range associated with it. If your real world object (entity) is going to be a truck for all of its life, then you would only ever need one template which is always primary. E.g. you can set the Truck template as Primary with NULL as the start and end times - this means it will be a truck for all of time.
|
| Template Definition |
A collection of attributes that can be assigned to entities for periods of time. It is used to assign data (attributes) to assets (entities) to create a model to be able to visualise and process the data. Example:
Set Internal to True only if you want to hide the template from appearing in P2 Explorer. |
| Template Instance |
When a template is assigned to an entity for a period of time this is known as a template instance. Within this time period, the template can be assigned as the primary template, denoted by the primary time periods. The primary template of an entity defines its type, e.g. a well that is assigned the Oil Producing Well template. The Primary Time Periods represent the period of time for which the template is the primary template. Templates that are assigned, but are not primary, are known as secondary templates. Attributes on a secondary template will not show up unless explicitly asked for, therefore it is recommended that all attributes be on the primary template.
|
| Attribute Group |
A concept that allows attributes to be grouped within the context of a single template, useful when modelling assets. This is the preferred way to group attributes, instead of grouping them by defining multiple templates. Example:
|
| Attribute Definition |
An attribute is a named item of data belonging to the template that allows data to be retrieved for that asset. Attributes have an additional level of modelling available by way of Attribute Values. Attributes are split into two levels: Attribute Definition and Attribute Value. The first level, Attribute Definition, defines the structure and data type of attributes on a template. It gives the attribute a name, specifies the template it applies to, and also acts as a group for the attributes themselves. The second level is the Attribute Value Definition. An Attribute Value Definition defines the named values that an attribute can have. One of the values has to be marked as default so that the system can fetch data for the attribute if a specific value is not supplied. An Attribute Definition can have multiple Attribute Value Definitions, but there must be at least one Attribute Value Definition defined for each Attribute Definition.
|
| Attribute Value |
The Attribute Value (AV_) sheets define the values of the attributes for a template for a given period of time. That time period must be within a template instance time period for the applicable template definition. A new instance of an attribute value is created each time an attribute changes data type or value, or whenever a new template instance is defined. The spreadsheet has a separate attribute value sheet for each template definition (prefixed with AV_ and using the template definition name e.g. AV_Water Injection Well). This allows you to quickly enter attribute values for each entity, working along the columns to assign the relevant properties.
Create a new row for the entity for each template instance. |
It may also help you to visualise how these things will appear in P2 Explorer as part of a trend.
Visual: How the Hierarchy is reflected in the trend in P2 Explorer
Tip 3. Export a template
Before importing, always export a template (or blank spreadsheet if you have an early version of P2 Server Management) for import. All import spreadsheets have a built-in version number, which are almost certain to change between versions of P2 Server in keeping with changes to the database. Old spreadsheets may be missing new required or optional columns, and may not work properly.
Always import using the latest version of the template (screenshot shows export options in version 4.4.5)
Tip 4. Understand object dependencies
When you import data using the spreadsheet, it’s not always necessary to complete all worksheets if you only need to update certain objects. If that’s the case, you only need to complete those worksheets related to the objects you want to import. These are indicated by the grey cells in the diagram below.
However, some objects require information from multiple worksheets, so you need to also complete the dependent worksheets in order for your data import to work correctly.
For example, if you want to only update links, you should complete the 'Link' and 'Link Group' worksheets.
The dependencies are listed in the following table – you should read this from left to right. Blue indicates a direct dependency, green indicates a dependency resulting from another dependency.
When exporting specific objects, ensure you select ‘Include Dependencies’. The relevant dependent objects will also be automatically selected, and the corresponding worksheets will be exported.
Tip 5. Read the help text in the column headers
On the header rows in the spreadsheet, there are help comments for every column. This should provide you with enough information on what should be entered in the column. Click the little red comment indicator on the header row to see the help.
Read the comment text in the columns headers of the spreadsheet
Tip 6. Set the correct Data Types for Attribute Values
If the Data Types on the Attribute Value tab are incorrectly set, the import will succeed, but the template mappings will be incorrect.
The data type needs to be set to the type of data that the value column is referencing. For example, if the value column references another tag, the Data Type should be set to TAG, rather than the type of the tag itself.
Data Type column needs to be set to the type of data the column is referencing
Tip 7. For updates, do an export first
If you need to update your configuration, you should first export the existing server data. This is important as updates will only occur where the Global ID in the spreadsheet matches the GUID in the database.
To speed things up, you can choose to export only those worksheets you need. We recommend having the 'Include Dependencies' option selected if you choose to do this.
If you want to retain your original spreadsheet as it may have embedded formulas, you should click the Download updated file button immediately after the import. You can then reuse the spreadsheet and it will retain your formulas and also have updated Global IDs.
Release History
- Tips on Filling in the Import Spreadsheet 4.5.1 (this release)
- Tips on Filling in the Import Spreadsheet 4.5.0