Syniti Migrate

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 page contains the following sections:

• About Adding or Editing Datasource Tables or Columns

• Add a Table

• Edit a Table

  • Additional Options for a Table

• Adding & Editing Columns

  • Column Do Not Import

  • Add a Column to an Existing Table

• Maintaining Table Constraints

• Follow Up Steps

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:

Heading	Definition
Name	Name of the system datasource table.
Schema	Database schema for the table.
Description	The description of the table.
Type	Type of Datasource table. A free form field that is part of the catalog of attributes associated with a table.
References	This field provides a count of where this table is actively being used in (Dataset table, Source Table, System View, Value Mapping check table, or Check Tables).
Import Group	Identify a group name to import this table within. (Only used for  Snapshot Datasource Tables)
Manual Import	Toggle to indicate that this table uses manual import. NOTE:  XML for import of data does not build the Workflow for this table as it is handled manually.
Active for Value Mapping	Toggle for a check table is provided as initial setup. Not tied to fields in the Value Translations page.
Record Count	Dependent upon the Syniti Knowledge Platform Data Catalog metadata . Not actively used.
System Datasource	The System Datasource associated with this table.
System Table	The System Datasource Table associated with this table.
Owner	Person responsible for this table.

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.

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:

Section	Heading	Definition
Details	Application	The Application defaults. (Display only)
Details	Instance	The Instance defaults. (Display only)
Details	Datasource	The Datasource displays.
Details	Table Name*	The Table Name. (Required)
Details	Description	Description of the datasource table.
Details	Schema	Allows for the table to have a specific schema per table for a specific table. Required if the user attempts to run Import Metadata at the table level.
Details	Type	Type of Datasource table. Ex: TRANSP, VIEW, POOL, CLUSTER, INTER, APPEND (Display Only)
Details	Records	Provides a display of the count of records in this table
Table Info	Active	Toggle for status of the Table for future-state design mapping and migration. NOTE: Defaults as Active
Table Info	Active for Value Mapping	Toggle to identify this table as a check table that may be used in XREF mapping. NOTE: Defaults as Inactive
Table Info	Refreshed On	Date the data was last refreshed.
SAP Data Services	Import Group	Identify a group name to import this table within. NOTE: There is a report to list all tables in an Import Group in System Reports
Stakeholders	Owner	The person responsible for this table.
Audit	Various	These are the system generated values for auditing the work.
	ID	Datasource Table GUID.
	Asset ID	Displays the Asset ID for the datasource table provided by Data Catalog.

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:

Heading	Definition
Index	Sequence of the Columns in the table. Value increases by a factor of 10 for each new entry. (Required)
Name	The name of the column.
Description	The description of the column.
Data Type	Data type for the column. Data types are unique to the database management or software system of the Datasource.
Length	Length of the column field.
Key Field	Toggle to indicate the Table Field is a key field and part of the primary key.
Nullable	Toggle to indicate the field can contain a NULL value
Do Not Import	Toggle to specify that this column is NOT included in the XML Import scripting for the ETL. Field is hidden. Refer to Column Do Not Import for details. NOTE:  The SQL script includes these columns in the table builds, but XML drops the same fields.
Snapshot System Column Name	Column name of the associated Snapshot system table / column
Check Table	Provides the table used for drop down list against this column. Field is Hidden
Encrypt	Provides status of encryption for the column on a Snapshot Management Datasource column. (See NOTE) Display Only
Deleted	Provides status of deletion of the column. (See NOTE) Display Only
Comments	Provides the comments for the column. (See NOTE). Display Only

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:

Section	Field	Definition
Details	Name*	Name of the column. (Required)
Details	Description	Description of the column.
Details	Index*	Sequence of the columns in the table. Value increases by a factor of 10 for each new entry. (Required)
Details	Data Type	Data type for the column. Data types are unique to the database management or software system of the Datasource.
Details	Data Length	Length of the column.
Details	Scale	Decimal places for the column.
Details	Default Value	The default value for this field.
Details	Nullable	Toggle to Indicate that the column may have NULL value.
Details	Key Field	Toggle to indicate the field is a key field and part of the primary key.
Details	Auto Generated	Toggle to Indicate that the column was auto generated.
Details	Do Not Import	Toggle to specify that this column is NOT included in the XML Import scripting for the ETL. NOTE: The SQL script includes these columns in the table builds, but the XML drops the same fields.
Details	Check Table	Name of the value or check table that contains acceptable or configuration values for the field.
Details	Column ID	GUID to store the unique ID for this column.
Details	Asset ID	Syniti Data Catalog Asset GUID.

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:

Section	Field	Definition
Details	Name*	Name of the column. (Required)
Details	Description	Description of the column.
Details	Index*	Sequence of the columns in the table. Value increases by a factor of 10 for each new entry. (Required)
Details	Data Type	Data type for the column. Data types are unique to the database management or software system of the Datasource.
Details	Data Length	Length of the column.
Details	Scale	Decimal places for the column.
Details	Default Value	The default value for this field.
Details	Nullable	Toggle to Indicate that the column may have NULL value.
Details	Key Field	Toggle to indicate the field is a key field and part of the primary key.
Details	Auto Generated	Toggle to Indicate that the column was auto generated.
Details	Do Not Import	Toggle to specify that this column is NOT included in the XML Import scripting for the ETL. NOTE: The SQL script includes these columns in the table builds, but the XML drops the same fields.
Details	Encrypt	Provides status of encryption for the column on a Snapshot Management Datasource column. (See NOTE) Display Only
Details	Snapshot System Column Name	Should the Name field be changed, this field stores the original column name for Snapshot Management Datasource columns.
Details	Check Table	Name of the value or check table that contains acceptable or configuration values for the field.
Details	Column ID	GUID to store the unique ID for this column.
Details	Asset ID	Syniti Data Catalog Asset GUID.

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.

Section	Definition
Dataset Design	Refer to section Catalog > Dataset Design > Create & Maintain Datasets
Mappings	Refer to section Migrate > Mappings
Report Generation	Refer to section Migrate > Mappings > Maintain Target Reports