COURSE OVERVIEW

Hexagons Background

Training Module:

ODX

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

Geometrical Illustration

Table of ContentS

  • Key Concepts: ODX
    • What is Operational Data Exchange?
    • ODX Retains Your Data Versions
  • ODX: Connect & Explore Data
    • Add a Data Source to Desktop
    • Data Source Explorer
    • Query Tool
    • Select Primary Keys
    • Override Data Types
    • Synchronize Objects (ODX)
  • ODX Tasks
    • Orchestrate the ODX with Tasks
    • Add a Transfer Task
    • Execute a Synchronize Task
    • View Execution Logs
    • Ingest Data On-Demand
  • ODX Incremental Load
    • What is Incremental Load?
    • Configure Incremental Load
    • “Subtract from Value” Incremental Load
    • Filter Rows
    • Manage Query Tables


Hexagons Background

Key Concepts: ODX

What is the

Operational Data Exchange?

The Operational Data Exchange (ODX) is the primary way to add raw data to your data solution.

Data Source Configuration

1

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

ODX retains your data Versions

Unless you setup a task to remove older versions of the data, then every load is kept in your Data Lake or SQL storage. This is why we say, “The ODX never forgets!”

Elephant

When new information feeds into ODX from various sources, it tucks each load gently away in long-term storage, ideally in a data lake. This means if users ever need to revisit past data, it remains nestled safely where ODX left it. Unless removal of older loads is specifically requested.

To free up some storage space by removing previous data versions, the User must configure a Storage Management Task, please check out Tasks in ODX Instances.

Hexagons Background

ODX: Connect & Explore Data

Add A DATA source To Desktop

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
Circle Pin Icon
White Circle Vector
Cute Organic Light Bulb

The Short Name cannot be changed after it is saved.

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

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]

Circle Pin Icon
White Circle Vector
Cute Organic Light Bulb

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

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.

Circle Pin Icon
White Circle Vector
Cute Organic Light Bulb

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 Objects (ODX)

Synchronize objects in the ODX identifies Data Warehouse instances that are impacted by structural changes in the ODX. Please check out Synchronize Objects for more information.

  1. Right-click the ODX Instance, and select Synchronize Objects
  2. TimeXtender will identify columns affected by any changes in metadata
  3. Fix "broken mappings" in Fields column, as needed.
  4. Click OK
Hexagons Background

ODX Tasks

Orchestrate the ODX Using Tasks

There are three (3) types of tasks in the ODX Server, to orchestrate and automate your raw data ingestion and storage. Of course, all of these tasks can be configured to the Users' specification, then scheduled and automated, which simplifies the end-to-end scheduling and reliable data refreshes.

Transfer Task

data ingestion from source

Data Transfer Icon

Synchronization Task

meta-data structure sync

Update Icon

Storage MGMT Task

data storage clean-up

Broom Icon

Please check out Tasks in ODX Instances to learn more.

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

Execute A 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 archived or deleted later using the Storage Management Task.

  1. Right-click the Synchronize task > 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
White Circle Vector
Cute Organic Light Bulb

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

Reports > ODX Execution Log

Ingest Data On-Demand

When the 'Data on Demand' option is enabled, the data source will transfer data into ODX storage before the MDW ingests the data. This will work without configuring an explicit ODX Task (i.e. Transfer Tasks, etc.).

Warnings about On-Demand:


  • On-Demand feature does NOT currently support ADF data sources.
  • On-Demand Jobs do NOT generate ODX Execution Logs, unlike ODX Transfer Tasks
White Circle Vector
Cute Organic Light Bulb

Enable Data On-Demand

  1. In TX Desktop, right-click the data source and select Edit Data Source
  2. Click on Advanced Settings
  3. Check the box Data on demand
  4. Select OK (or Apply) to close Advance Settings pane
  5. Select OK
Hexagons Background

ODX: Incremental Load

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 an 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.

Circle Pin Icon
White Circle Vector
Cute Organic Light Bulb
  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
White Circle Vector
Cute Organic Light Bulb
Circle Pin Icon

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

"Subtract from Value" Incremental Load

  1. Right-click the Data Source > Set Up Incremental Load
  2. Click Add
  3. Column > Equals (ignore case)
  4. In Subtract from value, enter a value to decrement from on each Incremental Load.
    1. Amount decreases a value.
    2. Time subtracts from the last DateTime (in seconds).
  5. Complete the setup, and click OK.

Filter Rows

  1. Right-click the data source and click Filter Rows… then, click Add... to add a new rule.
  2. In Schema/Table list, click on the operator you want to use and enter the value in the box.
  3. Click Add to add a new column and value combination.
  4. In the Column list, click on the operator to use and type the text to match in the box. In the Value list, click on the comparison operator to use and enter a value in the box.
  5. Click OK
  6. Click Refresh

Preview the Rows Affected by Filter Rule

Circle Pin Icon
White Circle Vector
Cute Organic Light Bulb

(optional) In Filter Rows window, click Refresh to see a list of affected tables. Click Preview to open the query tool with a preset query to see what rows will be transferred from a specific table.

Manage Query Tables

  1. Right-click the data source and click Manage Query Tables. Click Add
  2. Enter a Name.
    1. (Optional) In the Schema box, type the Schema name you want the table to use.
  3. In Query, type your SQL query to use for creating the table.
    1. (optional) Click Validate Query to check whether the query is valid for creating a query table.
  4. Click OK.
  5. Right-click the data source and click Select Tables, and ensure that the new query table is selected.
  6. Execute the synchronization task for the data source.
  7. Add the query table to a transfer task and then select Execute.
Circle Pin Icon
White Circle Vector
Cute Organic Light Bulb

Warning: Row filter rules are not applied on query tables.


Please check out Configure Data Sources for more information.

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?

Brushstroke Arrow Smooth Curve Down Small

When you're ready, see Answers Below

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

give FEEDBACK

Thumbs Up Illustration
Thumbs Up Illustration