COURSE OVERVIEW

Training Module:

ODX

The Operational Data Exchange facilitates the configuration of data sources and transfer of data into the ODX Storage

What is the

operational Data eXchange?

1

Data Source Configuration

Opening the ODX Instance in TimeXtender Desktop allows you to configure your data sources like selecting tables & fields for ingestion, identifying primary keys, configuring Incremental load, & scheduling recurring transfers etc.

2

Data transfer

The ODX Server service is remotely controlled from the desktop client to facilitate the transfer of data from the source to the ODX Storage using one of many ADO.net providers or Azure Data Factory. Incremental load ensures only new and updated rows are loaded.

3

raw data Storage

You can configure either an Azure Data Lake or SQL Database for the initial storage of raw data from the source. The ODX automatically handles changes in the source schema by creating a new version of the target table to match, keeping the old version by default (just in case).

TIMEXTENDER

DESKTOP

1

2

ODX

SERVER

3

ODX

STORAGE

Configure A DATA Source

Select one of the Data Source Connections you created in the portal to use in TimeXtender Desktop.

1. Right click Data Sources > Add Data Source
   
2. Enter Name: Adventure Works
3. Enter Short Name: AW
   
4. Click next
5. Select the data source connection Adventure Works
6. Click next
7. Check the option for All Tables in the Data Source
8. Click next
9. Select No, I'll add a task later
10. Click Finish
    

The Short Name cannot be changed after it is saved.

Select Primary Keys

A Primary Key is a field or set of fields which uniquely identifies each record in a table. In TimeXtender, a primary key must be identified on the tables you wish to enable incremental load. TimeXtender will automatically identify primary keys during Synchronize task execution on supported data sources.

1. Right click on a Data Source > Set Up Primary Keys
2. Select a table without a Primary Key
   
3. Click Override
4. Identify and Select the Primary Key
5. Click OK
6. Repeat as necessary
   

You can also identify primary keys on many tables at once by adding your own rules.

Query Tool

The query tool allows you to query the data source directly, before transferring any data. This is an incredibly useful tool during the initial data discovery.

1. Right click on the Data Source > Query Tool
2. Identify and drag your Table into the Query window
3. Click Execute
4. Review results
   

Validate Primary Key

1. Execute the following Query in the Query Tool:
   

SELECT

COUNT(*) AS [RowCount],

COUNT(DISTINCT [BusinessEntityID]) AS [DistinctBizEntityID]

FROM [HumanResources].[vEmployee]

This Query determines the number of rows in the Table and compares it with the number of distinct Primary Key Values

What is

Incremental Load?

Incremental load saves significant time and resources by only loading the new and changed source records.

To support this, TimeXtender requires a reliable Primary key and a Incremental value column. The Incremental value column is a field on the source table that contains information, often a date, indicating when that row was created or changed,

1

INITIAL FULL LOAD

The first execution after Incremental load is configured loads all available rows in the data source.

2

IDENTIFY MAX INCREMENTAL VALUE

TimeXtender will then find the largest incremental column value in the source and store this for the next step.

3

incremental load

Subsequent loads will only transfer the rows where the value in the incremental value column is greater than the previous max value.

configure Incremental Load

By leaving the schema and table selections to <All> the rule will apply to all Schemas and Tables.

1. Right click the Data Source > Set Up Incremental Load
2. Click Add
3. Column > Equals (ignore case)
4. Enter: ModifiedDate
5. Check box: Handle Primary Key Updates
6. Click OK
7. Click Refresh
8. Click OK
   

Handle Primary Key Updates ensures that records that match a previously loaded primary key will be updated in the Data Warehouse Instance

Add a Transfer Task

Transfer tasks load source data into the ODX Storage.

Configure as many transfer tasks as you need, each with different table selections.

Transfer tasks may be executed on-demand, in the right-click menu, or as part of a scheduled Job.

1. Right click the data source > Add Transfer Task
   
2. Enter Name: Transfer All
   
3. Click next
4. Choose the option: Let me Select the Tables
   
5. Click next
6. Click Add All
7. Click Finish
8. Right click transfer task Transfer All > Execute
   

View Execution Logs

The Execution Log contains details about previously run executions, a vital resource when troubleshooting execution errors.

1. Right click the Transfer Task > View Execution Log
2. Note the Status, Start Time, and End Time
   

You can also see execution logs for all tasks in the application menu:

Reports > ODX Execution Log

Override Data Types

When loading data from one data source to a storage of a different type, it's common to encounter incompatible data types. You'll typically see this appear as an error in the execution log. With the Data Type Override feature you can quickly convert data types across an entire data source.

1. Right click the Data Source > Override Data Types
2. Click Add
3. Convert From
   
1. Data Type > Contains (ignore case)
   
2. In the textbox enter: Money
   
4. Convert To
   
1. Data Type > Float
   
2. Fractional Digits enter: 53
   
3. Click OK
   

Synchronize Task

When executing the Synchronize Task TimeXtender reads the entire schema of the data source, identifying new or changed tables, fields, or data types.

When a source table's schema changes after it's been loaded into storage, this is known as a schema drift and can cause significant challenges when hand-coding. Luckily, the ODX handles this gracefully by automatically creating a new "version" of the source table in the ODX storage and loading the data into this new table. The old version is kept by default but can be archive or deleted later using the Storage Management Task.

1. Right click the Synchronize task > Execute
   

Data Source Explorer

The Data Source explorer is another vital resource that enables you to explore your data source tables & fields as well as examine your data source configuration and verify all your rules have been applied properly.

1. Right click the Data Source > Data Source Explorer
2. Check the box: Included in transfer task
3. Check the box: Incremental load rule applied
4. Check the box: Data type override
5. Click Search
   

exercise add a Transfer Task

Use what you've learned so far to complete the following exercise.

1. Create a new Transfer task that only includes tables from the Sales schema.
2. Execute the Task
3. View the results in the Execution Log
   

Section Quiz...

Planning to take the Solution Architect exam? Then you want to be sure these questions are easy to answer.

What are the 3 main roles of the ODX?

What 2 fields are required to support Incremental Load?

What does the Synchronize Task do?

When you're ready, see Answers Bellow

Section Quiz Answers

Planning to take the Solution Architect exam? Then you want to be sure these questions are easy to answer.

What are the 3 main roles of the ODX?

1. Data Source Configuration
   
2. Data Transfer
3. Raw Data Storage
   

What 2 fields are required to support Incremental Load?

1. Primary Key
   
2. Incremental Value Column
   

What does the Synchronize Task do?

It reads the schema of the data source, identifying new or changed tables, fields, or data types.

want to Learn even more?

Learn even more data loading techniques from TimeXtender Tuesdays

Congratulations! You've completed the training module

ODX

← Course Overview

Next Module →

give FEEDBACK