Data Reader Patterns - SmartPlant Foundation - IM Update 46 - Help - Hexagon

SmartPlant Foundation Help

Language
English
Product
SmartPlant Foundation
Search by Category
Help
SmartPlant Foundation / SDx Version
10
SmartPlant Markup Plus Version
10.0 (2019)
Smart Review Version
2020 (15.0)

In the Data Reader Patterns module, you can set various options to configure the Data Capture modules.

These options include:

Data Connections - Allows you to add and delete database connections that will be used in the Data Reader Patterns module to connect to the database and extract domain tags.

Data Reader Pattern - Allows you to define database query statements to extract information from the database. A data reader pattern consists of a database connection, query statement, and data restructure instructions.

Data Validator Job Definitions - Allows you to select a Data Validator Job Definition name.

  • You must ensure that stored procedures are loaded in the database when creating a site prior to using this functionality. For more information, see New Site Wizard > Database Settings page.

  • You must have Microsoft Access Database Engine (64-bit) installed on your computer to use the Microsoft Excel file as data connection.

Data Connections page

Allows you to add and delete database connections that will be used to extract domain tags from the existing databases.

Create Data Connection - Allows you to add a database connection.

Edit Data Connection - Allows you to modify an existing database connection.

Delete Data Connection - Allows you to delete one or more database connections.

Create Data Connection

Name - Type a name for the data connection.

Domain - Type the domain name that you want to create in SDx Operations database for storing the domain tags.

  • It is recommended you restrict the number of characters in the domain name so that it does not exceed 10 characters.

Connection Type - Select a database type from the list. The options are: Oracle, SQL, MS Access, Excel, and Custom.

  • For Oracle:

Data Source - Type the name of the database instance.

Username - Type the username that is used to connect to the database.

Password - Type the password for the user account.

  • For SQL:

Data Source - Type the name of the database instance.

Database Name - Type the name of the database server.

Username - Type the username that is used to connect to the database.

Password - Type the password for the user account.

  • For MS Access:

Provider Name - Type the provider name for the database (the default is Microsoft.ACE.OLEDB.12.0).

File Path - Type the path for the Microsoft Access database.

  • For Microsoft Excel:

Provider Name - Type the provider name for the connection to Microsoft Excel (the default is Microsoft.ACE.OLEDB.12.0).

  • For Custom:

Connection String - Type the connection string to connect to your custom database.

Create Data Reader Pattern dialog box

Allows you to create a data reader pattern, specify the class name, and select the data connection to use to connect to a database.

Name - Type a name for the data reader pattern.

Description - Type a description for the data reader pattern.

Class Name - Type the class definition name. This class definition name is used in combination with the domain name to create the objects in the SDx Operations database.

Data Connection - Select a data connection from the list. For more information on creating data connections, see Manage data connections.

Define Data Reader Pattern page

In the Define Data Reader Pattern page, you can create a query and provide data restructure instructions. This page consists of the following panes:

Tables

Allows you to add tables or views from the database that is referenced in the selected data connection. You can also create table joins with unique names.

Add table from database - Allows you to add tables or views from the database defined in the selected data connection.

Join Tables - Allows you to join one table with another. The new table name created with the join is added in the Tables pane. For more information, see Join tables.

Delete Tables - Allows you to delete selected tables from the Tables pane. You can delete the tables that are not used in the query.

Table Properties

Allows you to view the column names of the table or join that is selected in the Tables pane. You can add a look up file path, UOM, and condition in this pane.

Map Selected Fields – Allows you to map the selected fields to properties in Data Capture.

Apply Converter – Allows you to change the result of the field by applying a converter such as Replace or SubString.

Name – The name of the field in the database.

Display As – Allows you to add a suffix to the name of the field in the database. The name in the Name box is used in the SDx Operations database (if no mapping is applied).

Mapped – Shows whether the field is mapped to a specific Data Capture property or not.

Look up path - Allows you to add a file path to a lookup file in the Look up path column.

UOM - Allows you to add the unit of measurement for a property.

Where - Allows you to add a WHERE clause by clicking in the Where column.

Data Output

Allows you build the query and create custom properties using the following tabs.

Create Query tab

Allows you to create a query with table joins and property mappings.

Add table to query - Allows you to add tables or table joins listed in the Tables pane to the query.

Edit Join - Allows you to edit the type of join between two tables.

Delete table from query - Deletes the rightmost table in the Create Query tab.

Generate SQL - Generates and updates the query in the SQL Query tab.

Transpose Selected Fields - Allows you to transpose a field to restructure the data.

Preview Data - Displays the first 100 records that will be extracted by the query.

Custom Properties tab

Allows you to create custom properties.

Create new property - Allows you to create custom properties.

Edit property - Allows you to edit existing custom properties.

Delete Property - Deletes one or more selected custom properties.

SQL Query

Displays the query that is generated in the Define Data Reader Pattern page.

Join Tables dialog box

Allows you to join two tables and define the join type.

Name - Type a name for the join.

Right Table - Select a table name from the list that will represent the table on the right side of the join.

Join Type - Select the type of join from the list. The Join Type list consists of the following options:

  • Inner Join - An inner join requires each record in the two joined tables to have matching records and creates a new result table by combining the column values of two tables (Table1 and Table2) based on the condition that specifies how the tables are to be joined.

  • Left Outer Join - The result of a left outer join for tables Table1 and Table2 always contains all records of the left table (Table1), even if the join-condition does not find any matching record in the right table (Table2). If no matching row from the left table (Table1) exists, NULL will appear in columns from Table1 for those records that have no match in Table2.

  • Right Outer Join - A right outer join closely resembles a left outer join, except with the treatment of the tables is reversed. Every row from the right table (Table2) will appear in the joined table at least once. If no matching row from the left table (Table1) exists, NULL will appear in columns from Table1 for those records that have no match in Table2.

Condition - Select a column from the left table, select a comparison condition operator, and select a column from the right table to compare values in two tables that have common fields.

Generate Where Clause dialog box

Allows you to specify the criteria that will be used to extract specific records.

Condition - Select a comparison condition operator from the list.

Value - Type a string for comparison.

Table - Select a column from the list for comparison.

Create Custom Property dialog box

Allows you to create a custom property by combining one or more table properties.

Name - Type a name for the custom property.

Add Table Property - Adds a table property to the left pane.

Add Constant - Adds a constant value in the custom property.

Delete Property - Removes the selected properties from the left pane.

Up and Down arrows - Allows you to move the table properties up and down in the left pane.

SDV Job Definitions Page

Allows you to select a SDV Job Definition name from the list, and generate the required csv file and control file. The SDV application reads these files and imports data into the SDV target system.

Name – Allows you to type the name of SDV Job Definition.

Description – Allows you to type the description for the new SDV Job Definition.

Target System – Allows you to type the name of the target system as created in SDV.

Control File Directory – Allows you to type the location of Control File Directory.

Username – Allows you to type the SDV username.

Target Configuration – Allows you to configure the scope on the target system. For example: ConfigurationTop~PlantA

What do you want to do?