Adding & Editing Tables or Columns

This topic relates to the following sections:

• Datasources

• Adding & Editing Applications

• Adding & Editing Instances

• Adding & Editing Datasources

• Import Metadata

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 common applications like ECC, S4H, and JDE. Users may mark the application tables as Active or Inactive as required for the project. Refer to Applications for more information on configuring a standard set of Applications.

The Datasource Tables details grid displays the following information:

Note

All data panels in Migrate offer multiple filtering options for each column. Click the Column Menu button to show or hide columns, filter records, pin a column, and more. Column filters are especially useful when working with dynamic data or when reviewing large sets of records within the limited space of the list view.

Refer to the Page Grid Options section for more information.

Note

The section for XML is visible within Datasource details If the ETL Tool is set to Data Services either in the Administer > Setup > Parameters page (global setting) or in any active Release (Migrate > Project Setup > Release tab).

Add a Table

From within the Datasource Tables panel perform the following steps:

1. Click the 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.

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 Save 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.

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, click the Edit icon on the left side of the required Datasource Table. The Table’s details page is displayed.

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:

The Datasource Tables details grid displays the following information:

Additional Options for a Table

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

For Snapshot Datasources (SRCxxx or TGTxxx), you will see the Repopulate Snapshot Data option that allows you to perform a data refresh for the snapshot table.

Adding & Editing Columns

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

The Columns tab includes the following headings:

Note

Data profiling and other additional fields are hidden by default. Should the user prefer these additional fields, select Column Menu > Choose Columns to show or hide additional fields in this table grid.

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.

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.

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.

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

The Datasource Column Details dialog box contain the following fields:

Data Profiling

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 Datasources > Import Metadata for details for importing metadata for an added column.

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

Maintaining Table Constraints

During the Import Metadata process, Migrate scans and imports metadata with Constraints for each table and populates it in the Constraints tab. However, (if required) you can manually associate Columns with different Constraints Types such as, Foreign Key, Index, Unique, Primary Key, Domain. These Constraints assist in building of Views used to validate your legacy data against the Check Table data (business foreign keys stored in working database used in Data Migration).

You can use Check Table as a constraint type to generate the required Target Reports. The quality of Target Reports are dependent on the constraints set up on the required tables in a datasource. Note that if a report has invalid data, you must manually check, add, and update the constraints applied on the fields of the tables used for generating that report.

In the Datasource Tables panel, double-click the required table and click the Constraints tab to view the constraints applied for the table.

To add a new constraint, click the Add button. The Constraint dialog box is displayed.

Use the following table to create a constraint for a field in a table:

Click Save to create the constraint. After creating the constraint, you must add the required columns to the constraint.

To add the required columns to the constraint:

1. In the Constraints tab, select the required constraint in the Constraints panel.

2. In the Columns panel, click the Add button. The Column dialog box is displayed.
   

3. In the Column dialog box, enter 10 in the Index field, if this column is the first entry to the Columns panel. Increment the Index value by 10 for the subsequent entry to the Columns panel.

4. In the Name field, enter the name of the field in your system table.

5. In the Reference Column field, enter the name of the Foreign Key field in the Check Table.

6. If you want to sort the field of INDEX constraint type in descending order, turn on the Is Descending toggle button. By default, most indexes are sorted in ascending order.

   Note

   This toggle button applies only to INDEX constraint type.

7. If you want to include this column as part of the index but not use it for sorting or determining uniqueness, turn on the Included Column toggle button.

   Note

   • This toggle button applies only to INDEX constraint type.

   • Included Columns are used in non-clustered indexes to include extra columns that improve query performance without being part of the index's sorted key.

8. Click Save to add the column to the constraint.

Constraint Types

You can specify the following constraint types:

• PRIMARY-KEY: Ensures that each row in a table contains a unique identifier. You can also create composite primary keys under this constraint with multiple fields in a table to uniquely identify a record.

• INDEX: An index is a data structure that improves the speed of data retrieval operations on a database table. It works similarly to an index in a book, allowing the database to find records more quickly without having to scan the entire table.

• UNIQUE: A unique constraint is a rule applied to a database table to ensure that the data in a particular column (or a group of columns) is unique for each record. This means no two rows can have the same values in that column (or combination of columns). It's commonly used to prevent duplicate data entries.

• FOREIGN-KEY: Establishes relationship between two tables, ensuring that the value in one table matches a value in another table. For example, the CustomerID on a Sales Order must reference a valid ID on the Customer Master table.

• CHECK-TABLE: A Check Table is a similar to a foreign key except that it is enforced in the Application Layer, typically as a List Box.  

• DOMAIN: A domain defines the technical attributes of a data field. In SAP, a domain represents the fundamental characteristics of a field (such as data type, length, possible values, and so on), and it serves as a template that multiple table fields can refer to.

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 the following articles:

• Dataset Design: Create & Maintain Datasets

• Mappings

• Mappings: Maintain Target Reports