Microsoft Azure Synapse Analytics

This page shows how to set up and use the Microsoft Azure Synapse Analytics destination. The Microsoft Azure Synapse Analytics destination loads data to an Azure Synapse SQL Pool.

Requirements

To use the Azure Synapse Analytics SQL Pool destination, you need:

• An Azure Analytics SQL database.
• Azure portal firewall rules that grant access for the IP addresses Xtract Universal is running on.

Create a new Microsoft Azure Synapse Analytics Destination

Follow the steps below to add a new Microsoft Azure Synapse Analytics destination to Xtract Universal:

1. In the main window of the Designer, navigate to Server > Manage Destinations. The window “Manage Destinations” opens.
   
2. Click [Add] to create a new destination. The window "Destination Details" opens.
   
3. Enter a Name for the destination.
4. Select the destination type Microsoft Azure Synapse Analytics from the drop-down menu. A list of connection details opens.
5. Fill out the destination details to connect to the destination.
6. Click [OK] to confirm your input.

The destination can now be assigned to extractions.

Destination Details

The destination details define the connection to the destination.

Server Name

Enter the name of the Azure Servers in the following format:
[servername].database.windows.net

User Name

Enter the user name.

Password

Enter the password.

Database Name

Enter the name of the Azure Synapse SQl Pool.

Test Connection

Check the database connection.

Assign the Microsoft Azure Synapse Analytics Destination to an Extraction

Extractions write data to their assigned destination. Follow the steps below to assign a destination to an extraction:

1. In the main window of the Designer, select an extraction.
2. Click [ Destination]. The window “Destination Settings” opens.
   
3. In the “Destination Settings” window, select a destination from the dropdown list.
   
4. Optional: edit the destination settings.
5. Click [OK] to confirm your input.

When running the extraction, the extracted SAP data is now written to the destination.

Destination Settings

The destination settings only affect the extraction that the destination is assigned to. To open the destination settings, select an extraction in the main window of the Designer and click [ Destination]. The window "Destination Settings" opens.

File Name

Determines the name of the target table. The following options are available:

Append timestamp

Add the timestamp in the UTC format (_YYYY_MM_DD_hh_mm_ss_fff) to the file name of the extraction

Use Script Expressions as Dynamic File Names

Script expressions can be used to generate a dynamic file name. This allows generating file names that are composed of an extraction's properties, e.g. extraction name, SAP source object. This scenario supports script expressions based on .NET and the following XU-specific custom script expressions:

#{Source.Name}#

#{Extraction.ExtractionName}#

#{Extraction.Type}#

#{Extraction.SapObjectName}#

#{Extraction.Timestamp}#

#{Extraction.SapObjectName.TrimStart("/".ToCharArray())}#

#{Extraction.SapObjectName.Replace('/', '_')}#

#{Extraction.Fields["FISCPER"].RangeSelections[0].LowerValue}#

#{Extraction.Fields["FISCPER"].RangeSelections[0].UpperValue}#

#{Extraction.Context}#

#{Extraction.Fields["[NameSelectionFiels]"].Selections[0].Value}#

#{Odp.UpdateMode}#

#{TableExtraction.WhereClause}#

Column Name Style

Defines the style of the column name. Following options are available:

Convert dates

Converts the character-type SAP date (YYYYMMDD, e.g., 19900101) to a special date format (YYYY-MM-DD, e.g., 1990-01-01). Target data uses a real date data-type and not the string data-type to store dates.

Year 0

Converts the SAP date 00000000 to the entered value.

Year 9999

Converts the SAP date 9999XXXX to the entered value.

Invalid values

If an SAP date cannot be converted to a valid date format, the invalid date is converted to the entered value. NULL is supported as a value. When converting the SAP date the two special cases 00000000 and 9999XXXX are checked first.

Preparation

Defines the action on the target database before the data is inserted into the target table.

To only create the table in the first step and not insert any data, you have two options:

1. Copy the SQL statement and execute it directly on the target data database.
2. Select the None option for Row Processing and execute the extraction.

Once the table is created, it is up to you to change the table definition, e.g., by creating corresponding key fields and indexes or additional fields.

Row Processing

Defines how the data is inserted into the target table.

Finalization

Defines the action on the target database after the data has been successfully inserted into the target table.

Debugging

By activating the checkbox Disable bulk operations, the default bulk insert is deactivated when writing to a database.

This option enables detailed error analysis, if certain data rows cannot be persisted on the database. Possible causes for the incorrect behavior are incorrect values with regard to the stored data type.

Debugging needs to be deactivated again, after the successful error analysis, otherwise the performance of the database write processes remains low.

Transaction style

RowProcessingOnly

Only Row Processing is executed in a transaction. Preparation and Finalization without an explicit transaction (implicit commits).

• Advantage: DDL in 'Preparation and Finalization* for DBMS that do not allow DDL in explicit transactions (e.g. AzureDWH)
  
• Disadvantage: no rollback of Preparation/Finalization.

No Transaction

No explicit transactions.

• Advantage: no transaction management required by DBMS (locking, DB transaction log, etc.). This means no locking and possible performance advantages.
  
• Disadvantage: no rollback

Merge Data

The following example depicts the update of the existing data records in a database by running an extraction to merge data. In this case, merging means changing a value of a field or inserting a new data row or updating an existing record in SAP.

With a merge command, the updated value is written to the destination database table. The merge command ensures delta processing: new records are inserted into the database and / or existing records are updated.

Prerequisites

You need a table with existing SAP data, in which new data can be merged.
Ideally, the table with existing data is created in the initial load with the corresponding Preparation option and filled with data with the Row Processing option Insert.

After the table is created, open SAP and change a field value in the SAP table that is used for the data merge.

Merge Command

The merge process is performed using a staging table and takes place in three steps:

1. A temporary table is created.
2. The data is inserted in the temporary table.
3. The temporary table is merged with the target table and then the temporary table is deleted.

Follow the steps below to set up the merge process in Xtract Universal:

1. In the main window of the Xtract Universal Designer, select an extraction from the list of extractions.
2. Click [ Destination]. The window "Destination Settings" opens.
3. Make sure to assign Microsoft Azure Synapse Analytics destination to the extraction.
4. Apply the following destination settings:
   
5. Click [OK] and run the extraction.

More information about the updated fields can be found in the SQL statement.
It is possible to edit the SQL statement if necessary, e.g., to exclude certain columns from the update.
Fields that do not appear in the SQL statement are not affected by changes.

Custom SQL Statements

The Microsoft Azure Synapse Analytics destination supports the use of custom SQl statements in the staging steps of the database. Follow the steps below to create custom SQL statements in the Microsoft Azure Synapse Analytics destination:

1. In the main window of the Xtract Universal Designer, select an extraction from the list of extractions..
2. Click [ Destination], the window "Destination Settings" opens.
3. Make sure to assign the Microsoft Azure Synapse Analytics destination to the extraction.

4. Select the option Custom SQL from the drop-down list in one of the following sections:

   • Preparation
   • Row Processing
   • Finalization

   

5. Click [Edit SQL] . The window "Edit SQL" opens.

6. Enter your custom SQL statement and click [OK] to confirm your input.

Use Templates

Existing SQL commands can be used as templates.
You can write your user-defined SQL expressions and adapt the loading of the data to your needs. You can additionally execute stored procedures that exist in the database. To do so, use the SQL templates provided in the following staging steps:

• Preparation, e.g., in Drop & Create or Create if Not Exists
• Row Processing, e.g., in Insert or Merge
• Finalization

Follow the steps below to generate a Custom SQL command from a template:

1. In one of the staging steps, select the Custom SQL option from the drop-down list .
2. Click [Edit SQL] . The dialogue "Edit SQL" opens.
   
3. Navigate to the drop-down menu and select an existing command .
4. Click [Generate Statement]. A new statement is generated.
   
5. Click [Copy] to copy the statement to the clipboard.
6. Click [OK] to confirm your input.

Check out the Microsoft SQL Server example for details on predefined expressions.

Use Script Expressions

You can use script expressions for Custom SQL commands. The following Xtract Universal specific custom script expressions are supported:

#{Extraction.ExtractionName}#

#{Extraction.TableName }#

#{Extraction.RowsCount }#

#{Extraction.RunState}#

#{(int)Extraction.RunState}#

#{Extraction.Timestamp}#

For more information, see Script Expressions.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

#{
   iif
   (
      ExistsTable("MAKT"),
      "TRUNCATE TABLE \"MAKT\";",
      "
         CREATE TABLE \"MAKT\"(
            \"MATNR\" VARCHAR(18),
            \"SPRAS\" VARCHAR(2),
            \"MAKTX\" VARCHAR(40));
      "
   )
}#

Create a Status Overview

The table "ExtractionStatistics" provides an overview and status of the executed Xtract Universal extractions. To create the "ExtractionStatistics" table, create an SQL table according to the following example:

1
2
3
4
5
6
7

CREATE TABLE [dbo].[ExtractionStatistics](
    [TableName] [nchar](50) NULL,
    [RowsCount] [int] NULL,
    [Timestamp] [nchar](50) NULL,
    [RunState] [nchar](50) NULL
) ON [PRIMARY]
GO

The ExtractionStatistics table is filled in the Finalization process step, using the following SQL statement:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

INSERT INTO [ExtractionStatistics]
(
     [TableName], 
     [RowsCount], 
     [Timestamp],
     [RunState]
)
VALUES
(
     '#{Extraction.TableName}#', 
     '#{Extraction.RowsCount}#', 
     '#{Extraction.Timestamp}#',
     '#{Extraction.RunState}#'
);

Related Links

• Microsoft Documentation: Microsoft Azure Synapse Analytics
• Integration via Azure Data Factory