Data Type Conversion in the SKP

This topic is related to the following topics:

• Adding & Editing Datasources

• Create Target Tables

• Create Working Tables

• Database Types

• Snapshot Management

Overview

While working in the Syniti Knowledge Platform (SKP), the metadata of different available systems are imported, stored, processed, and utilized based on your business requirements. Whether it is master data, transactional data, or data for analytical insights, the way data is ingested, processed, and used downstream significantly impacts the efficiency and effectiveness of the SKP.

This topic is an example of how the metadata from the SAP application is consumed into the System Catalog, and how it is then processed and used downstream by the Migrate component (and Replicate) as part of the Snapshot Management process.

KONV: A Cluster Table in SAP

In this example, let us consider the KONV cluster table from the SAP application. You’ll understand how the data from this table is processed and utilized after ingestion into the SKP based on its database type.

SAP Data Dictionary

The above image displays the KONV table with its subset of fields in the SAP data dictionary. The KKURS field on this table is of the Decimal (DEC) data type with the Length of 9 and the Decimal Places of 5, that is, a maximum value of 9999.99999 can be stored in this field. Similarly, the KBETR and KAWRT fields are of the Currency (CURR) data type with the Length of 11 and 15, and with the Decimal Places of 2, such that they can have a maximum value in 9 and 13 digits before the decimal point and 2 digits after the decimal point.

SKP System Catalog

On ingestion into the SKP’s System Catalog, the following details are displayed.

The System Catalog replicates the metadata in the SAP data dictionary and displays the KKURS field of the DEC data type with a Size of 9 and a Scale of 5. Similarly, the KBETR and KAWRT fields of the CURR data type are displayed with the same Size (Length in SAP) and Scale (Decimal Places) as in SAP.

Migrate: Datasources

The metadata is then transferred to Migrate > Datasources when the System is registered in Syniti Migrate and the data types are aligned again to the System Catalog.

You can see in the above image that the length of the columns is aligned to the System Catalog values. Using the below images you can also verify that the scale values are set correctly and aligned to the scale value in the System Catalog and Decimal Places in the SAP data dictionary.

Migrate: Snapshot Management

The metadata is then imported into the Snapshot Management component of Syniti Migrate. Snapshot Management uses the Database Type conversions to define the snapshot target table structure based on the database type assigned to the working database server. The KONV cluster table is displayed as the below image.

You can observe from the above images that the KKURS field is still defined as a Decimal data type with a Length of 9 and a Scale of 5. The KAWRT and KBETR fields are now represented as Money, which is the currency data type in SQL Server. This data type conversion is due to the conversion rules being applied in Administer > Advanced > Database Types.

The data type conversion is from the source on the left to the database type on the right. In this example, from SAP to SQL Server. We can see one custom conversion setup from DATS to NVARCHAR(8).

Clicking the Count:1 button in the Translation Rules To column as highlighted in the above image displays the following page.

This image shows that the custom rule is defined to transfer SAP’s DATS data type columns to NVARCHAR(8) in the SQL Server target, thus allowing dates with the Null value in SAP to be processed into the snapshot database.

Click the Results tab to see the standard transformation rules being applied. You can see the CURR data type of various lengths being converted to the Money data type in SQL Server.

If you want to change transformation rule settings for a specific data type and size, then you can create an additional rule (as we have done for the DATS data type) to overwrite the delivered conversion rules.

SQL Server Working Database

In the SQL Server working database, the table is created when the Snapshot Refresh is performed.

The KKURS field is now defined as a Decimal data type with a Precision of 9 and a Scale of 5, which correctly aligns with the SAP data type definition for Length and Decimal Places that allows for a maximum value of 9999.99999 to be stored in the field. The KAWRT and KBETR fields use the Money data type with a Precision of 19 and a Scale of 4, which will hold all of the SAP’s CURR data type values. A few fields may require a manual data type conversion to be defined depending on the size of the values in the field.

Syniti Replicate

In Syniti Migrate, the Snapshot Management refresh creates the target table in the registered working database environment. It then calls the Replicate API telling the Source Connection and Table to snapshot to the Target Connection and Table.

After the API call is made, Syniti Replicate triggers the creation of the connection (if it’s the first time, a table from this source is refreshed) or imports the table metadata definition from the source and target databases (not from Syniti Migrate or the System Catalog module).

In SAP, the KONV source table’s details are displayed as the above image with the correct Precision and Scale values to support the extraction process. The Size column can be ignored for decimals as it is the Precision and Scale that define the column structure, that is, 9 digits with 5 digits after the decimal place for KKURS and decimal (15,2) for KAWRT.

In SQL Server, the target table definition is displayed as the below image and correctly represents what is defined in the SQL Server working database.