Adding & Editing Tables or Columns

This topic relates to the following sections:

• Datasources

• Adding & Editing Applications

• Adding & Editing Instances

• Adding & Editing Datasources

• Import Metadata

About Adding or Editing Datasource Tables or Columns

This section provides details for the adding and maintaining of tables or columns for all Datasource types.

After a Datasource is created and verified as having an active connection, table metadata may be imported into Syniti Migrate from Syniti Data Catalog.

Note

To verify the connection of a Datasource, refer to topic Catalog > Datasources > Adding & Editing Datasources for the section on Verify that the Connection is Active.

On the Datasources multi-panel page, the Datasource Tables are maintained under the Datasource Tables panel. The Columns for each Table are maintained within the panel for Datasource Columns as shown below.

Note

A predefined set of tables are stored as defaults for systems like ECC or S4H. The user may mark Inactive any tables not needed for the project.

Datasources: Tables panel

The System Datasource Tables grid displays the following:

Note

As with any page grid of data, on the right upper corner, click the Hamburger Details icon to view the options for show or hide column, filter of rows, and column width. This is extremely handy for situations where the data is fluid and changes or when there are many records that are hard to review in the small window provided for the list. Refer to section Page Grid Options for details.

Note

The section for XML is visible within Datasource details If the ETL Tool is not Syniti Migrate (Ex: Data Services). If the ETL Tool field within the Administer > Setup > Parameters page is set to Syniti Migrate, the XML section is hidden.

Add a Table

From within the Datasource Tables panel perform the following steps:

1. Click Add  icon to add a new datasource table for data connectivity and metadata import.

   Note

   The Enter Table field validates the entry to existing tables for Datasources that have a server. Also, the user may manually enter views as a Datasource Table. Ex: In Construct, XTVALUEMAP views are needed in the CONSTRUCT db and so they may be entered manually.

   

   Datasources: Import Table

2. Enter the Table Name (or View Name) and if necessary, update the Schema.

   Note

   The Enter Table field is case-sensitive. Ensure that you enter the table or view name correctly.

3. Click the IMPORT TABLE  button to complete.

Note

If the user neglects to enter the table name, the system proposes an Error message and does not save. The Error message looks like the example provided below. Click OK to close and return to the Import Table dialog box to update.

Datasource: Add Table: Error message

The result of adding a new table is for the list to contain the new table and the associated columns. The metadata for these tables is pulled from the system datasource.

Note

 If there are no columns for an added table, first check the System Datasource for the same table with columns. Add the same table if missing. Check the Job Queue for this process, and then check the Debug Log for indicators for the error.

Adding Tables and Columns for a Snapshot Datasource

A Snapshot Datasource provides for a few more scenarios than a System Datasource.

• Snapshot tables may be linked to a Source Datasource

• Snapshot table may be a View built in the SRC Snapshot and added as a table for use in migration mapping

Note

An example of the second variable is a view of the Customers Created within the last 2 years, showing active.

Refer to section Migrate > Snapshot Management for details on the definition and use of a Snapshot Datasource. All maintenance of Snapshot Datasources are handled within Snapshot Management pages.

Edit a Table

To edit the Table details perform these steps:

1. Click on the Edit  icon on the left side of the System Datasource Table.

2. The page displays details of the table.

Datasources: Datasource: Table: Edit

Edit Table Details

The Table Details page contains a left side panel for Table Details fields. Field values may be edited as shown below. On the right side of this page are two tabbed pages - Columns and Constraints.

The left side of the Table Details page contains the details fields. These fields are supportive of the Table Details, Table Info, XML, and Stakeholders. Each section of this panel stores valuable information. At the header of this left side panel there are quick use icons as shown below:

Icon	Name	Use
	Save	The Save icon saves the current entries.
	Cancel	The Cancel icon leaves the current page without saving changed data.
	Help	The Help icon links to the page in Syniti help text to provide a detailed explanation of this section and use.
	Options	This icon produces a list of options for the Datasource including -               Import Metadata  Refer to the section below that discusses options and use.

Within System Datasource Table Details contain the following fields:

Additional Options for a Table

Import Metadata - Refer to section Catalog > Datasources > Adding & Editing Datasources > Import Metadata for details of this process.

Datasources: Table Details page

Adding & Editing Columns

From within the Datasource Table Details page, the datasource columns are maintained as a tab of the datasource table.

Datasources: Table Details page

The System Datasource Columns grid provides the following headings:

Note

The hamburger list icon contains additional fields for these table grids should the user prefer these additional fields.

Note

Fields Deleted and Comments apply where a Datasource Column is added to the table within Syniti Migrate, and then selected for use in a Dataset, but the field is not actually set up in the table in the Datasource. Should the user decide to Import Metadata for this table, the field does not really exist, and therefore is removed from the table. However, if the field is selected and active in a Dataset, the system marks the field as Deleted (rather than actually remove it) and provide comments to show that 'Column removed from physical table' .

Note

Field Encrypt applies when the column data requires encryption to prevent visibility to users for a Snapshot Datasource only. This flag is set within the Snapshot Datasource to update the column during Replication of data to a GUID value and store the actual value hidden.

Column Do Not Import

Should the user flag columns for Do Not Import, those columns are left off the XML Import build scripting for the ETL. The fields remain included in the SQL scripting  for the table builds in the database system.

Datasource: Columns: Do Not Import flag

Note

The SET DO NOT IMPORT FLAGS button is present for Snapshot Datasource tables only. In this case, the user may click on the button to mark all fields (except Key fields) as Do Not Import. By defaulting all fields as Do Not Import, the user has reduced work to toggle the subset of fields to import. 

A warning message displays. Click OK to proceed to update all columns except the Key fields as Do Not Import or click Cancel to exit this step.

Datasource: Snapshot Datasource: Set Import Flags to Active

Click the Edit icon for a Datasource Column to view the Column details.

The System Datasource Column Details dialog box contain the following fields:

Add a Column to an Existing Table

While viewing the Datasources page, the following steps add a column to an existing table:

Click Add  icon within Datasource Columns panel of the page to create a new column for the selected datasource table and launch the Datasource Tables Columns window.

An example of where a user would add a column to a System Datasource Table would include a field that is going to be added to the target table but has not yet been added. The team needs this field for mapping and so they add it manually to the System Datasource table as a new column. Since the field does not exist in the target table at this time, it causes issues during the import of metadata to the Syniti Migrate System.

To remedy this issue, Syniti Migrate marks this field as Deleted with Comments to describe the situation within the column grid. No data is imported to this field as it does not exist within the Target system at this time. When it does get added to the target system, again Syniti Migrate ensures that the fields match and removes the Deleted flag and import the metadata and rows of data to the system.

Refer to section Import Metadata Response to Added Columns in a Table in this link: Catalog > Datasources > Adding & Editing System Datasources > Import Metadata for details for importing metadata for an added column.

Datasources: Columns Details panel

See below for field definitions for adding Table Column Details:

When finished with edits, click the Save  icon to complete.

Maintaining Table Constraints

As a part of the Import Metadata process, the Constraints data populates within the Datasource Table Details page tab for Constraints for all non RFC connections. The Constraint type is provided as well as associated Columns for the Constraints as example - Foreign Key, Index, Unique Index, Primary Key. These Constraints assist in the view builds for Check Table data.

Note

This functionality is not available at this time.

Follow-up Steps

Once the Datasource is set up and connections are active, there are follow-up steps that the Admin or IT Lead is required to complete to make the new Datasource ready for use in Dataset Design, Mappings, and Report Generation. Refer to these sections as indicated below.