Key Points: Incremental Load in ODX Server

• ODX Server can load data incrementally to get the latest data quickly and with minimal load on the data source.
• Since the ODX is built for handling sources with a lot of tables, you do not pick individual tables to load incrementally.
• Defining rules how and when to apply incremental loads enables ODX data source incremental loading.
• Each rule consists of a set of conditions and an action. For each field, the first set of conditions that matches is applied.

Key Steps: Incremental Loading with ODX

Add Incremental load on a data source

1. Open Set Up Incremental Load... on a data source in the ODX Server
2. Select Add... in the Incremental Load dialogue box
3. Choose the Incremental Value Column and Additional Actions options for the Add Incremental Load Setup Rule
4. You may preview or refresh the Rules will be applied on these tables

For more detailed instructions, please see Incremental Loading.

Understanding Incremental Loading in ODX

The ODX Server can load data incrementally to get the latest data quickly and with minimal load on the data source.

Since the ODX is built for handling sources with a lot of tables, you do not pick individual tables to load incrementally. Instead, you define the rules for when incremental load should or should not be applied. Each rule consists of a set of conditions and an action. When a condition matches a field, the action is applied to the table. For each field, the first set of conditions that matches is applied.

Incremental Load Setup Rule options include:

• Incremental Value Column select the Schema, Table, and Column to apply the rule to. Operators for each of the three selections include:
  
  • Contains, Contains (ignore case), Not Contains, Not Contains (ignore case)
  • Equals, Equals (ignore case), Not Equals, Not Equals(ignore case)
• Additional Actions have two options:
  • Handle primary key updates will update existing records in the storage that have changed
  • Handle primary key deletes will remove records deleted in the source from the storage

Incremental Loading is the process of loading ONLY the new or modified records during an execution. This configuration is typically preferable on large transaction tables. Say for example a grocery store that logs thousands of transactions each day in a Sales table. Over time, this table would become very large, storing millions or billions of records. This considerable amount of data could take hours to load for just this single table. If Incremental Load is enabled on the transaction tables in each database, it reduces the total number of records that need to be loaded, and therefore drastically reduces the total time it takes to refresh an organization's analytical infrastructure.

While this configuration may be preferable for transaction tables, it may not be necessary or even desired for dimension tables. Using our grocery store example, while they may have thousands of updated transaction each day, their list of items on the shelves changes at a much slower pace. They may have 1-2 new items each day, which makes for significantly smaller tables without the need to incrementally load.

You should consider enabling incremental loading if your scheduled executions run longer than your execution strategy allows. For instance, if you load data during the night when business is closed to have fresh data in the morning, the load obviously needs to be completed before the users begin their day. If you want to transfer data from sources that are in use, you might also like to use incremental loading to minimize the impact on performance data transfer can have on users.

For more detailed information, please see Incremental Loading.