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.
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.
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.
Validate Primary Key
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.
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.
View Execution Logs
The Execution Log contains details about previously run executions, a vital resource when troubleshooting execution errors.
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.
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.
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.
exercise add a Transfer Task
Use what you've learned so far to complete the following exercise.
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?
What 2 fields are required to support Incremental Load?
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