Microsoft Fabric (OneLake)

This page shows how to set up and use the Microsoft Fabric (OneLake) destination. The Microsoft Fabric (OneLake) destination enables users to load SAP data to a Microsoft Fabric Lakehouse.

Requirements

The Microsoft Fabric (OneLake) destination uses Microsoft Entra ID (formerly Azure Active Directory) with OAuth 2.0 for authentication. Make sure the authentication uses the following settings:

• Register Microsoft Fabric OneLake as a Mobile and desktop application, see Microsoft Documentation: Configure platform settings:
  

• Add the following API permissions:

  • Azure Storage - user impersonation
  • Microsoft Graph - User.Read

  For more information, see Microsoft Documentation: Add permissions to your web API.
  

Create a new Microsoft Fabric (OneLake) Destination

Follow the steps below to add a new Microsoft Fabric (OneLake) 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 Fabric (OneLake) 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.

Authentication

The Microsoft Fabric (OneLake) destination uses Microsoft Entra ID for authentication. Register Microsoft OneLake as an application in Entra ID and configure OneLake to use the registered application.

Tenant ID

Enter the Directory (tenant) ID of the registered app.

Client ID

Enter the Application (client) ID of the registered app.

Authenticate using Entra ID

1. Click [Authenticate using Entra ID]. The window "Entra ID" opens.
2. When prompted, sign in with your Microsoft credentials.
3. After the sign in, a list of requested permissions is displayed, see Knowledge Base Article: Authentication via Microsoft Entra ID.
   
4. Click [Accept] to establish a connection to the storage account.

If the connection is successful, a "Connection successful" info window opens.

Files Folder

URL

Enter the URL of the Lakehouse, including the folder path in which the data is written. The URL uses the following format:
https://onelake.dfs.fabric.microsoft.com/<workspaceGUID>/<itemGUID>/<folder>/

You can copy the URL in the Microsoft Fabric portal using the properties of a OneLake folder:

https://onelake.dfs.fabric.microsoft.com/12345678-aaaa-bbbb-cccc-123456789abc/12345678-dddd-ffff-gggg-123456789abc/folder-name

File Format

Select the required file format. You can choose between CSV, JSON and Parquet.

CVS Settings

The settings for file type CSV correspond to the settings of the Flat File CSV destination:

• CSV Settings
• Convert / Encoding

JSON Settings

To use the JSON file format, no further settings are necessary.

Parquet Settings

The settings for file type Parquet correspond to the settings of the Flat File Parquet destination - Compatibility Mode.

Connection Retry and Rollback

Connection retry is a built-in function of the Microsoft Fabric (OneLake) destination. The retry function is activated by default.

Connection retry is a functionality that prevents extractions from failing in case of transient connection interruptions to Microsoft Fabric (OneLake). Xtract Universal follows an exponential retry strategy. The selected exponential strategy results in seven retry attempts and an overall timespan of 140 seconds. If a connection is not established during the timespan of 140 seconds, the extraction fails.

The retry function is applied after receiving one of the following HTTP errors or exceptions:

• 503 Service Unavailable
• 504 Gateway Timeout
• WebExceptionStatus.ConnectionClosed
• WebExceptionStatus.ConnectFailure
• WebExceptionStatus.Timeout
• WebExceptionStatus.RequestCanceled
• WebExceptionStatus.SendFailure
• WebExceptionStatus.NameResolutionFailure

Rollback applies to situations where extractions fail, not because of connection issues, but due to an error when connecting to SAP. In these cases, Xtract Universal attempts to delete any files created in the Lakehouse during the extraction process.

Assign the Microsoft Fabric (OneLake) 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.FullyQualifiedExtractionName}#

#{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.

Empty Files

When this option is active, empty result sets create an empty file in the target environment. Deactivate this option to not create empty files.

Related Links

• Mirosoft Tutorial: Create a Fabric workspace
• Mirosoft Tutorial: Create a lakehouse, ingest sample data, and build a report