Adding & Editing Tables or Columns
  • 14 Mar 2024
  • 11 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Adding & Editing Tables or Columns

  • Dark
    Light
  • PDF

Article summary

This topic relates to the following sections:

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.

    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:

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 


Was this article helpful?

ESC

Eddy, a generative AI, facilitating knowledge discovery through conversational intelligence